Javier Guillen posted on September 08, 2010 13:10
One of the things I am fascinated with is how familiar data manipulation tools like Excel can be powerful BI enablers. While traditionally spreadsheet software hasn’t been part of the corporate BI initiative (in fact, it was sometimes seen as an enemy as it generated multiple isolated spreadmarts), newer BI technologies are now embracing it, in part due to the recognition that information workers love working with it and it is the BI program that needs to adapt to the way those workers crunch and analyze data and not the other way around. Cube functions/PowerPivot/DAX and the Vertipaq engine were born under that philosophy -- the possibility of having Excel interfaces connected to the data warehouse to enable data analysts to create fast ad hoc analysis needed for strategic decision making is what is now being touted as ‘BI for the masses’.
Pivoting in Excel is as easy as drag & drop. However, I have seen many power users needing to go beyond the implicit limitations of pivot tables and create more customized reports. PowerPivot is an excellent tool that can help in most of those cases, but in its current incarnation there is no concept of dimension hierarchies. A power user or BI developer can still leverage the corporate cube dimension hierarchies to build very customized reports in Excel, and even use some basic MDX directly in the Excel functions to build reports not normally available to standard Pivot tables (unless the BI team builds that capability in the cube of course)
In my experience, building some Excel templates using this technology can enable non technical power users to leverage and extend them and from there create very custom reports in a fairly fast way and without having to delve too much in the code. For example, changing the level retrieved from an Descendants function can be a simple thing from a code perspective but can quickly empower a data analyst to satisfy a quick request, provided he has some basic conceptual understanding of what a hierarchy is –
I wrote a small article that describes the process here: http://www.msbicentral.com/Resources/Articles/tabid/88/articleType/ArticleView/articleId/103/Beyond-Excel-pivot-tables-Leveraging-cube-formulas-with-MDX.aspx