Wednesday, August 5, 2009

Foreign Keys with TinyInt do not recongnize by SSAS

This is still there, with SQL Server 2008 too. I usually use TinyInt for tables' primary key when the number of records to be held are less than hundred, so applied the same rule for my newest Data Warehousing project, completely forgetting the fact that Analysis Services cannot recognize the relationships between Fact and Dimensions that have TinyInt as the primary key. As usual, had to change all my Dimensions tables, changing primary keys to Smallint. Remember this, if you are designing a data warehouse.
at

2 comments:

  1. UnknownAugust 6, 2009 at 2:06 AM

    There is a work around that you could have used for this:

    In the Data Source View you could replace the table with a named query (where in the named query you would define the "tinynt" column as an "int" column)

    ReplyDelete
  2. Dinesh PriyankaraAugust 8, 2009 at 8:39 PM

    Yes, it is possible, but have to make named queries for each table, and maintenance cost is high too. Even with named queries, have to use CONVERT to convert from TinyInt to Smallint.

    ReplyDelete