What is PowerPivot for Excel Doing Under the Hood?

For those of you who have been long-time or experienced modelers in SQL Server Analysis Services and have used UDM with MDX, you may want to begin looking at how to build similar (albeit probably simpler) analytical models in PowerPivot. You can build surprisingly powerful analytics with PowerPivot for Excel and use the Excel add-in for PowerPivot to model, analyze, pivot and maintain your data, metadata and relationships all in one tool (Excel). I’ll point you back to one of Derek’s excellent pieces in the SQL Mag BI Blog on PowerPivot for more insights on using PowerPivot.

Related: Understanding PowerPivot and Power View in Microsoft Excel 2013

Since Excel is the world’s #1 most popular BI tool, it makes sense to use it as your primary tool for all things BI, including building models, maintaining metadata and data source connections. That being said, Excel and PowerPivot are not the answers for all use cases. But as you begin to step into the PowerPivot world, as an experienced SSAS developer, you will probably wonder where do you build custom hierarchies? How do I partition this thing? Where is my MDX? I’m not going to go into those areas today, but you do need to understand the mindset change from the classic cube structure & processing model that is maintained by a server process on disk to the in-memory analysis engine that PowerPivot provides, called Vertipaq.

What I wanted to just briefly point you to today is using SQL Profiler and tracing the activity from the Excel PivotTable to the PowerPivot model. Tracing and Profiling are very common tasks to SQL Server developers and DBAs both in the relational world as well as the SSAS multi-dimensional world. PowerPivot shields users and developers from MDX and uses a new set of Excel formulas created specifically for PowerPivot called DAX. Many of these formulas will look familiar to you when you think about MDX calculation functions.

When you build an analysis in Excel from your classic SSAS cube, the analysis services connection from the Excel client to the back-end database will use MDX to query and bring back result sets. Most SSAS BI developers will use SQL Profiler to troubleshoot or to watch events between the client and database. This capability is available to you as well when you use PowerPivot for Excel to develop models. To do this, enable the client tracing option from the PowerPivot ribbon bar in Excel. The dialog box that I am showing here is available to you when you click on settings under PowerPivot on the Excel ribbon. Below that, I’ve pasted a copy of my Excel PowerPivot trace in SQL Profiler. What the tracing option will do is to output the trace for you in trace files that you can then open in SQL Profiler showing you the MDX and XMLA just like you would see from a profile trace in SSAS.

It’s a good idea to become familiar with both the UDM and PowerPivot modeling techniques as you prepare for the roll-out of Denali. The BI Semantic Model (BISM) will incorporate both the UDM and PowerPivot in BI Developer Studio as Derek outlined here. So learning now about troubleshooting and managing the Vertipaq model will come in handy later. You will even see VertiPaq events in the current SQL Server 2008 R2 SQL Server Profiler that you can capture.