Kimball Forum
Would you like to react to this message? Create an account in a few clicks or log in to continue.

Role-playing dimensions

4 posters

Go down

Role-playing dimensions Empty Role-playing dimensions

Post  erazon Wed Jun 27, 2012 1:40 pm

Hi,

We have this debate in our group about representing a single dimension that appears several times in the same fact table. For example, our factSale table may have ShipDateKey, OrderDateKey, and ReturnDateKey.

(Solution A)
One side in our group are advocating in creating a separate date dimension for each role (either by a physical table or through a view). Hence,there will be 3 additional date dimension which may be called dimShipDate, dimOrderDate and dimReturnDate with the corresponding "ShipDateKey", OrderDateKey, and "ReturnDateKey" as their PK respectively. These are exact replications of the date dimension. This idea was actually directly lifted from Laura Reeves manual (Dimensional Modeling: Intermediate and Advance Topics c.2010 Starsoft Solutions pages 170-173).

(Solution B)
My solution was to use just one Date dimension table and make each FK's in the factTable takes the role as it's name. Our dev tem are not too keen on creating dimension tables whose keys doesn't match the names of their corresponding FK's in the fact table.
My question to everyone is what was your solution in this type of a problem. I'm sure most here had to one way or another had to create a relations more than once from a fact to a dimension (first one guilty would be the date dimension). Which one of the above did you implement?

erazon

Posts : 2
Join date : 2012-06-25

Back to top Go down

Role-playing dimensions Empty Re: Role-playing dimensions

Post  ngalemmo Wed Jun 27, 2012 2:00 pm

You would never create additional tables, period.

Solution B is the one most commonly used (usually a prefix to the normal key name). You can consider creating views if you do not have a reasonable BI layer that would resolve the confusion for end users (which is probably the context for Laura's suggestion). If you have an end-user friendly BI layer (full metadata layer), and your dev team is made up of even marginally compentent technical people, don't bother with the views.
ngalemmo
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

http://aginity.com

Back to top Go down

Role-playing dimensions Empty Re: Role-playing dimensions

Post  BoxesAndLines Wed Jun 27, 2012 9:11 pm

I've never even heard of solution A as an option. You are much smarter than the rest of your group. :-)
BoxesAndLines
BoxesAndLines

Posts : 1212
Join date : 2009-02-03
Location : USA

Back to top Go down

Role-playing dimensions Empty Re: Role-playing dimensions

Post  redpoint_13 Mon Jul 02, 2012 8:11 pm

BoxesAndLines wrote:I've never even heard of solution A as an option. You are much smarter than the rest of your group. :-)
The top of this page displays "Kimball Forum", and here is a quote from Mr. Kimball (chapter 5):
"Even though we cannot literally join to a single date dimension table, we can build and administer a single date dimension table behind the scenes. We create the illusion of two independent date tables by using views. We are careful to uniquely label the columns in each of the SQL views. For example, order month should be uniquely labeled to distinguish it from requested ship month. If we don't practice good data housekeeping, we could find ourselves in the uncomfortable position of not being able to tell the columns apart when both are dragged into a report."


redpoint_13

Posts : 1
Join date : 2012-07-02

Back to top Go down

Role-playing dimensions Empty Re: Role-playing dimensions

Post  BoxesAndLines Tue Jul 03, 2012 10:09 am

I think Dr. Kimball and I are in complete agreement. :-)
BoxesAndLines
BoxesAndLines

Posts : 1212
Join date : 2009-02-03
Location : USA

Back to top Go down

Role-playing dimensions Empty Re: Role-playing dimensions

Post  Sponsored content


Sponsored content


Back to top Go down

Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum