Hi,

(I posted a similar message to this yesterday in the
sqlserver.datawarehouse group. I should have really targeted or at
least cross-posted to this group. It seems more appropriate. Apologies
for that.)

Has anybody come across or implemented a time dimension as follows and
if so are there any comments?

I am designing a time dimension for a reportng system that requires
work shift-based reporting.

There are a few points which complicate things.
1. Different production lines can have different shift starts. This is
so that the shift transitions are smoothed out.
2. Different production lines can have different shift regimes. A shift
regime is the shift structure during the course of 1 week. For example:

(a) 4 shift teams working 12hrs x 2 shifts x 7 days
(b) 4 shift teams working 12hrs x 2 shifts x 6 days
(c) 3 shift teams working 8hrs x 3 shifts x 5 days
(c) 1 shift team working 8hrs x 1 shift x 5 days


Shift starts and shift regimes can change independently of each other.
Production line number is part of the time dimension.

I have modelled this time dimension meta-data and I am confident that I
can generate the time dimension data, 1 week in advance at a time, i.e.
up to the end of the following weeks shift for each line.

Each production event on any individual line will fall into 1 and only
1 time bin or time bucket and should roll-up correctly to 'day' (i.e. a
24 period not necessarily matching a calendar day) and so forth to
shift-team to week to month, etc, all following ISO weeks.

I would appreciate any feeback or advice on such an approach.

Regards

Liam Caffrey

Liam,

Wow, I'm gald it's you and not me building this!

Whether this is a good dimensional design really depends on how your
users want to view the data. You sometimes find you have to "bend" the
rules of what would normally be considered good dimensional design in
order to present the data in a certain way.

So bearing in mind that I do not know your full requirements - I will
offer the following observation.

If it were me, I would normally try to split up different logical
elements into their own dimensions. In your circumstance I would have
thought time, production line and shift could be 3 separate dimensions
(sometimes I even split date and time of day).

Having these items in separate dimensions gives you the flexibility to
isolate factors when you are exploring the data. So if you were
examining a rise in defect rates you could look at it by production line
or by time of day or by date or by shift.

HTH

--
Regards
Darren Gosbell [MCSD]
<dgosbell_at_yahoo_dot_com>
Blog: http://www.geekswithblogs.net/darrengosbell

Darren,

Thank you for your reply.

Line Number will have its own separate dimension. However, the shift
start is a function of the line number. Even though line number is also
part of the time dimension, it doesn't need to be part of the time
hierarchy (i.e. I still want to roll up data from all lines to week 48,
for example). Does that make sense? Does that even work? I think it
should.

I did consider having two time dimensions - one based on shift starts
and one based on the normal calendar. The calendar view is meaningless.
Nobody counts anything from midnight to midnight, they go shift start
to shift start.

Here is an example of the data generated automatically for next week's
shift.
(For clarity, not all columns are reproduced, e.g. month, quarter,
abbreviations, etc)

TimeID Line Year Week Shift Team Day Shift Start Shift End
1001 7 2005 41 Nights_1 Mon 2005-10-09 19:30:00 2005-10-10 07:30:00
1002 7 2005 41 Days___1 Mon 2005-10-10 07:30:00 2005-10-10 19:30:00
1003 7 2005 41 Nights_1 Tue 2005-10-10 19:30:00 2005-10-11 07:30:00
1004 7 2005 41 Days___1 Tue 2005-10-11 07:30:00 2005-10-11 19:30:00
1005 7 2005 41 Nights_1 Wed 2005-10-11 19:30:00 2005-10-12 07:30:00
1006 7 2005 41 Days___1 Wed 2005-10-12 07:30:00 2005-10-12 19:30:00
1007 7 2005 41 Nights_2 Thu 2005-10-12 19:30:00 2005-10-13 07:30:00
1008 7 2005 41 Days___2 Thu 2005-10-13 07:30:00 2005-10-13 19:30:00
1009 7 2005 41 Nights_2 Fri 2005-10-13 19:30:00 2005-10-14 07:30:00
1010 7 2005 41 Days___2 Fri 2005-10-14 07:30:00 2005-10-14 19:30:00
1011 7 2005 41 Nights_2 Sat 2005-10-14 19:30:00 2005-10-15 07:30:00
1012 7 2005 41 Days___2 Sat 2005-10-15 07:30:00 2005-10-15 19:30:00
1013 7 2005 41 Overtime Sun 2005-10-15 19:30:00 2005-10-16 19:30:00

This is a 24x6 regime, 4 shift teams working 3 x 12 hours shifts with
people clocking in for overtime on Sunday as it suits.

You can see that Monday really starts at 8pm on Sunday evening!!!
Consequently Week 41 (ISO week) starts at the same time.
Months follow the ISO calendar as would quarters and years. Obviously
lower date bounds are inclusive and upper bounds are exclusive. Great
fun, eh?

If the worst comes to the worst, i.e. they change the shift regime to
some complete infernal mess that is beyond modelling or coding well
then the data can be created by hand in advance and loaded.

Is this really going to the nth degree or is it perfectely normal?

Regards

Liam

Hi Liam,

It sounds like you have thought things through pretty well. The main
reason that people build a Data Warehouses is to provide a consistent
view of the business that can be queried with reasonable speed.

One of the hardest things with a Data Warehouse is to define the
structure to cope with Ad-Hoc queries while still providing speed and a
consistent view of the business.

I don't know if there is such a thing as "perfectly normal" in the Olap/
Data Warhouse world. In fact when you are building the data warehouse
you need to de-normalize things. <grin> (sorry could not help myself)

But seriously, you need to know how the business works and what makes
sense to them. It sounds like the line is an attribute of the shift so
it makes sense to have the two associated. And provided that you also
have a separate line dimension (either a real or virtual dimension) that
will give the users the flexibility to analyse things either:

a) for a shift, over a range of lines
b) for a line, over a range of shifts

One of the things that prompted me to respond initially was that it
looked like you may have been linking shift and line, which would not
have let you easily analyze them separately. But it sounds like you have
everything under control.

If you are still not sure of anything, probably the next step would be
to mock up a small sample, either using some made up data or a sub-set
of the real data. If you have a small set of data that you can re-
process in a few minutes you can try out a few things and see how they
work.

--
Regards
Darren Gosbell [MCSD]
<dgosbell_at_yahoo_dot_com>
Blog: http://www.geekswithblogs.net/darrengosbell