Multiple Date Values for a Single Fact Row

View previous topic View next topic Go down

Multiple Date Values for a Single Fact Row

Post  bdaniel on Fri Nov 30, 2012 5:35 pm

In modeling a hotel scenario, there can be two date values for a single fact row: when the booking was made and when the stay date occurred.

When modeling this scenario, is it advisable to have
1) a single date table tied to both of these fields (i.e. the fact table joins to the date table twice)
2) or two independent date dimension tables (one for booking date and one for stay date).

3) Or, should there be one table containing both date dimensions, and in the fact table have a date code that relates back to the dimensional table corresponding to the intersection of the booking date and stay night date? This would result in a HUGE date table as there would have to be a host of horizon dates (booking dates) leading up to the stay night date.



Last edited by bdaniel on Mon Dec 03, 2012 4:22 pm; edited 1 time in total

bdaniel

Posts : 3
Join date : 2012-11-30

View user profile

Back to top Go down

Re: Multiple Date Values for a Single Fact Row

Post  ngalemmo on Fri Nov 30, 2012 5:51 pm

Why on earth would you consider two separate date tables? Given any data warehouse would have many, many date references, are you saying there should be dozens of date dimension tables? And what about customer? If someone purchases a gift for another, does that mean two customer tables?

There is no performance advantage to having separate tables. In fact, if anything, it can be a significant impediment as it requires the database to physically read more data than it otherwise has to. Plus, you have to store more data, back up more data, maintain more database objects, etc... You also ignore the fact that a database engine has access to memory which is used, for the most part, to cache data so it doesn't need to read disk again. In other words if it needs data for the 'other' date that happens to be in the same block of data it just read, it doesn't need to read disk. If there are separate tables, this would not happen.
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Multiple Date Values

Post  bdaniel on Fri Nov 30, 2012 9:09 pm

The interval component of modeling data in hospitality, where there can be several post dates for a single stay night date, really makes the data balloon as future dates come into the horizon. This is something I am planning for carefully. I appreciate your input on this subject though. Perhaps it is not as serious an issue as I first believed.

bdaniel

Posts : 3
Join date : 2012-11-30

View user profile

Back to top Go down

Re: Multiple Date Values for a Single Fact Row

Post  hang on Fri Nov 30, 2012 10:25 pm

You need to understand the concept of 'Role Playing Dimension' to be on the same page as Ngalemmo. It comes down to the core principle of dimensional modeling, Dimension Conformance. It is more obvious when a dimension is shared by multiple fact tables than playing multiple roles in a single fact table. To connect to the fact, a role playing dimension behaves like multiple tables in form of multijoin statements, but it should always be a single physical table.

hang

Posts : 528
Join date : 2010-05-07
Location : Brisbane, Australia

View user profile

Back to top Go down

Re: Multiple Date Values for a Single Fact Row

Post  BoxesAndLines on Mon Dec 03, 2012 11:37 am

Option 1. This goes for any dimension. Always role name the foreign key columns when more than one relationship exists.
avatar
BoxesAndLines

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

View user profile

Back to top Go down

Dimensonal Role-Playing and Multiple Date Columns in the Fact Table.

Post  bdaniel on Mon Dec 03, 2012 4:21 pm

I agree with Hang. Dimensional role-playing is likely the answer to my question.

Will use views, but stick with one single physical table. Thank you.

bdaniel

Posts : 3
Join date : 2012-11-30

View user profile

Back to top Go down

Re: Multiple Date Values for a Single Fact Row

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