Time Dimension

[intapiuser]

Analytical Need 

Many times we would like to deep dive into our data and view it in a more granular fashion to conclude from it.
Certain business needs might require us to use date-time hierarchies to help us understand performance trends such as Sales by month/week/day/hour and others.
On the date-time perspective, we can drill into a minute level perspective.

Modeling Challenge

In our use case, the sales manager wants to increase his team's productivity and to answer the following question: What are the total sales by day and time of the day (minute granularity)?

Potentially, it will result in a very large DateTime dimension which is not a scalable solution.

Solution

To answer this question we will need to model our ElastiCube Accordingly.
Our 'Fact Sales' table has the following structure:

To analyze the total sales by day and time of the day, we will need to create the relevant dimensions, where we will have a distinct selection of each of the dates and times.

One option would be to create a DateTime dimension which will hold a distinct selection of all the different dates+times.
As the highest granularity is minutes, we could potentially get a very big dimension which is a combination of [total distinct days]*1440 minutes a day.

To avoid holding a big dimension for that, we can separate that into two dimensions: one to hold the distinct days and the other to hold the total minutes in a day.

Steps:

1) First, we will need to transform each 'DateTime' to a minute granularity on our 'Fact Sales' table to be used as a foreign key.
The following formula first returns only the time in the 'DateTime' field and sets its seconds to '00':

The next step is to make sure we normalize the date itself to a one selected date. We will use 01/01/2000.

For copy purposes:

todatetime('2000-01-01 ' + LEFT(Right(tostring(Date),15),6) + '00')

Here is the result:

2)To create our Time Dimension we will need to pick a distinct selection of our newly normalized foreign key in the Fact Sales table:

This will create a dimension which has 1440 rows at most as the granularity is defined by the number of minutes in a day.

3) Next, we need to create a relationship between the newly created dimension to our Fact Sales:

We are now able to create a daily/hourly and other date-time hierarchies of our Sales data.

Important note: As we have separated our DateTime field into two dimensions, the suggested hierarchy in Sisense web application will not work, as each day instance was transformed to midnight (YYYY/MM/DD 12:00:00AM) and we lost the minute granularity.
For that, we need to create the hierarchy again from the hierarchy section from the Dim_Date's Year level all the way to the Dim_Time's Minute level:

 That's it!

Note: If you wish to only have the date and the hour (remove the minutes) you can use this code:

todatetime(toString(tostring(getYear(date)) + '-' + tostring(getMonth(date)) + '-' + tostring(getDay(date)) + ' ' + toString(getHour(date)) + ':00'))