1

We have a requirement to come up with a strategy to show Sales revenue data weighted by dates differently on different schedules.

We currently have a FactSales table with a grain of one row per order with the measure of sales amount. We have separate DimDate and DimTime dimensions,and a DimBusinessUnit dimension with one row for each entity within the organization.

In DimDate we have a flag for the major US holidays so we know reduced sales revenue may be expected. This flag would apply globally.

The ask is that different business units might have slow revenue days. For example, Monday's might be slow in one business unit, and Friday's slow in another. For analysis it is desireable to capture these different schedules with a flag or a weighting.

Ultimately this probably be reflected as a projected sales amount in a calculated measure.

How can I best add this weighting? Does it belong in the Date dimension, Business Unit dimension, or maybe a degenerate dimension in the Fact table, or something else altogether?

K99ja04
  • 11
  • 3

2 Answers2

0

The DimDate is probably not a good place to keep this information, as each Business Unit (BU) may have a different schedule, so quite possibly you will have to have a flag on each of the dates per a combination of BU and a slow day. So for example if BU1 and BU2 has a slow day on Monday, each Monday in your DimDate will have to have a way showing that it's slow for BU1 and BU2.

The Dimension BU, might be a better place, as schedule is specific to each of the unit. So you may opt for extending your dim by adding 7 days as an attributes and flag them as slow or not using for example false or true flags. You could also have one attribute with the bit mask i.e. 0100000 where position of the value corresponds to the day i.e. M T W T F S S and 0 is not slow and 1 is slow, so in this example T is a slow day.

This will also allow you to trace a history if you whish selecting relevant SCD process.

Another option may be a separate Dimension i.e. DimSchedule and Factless Fact Table.

http://www.kimballgroup.com/data-warehouse-business-intelligence-resources/kimball-techniques/dimensional-modeling-techniques/factless-fact-table/

I hope this helps.

Dariusz Bielak
  • 415
  • 2
  • 7
  • 1
    7 days of flags is fine, but not a bit mask. It would not translate well into end-user tools. – Ron Dunn Apr 07 '16 at 12:05
  • True, bit mask is an example of a packed attribute and its something which I also would avoid. However question was around possible solutions, and bit mask is certainly one of them. – Dariusz Bielak Apr 07 '16 at 14:06
0

Your situation seems to be the same as the Multiple National Calendars problem described by Kimball:

http://www.kimballgroup.com/1998/12/think-globally-act-locally/

Where Kimball is describing holidays in the left-most table, you could also add a "slow day" flag.

Ron Dunn
  • 2,971
  • 20
  • 27