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:
I had the same problem.
Very annoying and sloppy on MSFT’s part. Thanks for the heads-up.
Thank you! That has just wasted a good couple of my hours of head scratching, having tried to optimise my DW for a large fact table.
..so all the hoopla about SSAS ready for big data, is just that then.
Very useful, thank you. Confirmed that this still happens using VS2015, with SQL Server 12.0.5203.0 as the data source.