5

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.

bmcbee
  • 85
  • 1
  • 2
  • 9
  • 1
    You should consider moving this question to the sister site: [dba.stackexchange.com](http://dba.stackexchange.com/) Questions tagged with [tag:ssas] and/or [tag:mdx] may have better chances to be answered there. – ypercubeᵀᴹ Apr 30 '12 at 15:07
  • You could either flag it (so a moderator transfers it) or you post it there and remove this question yourself. – ypercubeᵀᴹ Apr 30 '12 at 15:09
  • Why are you trying to create a 1-M relationship rather than a hierarchy? If ContractName in Model Contract is the same as in NYMEX Contract, shouldn't that be the foreign key? ModelContractName appears to be the finest grain element in Model Contract, so the distinct list of names should be a suitable candidate key (though an integer surrogate key would likely be better.) – JAQFrost Apr 30 '12 at 19:28

2 Answers2

6

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

Ali_Abadani
  • 725
  • 3
  • 11
  • 24
0

you need to do 2 dimensions here.

first, you should not add your date as part of the whole dimension. The date itself should exist as a dimension.

second, as I see, you should join NYMEX Contract and Model Contract and make a dimension. You can join them on the DSV with a named query or make a SQL view or you can have both tables separated on the DSV and join them on the dimension.

Third, NYMEX Trades is your fact table. Once you have both previous dimensions created, you should add them to the cube and join them with the fact table on the dimension relationship tab using the fields ContractName and TradeDate

Diego
  • 34,802
  • 21
  • 91
  • 134