Office Hours

View previous topic View next topic Go down

Office Hours

Post  LxFx on Fri Mar 26, 2010 4:38 am

Good day!

I am having a question concerning the implementation of Office Hours in a generic Dimensional Model.
I recently started reading the Data Warehouse Toolkit and I just arrived at pg 53 (Retail Schema Extensibility)
where they add a Time dimension to the retail model.
I agree with the fact that a smaller Date dimension + a smaller Time dimension is better than one huge DateTime dimension, but this made me wonder what the best practice would be concerning office hours...

You can put holidays in your DateDim, even weekdays etcetera.. It's also simple to flag each specific moment in the TimeDim as within Office Hours. But what happens when my hours are different for each weekday? There is no link between Date and Time so it seems impossible to implement this.
Would it be correct to add columns to my TimeDim like 'MondayOfficeHours', 'TuesdayOfficeHours', etcetera and flag them appropriately?
Any suggestions?

Thanks for your time!

By the way, there is no specific business case or context here, just a generic retail situation...
avatar
LxFx

Posts : 7
Join date : 2010-03-26
Location : Belgium

View user profile

Back to top Go down

Re: Office Hours

Post  LxFx on Tue Mar 30, 2010 11:17 am

Nobody?
avatar
LxFx

Posts : 7
Join date : 2010-03-26
Location : Belgium

View user profile

Back to top Go down

Re: Office Hours

Post  ngalemmo on Tue Mar 30, 2010 1:09 pm

Do you have an office dimension? It may make sense to put the hours there. But it depends on how you plan to use them.
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: Office Hours

Post  LxFx on Wed Mar 31, 2010 3:00 am

I would like to know if a sale or call (fact) was made during office hours or not.
This simply to be able to define which activities took place during or after work time.
After all, the time dimension is one of the more widely used dimensions, is it not?

Clearly, this is an unusual request, but thanks for your reply ngalemmo
avatar
LxFx

Posts : 7
Join date : 2010-03-26
Location : Belgium

View user profile

Back to top Go down

Re: Office Hours

Post  BoxesAndLines on Wed Mar 31, 2010 8:53 am

Your approach seems fine to me. If all you need to know is whether a call occurred during office hours, you could simply add 1 byte metric to the fact table. Something like OFFICE_HOUR_IND NUMBER(1). Then you could easily sum up office hour calls as well as identify them.
avatar
BoxesAndLines

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

View user profile

Back to top Go down

Re: Office Hours

Post  fearless_fool on Fri Apr 02, 2010 3:22 pm

As a newbie, I have no credentials for a credible reply. But that won't stop me from trying!

Instead of a 24 hour time dimension, could you create a 24*7=168 hour time dimension, one for each day of the week? Each one could be individually flagged as to office hour or not. The amount of extra storage would be trivial, and the logic is simple.

Am I missing something?

-ff

fearless_fool

Posts : 4
Join date : 2010-04-02

View user profile

Back to top Go down

Re: Office Hours

Post  LxFx on Fri Apr 02, 2010 3:36 pm

I suppose that like this, your dimensions time and date are linked and should in fact be merged into a time-date dimension.
It also does not solve the "multiple branches with different office hours" issue.

I'm not that experienced myself, so no reason to worry! I appreciate your cooperation
avatar
LxFx

Posts : 7
Join date : 2010-03-26
Location : Belgium

View user profile

Back to top Go down

Re: Office Hours

Post  fearless_fool on Fri Apr 02, 2010 4:08 pm

I suppose that like this, your dimensions time and date are linked and should in fact be merged into a time-date dimension.
I'm not so sure they should be merged, but it depends on your application. The 168 "Week Hour" dimension repeats reliably. A Date dimension would include likely include columns such as "is_holiday?" (e.g. July 21 = Nationale feestdag). I'm not sure that you'd want to merge them.

It also does not solve the "multiple branches with different office hours" issue.
Ah! Agreed, but that wasn't presented in the original problem statement! If that's the case, adding a bit to your Sales fact table is clearly the way to go.

fearless_fool

Posts : 4
Join date : 2010-04-02

View user profile

Back to top Go down

Re: Office Hours

Post  LxFx on Fri Apr 02, 2010 4:19 pm

It's true, I did not present the branch idea in my original post, although I swear I thought I did..
.. and indeed, it looks like your 168 week hour proposal is a valid solution to my fictive problem.

What exactly do you mean with "adding a bit to the Sales Fact table"?
avatar
LxFx

Posts : 7
Join date : 2010-03-26
Location : Belgium

View user profile

Back to top Go down

Re: Office Hours

Post  ngalemmo on Fri Apr 02, 2010 4:21 pm

Assuming you have an office dimension, a date dimension, and a time dimension then the ultimate solution is to have a factless fact table that is the intersection of the 3 dimensions and a 4th degenerate dimension... a Y/N flag indicating it is an office hour.

This will cover every imaginable situation and avenue of analysis. But is it practical? You tell me.

But, to keep things simple and efficient, you need to put some complexity in the ETL process. So, you need to keep track of the office hours by day by office in either a type 2 office dimension (so you know the date range the hours were in effect) or by some background staging structure.

B&L's suggestion is simple. But if you also need to know the actual office hours for that office and day as it relates to the fact, you can handle it with a simple, and very small, junk dimension (that contains the open and close times) and a little ETL work to figure out what those hours are and load the correct FK.
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: Office Hours

Post  LxFx on Fri Apr 02, 2010 4:36 pm

Some very interesting ideas. I'm glad I found the way to this forum...
My "issues" like this Office Hours situation are probably a result of my compulsion to create a model in which I can not only list facts,
but also all members of a specific level, even if there is no fact for it (yet)...

That's why I try to find another solution instead of the one B&L suggested - which is correct and straightforward nonetheless -
because I would like (to be able) to list all office hours for every specific branch on every specific day.

I guess I'm still looking at DWH from a 3NF perspective...
avatar
LxFx

Posts : 7
Join date : 2010-03-26
Location : Belgium

View user profile

Back to top Go down

Re: Office Hours

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