Project Botticelli

Status message

Video playback issues? See tips for supported browsers. Clearing cookies and cache often helps.

Multidimensional Analysis with Microsoft Excel and SQL Server

28 December 2011 · 7787 views

Loading the video player ...

If this message stays on, please ensure JavaScript is enabled and that you are using a supported device and browser. Ad blockers may also cause this issue.

For more information please visit our device support page.

OLAP Cubes in Excel, Slicers, and Sparklines

For most people, the easiest tool for opening and navigating a multidimensional (OLAP) cube is Microsoft Excel. In this short demo you can also see how to perform basic slice-and-dice analysis, drill into and filter using dimensions, and summarise trends with a sparkline.

There are many tools that can be used to work with OLAP cubes running inside SQL Server Analysis Services (SSAS). If you use Microsoft Office, however, it is hard to beat the familiarity of Excel coupled with its deep cube integration, including even more specialised features, such as What-If analysis (cube writebacks).

While older versions supported cubes, Excel 2010 introduced slicers, which allow for truly multidimensional analysis, as you can now add any number of dimensions to your worksheet, easily allowing for even complex filtering scenarios. For example, as shown in this video (00:59), you can first slice-and-dice sales amounts by the date of the sale and the product categories. Subsequently, you might want to use a slicer (01:59), to add the sales territory, to permit a more in-depth analysis of the sales figures, and so on, adding more slicers as you need to.

Slicers are incredibly easy to use, they literally require just three clicks to create: insert a slicer, select the dimension/attribute, and click OK. They work immediately, and they permit multi-select (Ctrl-click for that), and they allow you to build even a complex multi-hierarchical browser.

Another simple-to-use, yet quite powerful tool introduced in Excel 2010 is the sparkline. You can see it at the end of this video (03:02), when it is used to summarise the trend of sales figures over the span of time. You could also use them to show the strongest/weakest points, and there is nothing to stop you from looking at trends over other attributes, for example over stages in a sales cycle, or even over non-time-oriented dimensions.

If you are evaluating analytical tools that work with cubes, you should also consider reviewing the upcoming Power View technology, which runs in SharePoint server. We will upload videos and courses on this subject in early 2012.

On the other hand, if you would like to try these Excel demos all by yourself, you will need a running SQL Server Analysis Services with a database that contains a cube. The cube shown in this video comes from a SSAS database Adventure Works 2008 R2 DW. You can download it from here on Codeplex, make sure you download the version that matches your version of SQL Server. If you want to play with SQL Server and you do not have it, you can download a trial version from here. If you need a trial version of Office 2010 Excel you can get it from here.