Software: Microsoft Office 2014, XLCubed Excel Edition v8
My previous blog Multi-Dimensional Data Part I: Building a Cube was a precursor to this article on Excel and XLCubed – a great add-in to Microsoft Excel for simple reporting that expands on Excel’s limited visualisations and makes slicing data from a cube simple. If you read my blog on building a simple cube you will know that I used a small sample of data to make the process quick and fairly painless; there was a reason for this. In this article I will provide some graphics using the data in the cube I built for the previous blog, and explain some pros and cons of XLCubed.
Installation and Connection
As you can see in Fig.1, there are a few components needed in order for XLCubed to be installed. Luckily for me, all of them were pre-installed when I updated to SQL Server 2014, except the add-in itself (obviously). It should go without saying that all other components should be installed in order to use XLCubed successfully.
The install is quick and easy, so you will be up and running in no time.
The first thing you need to do once you have enabled the add-in in your Excel options, is to set a connection to your cube. Click Connections then Connections on you XLCubed ribbon; this will open the Connection Manager. Click Add in the bottom left-hand corner, then choose the New Connection tab. If your cube is on your local machine, a simple period mark will suffice for the Analysis Services Server that you chose from the dropdown list. Hit Connect, and your cube should appear in the boxes below (Fig.2). Now you have a connection, you are ready to build you tables and/or visualisations.
The Basic Grid
As you can see in Fig.3, the grid designer is fairly user-friendly. Simply drag and drop the measures and dimensions you want into the corresponding sections. For example, I’ll drop my ‘Unique Visits’ into the measures, my Date hierarchy into the columns, and Geography hierarchy into the rows. Once these have been dropped into the appropriate boxes you can select exactly what you want from the hierarchy in the Members section, as seen in Fig.4.
I’m only using June, as my original data set was only a month and a half long, and it is unlikely that someone will ask for a report on that time-span. If you want a quick way to grab all levels of the hierarchy, drag Allacross and select Descendantsfrom the drop down list; the good thing about this option is that it will not return any dates for which there is no data. Below is the grid that this design creates. (Fig.5)
XLCubed is great for quickly building a grid report, as seen above, but it can do so much more based on the same data. Whether you want maps, treemaps, or simple graphs, these are all possible; please note you may have to put in a little extra work for maps as using only place names can be a volatile experience – it is best to include the longitudinal and latitudinal coordinates in manually written columns if you don’t already have them. Treemaps do not require the same amount of information, but can be just as interesting.
Building a Treemap
Treemaps in XLCubed are great for geographic information, and are surprisingly easy to create. Fig.6 is theTreemap Designer.
The basic ingredients for this are the Plot and Values.For the Values you will need at least two of your selected values; I’ve gone with measures, and selected the total number of page hits as well as the count of unique visits to our website. For the Plot, my Geo hierarchy is one of the better natural hierarchies to use, as it provides a recognisable drill-down, which I will show you shortly. By selecting All – Children from the drop-down list on the far right in Fig.6, the Treemap will show all countries with their counts. Fig.7 shows the formatted Treemap. You’ll notice the arrows in the bottom left hand corner are greyed out; this is because we are yet to drill down into the data. In the same way as you can drill down into a hierarchy on a basic grid, double clicking on an area of your Treemap will drill down to the next level – in this example, I will drill down into the United Kingdom as it has both the most page hits and unique visits.
Fig.8 shows the next level of the hierarchy – regions within the UK. One of the arrows is now blue, showing that the user has drilled down at least one level, and can keep going for as many levels as the hierarchy allows, and still be able to navigate back the highest.
So far I have found that Treemaps work best for natural hierarchies as the levels create a simple view data, compared to slightly erratic views created by unnatural ones.
Graphs and In-Cell Charts
In my list of pros and cons of XLCubed that I will provide after this section, the main con I have for it is that in-cell charts are not exactly user friendly – although, this is not exactly the worst thing to happen, as Excel itself is pretty good at creating charts and graphs from Analysis Services. The only chart type I found easy to accomplish with XLCubed, was a heat map that simply replaces the count digit in the cell with a colour block of a chosen gradient. Fig.9 is an example of this based on the same data used for the Treemap. Personally, I am still undecided if the lack of colour in the cells with a NULL value helps or hinders, but this is for you to decide; if you do replace the blanks with ‘0’s then the heat map will be filled across all cells.
Building the charts with Excel is just much simpler. PivotChart is a great way to make Excel and your data do the work, using the same measures and dimensions that you used in XLCubed. Figs 10 and 11 show a chart that took all of five seconds to create and the PivotChart options ribbon that built it for me.
The chart in Fig.10 has the count of unique visits by browser for those who spent between 4 and 8 minutes. As with the Grid design in XLCubed, the PivotChart options are a simple drag and drop, and you can format your chart in any way you wish, whether that be in company colours, or to fit your personal website.
You can create a PivotChart based on an XLCubed grid by creating just a connection if you have clicked on From Other Sources option on the Data ribbon. Fig.12 shows the option to choose in order to build Pivot charts with a connection to your cube. For this example I have selected to create a line chart. In the empty space, right click and select Select Data to bring up a blank version of Fig.13.
I created a basic XLCubed grid on a second sheet from which to select my data, as also seen in Fig.13. Once you have selected the data you want, you will be able to slice and filter however you wish. Fig.14 is one such graph that is filtered to just two countries on a simple data set showing the count of visits by people in a country, and banded into time periods they spent on our website – the formatting of the graph is just an example.
Some XLCubed Pros and Cons
- Easy install and set up due to being an add-in rather than a separate piece of software
- User-friendly connection setup and designer windows
- Efficiency of grid designer to create basic table reports
- Expansive online wiki for help with use of XLCubed
- Easy to link grids with Pivot Charts and other Excel functionality
- Can be used on various connections, not just Analysis Services
- Hierarchical drill-down is smooth and easily read by end-users
- Unable to handle/process data sets larger than Excel can handle
- Large data sets will slow down Excel and may cause it to crash
- In-Cell charts can be difficult to format
- Various options in the ribbon are poorly explained in the designer. E.g. the different slicer formats in the Analysis Services Slicer option
Just like any other add-in to Excel, or any other software, there are some issues, but I will maintain that for quick and easy basic reporting on multi-dimensional data from Analysis Services, XLCubed is a great option. Not only can it be an additional tool in the belt of a data analyst, but it can easily be taught to members of other teams that need to create quick reports on short notice (make sure that they have the correct SSAS permissions – we don’t want the cube to be broken). I recommend downloading and having a play around to see if this a piece of kit you can utilise, whether just for your own needs, or as a company-wide solution to basic reporting.