Role-playing dimensions

View previous topic View next topic Go down

Role-playing dimensions

Post  erazon on 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

View user profile

Back to top Go down

Re: Role-playing dimensions

Post  ngalemmo on 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.
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: Role-playing dimensions

Post  BoxesAndLines on 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. :-)
avatar
BoxesAndLines

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

View user profile

Back to top Go down

Re: Role-playing dimensions

Post  redpoint_13 on 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

View user profile

Back to top Go down

Re: Role-playing dimensions

Post  BoxesAndLines on Tue Jul 03, 2012 10:09 am

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

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

View user profile

Back to top Go down

Re: Role-playing dimensions

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top

- Similar topics

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