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

Office Hours

4 posters

Go down

Office Hours Empty Office Hours

Post  LxFx 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...
LxFx
LxFx

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

Back to top Go down

Office Hours Empty Re: Office Hours

Post  LxFx Tue Mar 30, 2010 11:17 am

Nobody?
LxFx
LxFx

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

Back to top Go down

Office Hours Empty Re: Office Hours

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

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

http://aginity.com

Back to top Go down

Office Hours Empty Re: Office Hours

Post  LxFx 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
LxFx
LxFx

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

Back to top Go down

Office Hours Empty Re: Office Hours

Post  BoxesAndLines 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.
BoxesAndLines
BoxesAndLines

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

Back to top Go down

Office Hours Empty Re: Office Hours

Post  fearless_fool 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

Back to top Go down

Office Hours Empty Re: Office Hours

Post  LxFx 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
LxFx
LxFx

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

Back to top Go down

Office Hours Empty Re: Office Hours

Post  fearless_fool 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

Back to top Go down

Office Hours Empty Re: Office Hours

Post  LxFx 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"?
LxFx
LxFx

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

Back to top Go down

Office Hours Empty Re: Office Hours

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

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

http://aginity.com

Back to top Go down

Office Hours Empty Re: Office Hours

Post  LxFx 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...
LxFx
LxFx

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

Back to top Go down

Office Hours Empty Re: Office Hours

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