Thomas.I Microsoft BI & Analytics

A Site Dedicated to General BI Issues and MS BI Issues: Architecture and Technology

Twenty signs that you have hired the wrong SSAS consultant

Posted by thomasivarssonmalmo on June 15, 2011

These are some of my observations during my years with Analysis Services since OLAP Services in 1999.

All dimensions have parent child hierarchies. There is absolutely no excuse for this. Use parent-child hierarchies only when you have no other option.

The data source view has named queries with a lot of transformations, name changes of columns, derived tables, sub queries and unions in the same mess.  The ETL system has moved in to the cubes and the source data warehouse (If there is one) is not properly structured. It is a clear indication of that the back end data warehouse is a mess.

The measure names are still not right so all measure group measures have visibility = false and are replaced by calculated measures.

The dimension sources consist of 5 to 15 views joined together and have several layers of views on top of each other, the last with different naming standards. This makes it very hard to follow the ETL flow back to the source tables.

The dimension keys are text based, not integers.

All dimensions have no attribute relations in the user or natural hierarchies.

The natural hierarchies in the source dimension tables or views have undetected many-to many relations that will make the distribution of data across dimension members random. This can happen when you have hidden relations in the source table between attributes that are not primary keys.

The cube dimensions have a lot of report hierarchies, which is a hierarchy where the attributes do not have a one to many relations from the top level to the subordinate attributes. Color and the size of a product is an example.

The cube has no aggregations.

The cube consist of more than 15 dimensions in a single measure group and many of the dimensions consist mainly of single attributes with 1-10 members and no hierarchies.

The cube has 50-200 measures in a single measure group. Sometimes dimensions moves in to measures like actual, budget and forecast measures.

The cube has 5000 rows of MDX code when you copy and paste it in to word and use 8 as the font size. To be fair you can be forced to do this by business requirements but you should warn about it from a maintenance perspective. It is very hard to debug.

Measures have null for many dimensions. This is a clear sign that the data model behind the cube is not properly designed.

The cube has no check for division by zero errors in calculated measures so users will see ugly error messages in the table cells.

The consultants do not know what UAT is, has never seen a test protocol and do not know how to do tests while developing the cube.

The consultant thinks that Kimball and Inmon are two soccerteams.

The consultants do not know how to write documentation for a cube except for dropping all the MDX scripts in tables in word.

Dimensions key errors, while processing have been set to ignore errors and discard records in a production system. This is a sign of no handling of orphan dimension foreign keys in the fact table. SSAS has a great feature to take care of this with missing dimension keys.  With this approach the cube will have a subset of  the same data as in the source table.

The cube has 4-5 customer dimensions, not one customer dimension with 4-5 natural hierarchies. This is a migration from AS 2000 without a review of the cube model despite all the changes that came in SSAS 2005 and later.

There are no partitions in the cube when the fact table has 50-100 million records.


4 Responses to “Twenty signs that you have hired the wrong SSAS consultant”

  1. I am really enjoying the last few “soft” posts, Thomas 🙂

    I must say, though, that you are writing this one with the presumption you have a lot of time to fiddle with the cube. I should write one called: “Twenty signs your SSAS implementation will fail even if you did hire the right SSAS consultant”…So often I have _no_ time to do things properly – e.g. aggs, docs, and sometimes even ETL, making me hack my way through it (think 3 weeks from 0 to Prod for a large retailer Inventory and Sales db, ETL, cube and some reports; then another 3 for Order analysis, etc…). But you are right – I _do_ get a very bitter aftertaste when I, unfortunately, have to skip some of the these steps in order to deliver a poor solution…

  2. Nice comment Boyan. I am also on the guilty list for some of the listed signs, like adding too much MDX to a cube. Like you say it also depends on what the customer pays for. To care and know about these left issues is a good sign. I have met consultants that don’t.

  3. arcanecode said

    Just a suggestion, but it looks like you have an excellent basis for 20 future blog posts. Having done SSAS for a while I understand, for example, the need for partitioning, but a noob may not. Be a good opportunity to explain each mistake and show how to correct it. You made some great points here, would like to see the momentum continue!

Leave a Reply

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

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

Google+ photo

You are commenting using your Google+ 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 )


Connecting to %s