When I build OLAP cubes I take into account quite a number of factors:

  • The Data - Know the Data! Discuss it with your Users!
  • The Users - Know what data they have and where...And especially what data they really want to see!
  • Data Cleanliness - Clean your data! Make your ETL resilient to data problems...Else that will cause recurrent problems in production.
  • Data Complexity - Data should be "cube ready" before being put into the cube...This is a bit of an art...
  • Data Prep - You need to prep as well as clean the data going into cubes.
  • The Desired Outputs to See/View/Report One
  • Whether it's an Analysis Cube, a Data Summary Cube, a Monitoring Cube, or a Dashboard Cube - All of these are a bit different...And there's nothing published that I can see on the practical differences between them...But there are significant differences and this is one areas that often caused performance problems.
  • ETL - Needs to be fast, performant, and able to handle bad data such as nulls, too long text, special characters - All these can cause significant production problems and really need to handled from the start not with something tacked on later.
  • Logging - If you don't log your data operations...You will not know what's happening in your data system and OLAP cubes?
  • Queries - The ETL need strong and fast and performant SQL...There's a bunch of standard performance optimizations here...And some not so obvious or well known. The Cube queries...MDX and/or DAX...also need to be performant...and this is more of an art than a science in some ways. It helps to test and test and do performance monitoring. Also one needs to take into account data growth - Data will grow over time and sometimes reach a point where the ETL and the associated queries...Wilt! Logging and monitoring hand in hand with good design from Day One prevents these common production problems.

There's a lot more to discuss and teach here...

But this above image is a good illustration of how OLAP Cubes rely on the foundations built below them...

If/when you haven't properly addressed each level and step...

You're going to have production performance problems.

And you're not going to be able to quick do some SQL or MDX/DAX optimization and fix everything since what it's built on still has problems.

I still remember getting a call at a major corporation...They had a server that was pegged at 100% CPU utilization and 98% RAM utilization and they asked for some quick solutions...

There are no "quick solutions" when you've built a really bad and poorly performant system...

You have to really redo just about everything...

It's best to Do It Right From The Start!

Build Performance In!

Else be prepared to implement an incremental staged performance improvements in the entire system...

And don't neglect it until it crashes!