Building Tier 1 SSAS Solutions webseminar

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

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.

Summarizing

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

Reference

http://northamerica.msteched.com

http://cms.pragmaticworks.com/videos/default.aspx?VidID=641ff359183e408db1d89666aad94037

ps

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…

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s