Question in SSAS for y'all. I am attempting to define a One to Many relationship in an OLAP SSAS cube environment. However, i'm running into problems with defining a primary key. Example tables are below. Relationships between the first 3 tables are easily defined (TradeDate, NYMEX Trades & NYMEX Contract). However, my goal is to create a one to many relationship between NYMEX Contract and Model Contract. In other words, the data from 1 NYMEX Contract will be utilized multiple times in the Model Contract dataset.
Table: TradeDate
Table: NYMEX Trades (NO PK)
Table: NYMEX Contract
Table: Model Contract
I need to create a one to many relationship between NYMEX Contract and Model Contract...however my PK is on the wrong column in Model Contract. Any ideas?
To help clarify - i am attempting to define a dimension --> dimension relationship. The Table "Model Contract" is a lookup table.
What you have is an example of a many-to-many relationship between you fact data (Trades) and your dimension data (Model Contract). have a look at this: dimension relationship . Your case is a rare case since model contracts can only be assigned to one Contract Name but it still falls under the many-to-many case. This is handled using a bridge-table in dimensional modeling and in SSAS they call it an intermidiate fact table. Once you have your DSV setup like this:
You would then create your dimensions in your project and then create a measure group for ModelContract to be able to use it as an intermediate fact table. You would then setup the dimension usage for the cube choosing a many-to-many relationship:
As a side note, you should always use surrogate keys for your dimensions. They have several advantages such as storage space in you fact table, slowly changing dimensions, source system decoupling,...