Search code examples
sql-serverssascube

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?

Solution

  • 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.