Detour: Lifting the Veil on PowerPivot for Excel

I’m going to take a slight detour from the Mobile BI posts and provide you some insight into PowerPivot for Excel and what exactly makes it tick…

Above is B.I. Voyage’s first online PowerPivot demo for retail analysis…

PowerPivot for Excel Trace

PowerPivot Trace is an optional setting for diagnosing problems with PowerPivot for Excel workbooks. If you are familiar with SQL Server Profiler and Trace this is going to feel very familiar to you (a nice benefit for us SQL/BI folks). To start off, I click on the PowerPivot ribbon menu and then click Settings (as shown above). This event displays the PowerPivot Options & Diagnostics window. Next, I click on the ‘Client tracing is enabled’ checkbox and I then select the Ok button.

Immediately before doing anything else if I go and open the trace file PowerPivot for Excel created (using SQL Server Profiler 2008 R2) I see a basic trace start collection of events.

I’ll go back to the PowerPivot Options screen and click on the ‘Take snapshot’ button. Going back to the trace file we now see a collection of Server State Discover Data events.

If I then go back to our PowerPivot demo for Retail dashboard and begin clicking on slicers we see a collection of Query Cube events in the corresponding trace file.

Finally, if I right-click on any of the events listed in SQL Server Profiler and select Properties I can now see the exact SQL Server Profiler Trace Provider Type: Microsoft SQL Server 2008 R2 Analysis Services.

Manually Restoring a PowerPivot Cube

Another neat way to show that PowerPivot for Excel really is Analysis Services is by manually restoring the database that resides within a PowerPivot for Excel workbook. This exercise requires you to have access to a PowerPivot Pilot (all-in-one) server configuration. To do this I perform the following tasks:

Note This is not a Microsoft supported procedure but it is a good exercise for learning purposes

1.       Rename the workbook’s file extension to .zip

2.       Open the new.zip file and navigate to the xl/customData directory

3.       Copy and paste the file labeled “Item1.data” to your local PowerPivot for SharePoint SQL Server Analysis Services 2008 R2 instance’s backup directory. In my case this is C:Program FilesMicrosoft SQL ServerMSAS10_50.POWERPIVOTOLAPBackupSandboxes

4.       Rename the .data file’s extension to .abf (Analysis Services backup file extension)

5.       Start SQL Server 2008 R2 Management Studio and connect to the Analysis Services 2008 R2 PowerPivot for SharePoint Instance

6.       In Object Explorer, underneath the analysis services powerpivot instance right-click on the Databases node and select Restore.

7.       In the Analysis Services Restore screen browse to the .abf file you previously pasted into the instance’s backup directory and click the OK button to restore the backup file

8.       You should now see the new multidimensional database restored and if you navigate down into the Cubes node in Object Explorer you will in fact see a cube labeled ‘Sandbox’.

Finally, you can Browse your new cube in Management Studio and begin to see how PowerPivot for Excel internally creates it’s cubes. Every table of data you import into the PowerPivot for Excel client window becomes a Dimension and a Measure Group with a default measure created for COUNT. Additional measures are added into the cube as you create them.

So, now you fully see what PowerPivot for Excel is doing underneath the business user friendly canvas of Excel 2010. Microsoft truly is bringing OLAP (BI) to the masses with PowerPivot for Excel.