Twenty signs that you have hired the wrong SSAS consultant

Reposted from Thomas Ivarsson blog with the author's permission.

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.