Office Hours

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

Nobody?

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.

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

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.

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

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

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.

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

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.

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