Author: XLCubed

Some Excel BI myths debunked

XLCubed/ / Data

“Excel: Great hammer, lousy screwdriver’”

When evaluating BI tools, many of our customers are hit by marketing messages about the limitations and woes of Excel. One white paper we were pointed to is Tableau’s ‘Excel: Great hammer, lousy screwdriver’. It contains 5 key points concerning Excel limitations for BI which we’ll take a look at over the next few weeks, along with a few others which we hear frequently.

“Don’t throw the baby out with the bathwater”

We fully appreciate that Excel isn’t perfect for all needs, but XLCubed chooses to address the weaknesses and embrace the very significant strengths, rather than throwing everything away.

XLCubed helps users get most value and benefit from Microsoft’s Analysis Services platform by taking the best of Excel, and extending that with an optimised query and reporting environment which lets them do more, and do it more quickly. Excel becomes a very flexible presentation layer, and Analysis Services removes the scalability and data integrity issues.

Let’s take a look at some of the key Excel objections raised, with #2 to follow next week:

#1) Limited data volumes

“Excel only handles 1 million rows – that’s not nearly enough for my business”

The advent of Big data makes everyone think of huge data volumes. In reality if you’re looking at core Financial reporting a million rows may well be more than enough but that’s not the point: for sales and operational reporting over several years a million rows won’t come close.  Big Data is partly around volumes, but also concerns the data structure. These days the challenge of big data isn’t the ability to store it, it’s the ability to do something useful with it.  And doing something useful with it isn’t creating reports which run to a million rows.

We see Excel as a presentation layer, not as a database. While Power Pivot muddies that argument a little, very few people see Power Pivot models as a central repository for Corporate data. XLCubed is a client front end tool for SQL Server Analysis Services (which  laughs at 1 million rows). 1 million new rows per day over several years is starting to ramp up the volume, but the technology is designed to scale, and to scale on significantly less expensive hardware than in-memory technologies (of which Analysis Services 2012 of course now has its own player with xVelocity).

So while Excel and hence XLCubed can only display 1 million rows at a time, the underlying cubes can run to billions of rows. XLCubed gives the user flexible and fast filtering and ranking capabilities, simple ways to leverage the cube hierarchies, and effective data visualisation techniques to let you work with these large volumes of data.

Aside from that, if someone wants a report (a report!) which is a million rows long, our first question is always ‘and can you show me how you use that report?’.  If you print it you’ll get around 25,000 pages of deforestation. By comparison Tolstoy’s War and Peace is  around 1,400 pages in most print editions… We believe there is a lot to be said for a combination of top-down reporting, and ranking and filtering to make that type of data volume useful rather than burdensome.

So in summary, when you’re using XLCubed and the Microsoft BI stack, more than a million rows of data is really not a limitation (though if you put a million rows in the report you’re creating your own limitation in terms of its usefulness).

Current and Previous month reporting made easy

XLCubed/ / Reporting & Analytics

We’ve all been there. Our shiny new dashboard or report pack is finished and ready to go meet its users.  We’ve presented the key information clearly, we’ve followed all the data vis guidelines on effective charting and use of colour. We like it a lot, and we’ve thought ahead and built in lots of flexibility with slicers and managed drill paths so it can already help answer some of the questions it will doubtless raise.

It’s a little disappointing then that one of the first pieces of feedback is that the senior execs don’t actually want to use the interactivity much. They want to open the dashboard and see the current month picture (or previous month), and don’t want to waste valuable seconds selecting the month in a slicer…

Joking aside, in lots of situations it’s a sensible request, and there are various different ways to handle it in an XLCubed / Analysis Services world. Often this will be for a multi-sheet report incorporating grids, formulae, and charting elements and we need a centralised point to handle the month selection – enter the XL3MemberNavigate() formula.

MemberNav

This lets you pick a hierarchy and a level, and you can specify that you want the Last member (first and previous / next are also available). It’s available in the XLCubed Insert Formula dialog. In our case we’d pick the date hierarchy, the month level and choose Last, generating a formula as below:

=XL3MemberNavigate(1,”[Date].[Calendar]”,”[Date].[Calendar].[Month]”,”LastMember”)

The issue is that at this point it has no concept of data, it will give you the last month available in the hierarchy, not the last month with data. However that’s just another parameter away, we can add dimension member pairs to force a data check, as below, where we are checking that data exists for the “Reseller Sales Amount” measure.

=XL3MemberNavigate(1,”[Date].[Calendar]”,”[Date].[Calendar].[Month]”,”LastMember”,0,”[Measures]”,”Reseller Sales Amount”)

