Author Topic: Handling Composite Key in DMR  (Read 566 times)

Offline sukanya

  • Full Member
  • ***
  • Join Date: Apr 2016
  • Posts: 32
  • Forum Citizenship: +0/-0
Handling Composite Key in DMR
« on: 23 Aug 2016 02:17:02 pm »
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?

Offline bdbits

  • Super Moderator
  • Statesman
  • ******
  • Join Date: Feb 2010
  • Posts: 1,703
  • Forum Citizenship: +97/-0
Re: Handling Composite Key in DMR
« Reply #1 on: 23 Aug 2016 03:25:27 pm »
I think you should take a serious look at using surrogate keys if possible.

Offline sukanya

  • Full Member
  • ***
  • Join Date: Apr 2016
  • Posts: 32
  • Forum Citizenship: +0/-0
Re: Handling Composite Key in DMR
« Reply #2 on: 23 Aug 2016 03:58:19 pm »
is there any other way like concatenating the id columns and make it as business key?

Kind Regards,

Offline bdbits

  • Super Moderator
  • Statesman
  • ******
  • Join Date: Feb 2010
  • Posts: 1,703
  • Forum Citizenship: +97/-0
Re: Handling Composite Key in DMR
« Reply #3 on: 23 Aug 2016 05:08:00 pm »
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.

Offline bus_pass_man

  • Community Leader
  • *****
  • Join Date: May 2008
  • Posts: 221
  • Forum Citizenship: +28/-0
Re: Handling Composite Key in DMR
« Reply #4 on: 23 Aug 2016 07:29:10 pm »
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.

Offline sukanya

  • Full Member
  • ***
  • Join Date: Apr 2016
  • Posts: 32
  • Forum Citizenship: +0/-0
Re: Handling Composite Key in DMR
« Reply #5 on: 24 Aug 2016 12:32:54 pm »
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,

Offline bus_pass_man

  • Community Leader
  • *****
  • Join Date: May 2008
  • Posts: 221
  • Forum Citizenship: +28/-0
Re: Handling Composite Key in DMR
« Reply #6 on: 24 Aug 2016 01:56:29 pm »
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/

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


Offline sukanya

  • Full Member
  • ***
  • Join Date: Apr 2016
  • Posts: 32
  • Forum Citizenship: +0/-0
Re: Handling Composite Key in DMR
« Reply #7 on: 24 Aug 2016 02:16:59 pm »
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,

Offline sukanya

  • Full Member
  • ***
  • Join Date: Apr 2016
  • Posts: 32
  • Forum Citizenship: +0/-0
Re: Handling Composite Key in DMR
« Reply #8 on: 25 Aug 2016 06:18:23 pm »
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,

 



           
Twittear