SSAS One to Many Dimensional Relationship

bmcbee picture bmcbee · Apr 30, 2012 · Viewed 9.7k times · Source

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

Ali_Abadani picture Ali_Abadani · May 1, 2012

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: enter image description here

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:

enter image description here enter image description here

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