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.
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.
There are already two open cases for this.
I opened in 2007 this one: https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=278682
And this one is dated 2006:
Will the 2009 be a good year for a fix? :)
Apparently not... how many years will it take?
Philip I am guessing this hasn't been fixed because it's pretty rare and there are the workarounds I mention here, as the product team will respond to how may votes this gets, and the impact of the problem.