So that will return the last month with data, and as we know in XLCubed all grids, formulae and XLCubed charts can be based off a cell. The Xl3MemberNavigate() cell becomes the driver for all time selections in the report. Job done. Or is it? What if you actually wanted the last complete month? :

=XL3MemberNavigate(1,”[Date].[Calendar]”,”[Date].[Calendar].[Month]”,”LastMember”,2,“[Measures]”,”Reseller Sales Amount”)

Adding the addional ‘2’ parameter means it will go back an additional month, hence giving you the last completed month.

In our experience this is far and away the easiest way to handle current or Previous month reporting, and we hope you find it useful if it’s new to you. For more information on XL3MemberNavigate check our wiki.

 

Rolling Forecast reporting in Excel

XLCubed/ / Reporting & Analytics

In monthly reporting for the current year the most pertinent numbers for closed periods are the actuals, and for open or future months the forecast. In most cubes actual and forecast (or budget) are implemented as either different measures, or as part of a scenario hierarchy. This can lead to challenges in reporting, where putting both the period and the actual/forecast on the same axis means you will get duplicated months which show the forecast for periods when the actuals are already in as in the PivotTable below.

 

 RP10

We would want the report to be asymmetric (i.e. different months returned for Actual and Budget). Various tools can handle that, and it’s always been possible to achieve that in XLCubed through formulae or in a grid. What’s more difficult is to make the report fully dynamic without the need to redesign it each time. Ideally we’d want the user to be able to change a slicer to select the last completed month (or pick this up directly from the cube), and have the appropriate months reported under Actual and Budget.  Here’s how to achieve it in one grid…..

The approach uses a combination of:

  • Excel Ranges to determine the available months, and which are for Actual and Budget
  • An XLCubed Slicer to pick the ‘Last Actuals’
  • One XLCubed grid with the ‘Exclude from display’ pointing to the relevant ranges above

Let’s work through this example which uses the Finance cube on the AdventureWorks DW 2008R2 database.

We’re going to use data from FY 2006 and report Actual and Budget figures across departments.  We will use a slicer to select the last Actual month we want reported.  For the remaining FY 2006 months we will report Budget values.

Entire year of Months in the report

There are several ways to achieve this – we added an XLCubed Excel driven slicer (XLCubed – Slicer – Excel) based on the range $AA$1:$AB$12 as shown below, containing all months in FY 2006. This lets the user pick the last month for which we want to report Actuals.

 

RP1

RP3

 

 

 

 

 

 

 

 

 

The slicer is set to output its selection into $B$1.  In $C$1 the formula =VALUE(B1) converts the text output of the slicer into a number which we’ll use to calculate which months should report Actual and which Budget, see the screenshot below:

RP2

 

 

 

 

Date Table

This is an Excel range supporting the reporting logic. Row 2 contains every month in the reporting year. Row 3 assigns an incremental number to each month, 1-12.

We can then compare the value of the selected month from the slicer ($C$1) with the value for each month. Basically if the month is less than or equal to the selected last actuals month it should be reported as Actuals, otherwise as Budget.  An example formula contained in B4 for Actuals would be: =IF(B3<=$C$1,B2,””) . For the Budget row the formula has the inverse logic.

At this stage we’ve used a little XLCubed, and the flexibility of Excel to match the appropriate months to Actual and Budget. We now need to add the report itself.

We’ll create our grid – initially we set it up to report Budget and Actual figures across the year, by basing the selection for Date.Fiscal on the range containing all months for the year ( $B$2:$M$2).

We then use XLCubed’s right-click menu (Keep – Exclude From Display) to exclude members.  It doesn’t matter which slices are excluded at this point. To do this highlight ‘Actual’, followed by the first available month and then choose Exclude From Display. Repeat this for ‘Budget’ and the first available month.  We will exclude July 2005 for Actuals and August 2005 for Budget.  You will notice the red comment marker on the grid. Right-click on this cell and choose Axis – Edit, and go to the Excluded Slices tab.

RP6 RP7

 

 

 

 

 

 

 

 

The above screenshots show that we are currently excluding July 2005 from Actual as well as August 2005 from Budget.

We can then easily change the Date Fiscal selection for each scenario.  For Budget we will exclude Budget values for months in cell locations $B$4:$M$4 (as we want to report Actuals for those entries) and Actuals values for the months in cell locations $B$5:$M$5 (as we want to report Budgets for those entries).

RP8

The grid report now looks like this with Actuals being reported up to November 2005 and Budgeted for the remainder of FY 2006:

RP5

We have also set Merge Repeating Cells on (in Grid Properties).  You could do further formatting – for example by colour filling all Budget values and hiding the grid headers.

RP9

