TinyInt problem in SSAS

If you have a source table that has a Tinyint primary key column defined as IDENTITY and you create a Data Source View (DSV) in SSAS and add that table (usually a dimension table), that IDENTITY field will be incorrectly typed as System.int32.

This causes problems when attempting to define FK relationships between this column and related FK columns in other tables in the DSV.  If you try to define a relationship, you will get the message “The <source> column and the <destination> column have different data types”.  It will also not automatically create an relationship when using the Data Source View Wizard.

Note that if the column is not defined as an IDENTITY column it is correctly represented with a type of System.Byte in the Data Source View.

As a workaround, you can create a view or named query for each table in which you explicitly convert your tinyint field to tinyint in the view, and then refresh the DSV, the column type will correctly be recognized as System.Byte in BIDS.  For example, “SELECT Cast(KeyID as Tinyint) as KeyID”.  You can then create the relationship.

Another solution is to just change the data types in your source table to int.

I have found two Connect items about this: Data Source View incorrectly types a Tinyint Identity column as int32 and Data Source View does not support PK with tinyint type, but it has not been fixed yet.

More info:

Using TinyInt as keys in Analysis Services

About James Serra

James is SQL Server MVP and a independent consultant with the title of Business Intelligence/Data Warehouse/Master Data Management Architect and Developer, specializing in the Microsoft SQL Server BI stack.
This entry was posted in SQLServerPedia Syndication, SSAS. Bookmark the permalink.

2 Responses to TinyInt problem in SSAS

  1. bloger7791 says:

    I had the same problem.

  2. Very annoying and sloppy on MSFT’s part. Thanks for the heads-up.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>