How to create a culturally specific calendar in Oracle BI

Despite globalisation, we still live in a multicultural world. It is very important to take this into account as some of the data to be analysed may be dependent on the period of the year it was generated. Not taking these outside factors into account could lead to wrong conclusions for an organisation and therefore bad decisions could be taken.
In this article we will see an example of how important it is to analyse the data based on its’ cultural environment and will present a technical solution to achieve that.

A Definition: Seasonality

We say that there is seasonality in data when the performance of an organisation in a business process is directly related to the period of time it happens. For instance, in western countries, during Christmas, retail sales increase considerably.
In other words, by analysing the data over a long period of time we can identify a changing behaviour in business processes in one or multiple periods of time across the years.

The Challenge

Due to globalisation, we may wrongly assume that the Gregorian calendar (months January → December) is the official and only calendar worldwide. This false assumption may lead to a false perception of reality when analysing data generated in another culture.
Worldwide multiple calendars are used by many cultures (ex. Gregorian, Hijrah, Japanese Imperial, Persian, Thai Buddha…). Our goal is to analyse data based on a calendar different from the Gregorian calendar.

The Scenario

Let’s take a look at the following chart:

Figure 1: Sales analysis using the Gregorian Calendar

By analysing this chart, we can observe that sales in October, November and December are very irregular if we compare them to the sales throughout the rest of the months. However, this analysis is not leading us to any conclusion on the reasons behind this behaviour.

Now let’s take a look at the same chart, this time with some visual aid:

Figure 2: Sales analysis using the Gregorian Calendar (low valued highlighted)

Now we can observe something very interesting: As years go by, the low values move from December to October progressively. What is driving this behaviour? Hint: This data is showing sales in an Arabic country.

In the Arabic culture the official calendar (Hijrah) consists of 354 or 355 days grouped in 12 lunar months. One of these months is called Ramadan. During this month, apart from fasting, people usually work less hours per day instead of the usual 8 hours.

Taking this into account, it is not unusual to see a decrease in the performance of some business processes such as sales.

The Solution

In order to show the appropriate information, we need to go through the following steps:

· Add the Hijrah Calendar columns to the Date dimension table

· Modify the OBI Repository to use these new columns

· Create a report using the new Hijrah calendar columns

· Add the Hijrah Calendar columns to the Date dimension table

· Modify the OBI Repository to use these new columns

· Create a report using the new Hijrah calendar columns

Add the Hijrah Calendar columns to the Date dimension table

We will start adding columns showing the Hijrah calendar information in the Date dimension table (Date_D).

For this purpose, we first need to alter the table to add the new columns:

Next we need to populate these two columns with the appropriate values. This requires a two step process:

1. Modify the calendar in our database session

Execute the following select statement to verify that the change is done:

At this point our database will show dates based on the Hijrah calendar as you can see in the following query:

2. Populate the new columns

Don’t forget to commit your changes!

After this change, we can revert the calendar session and check the data in the DATE_ID table:

Modify the OBI Repository to use these new columns

The steps required by layer are:

· Physical Layer: Import the new physical columns

· Business Model & Mapping Layer: Create logical columns, create a new hierarchy in the Date dimension hierarchy

· Presentation Layer: Add the new presentation columns to a subject area

This is the final view of the repository based on the changes described above (only relevant parts are shown):

Figure 3: Changes in the OBI Repository

Create a report using the new Hijrah calendar columns

At that point you can create a new request in OBI Answers using the new columns containing the Hijrah calendar information, now available in the subject area. The final result, as shown previously, highlights the seasonality of sales in Arabic culture.

Figure 4: Sales analysis using the Hijrah Calendar (highlighting seasonality of sales)

Wrap up

Information is useless if its message is not conveyed properly. Analysing the information in its appropriate context is essential to that purpose. When analysing data from other cultures; consider the seasonality of the data when preparing your calendar. The success or failure of your BI project can be dependent on this adjustment.