SSAS One to Many Dimensional Relationship
dimensions, primary-key-design, relationship, sql, ssas
Solution
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,...
Problem
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.