Thomas.I Microsoft BI & Analytics

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

Archive for the ‘BI War Stories’ Category

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.


Posted in BI War Stories | 4 Comments »

How Qualified is Your Hired BI Consultant?

Posted by thomasivarssonmalmo on April 2, 2011

Since I am on my fourth year as a inhouse working BI so called BI expert I have seen the competence and incompetence of consultants coming and going.

A BI expert can work on several technical platforms so the discssuion is not about that. Also, a good BI expert never starts from technology but from business requirements and use his/her technical skills to promote business requirements.

When should you start to worry?

My own baseline is that the only true knowledge is how little I know and how much I have to catch up with it is amazing to see some consultants that know everything about BI and the MS BI platform, down to nitty gritty details in each product. That is simply not possible and a consultant with these opinions are not serious. Most MS BI professionals that I meet know one or two product really well in the MS BI platform but not all. If you dig that deep into technology you are probably missing the main story, business requirements.

Good consultants always discuss their ideas, not technical ideas first but how to implement business requirements. If they are silent on business requirements  but big mouths on technology you have probably hired the wrong consultant.

From a MSBI technology standpoint I am also amazed on how little influence some consultants takes from good outside sources like SQLCAT and hoards of white papers, blog posts and news group comments about best practises. The same guy failing on the first point above can still say that he/she knows about all the information in these sources. Still in their daily work they do not follow these practises and practically work against them.

Finally, these technology knows it all guys have never read a book about BI  methods / best design practises. Mostly they are good coders that have started in BI because it is better paid.

With PowerPivot an organization have the opportunity to build strong BI models and minimize the influence of the techies that I have mentioned above. Please do not throw out the good BI progress that I still think is the mainstream in this business.

Be aware of these so called BI consultants that will turn your BI solutions in to a business requirement failure.

Posted in BI War Stories | Tagged: | 2 Comments »