The end result is a one grid report giving the Actual:Budget month mix that we want with the monthly maintenance process a case of simply changing a combo box selection.

 

 

XLCubed en Français, dans Lausanne!

XLCubed/ / Customers, Case Studies & News

XLCubed is now available in French, and we’re pleased to sponsor the Swiss BI Forum in Lausanne, Switzerland on Tuesday 12th March. We look forward to meeting with customers and attendees on the day.

The addition of French brings the supported languages in XLCubed to six in total: English, German, Italian, Spanish, Portuguese and Russian. If a localised version would help in your market, we’re happy to discuss in more detail.

Bandlines in XLCubed

XLCubed/ / Charting & Visualisation

In early January this year Stephen Few introduced the concept of Bandlines. He identified a useful extension to Sparklines, making use of shaded or coloured horizontal bands to provide more information on the context of the trend line itself. See Stephen’s article on Bandlines and the thinking behind them for a detail description.

The Sparklines are ideal for showing individual trends in a small amount of screen real estate, and we use them extensively in dashboards, typically in a ‘visual table’. By definition Sparklines are small, and to make the trend easily readable, they are typically scaled individually so that each Sparkline uses the whole vertical axis. This means they do not give any impression of the scale of the numbers involved across different rows. It’s possible to use a common scale, and while sometimes that works more often it means many of the rows with smaller values are excessively flattened.

Bandlines address this by introducing horizontal shaded areas depicting the lower, middle and upper quartiles and the median represented by a line. The user can determine the context of the bands. The two most common examples would be plotting recent trend in the context of a longer period, or plotting individual rows in the context of the overall set of data being displayed.

We think Bandlines add real value, so hats off once again to Stephen, and we’re pleased to say that Bandlines are now available in the current version of XLCubed (see here for more detail).

The screenshots below show two examples, displayed in two colour schemes.

 

Bandlines3

The charts depict historic margin by store. The ‘Banding across all stores’ charts show the 30-day trend for the individual store, set in the quartile context of the data for all 11 stores in the table. We can see that for the Gilroy store in row 1, while the margin has varied, it remained in the top quartile when set against all stores for almost the whole period.

The ‘Banding by store, 90 days’ charts show the individual 30 day trend, set in the context of the previous 90 days for the individual store. This helps provide much more historical context, but the line itself still focuses on the more recent trend. Stockton is probably most noteworty here as across the 30 day period it has dropped from the top quartile into the 1st quartile across the whole 90 day period.

We’d love to hear your thoughts (and also which colour scheme works best!), we will also be adding Sparkstrips in the near future so watch this space.

 

 

 

 

Excel Pareto Charts the XLCubed way!

XLCubed/ / Charting & Visualisation

V7.2 of XLCubed is released soon and we thought we’d take the opportunity to run through one of the new features that you’ll be seeing, Pareto Charts.

The Pareto Principle is often referred to as the 80-20 rule, that 80% of outcomes are attributable to 20% of causes. They are named after Vilfredo Pareto who lived in Italy in the 19thcentury and observed that 80% of the land was owned by 20% of the people.   Pareto charts have both bar charts and a line graph where the bars represent individual values and the line represents the cumulative total.

So how do you use Pareto Charts from XLCubed?  Very simply, within a grid you right-click on the column header to access XLCubed’s right-click menu, Grid Charts and Add Pareto Analysis.

Take this simple grid showing Reseller Sales for Product Model Categories for Canadian cities:

Right-clicking on All Products to Add Pareto Analysis brings up this window:

Click OK to return to the workbook – you will see that we have a chart showing that the top 9 cities provide some 80% of the sales.

You could also include the rolling total and percentage in your Pareto Chart.

Notice that we now also have some extra columns on the grid showing the cumulative total of all sales, the sales percentage per category and the cumulative percentage.

 

 

So that’s Pareto Charts – in a nutshell, an easy to use graphical tool which ties directly into dynamic XLCubed grids.

XLCubed as an alternative to ProClarity

XLCubed/ / Data

With the launch of 7.1 of XLCubed Excel Edition we introduced the ability to import ProClarity Briefing Books – with support for ProClarity ending this year and many customers looking for a replacement, now is a great time for us to show you how the import works to help move users from ProClarity to an alternate solution.

Importing

Let’s start with importing from ProClarity, we’ve built a simple example briefing book based on the usual AdventureWorks sample cube, it includes a sample grid:

 

a performance map:

 

and a chart:

 

To get to the import option we load Excel and select XLCubed -> Extras -> Import -> Import ProClarity Briefing book. After selecting the file to import we are given a summary of each item that is going to be imported:

 

At this point you can control the resulting worksheet name, as well as switching the type of XLCubed object you’ll end up with. Clicking “Import” will now give us 1 sheet for each briefing page:

 

