Transcript [Demos] - CubeProcessingx

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
 [email protected]
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
Build Processing Schedule
Process Dimension
Allocate
Resources
Process Attribute
BuildIndex
Write Data
Attribute
Build Decode Stores (Hierarchies)
Begin
Processing
???
Read Data
Write Decode Stores
Process
Data
Read Data
Execute SQL
Write Data
Wait Time
Read
Buffer 1
Data
Cache 1
Read
Buffer 2
Data
Cache 1
End
Attribute
Processing
Measure Group Processing
Process Cube
Process Measure Group
Process Partition
Write Data
Read Data
Execute SQL
BuildAggsAndIndexes
BuildIndex
Wait Time
Aggregate
MergeAggsOnDisk
Recap: Tuning Attribute Properties





Key Column
Name Column
AttributeHierarchyEnabled
AttributeHierarchyOptimized
AttributeHierarchyOrdered
 Add correct relationships
 Avoid Large Dimensions
 Or
 DELETE THEM
Dimension / Fact Structures
Extension
Description
Kstore, Asstore, Ahstore, bStore,
Ahstore
Attribute Stored
Dstore
Hierarchy Decode Store
lStore
Hierarchy Child/Parent Structure
Sstore
Set Store. Ancestors of a level
oStore
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/MolapTools
Slow Execute SQL ?
Read Data
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
•
•
•
•
512k Read Ahead
Fast Disks
Good File Group Layout
No Extent Fragmentation (-E trace flag)
Execute SQL
Wait
Time
Write Data
Process Data
 Review Grain
 Increase BufferRecordLimit
 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
Read Data
Execute SQL
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 ?
BuildAggsAndIndexes
BuildIndex
Aggregate
Optimising Compression by Sorting
 Each Segment in MOLAP is 64k
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!
BuildAggsAndIndexes
BuildIndex
Aggregate
MergeAggsOnDisk
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 SqlClient
 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
OLAP\Process\
AggregationMemoryLimitMin
AggregationMemoryLimitMax
Increase Max if spill to disk
Decrease Min if lots of partitions
ThreadPool\Process\MaxThreads
Increase if maxed out
CoordinatorExecutionMode
Increase Threads per core
BufferMemoryLimit
Improve data grouping
TempDir
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 350GB cube
Case Study II – Data Types
 All Numeric Converted to money or float in sql view.
 30% decrease in time for ProcessData
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/MolapTools
 Excel tool for MOLAP disk space analysis
 http://tinyurl.com/MolapDiskSize
 Scripts for baselining Cube Processing
 http://tinyurl.com/MolapHarness
Your feedback is important!
Please let us know what you thought of this session 
http://sqlsaturday.com/414/sessions/sessionevaluation.aspx
Thank You