CODEWITHSUNDEEP
sql-serverssascube
BlackCat
BlackCat

Reputation: 2044

Error in specify relationship in data source view of ssas

While I was trying to set relationship between two objects through data source view this error message occured:-

enter image description here

The underlying database table hold different data type of the columns(suppose,one is int while another is smallint or bigint ).what is the way to solve this issue?

Upvotes: 0

Views: 902

Answers (2)

Mukta
Mukta

Reputation: 11

For me, it is a problem with the Fact table. We have a Named Query for the fact table that refers to the View in DWH. Well! I hardcoded this column (Dimension Key column) as -1 in the View and added in a Named Query in Cube. This took the datatype as System.int32 instead of System.Decimal and hence I faced this issue.

I changed the view (removed the hard coded value and fetched directly from the table), removed the column from Named Query, and added it again. That resolves it.

Upvotes: 1

Tanner
Tanner

Reputation: 22743

The source column and the destination column have different data types

I fail to see what isn't clear with this message. If you have a primary key, any foreign keys that relate to that primary key need to be the same data type.

If you consider the range and storage requirements of the data types:

Data type   Range                                                    Storage
bigint      -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807  8 Bytes
int         -2,147,483,648 to 2,147,483,647                          4 Bytes
smallint    -32,768 to 32,767                                        2 Bytes
tinyint     0 to 255                                                 1 Byte

You can see that the range of values that the column can hold varies significantly between the data types. To maintain the relationship between primary and foreign keys, the type must be the same so that the range of values it can hold match.

If you had an int primary key but a smallint foreign key, then it would be impossible for the foreign key column to hold a value greater than 32,767, where as the primary key would be able to hold a much greater value.

So the way to solve this is to update the data type of the column you want to use as a foreign key to match the primary key before you create a relationship.

Upvotes: 1

Related Questions