Oracle BI: A week in a life

Recently, during an implementation of Oracle BI, I was asked to provide an insight of expected receipts of goods per week. The logistic manager needed this report to fulfil his operational planning of human resources in his warehouse and for agreements with carriers on times of arrival over the day.

This straight-forward question soon proved “the devil is in the details” to be true once more. Before you know it I (again) was this “annoying consultant, only capable of asking those awkward questions”.

On the way back home I was a bit stunned. How can something so evident as a simple list containing data per week be root for such a fundamental discussion. This inspired me to write this blog: Not all weeks are the same…

Weeks in the world

A company reporting within Europe sort of gets away with it. But as soon as a company operates and reports on a global level the differences in definition start to interfere. Across the globe weeks will start at different days, varying from Saturday through Monday. Numbering weeks will be subdued to this difference in definition.

What about week number 1? What is week number 1 when we closely look at it? In Europe week number 1 is the week containing four days in January. In the United States week number 1 is the week in which the 1st of January sits.

So in Europe: What if the 1st of January is actually under week 52 or 53? Does the 1st of January actually belong to the previous year?
So in the United States: What if the 31st of December is actually also in week 1? Does the 31st of December actually belong to the next year?

What about the transition between one month or quarter to another? Does for instance the 1st of April 2017 belong to quarter 1 or quarter 2?

Hierarchy time and comparisons with prior years

Years ago during an implementation of Oracle BI I have bumped my head quite hard on week as part of the dimension Time, including its’ hierarchy. The customer asked me to embed the level Week. Technically the construction and development were done quick and easy.

This is not the way

This hierarchy functioned perfectly well in drill-down. The user was able to zoom-in from any level onto the underlying structure. Results were less encouraging when to the measure Quantity Supplied the column Prior Year was added. The figures were all over the place, except for the right one. Also when from single days the information was rolled up to higher levels, things didn’t turn out right. The 2nd of April turned out to end up in Month 3 and Quarter 1.

Now this is an open door to me, but at the time this was to me (as well as to the customer) a very valuable lesson learned.

Pitfall

The level Week turned out to be the catch and pitfall. Weeks in the centre of the Month were okay, but that was purely based on luck. Weeks at the crossover between months and years really revealed the problem in its’ worst appearance.. Week 13 was in Quarter 1 the one year and in Quarter 2 the next year. Week 53 did occur in one year, but not in the next. The date the 1st of January was sometimes part of the last week of the previous year. To illustrate this: Where should we best put the 1st of April 2017 in the calendar picture to the right? Week 13 and therefore March? Try and explain that.

When the work week confines to business hours comparing last week (five day period) with that same period last year, is then likely to accidentally include a weekend. Thus five days of data this year are compared to three days of data last year.

To cut things short: A lions’ den was created.

Then what?

How do we basically define a week? What is that? Its’ definition might even change per company. Running a 24×7 business does not treat the weekend differently. Companies that operate during business hours only, identify the week as the period between Monday through Friday as the relevant time frame.

Of course it surely is possible to use the week as a level of aggregation. To help you do so, this list hands some ground rules to take into account, to prevent pitfalls and to create the possibility:

• Never put the Week as a level in the hierarchy on the dimension Time.
• Use the Week as an attribute of the dimension Time for aggregation, but never try to create a Year Ago, or PY column next to this on this same time level.
• Unify the Week when used for reporting across the world. It demands one single definition being either the American or European. Unify the start of the week (Sunday or Monday) and unify Week number 1.
• Try avoiding weeks and rather work with the passed seven days as a reporting time frame. This will default a date from and a date through. This can perfectly be compared with the same period last year. Both time frames will contain 2 days of weekend, thus supplying a comparable dataset. Take for granted that this may or may not include a bank holiday.
• Create so called bins aggregating periods of 7 days, or set a formula imitating a week number. Especially at 24×7 companies this should supply a useful time frame. Combine name or number of the day in the week and you have something meaningful.
• At the earlier mentioned implementation one solution was found in a smart dashboard prompt. This prompt defaults the dates of the passed work week. It selects all data between Monday and Friday last week by default.

And this is the way to do it: Oracle BI – dimension Time, containing a hierarchy without the level Week, but as an attribute of the dimension

In the end of course both customers and Oracle BI implementations where serviced with a dashboard pages showing deliveries to customers per week (see example below). The logistics manager was provided with a dashboard showing the expected receipt for the future weeks. These weeks however were unified to European standard. Communication with the supplier takes place about the Date Requested and not about the Week Requested. Chances are that a purchase order handed to an American supplier will arrive at the door a week sooner than expected.

Oracle BI dashboard – expected receipt of goods