Honest Bobs Cube Processing Bob Duffy Database Architect

Honest Bob’s Cube Processing Bob Duffy Database Architect

Thank you to our sponsors!

Speaker - Bob Duffy § § § § 20 years in database sector, 250+ projects Senior Consultant with Microsoft 2005 -2008 One of about 25 MCA for SQL Server globally (aka SQL Ranger) SQL MCM on SQL 2005 and 2008 SQL Server MVP 2009+ SSAS Maestro Database Architect at Prodata SQL Centre of Excellence § http: //blogs. prodata. ie/author/bob. aspx § bob@Prodata. ie

What we Will Cover § Processing Internals § Dimensions Deep Dive § Measure Group Deep Dive § Tuning Patterns § Baseline Harness § Case Study

What's a cube ?

Dimension Processing

Measure Group Processing

Recap: Tuning Attribute Properties § § § Key Column Name Column Attribute. Hierarchy. Enabled Attribute. Hierarchy. Optimized Attribute. Hierarchy. Ordered § Add correct relationships § Avoid Large Dimensions § Or § DELETE THEM

Dimension / Fact Structures Extension Description Kstore, Asstore, Ahstore, b. Store, Ahstore Attribute Stored Dstore Hierarchy Decode Store l. Store Hierarchy Child/Parent Structure Sstore Set Store. Ancestors of a level o. Store Order Store. Contains Position of each level. Bstore Blob Store Map Bitmap Index Extension Description Fact. data Raw Fact Data Map, Hdr Bitmap index Files Agg. flex. data Flexible Aggregations Aff. Rigid. data Rigid Aggregations

Determining Size of Objects http: //tinyurl. com/Molap. Tools

Slow Execute SQL ? 1. Check WAIT Statistics • What does ASYNC_NETWORK mean ? 2. For Dimensions • • Optimise for Select Distinct Or Switch “by Table Mode” 3. For Facts 1. Ensure narrow base table (no joins!) 2. Optimise Scan speed • • 512 k Read Ahead Fast Disks Good File Group Layout No Extent Fragmentation (-E trace flag)

Process Data § Review Grain § Increase Buffer. Record. Limit § Or Aggregate in SQL § Too many Measures ? § Use Calculated Measures § Do we have disk bottleneck § Do we have Network bottleneck § Are we consuming a lot of CPU § Match SSAS to SQL Types § Idle Server Resources § Increase Parallelism

Optimising Bitmap Indexes 1. 2. 3. 4. 5. Optimise Attributes on Dimensions Ensure good CPUs ! Ensure max parallelism Don’t over partition Will sorting and Compression Help ?

Optimising Compression by Sorting § Each Segment in MOLAP is 64 k http: //dennyglee. com/2013/09/30/analysis-services-multidimensional-it-is-the-order-of-things/

Optimising Aggregations § § § Use correct Attribute Relationships Don’t over aggregate (say 20) Consider consolidating aggregations Avoid spilling to disk Ensure max parallelism § CPU should be saturated!

Benchmarking Processing Time § Use XMLA to Process interesting objects § Use XMLA to capture a trace file § Use logman to capture perfmon counters § Disk throughput and latency § CPU § SSAS Threads § Automate with a batch file http: //blogs. prodata. ie/post/capturing-a-service-side-trace-for-mdx. aspx

Analysing the Trace File http: //blogs. prodata. ie/post/Tool-for-baselining-MOLAP-Processing. aspx

ENGINE TUNING TIPS

Connection String § Check Data Source § Try OLEDB driver instead of Sql. Client § Max TCP/IP Packet Size set to 32, 756 § Up default connections from 10 § Don’t use TCP/IP on shared server

INI Settings (before 2012) § INI Setting Impact OLAPProcess Aggregation. Memory. Limit. Min Aggregation. Memory. Limit. Max Increase Max if spill to disk Decrease Min if lots of partitions Thread. PoolProcessMax. Threads Increase if maxed out Coordinator. Execution. Mode Increase Threads per core Buffer. Memory. Limit Improve data grouping Temp. Dir Can place on faster disk See Analysis Services Performance Tuning paper

Case Study – Tuning AW § Size Before § 8. 8 GB Data, 1. 2 GB Index § Size After § 8. 5 GB, 0. 8 GB Index

Case Study II – Retail Cube Baseline § 2. 5 Hours Starting Point for 350 GB cube

Case Study II – Data Types § All Numeric Converted to money or float in sql view. § 30% decrease in time for Process. Data

Case Study II – Attribute/Aggs § 53% overall reduction in cube size § 55% reduction in Aggregations § 73% reduction in size of Bitmap Indexes Processing Time (Hours) 3 2. 5 2 1. 5 1 0. 5 0 Before After

Case Study II – Tuning the TSQL § Need to denormalise Fact Table

References § Excel Tool for Analysing Process Time § http: //tinyurl. com/Molap. Tools § Excel tool for MOLAP disk space analysis § http: //tinyurl. com/Molap. Disk. Size § Scripts for baselining Cube Processing § http: //tinyurl. com/Molap. Harness

Your feedback is important! Please let us know what you thought of this session http: //sqlsaturday. com/414/sessions/sessionevaluation. aspx

Thank You