Different Data Types in Data Source View

I have 2 views in my data warehouse where the dimension table primary_key has a data type of tinyint and the fact table foreign_key has a data type of tinyint. When I bring these into SSAS 2005 data source view, the data types change. The dimension key is now a system.int32 and the fact key is now a system.byte. I can no longer relate these two tables together because I get an error of "different data types".

Has anyone encountered this yet?

Thanks,

Brian

Brian,

There is a section on this issue in the "Project REAL: Analysis Services Technical Drilldown" whitepaper by Dave Wickert. You can find the paper at the following URL:

http://www.microsoft.com/technet/prodtechnol/sql/2005/realastd.mspx

Search for: "Data type mismatches with tinyint keys"

HTH,

- Steve

|||Very helpful, thanks so much|||I'm getting the same problem but with system.decimal keys. Everything was fine until I updated the named query for my fact table. All the numeric fields changed from system.decimal to system.byte. The data warehouse has not changed so I don't know why this has happened. I read the article and tried to recast the data type on one of the related tables to match the fact table, but the cast didn't seem to have any effect. I even tried to set the field on both tables to 1 and they still didn't match. The fact table and the dimension tables are created by named queries. Could this have something to do with SP1?|||

Sherrill,

This may have to do with an issue around the data source view definition for the named queries that you modified. The data types for particular columns in your query will not always "recast" themselves in the data source view definition even though the underlying data type in either the table or named query has changed. Try commenting out the definition for the columns that were changed and then save the data source view. Go back and un-comment and then save the data source view again. This should clear out the existing type binding and create a new one that is correct for the changes you made.

HTH,

Steve

|||

That didn't fix it. The changes I made really had nothing to do with the data type. All I have to do is change one thing on the named query - such as change a literal from 1016 to 9999 - and when I save the dataview, the type changes on all numeric fields from decimal to byte - blowing away all my relationship links. It's as if it isn't seeing the datatype in the underlying table. I even tried to cast a field to a specific type in the named query, and it still came out as byte.

I spoke with someone else here who also ran into the problem. He said it started after we upgraded to SP1. His solution was to make the changes in the xml code view. The data warehouse I'm using is Oracle and I'm using the .NET provider. The dataview was created before SP1 and changed several times with no problems prior to the upgrade.