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.
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:
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.
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 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:
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'))