Handling Composite Key in DMR

[sukanya]

1. I have a composite key (6 different columns) Join between Dimension and Fact in our star schema. To create business key in DMR FM model, do I need to have business key defined in the underlined database table that joins to the Fact? Since, business key in DMR doesn't allow to use multiple columns.

2. Can't find any notes on how to model degenerate dimesion in DMR.  I am creating as as seperate Regular Dimension in FM. is this how i need to?

[bdbits]

I think you should take a serious look at using surrogate keys if possible.

[sukanya]

is there any other way like concatenating the id columns and make it as business key?

Kind Regards,

[bdbits]

I think you could probably do that. However, that means it is going to get evaluated every time it is used and likely injected into the generated SQL's WHERE clause. This could have significant performance implications.

[bus_pass_man]

I concur with bdbits.   

Are you sure that all 6 keys are necessary-- do you have role-playing? 

Just out of curiosity, what are these keys? What sort of business role are they performing?



Degenerate dimension stuff
If you are working with DMR then creating a separate regular dimension is probably a good idea as that allows you to generate members.

[sukanya]

The columns are General Ledger, Business Unit, Project etc. Don't exactly know what it means by role playing. Our datamodeller doesn't like to create a business key on database tables using these 6 columns. Do I have to insist to create a business key or are there any other ways to do this.

Kind Regards,

[bus_pass_man]

It's an industry term. This might help you. 

http://www.kimballgroup.com/data-warehouse-business-intelligence-resources/kimball-techniques/dimensional-modeling-techniques/role-playing-dimension/

A single physical dimension can be referenced multiple times in a fact table, with each reference linking to a logically distinct role for the dimension. For instance, a fact table can have several dates, each of which is represented by a foreign key to the date dimension.  It is essential that each foreign key refers to a separate view of the date dimension so that the references are independent. These separate dimension views (with unique attribute column names) are called roles.

[sukanya]

These 6 columns I referred above define a business process. Not a role playing dimension. For the case of creating a business key in DMR, is it necessary that a surrogate key has to be created in database? If not, is there a way we can handle this in FM model.

Regards,

[sukanya]

I have resolved it by identifying project as lower level among the 6 columns and created 2 levels. In higher level, all the columns as no role. Thank You for your inputs.

Kind Regards,