On Oct 26, 2013 I presented at “Big Data Saturday” event in Toronto, Canada. Here I am posting my session key points and notes.<\/p>\n
<\/p>\n
For data to be updated in Pivot Tables\/Charts you have to consider 3 load stages:<\/p>\n
Tip: you should know how long each step takes for your sample set of data.<\/p>\n
In Excel 2013 status bar messages are:<\/p>\n
Processing and Querying performance can be tuned using Power Pivot trace.<\/p>\n
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:<\/p>\n
1 \u2013 Process (Partition, Table , MG , Calc Column, DB)
\n17 \u2013 ReadData
\n43 \u2013 Analyze\\Encode Data
\n54 \u2013 Hierarchy Processing<\/p><\/blockquote>\nFor 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”.<\/p>\n
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<\/p>\n
Power Pivot Dynamic Management Views (DMVs)<\/h2>\n
To run DMV query in Excel 2010 use DMV trick:<\/p>\n
Double click inside the Pivot (drill-through), right mouse click on new table, menu item \u201cTable\u201d then select \u201cEdit Query\u2026\u201d and enter DMV query<\/p><\/blockquote>\n
Tu run DMV query in Excel 2013 use DAX Query Table trick:<\/p>\n
Select Cell outside pivot, menu \u201cData\u201d, \u201cExisting Connections\u201d, \u201cTables\u201d, select any table then \u201cOpen\u201d in new worksheet, then right mouse click, \u201cTable\u201d >\u201dEdit DAX\u201d, change command type to \u201cDAX\u201d and enter DMV query<\/p><\/blockquote>\n
My most used Power Pivot DMV queries are:<\/p>\n
— ———————————————- List of available DMV tables
\nSELECT TABLE_NAME
\nFROM $SYSTEM.DBSCHEMA_TABLES
\nWHERE TABLE_SCHEMA = ‘$System’
\nORDER BY 1<\/p>\n————————————————- List calculated measures
\nSELECT CUBE_NAME AS PerspectiveName
\n, MEASUREGROUP_NAME AS TableName
\n, MEASURE_NAME AS MeasureName
\n, [DESCRIPTION] AS MeasureDescription
\n, EXPRESSION AS MeasureExpression
\n, MEASURE_IS_VISIBLE AS IsVisible
\n, DEFAULT_FORMAT_STRING AS FormatString
\nFROM $SYSTEM.MDSCHEMA_MEASURES
\nWHERE MEASURE_AGGREGATOR=0\u00a0 — Just include calculated measures<\/p>\n————————————————- Tables violating Referential Integrity
\nSELECT *
\nFROM $SYSTEM.DISCOVER_STORAGE_TABLES
\nWHERE RIVIOLATION_COUNT > 0<\/p>\n————————————————- Tables record count and columns distinct count
\nSELECT DIMENSION_NAME AS TableName
\n, TABLE_ID
\n, ROWS_COUNT AS RecCount
\nFROM $SYSTEM.DISCOVER_STORAGE_TABLES
\nORDER BY ROWS_COUNT DESC<\/p>\n————————————————- Table Column Memory used (by segment). Need aggregation
\nSELECT DIMENSION_NAME AS TableName
\n, COLUMN_ID AS ColumnID
\n, SEGMENT_NUMBER AS SegmentNo
\n, USED_SIZE \/ 1024 AS SizeKB
\nFROM $system.DISCOVER_STORAGE_TABLE_COLUMN_SEGMENTS
\nORDER BY USED_SIZE DESC<\/p>\n————————————————- Memory used (just hash encoded columns, not value encoded columns)
\nSELECT DIMENSION_NAME AS TableName
\n, ATTRIBUTE_NAME AS ColumnName
\n, DataType
\n, DICTIONARY_SIZE \/ 1024.0 AS SizeKB
\nFROM $system.DISCOVER_STORAGE_TABLE_COLUMNS
\nWHERE dictionary_size > 0<\/p><\/blockquote>\nPower Pivot Tools<\/h2>\n
\n
- Workbook Size Optimizer: http:\/\/tinyurl.com\/PPSizeOptimizer<\/a><\/li>\n
- DAX Studio: http:\/\/daxstudio.codeplex.com<\/a><\/li>\n<\/ul>\n
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:<\/p>\n
<ClearCache xmlns=”http:\/\/schemas.microsoft.com\/analysisservices\/2003\/engine”>
\n<Object>
\n<DatabaseID>E53BEF70384B40109FC4<\/DatabaseID>
\n<\/Object>
\n<\/ClearCache><\/p><\/blockquote>\nTo get DatabaseID go into %temp%\\Vertipaq_<GUID> folder and use sub-folder with extension “.db”. File name part will be DatabaseID: <DatabaseID>.db.<\/p>\n
Other<\/h2>\n
\n
- Power Pivot Applicaton example: http:\/\/PortfolioSlicer.com<\/a><\/li>\n
- Must read whitepaper: “Performance Tuning of Tabular Models in SQL Server 2012 Analysis Services<\/a>“<\/li>\n<\/ul>\n
<\/p>\n","protected":false},"excerpt":{"rendered":"
On Oct 26, 2013 I presented at “Big Data Saturday” event in Toronto, Canada. Here I am posting my session key points and notes.<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":[],"categories":[20],"tags":[],"aioseo_notices":[],"_links":{"self":[{"href":"http:\/\/www.ssas-info.com\/VidasMatelisBlog\/wp-json\/wp\/v2\/posts\/258"}],"collection":[{"href":"http:\/\/www.ssas-info.com\/VidasMatelisBlog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/www.ssas-info.com\/VidasMatelisBlog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/www.ssas-info.com\/VidasMatelisBlog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/www.ssas-info.com\/VidasMatelisBlog\/wp-json\/wp\/v2\/comments?post=258"}],"version-history":[{"count":10,"href":"http:\/\/www.ssas-info.com\/VidasMatelisBlog\/wp-json\/wp\/v2\/posts\/258\/revisions"}],"predecessor-version":[{"id":269,"href":"http:\/\/www.ssas-info.com\/VidasMatelisBlog\/wp-json\/wp\/v2\/posts\/258\/revisions\/269"}],"wp:attachment":[{"href":"http:\/\/www.ssas-info.com\/VidasMatelisBlog\/wp-json\/wp\/v2\/media?parent=258"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/www.ssas-info.com\/VidasMatelisBlog\/wp-json\/wp\/v2\/categories?post=258"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/www.ssas-info.com\/VidasMatelisBlog\/wp-json\/wp\/v2\/tags?post=258"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}