Vidas Matelis Analysis Services Blog

On Oct 26, 2013 I presented at “Big Data Saturday” event in Toronto, Canada. Here I am posting my session key points and notes.

Power Pivot Database

• Make a copy of your workbook and change extension from .xlsx to .zip. Inside zip file in the folder “xl\customData” there is file item1.data that is backup of Power Pivot database.
• When Power Pivot engine starts it restores database backup on your machine in the folder %temp%. Power Pivot will create sub-folder “Vertipaq_<GUID>” where in .db sub-folder you will find folder for each table in your model. Tip – go into biggest table folder, order files by size and see if you really need columns that use most of the space.

Power Pivot Application performance tuning

For data to be updated in Pivot Tables/Charts you have to consider 3 load stages:

1. Loading of Power Pivot model into memory. The bigger the model (amount of data and number of tables/attributes) the longer it takes. There is no direct performance tuning here.
2. Processing of Power Pivot model – that is loading data from source into Power Pivot model. Most important tip – do not load columns that you really do not need. Also, calculated columns are materialized in this step and might affect processing performance.
3. Querying of Power Pivot model – that is refreshing Pivot Table/Chart results with queries from Power Pivot database. Most performance tuning time will be spent on writing better DAX calculated measures.

Tip: you should know how long each step takes for your sample set of data.

In Excel 2013 status bar messages are:

• “Loading Data Model…” – loading Power Pivot model into memory.
• “Retrieving Data…” – Processing data (loading from source into Power Pivot model)
• “Reading Data…” – Querying data from Power Pivot model and updating Pivot Tables/Charts.

Processing and Querying performance can be tuned using Power Pivot trace.

For Processing performance apply filter for “Progress Reports / Progress Report End” EventClass and then add filter for column duration (example >= 35ms). Most important EventSubclass values are:

1 – Process (Partition, Table , MG , Calc Column, DB)
17 – ReadData
43 – Analyze\Encode Data
54 – Hierarchy Processing

For Querying performance tuning start by filtering for “Query End” Even and apply additional duration. This way you will be able to identify slow Pivot Tables/Charts. Then it is best to use SSAS Tabular Instance to continue tuning. Another option is using “DAX Studio”.

Tip: Enabling Tracing can affect Power Pivot tuning if your DAX formulas are more complicated. You might see queries running longer and trace file growing to 10s or 100s of megabytes

Power Pivot Dynamic Management Views (DMVs)

To run DMV query in Excel 2010 use DMV trick:

Double click inside the Pivot (drill-through), right mouse click on new table, menu item “Table” then select “Edit Query…” and enter DMV query

Tu run DMV query in Excel 2013 use DAX Query Table trick:

Select Cell outside pivot, menu “Data”, “Existing Connections”, “Tables”, select any table then “Open” in new worksheet, then right mouse click, “Table” >”Edit DAX”, change command type to “DAX” and enter DMV query

My most used Power Pivot DMV queries are:

— ———————————————- List of available DMV tables
SELECT TABLE_NAME
FROM $SYSTEM.DBSCHEMA_TABLES
WHERE TABLE_SCHEMA = ‘$System’
ORDER BY 1

————————————————- List calculated measures
SELECT CUBE_NAME AS PerspectiveName
, MEASUREGROUP_NAME AS TableName
, MEASURE_NAME AS MeasureName
, [DESCRIPTION] AS MeasureDescription
, EXPRESSION AS MeasureExpression
, MEASURE_IS_VISIBLE AS IsVisible
, DEFAULT_FORMAT_STRING AS FormatString
FROM $SYSTEM.MDSCHEMA_MEASURES
WHERE MEASURE_AGGREGATOR=0  — Just include calculated measures

————————————————- Tables violating Referential Integrity
SELECT *
FROM $SYSTEM.DISCOVER_STORAGE_TABLES
WHERE RIVIOLATION_COUNT > 0

————————————————- Tables record count and columns distinct count
SELECT DIMENSION_NAME AS TableName
, TABLE_ID
, ROWS_COUNT AS RecCount
FROM $SYSTEM.DISCOVER_STORAGE_TABLES
ORDER BY ROWS_COUNT DESC

————————————————- Table Column Memory used (by segment). Need aggregation
SELECT DIMENSION_NAME AS TableName
, COLUMN_ID AS ColumnID
, SEGMENT_NUMBER AS SegmentNo
, USED_SIZE / 1024 AS SizeKB
FROM $system.DISCOVER_STORAGE_TABLE_COLUMN_SEGMENTS
ORDER BY USED_SIZE DESC

————————————————- Memory used (just hash encoded columns, not value encoded columns)
SELECT DIMENSION_NAME AS TableName
, ATTRIBUTE_NAME AS ColumnName
, DataType
, DICTIONARY_SIZE / 1024.0 AS SizeKB
FROM $system.DISCOVER_STORAGE_TABLE_COLUMNS
WHERE dictionary_size > 0

Power Pivot Tools

• Workbook Size Optimizer: http://tinyurl.com/PPSizeOptimizer
• DAX Studio: http://daxstudio.codeplex.com

DAX studio could be used to run DAX queries against your Power Pivot database. To clear cache provided functionality (menu item) does not work. Instead use XMLA query:

<ClearCache xmlns=”http://schemas.microsoft.com/analysisservices/2003/engine”>
<Object>
<DatabaseID>E53BEF70384B40109FC4</DatabaseID>
</Object>
</ClearCache>

To get DatabaseID go into %temp%\Vertipaq_<GUID> folder and use sub-folder with extension “.db”. File name part will be DatabaseID: <DatabaseID>.db.

Other

• Power Pivot Applicaton example: http://PortfolioSlicer.com
• Must read whitepaper: “Performance Tuning of Tabular Models in SQL Server 2012 Analysis Services“