You’ll notice that the import process has created any required slicers so the report is good to go. You could now also spend a bit more time adding any extra XLCubed functionality to the report such as Incell charts or Excel calculations to leverage the power of Excel or publish to XLCubedWeb for consumption by a wider audience.

The import process is very straight forward and we have some great feedback from our customers regarding the speed and ease that they have been able to migrate users’ reports into XLCubed.

Look out for some more blogs showing other features of XLCubed that will help users transition from ProClarity!

Creating rounded corners in Excel – revisited

XLCubed/ / Grid & Report Formatting

Today we’re revisiting one of our more popular guides, Creating rounded corners in Excel Tables, and have updated it for v7.1. When Igor Asselbergs was contemplating the value of round corners in design, he came to the conclusion that in many cases they added real value to the user experience.

The effect can be explained by the Gestalt Law of Continuity. Gestalt is a set of rules based on research into perception psychology, and a very powerful tool for Excel table design. In table design this effect can help us to see the table columns as a unit.

The previous process to create rounded corners in Excel tables required quite a bit of persistence and patience. In Version 7.1, we’ve introduced a feature to enable adding rounded corners in a few seconds rather than several minutes, so while the theory is identical the implementation is much improved. Take this report showing sales KPIs, where we would like to add rounded corners to the header row in the table.

To do this we first highlight the required area:


Then we go to Extras -> Add/Edit Round Corners:


The Colours and Border thickness will be picked up from the selected cells. Select the corners to be made round (in this case the Top Left and Top Right corners):


Click OK to apply the borders

 

To edit existing corners which were created by XLCubed then you can just highlight the cell or range and Go to Extras -> Add/Edit Round Corners. The changes will be applied to the existing corners (or the corners can be removed by unselecting them).

It’s a simple addition to the product which would have saved us quite a bit of time in customer implementations over the years, and hopefully now does the same for our users.

Mind the gap!

XLCubed/ / Grid & Report Formatting, Reporting & Analytics

Today’s blog is going to show you how to use XLCubed’s custom calculation functionality to create column breaks in a grid.  Imagine that you have a report that shows you Reseller Sales across Product Model Categories over a 12-month time period.

 

 

 

There’s nothing wrong with this report but don’t you think it would be nicer if there was a way to separate out each quarter block ie put in a divider column between March and April, June and July, September and October.  That would make it much easier to read and show clearly where each quarter period started and ended.

So let’s start by creating a custom calculation.  Click the highlighted icon and give your custom calculation a name – let’s call it ColBreak.  It’s connected to the Date.Calendar hierarchy.

 

Now in the Expression area enter  a blank string starting and ending with ” (double-quote).  Click OK.

To insert this into our report we now go to the Hierarchy Editor for Calendar Date – expand the All member and you will see ColBreak.

Drag this across and insert it into the report.  We will insert it after March, June and September and click OK.

 

 

The report now looks like this:

 

Now let’s format this column break so that the we don’t see ColBreak appearing as a column heading.   You need to right-click to get XLCubed’s right-click menu and then choose Format This Member.

We will choose white for the Font colour before clicking OK.

The report now looks like this with clear demarcations between each quarter:

 

 

 

Olympics Treemap

XLCubed/ / Charting & Visualisation

The 2012 London Olympics have now finished, and as a UK company we were pleased to see the games were such a success, and of course that team GB did so spectacularly well! We’re looking forward now to the Paralympics in a couple of weeks, and once the dust has settled there we’ll be shipping a new point release of XLCubed in September.

We’ll keep most of the changes under wraps for now, but one item which we are introducing is treemaps. The Olympic medal table gives us a nice opportunity to better understand the medal breakdown through the  new chart type. In XLCubed, treemaps can be produced directly from a cube or from a table held in Excel, as is the case here. The first example below shows the medals split by country and sport. The size of the rectangle depicts the total number of medals, and the colour shows the number of gold medals, the darker the colour the more gold. The numeric values list the total number of medals, then the number of golds. We can see the USA at the top, and that over half their medals came from swimming and athletics, with a bigger percentage of golds in the pool.

Any of the countries can be drilled into for a large view on their medal breakdown, not that we’re partisan of course… , but the view below is for Great Britain (GBR) where the particularly good showing by the cycling team stands out.

Taking a look at the same data split first by sport and then country, it’s easy to see the countries dominating the medals in each sport, and to delve into more detail by sport where required.


 Drilling into Athletics we can see that USA won most medals, and also most gold. Great Britain had just the 6 medals, but 4 were gold and hence the darker colour on their tile.

We’ll be making an interactive version of this available over the next few days.