Microsoft OLAP

Microsoft OLAP technology is part of their Analysis Services offering and also their Excel and Powerpivot front-end pieces. Their OLAP systems were originally licensed from Panorama software back in 1996. It was a bit of a strange transaction since they did not buy the company outright. In fact, Panorama is still in business as a Microsoft partner.Since then, they have refined it through several releases of SQL 7, 2000, 2005, and 2008. This could be considered the “back-end” engine and cube building infrastructure. On the front end is the familiar Excel, and recently Powerpivot.


For heavy duty analysis, most organization would use SSAS to create cubes and then use Excel for the desktop analysis. However, for smaller sets of data Excel can be used to do OLAP querying against a datasource by itself. The most common usage is against an Access database. The built in wizard can easily allow you to setup the dimensions. Once created, users can use the Pivottable and PivotChart functionality to produce charts and graphs. This has the limitation of being able to be used only locally, and you can bump into Excel ‘s memory limits, which is 2GB for PivotTables for Excel 2007 but may be less depending on other memory usage. Excel is fine for a quick and dirty analysis but for deep OLAP bigger tools are needed.


To address some of these issues, Microsoft created PowerPivot, which is an add-in for Excel. This is a little bit like Excel on steroids, and can make working with cubes much easier. One powerful function is the ability to easily write formulas across dimensions, called DAX. You can also share models across users and departments much easier with Sharepoint integration. PowerPivot models can make the SSAS models fit into a more user friendly, Excel based world more easily.

Go from Microsoft OLAP to Home

Contact Us * About Us

Copyright 2011-2017