Date/Time Dim - Manufactur​ing business process

I am modeling the data mart for the manufacturing function of a large consumer products coompany. My question is about modeling of date/time dimension in the following context:

The lowest time grain is shift. After discussion with the business users, I came up with the following time dimension structure:

Column	Data type	Constraint(s)	Comments
ShiftSK	int	PK
ShiftCode	nvarchar(1)	Unique Key	One char shift code; usually numeric values, e.g. 1, 2, etc.
ShiftModel	nvarchar(1)		2 = 2 shift model 3 = 3 shift model The two models may run in parallel. This scheme varies from one factory to another and also at different times of the year.
Date	date
Factory	nvarchar(5)
MonthNumber	smallint	Not Null
MonthName	nvarchar(9)	Not Null
Quarter	nchar(7)	Not Null
DayOfWeek	smallint	Not Null
DayOfMonth	smallint	Not Null
DayOfQuarter	smallint	Not Null
DayOfYear	int	Not Null
WeekOfYear	smallint	Not Null
IsWorkDay	bit	Not Null
DayIndicator	bit	Not Null	Please refer to the 2nd option below
And few more audit and shift related less important columns…

The problem I faced is that, different measures (to calculate a KPI) are coming at different time granularity from the source systems. Let’s take an example: Reject Rate KPI. The formula is:

Reject Rate % = { A / (A + B) } * 100 %

Where, A = Reject Weight in kg and B = Good Production Volume in kg

Rejection measurements are done at the end of the day and hence the time granularity of measure A is day; whereas, production volume measurements are done after every shift and it comes from adifferent source system; hence the time granularity of measure B is shift. Now, it’s obvious that in order to calculate the KPI, B has to be aggregated at day level and the KPI will also be calculated at the day level (lowest). [FYI, KPI calculations and aggregations along different dimensions are being handled using MOLAP cube.]

At this point, I can think of the following options regarding Time dimension design:

1. Have different Time dimensions: DIM1 – at the shift granularity and DIM2 – at day granularity. B will be stored in a fact table (FACT1) that is connected to DIM1 and A will be stored in a fact table (FACT2) that is connected to DIM2. Either a view can be used to aggregate B at day level or FACT2 can store the aggregated value of B at day level. The rest of the work is done in the cube.

2. Have only one time dimension and one fact table at the shift granularity. A shift is uniquely identified by (a) Date, (b) Shift model (c) Shift code and (d) Factory (please refer to the table structure above). There can be 5 shifts (max) per day (3 from 3-shift model and 2 from 2-shift model); one of these shifts (e.g. 3rd shift of 3-shift model) will be used to identify a day level grain and measures at daily grain in fact table will be connected to the time dimension via the surrogate key of that shift. For this to happen we need an additional flag in the time dimension (viz, DayIndicator) because two shiftsbelonging to two shift models can start/end at the same time on a given day. Both the measures will be stored in a single fact table. The rest is more or less the same as the previous approach; the aggregation of measure B has to be done anyway to bring both the measures at the same level - day.

I believe both of these approaches will work. However, I need to know what the best practice is. If there is/are even better approach(es), kindly let me know. Looking forward to your valuable opinion.

Regards
Kajal

I would put shift and factory in a different dimension table. By adding them to the date dimension it's no longer a date dimension. It's more of a junk of mini dimension. Date dimensions are different from regular dimensions. By adding the Shift and Factory, you are reducing the usefulness of the date dimension.

There has to be instances in which information for the shift and factory is presented without regard to the date.

Can the Rejection Weights be allocated at the Shift level at all? E.g. if the end of the day Rejection Weight is 100 kg, is there any way you can distribute this Rejection Weight across shifts: Shift A - 10kg, Shift B - 50kg, Shift C - 40kg?

gsidhu wrote:Can the Rejection Weights be allocated at the Shift level at all? E.g. if the end of the day Rejection Weight is 100 kg, is there any way you can distribute this Rejection Weight across shifts: Shift A - 10kg, Shift B - 50kg, Shift C - 40kg?

If shift is a dimension of the fact, you can do whatever you want provided it makes sense.

The thing is, as far as I can understand, measure A is at a daily level, while measure B is at a shift level. So the overall formula: Reject Rate % = { A / (A + B) } * 100 % can only be calculated at the daily level.

