Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

James Serra's Blog

James is currently a Senior Business Intelligence Architect/Developer and has over 20 years of IT experience. James started his career as a software developer, then became a DBA 12 years ago, and for the last five years he has been working extensively with Business Intelligence using the SQL Server BI stack (SSAS, SSRS, and SSIS). James has been at times a permanent employee, consultant, contractor, and owner of his own business. All these experiences along with continuous learning has helped James to develop many successful data warehouse and BI projects. James has earned the MCITP Business Developer 2008, MCITP Database Administrator 2008, and MCITP Database Developer 2008, and has a Bachelor of Science degree in Computer Engineering. His blog is at .

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

Comments

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

Loading comments...