Thanks, Not quite what the ask is. Let me explain
- You are creating LDM datamart diagram. You have a date dimension, that has fields like Date, Month, Year etc. Now you want to create two separate instances of this table a) Open Date Dim and b) Closed Date Dim. The # of fields in these two tables are same, but named differently. E.g. First one has Open Date, Open Month, Open Year while the second has Closed Date, Closed Month and Closed Year and so on
- The Open Date Dim and Closed Date Dim are 1:M joined with the Fact table individually on two different Fact table columns. “Open Date Dim”.“Row Id” = Fact.“Open Date Id” and “Closed Date Dim”.“Row Id” = Fact.“Closed Date Id”
- Now when you convert this diagram to physical diagram, these two become one single table merged as w_day_d. In other words, the dba knows she does need to create 2 tables, but only 1 will suffice. By virtue of the LDM however, the user knows that he needs to create two aliases (Open and Closed) that will join on different columns in the fact table.
Please do let me Jick if this makes sense. Such dimensions are called Role Playing Dimensions (Date, Employee etc.)