If the fact is at the shift level, it may not make sense to store measure A in that fact unless measure A could be broken down to the shift level. That's why I asked the question, whether or not it is possible to distribute measure A, Rejection Weight across shifts. Now one alternative I can think of is to maintain separate facts for daily level and shift level. The fact at the shift level won't have measure A in it, but will be connected to Shift dimension, and the fact at the daily level will have measure A as well as sum of measure B for the entire day. This fact, however, won't have Shift as a dimension. So, the formula for Reject Rate can be calculated using the fact with the daily grain and the shift level fact could be used for reports requiring drill down to individual shifts.

Looking forward to hear your thoughts on this.

You do not want to mix grains in a fact table. There are three options to resolve it: 1. Allocate the higher level fact to the more detailed level; 2. Aggregate the lower detail fact to the higher level; or 3. have two fact tables.

If you must have the shift level measures, your options are limited to #1 or #3. Wither allocating the higher level measures makes sense is something you need to figure out. Generally speaking, performing allocations is often a very messy business and may take a while to get consensus on the method. #3 is usually the politically savvy choice.

To Jeff:
There is already an org dimension and Factory is part of that org hierarchy. In Shift (time dimension), I had to put the Factory identifier because shift models, names, etc. are factory specific and this datamart is going to cater all the factories across the world.

To gsidhu and ngalemmo:
Thanks for your points of view. Allocating the reject weights at shift level is not an option. So, option 1 is ruled out. Option 3 seems to be the only viable option, i.e. having separate fact tables, one at shift grain and the other at daily grain.

Question regarding the design of the time dimension. Actually, this was my original question. Whether to have one time dimension or two time dimensions. Please let me know your opinions.

Best Regards
Kajal

You will have one Date dimension. Shift will be a separate dimension as Jeff initially mentioned.

Your Date dimension will have all the attributes you had listed except for Shift and Factory related fields.

Your Shift dimension will look like this:
Shift Key
Shift Model
Shift Code

Your fact table at the shift level will be:
1)
Shift key
Organization Key
Date Key
Good Production Volume
etc.

Your fact table at the daily level will be:
2)
Organization Key
Date Key
Reject Weight
Daily Good Production Volume (Sum of Good Production Volume for all shifts for the day)
etc.

If there is a fixed number of shifts during a day, you may consider splitting Production Volume across all shifts in this fact, e.g.:
3)
Organization key
Date Key
Reject Weight
Shift 1 Key
Shift 1 Good Production Volume
Shift 2 Key
Shift 2 Good Production Volume
Shift 3 Key
Shift 3 Good Production Volume
Daily Good Production Volume
etc.

The table is still at daily level and you may be able to do without fact table 1) but I'm not much in favor of this design because if the number of shifts change in the future, that will require restructuring of this table. I just put it here in case this is something you may find of interest.

So, to conclude, you only need one Date dimension. However, if you wish to store start and end times of each shift, you may consider adding a Time of Day dimension to your schema as well. Your fact will then have some additional columns:

Shift Start Date Key
Shift Start Time Key
Shift End Date Key
Shift End Time Key

But this is only if you need to store this information.

A few points:
1. Except Factory code itself, there is no other factory related fields in shift table. This is required because the shift calendar (shift code, number of shifts, shift model, shift timing) varies from one factory to another.

2. The current design is following:
a) there is a shift dimension - this cannot be avoided, because shift is so important in terms of production floor processes; Also, for any KPI, they want to drill down to the level of shift (wherever possible).
b) there is a separate date dimension - however shift dimension contains all the date fields as well; so there is no snow-flaking.
c) production volume is stored in shift level fact.
d) reject weight is stored in day level fact (i.e. different fact table).
e) the aggregation (from shift level to day level) is done using a view and KPI is calculated in the cube at day level.

3. Your suggestion #3 won't work because, as mentioned above, number shifts and models vary from one factory to another.

Thanks for sharing your point of view.

BR - Kajal

If shift is factory specific, you need factory code to differentiate the shift definitions. But you should not repeat all the date attributes in the shift dimension, as it will explode to much bigger size, especially with factory code in it. It's like time of the day dimension, and there is no snowflaking between date and shift dimensions as you always have date key and shift key in your fact table.