SSAS One to Many Dimensional Relationship

Question

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

• TradeDate (PK)
• Year
• Month etc...

Table: NYMEX Trades (NO PK)

• ContractName
• TradeDate
• Price

Table: NYMEX Contract

• ContractName (PK)

Table: Model Contract

• ModelContractName (PK)
• ContractName

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.

Answer 1

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