Yesterday Pragmatics Works offered another 1h of free training on the BI stack. This time was Building Tier 1 SSAS Solutions for Maximum Performance by Adam Jorgensen, an expert on Dimensional modeling.
The most interesting tips given by Adam were about how to build a cube without incurring in bottle neck issue…
Design and Query Considerations
Simplicity is Key Thant means don’t complicate too much the design of the cube itself, this is a general rule!
- Parent child use It could created performance problems , I’ve found this interesting project http://pcdimnaturalize.codeplex.com/
- Many to Many
when you have too many of this it’s better to rethink the data model itself
- Cell by Cell vs. Block Mode some consideration http://www.ssas-info.com/analysis-services-articles/50-mdx/1177-optimizing-mdx-aggregation-functions
- Data Types it’s very important the usage for integer when dealing with indexes
Increasing Hardware Requirements
- Using SSD’s Using ssds disks improves the usage of the cubes, just because the random access will be much much faster, mind when the cube is used by a lot of users at the same time, maybe each one is doing some sequential processing (ex parallel period usage) but all together will do random access on the cube
- ROLAP (Preview) using rolap means to use relational structure to store the cube; each ROLAP report is essentially a SQL query (or multiple SQL queries) in the relational database, the query time can be long if the underlying data size is large. http://businessintelligence.ittoolbox.com/documents/advantagesdisadvantages-of-molap-rolap-and-holap-15897
Case study example: Usage Cube Distinct Count Performance
This graph shows the performance improvement on using the ssd (taken by some case study you can find on http://sqlcat.com/)
Using SSD automatically increase the performance comparing to HDD /SAN approach, but as you see if you build the cube properly (V2 Cube) the performance increase is 8 times bigger.
The lesson😎 is to focus on the following 5 areas:
- You HAVE to get the design right if you want to scale
- Partitioning is absolute crucial
- Hardware really matters for large cubes
- Locking is your enemy
- Multi User Settings
At the end of the webmianr I asked for any tool to use for MDX tuning (just curios for now), Adam said therea re some interal tools used by microsf, unfortunalty there is no real mdx query planner…