Big Dimensions – Just because you can …

One of the limitations of Analysis Services in SQL Server 2000 (AS 2000) was that a member in a dimension could only have  64,000 children, which was removed in SQL Server 2005 (SSAS 2005). So now you can create a customer hierarchy for your 1 million customers which has [all] at the top with each individual  customer underneath.

The problem hasn’t really gone away, Excel will probably give up if you expand this dimension even if you have Excel 2007 which can have a million row spreadsheet. In fact problem arises at about the 500,000 row mark.  But suppose this was possible in a future release of Excel (Note this is pure hypothesis, I know nothing about Excel 14 OK), how useful would that be to the end user?

The only way I can seeing this being of use is if the non–empty filter is on and the filters and selections made before this dimension is expanded severely limit the number of customers returned.  If the user wants to find an individual customer then they can search for it, otherwise they will be overwhelmed by the huge grid they are presented with

My AS2000 best practice was to create extra levels to make navigation easier. For customers I would take the first letter of surname for personal customers and the first word of a commercial customer unless this was 'The'.  Of course with a really large dimension you might several of these levels. Similar approaches work for post codes, ip addresses etc.

So make use of deep hierarchies for big dimensions and your users will thank you in the long run.

Technorati Tags: Analysis Services,OLAP,dimensiosn,hierarchies