November 16, 2008 at 5:36 pm
I'm modelling date dim and I've run into SMALL problem.
Scenario:
Company has brands. Brands have stores. Each brand has business day (time column e.g. 6am). Stores have opening hours. Stores can sell only during opening hours.
Example:
Company: Acme
Brand: AcmeExplosives
Business day: 6am
Store opening hours: on Mon: 08.00 - 17.00; 22.00-04.00
on Thu: 08.00 - 15.00; 17.00-21.00; 23.00-05.00
etc...
(there can't be opening period that includes start of business day e.g. 5.00-7.00)
FactTable (transactional - not snapshot)
dateId,
timeId,
itemId,
...
qty
price
It's easy to model dims for 'physical' date and time, but I'm having problems with business days. Product managers should be able to switch between physical and business days in reports. The easiest way is business day 'projection' to fact table (some marker just like 'orderId') ... but in this situation I can't reuse date, time dims (with all groupings, holidays, events etc). Another possibility is to add to fact table businnesDayStartDateId, businnesDayEndDateId (+ times) that refers to date,time dims ... but it doesn't seem to be perfect (4 additional cols in fact table). Is there any way of showing business day as some kind of grouping based on date, time dims ?
November 16, 2008 at 5:45 pm
Before in this sort of scenario i have created separate Date and Time dimensions - I have also created sometimes a dimension showing offset from GMT/UTC (in hours) - other times, people have been happy with UTC offset as an attribute of store/location
Kind Regards, Will
November 17, 2008 at 5:47 am
I agree with Will. When time is required in your warehouse, it's best to split this into it's own dimension.
November 17, 2008 at 2:36 pm
Thx for replays. In my solution time is separated from date. I've got an idea how to store business day only by reusing date and time but need some feedback. If there's business day 6.00am - 5.59am 'next calendar day' in fact table can be added businessDayStartDateId (quasi surrogate key) so date dim can be reused without any changes and in time dim there should be added new column storing order for hours (included offset)
e.g. (grain is minute):
timeId hour minute businessDayIndex (for business day that starts at 6am)
1 0 0 1080 (approx calc)
2 0 1 1081
...
360 6 0 1
Advantages: no need to do any 'on fly' calculations (add offsets etc), date and time dims can be reused (derived), simplicity (reports, business apps - easy to switch between calendar days and business days), maintenance
Disadvantages: other brand can have business day that starts at different time - what needs adding new column.
What do you think ? Is there better way of doing this ? Any suggestions / concepts ? Thx in advance!
November 17, 2008 at 5:27 pm
Apologies for my ignorance, however despite your additional & edited posts, I am struggling to understand the definition of what "Business Day" is.
A day?
A period of time?
Is it related to a Calendar date/time (but merely offset by a period of time for example.)
I get the impression that it's almost like a calendar (i.e. with a date and time) but only includes certain weekdays etc and is often (but maybe not always??) offset from a regular 24 hour day...
Kind Regards, Will
November 17, 2008 at 5:58 pm
Calendar day is business day that starts at 0.00. Business day in my situation usually has offset.
e.g. comparison of calendar day and business day that starts at 6am (6h offset):
calendar day: 2008-01-01 which is 2008-01-01 00:00 to 2008-01-01 23:59
business day: 2008-01-01 which is 2008-01-01 06:00 to 2008-01-02 05:59
Thx
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply