Within the first article of this Microsoft business intelligence tooling series I’ve briefly covered some of the basics of Power Query for Excel. However finding, consuming, transforming and sharing data using Power Query is just the first step when it comes to analyzing data within Excel.
Once you have formatted your data correctly, it’s time to start the analysis proses using another excel add-in called PowerPivot . Given that I’m telling my story from an IT perspective, I will include some technical details as well.
PowerPivot – Analyzing large amounts of data
PowerPivot basically extends the capabilities of the existing pivot table in excel. However it has some unique features allowing users to analyze large quantities of data swiftly in memory (no 1048576 rows limitation). This is possible due to a new in-memory BI engine (VertiPaq) and advanced compression algorithms to load even the biggest data sets into memory. The latter point allows the PowerPivot / VertiPaq engine to compress data typically at a 10:1 – 15:1 ratio; however 1000:1 compression ratios aren’t uncommon for highly duplicative data sets. Therefore processing millions of rows takes about the same time as processing thousands and no comparable with traditional processing within Excel.
VertiPaq isn’t new and has an interesting history in the Microsoft BI stack. VertiPaq, the underlying engine, has been around for years. Now in 2012 Microsoft announced a name change from Vertipaq to xVelocity In-Memory Analytics Engine. Microsoft also decided to integrate the same engine within SQL Server 2012. Features like the Columnstore Indexes, Analysis Services (SSAS) and PowerPivot for SQL Server all rely on xVelocity. In all of those cases, you can see some commonality; compression, scanning, and aggregation, is shared between the three. The following articles cover some this is greater detail.
This is all irrelevant information for the end user, but if you’re in IT and interested into the bigger picture of Microsoft’s Business Intelligence platform (Analysis Services), I would highly recommend reading up on xVelocity.
Pivot Tables Changes
At this point we know that the underlying engine of a PowerPivot table differs from a normal excel Pivot table. Due to this PowerPivot also includes some changes when it comes to creating Pivot Tables. The most important part is the introduction of Data Analysis Expressions (DAX) in addition to standard Excel features.
By leveraging DAX, users can easily create advanced workbook applications that rely on data relationships between tables as in a database and therefore eliminating slow vlookups. This is required because the expressions, including calculations etc., will be processed by VertiPaq.
The good part is that users don’t need to know much about what’s happening behind the scenes. However it does require setting up data relationships (using Excel 2013 is highly recommended when using power Query as a data source).
After this is completed, make sure to use the PowerPivot toolbox for the creation and alteration of PowerPivot tables.
With great power, comes great responsibility. Bigger, more powerful, mission-critical workbook applications pose significant challenges for the IT department. Therefore it is crucial to be able to discover mission-critical workbook applications and keep an eye on performance, availability, and quality of service.
Depending on the size of the organization, moving assets within Power BI might be an option to deal with the challenges mentioned above.
This was PowerPivot in a nutshell, one of the BI tools available within the Microsoft BI suite. The next article covers PowerView and the Power BI portal.
PowerPivot Component Architecture – http://blogs.msdn.com/b/analysisservices/archive/2010/03/22/powerpivot-component-architecture.aspx