SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

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

James Serra's Blog

James is a big data and data warehousing technology specialist at Microsoft. He is a thought leader in the use and application of Big Data technologies, including MPP solutions involving hybrid technologies of relational data, Hadoop, and private and public cloud. Previously he was an independent consultant working as a Data Warehouse/Business Intelligence architect and developer. He is a prior SQL Server MVP with over 30 years of IT experience. James is a popular blogger (JamesSerra.com) and speaker, having presented at dozens of PASS events including the PASS Business Analytics conference and the PASS Summit. He is the author of the book “Reporting with Microsoft SQL Server 2012”. He received a Bachelor of Science degree in Computer Engineering from the University of Nevada-Las Vegas.


Leave a comment on the original post [www.jamesserra.com, opens in a new window]

Loading comments...