Search code examples
ssasforeign-key-relationshipdimension

SSAS: Linking a Time Dimension from Another Database

In one cube, I have a composite key in one of my fact tables that uses DateKey + TransactionKey + MemberKey. The DateKey has the datatype of integer and looks like 20130306. This cube is built on server a; dw instance x.

On another linked server (server b), I have dw instance y that has a time dimension commonly used in our organization. The id value on that is the same datatype/format as the Datekey on server a; dw instance x.

I want to link the time dimension to this cube. How do I get this to happen?

As an aside, if I were to model this relationship, how would I treat the foreign key relationship between the time table to the fact table--since the datekey is only a partial fk.

Solution

  • You can either

    • access server A via the linked server and tie the two tables up in the database prior to entering the cube
    • Create a second data source and tie the fact / dimension up in your cubes Data Source View

    To tie the two columns up, you would need to create a column based on the first 8 characters of the DateKey. You could do this using Left(DateKey, 8) either as a Named Query (to replace the table in cube) or as an additional column (temporary or permanent depending on volume) in your database.