Techniques for optimizing Excel queries against SSAS Javier

Techniques for optimizing Excel queries against SSAS Javier Guillen - Blue. Granite

About this session I. Optimization = better performing II. Techniques assume BI dev has little to no degree of report layout control III. Demos in Tabular but mostly applicable to Multidimensional

Why do we care? I. User adoption is critical for project success! ü Performance is a key component of user satisfaction II. Badly performing cubes can prompt users to draw generic conclusions about the appropriateness (or not) of using this technology to solve their use cases III. Can generate perceptions that ultimately affect BI budgets and resource allocation IV. Who wants to see disappointed faces over a nicely engineered cube?

A common scenario i. Huge pivot tables (lots of rows, columns) ii. Lots of slicers iii. Truly represent a ‘data dump’ – that business intends to use for further manipulation iv. Generally performs poorly

Why do users do this? i. Lack of understanding (and/or interest) in the technology ii. Lack of training and/or training strategy iii. Fear/need of control iv. Do not want to experience slow IT development cycles v. Power Pivot against SSAS strategy not as clear and ideal for users… companies usually end up recommending querying DB directly – creating redundancy. This requires its own strategy

What users think “cubes” are i. The pivot table ii. A way to “extract” corporate data out, allowing further manipulation (“data dumps”) iii. To built template pivot tables, starting at a large scope Demo: Analyzing products for highest selling store

Report Ownership Spectrum Reports owned by I. T. (Corporate BI) Custom Drill-through SSRS Actions Reports owned by the business (Self Service BI) Large tables High level of detail Attempt to satisfy multiple use cases

Anybody heard of the concept “Unclear Requirements”? I. Performance problems would be avoided if we had clear requirements II. However, its common to start development under unclear requirements, III. Cubes are supposed to target ad hoc query use Recommendation: have at least have one requirement – how fast should this report be?

Note In MD - Usage based optimization can help target performance. As mention, will focus on Tabular because: i. Tabular is now the default choice for many SSAS projects now ii. MD usage optimizes specific query patterns iii. Some of the techniques mentioned attempt wider ad hoc use

What can be gained? i. Increase adoption as we demonstrate ability to improve while respecting report layouts ii. Report ownership can remain with the business – even if only as a temporary phase required to build trust

Demo data i. Contoso DW - 3. 5 million fact table ii. One semi additive measure – 5 fully additive measures iii. 12 dimension attributes iv. Large pivot table – 46 columns and 21, 500 rows v. All performance testing will be done in cold cache

Environment Specs Windows 10 64 bit 8 GB RAM Intel Core i 7 @ 1. 80 Ghz (4 CPUs) SQL Server 2014

Demo Report

Demo Report Stats Test Environment Test 1 – Full Refresh Test 2 - Interaction SQL Profiler 28 - SQL Server Management Studio 35 - Excel 31 26 Test 1 = Full report refresh Test 2 = Slicer interaction Step 1. Select Channel Name = Online Step 2. Select Promotion Category = Store i. Over same query, Excel UI is 11. 4% than SSMS. Ø Over large result sets, Excel renders faster than SSMS results pane. ii. Excel performance numbers will be used for benchmark comparison

Technique # 1 – Controlling inefficient MDX queries via Excel Sets Goal: replace badly shaped or inefficient MDX queries with custom MDX code Approach: Leverage custom set Excel functionality to partially control the query

Demo Results Test 1 – Full Refresh Test 2 - Interactivity 20 s 18 s 35. 6 % 30. 5 % Observations: i. When using sets with ODC, any change in the ODC will remove the set ii. Pivot Table Filters cannot have fields already defined in the Set iii. Expand/collapse functionality is removed

Technique # 2 – Controlling output of ALL member when not needed Goal: avoid the need to compute expensive ALL members when not needed. Approach: detect levels in DAX and create dummy output for ALL members

Demo Results Approach 1 – Greyed Slicers Test 1 – Full Refresh Test 2 - Interactivity 17 s 20 s 45. 2 % 23 % Test 1 – Full Refresh Test 2 - Interactivity 21 s 26 s 32. 2 % 0% Approach 2 – Regular Slicers Observations i. Be careful with impact on slicers – all members may appear greyed out if All member is fully excluded ii. Technique is Ideal for use with highly complex calculations. If using simple aggregates (like straight counts) then impact is low to none.

Technique # 3 – Aggregate Aware Measures Goal: improve performance by reducing granularity of fact tables Approach: i. create multiple fact tables (on per grain target) ii. use DAX information functions to direct users to the correct table based on dimension attributes requested

Demo Results Test 1 – Full Refresh Test 2 - Interactivity 17 s 26 s 45. 2 % 0% Observations i. Ideal for when display is at much lower grain than the fact table and there is calculation complexity ii. assumes some understanding of how people access data (based on hierarchies or multiple levels of granularity) iii. Optimizes query time but can Increases processing time and model size

Technique # 4 – Reducing query traffic Goal: improve performance by submitting less queries to SSAS Approach: only submit a query - when user is actually ready to view results

Demo Results Test 1 – Full Refresh Test 2 - Interactivity 31 s 14 s 0% 46. 2 % Observations i. Test uses base measures (hence the 0% gain for full refresh) ii. Requires adding code Not all users are VBA savvy iii. VBA not supported in Excel Services

Technique # 5 – Pre-loading data over fast DAX queries Goal: Improve performance by pre-loading data in the report Approach: Use DAX query in ODC connection to retrieve the entire dataset

Demo Results Test 1 – Full Refresh Test 2 - Interactivity 77 s 3 s -225 % 88. 5 % Observations: i. Requires use of external ODC file. ii. Not all users familiar with DAX querying. iii. Semi additive measures have to be pre-calculated for the totals, as totals and subtotals can only be fully additive. iv. Larger file footprint

Summary – Performance Benchmarks

Conclusions i. It is possible to improve cube performance without changing a report ii. Sometimes this can enough to pass user acceptance testing iii. Could prevent change of ownership (from the business to I. T. ) iv. These techniques can be combined for better performance: Ø For example: Controlled Slicer Refresh + Aggregate Aware measures Be aware of Super DAX performance improvements in SQL Server 2016

Thank you! Javier Guillen Email: jguillen@blue-granite. com Twitter: @javiguillen Home Address: NO