Using TinyInt as keys in Analysis Services

Fact tables should be narrow and deep.  If you are recording every debit card transaction for every customer across the fifteen retail banks you own, you end up with 15 million rows a day, so to save space they need to be as thin as possible.  If one of your dimensions can only ever have a small number of values then use tinyint as the key to this dimension.  Actually I always use tinyint instead of boolean for yes/no male/female stuff because you often need to have three or four values i.e. to include ‘not applicable’, ‘unknown’ etc. and I also break with Kimball and use special keys for these such as 0.

OK so tinyint and smallint can save space in data warehouses, however you can run into trouble when you try and use these keys to build cubes in analysis services (in SQL Server 2005 and 2008).  This is because analysis service doesn’t support tinyint and converts the type to something else.  Unfortunately the something else it gets converted to is different for the foreign key to the dimension on the fact table (System.Byte) than for the primary key on the dimension (System.int32).  So now analysis services can’t join the two tables properly and this is quite rightly confused and annoyed a few people.

I can’t fix analysis services to correct this and I understand the issue still exists in Analysis Services 2008, but I can tell you two approaches to get around it.

  • Change the data types of all of the tinyint and smallint in the data source view (DSV) used to build the unified dimensional model (UDM) for your cube.
  • Build the UDM up from a set of SQL Server views and not the base tables, and do the conversion there.  I used this last approach all the time in Analysis Services 2000, because there was no UDM, back then.

The longer term approach is to voice concern on connect, where you have a much better chance of influencing the shape of SQL Server than I do as an evangelist as the product team do take customer feedback seriously. Incidentally I notice that connect is starting to look like a proper portal, so do have a look around anyway if your interested in participating in any betas.

Technorati Tags: Analysis Servicesl,SQL Server,keys,data warehouse