Extending Time Intelligence Functionalities
Let us learn to add an extended time intelligence to the date dimension in Pyramid Analytics. To begin, let us go through an overview of the date tables.
A traditional date table will have information about the date, time, week, month, week, year, and financial date information as well. But most tables need previous year stats in their table. Pyramid Analytics has a lot of features, but sometimes we have to add the above features. Now to add these features, we need some extra implementations depending on the storage capacity that you have.
So you must be thinking about how to extend the time intelligence functionalities. Let us look at some scenarios and their solutions to reach your goals.
Scenario 1 and solution: If a date dimension is updated daily function, it won’t have a future date option. Therefore, we should look for the table with futuristic dates and then create a separate table or view to achieve up to today dates. Now let’s get into the solution.
To begin, we can create Formula as a list object using “standard list” and then filter it using “last”. In the table, the last with value 1 will represent the current period, and the “first” filter can be used to view the first date in the past date table.
Scenario 2 and solution: Now, you see a scenario where the date table has no up-to-today function. What does that mean? It means that the table has a future date, but the sales value is set up to today.
To counter this, we can use the case measures value to identify the current period. We can use a “non-empty” filter to filter dates that are up to today. This filter can be followed by using “last” or “first” with or without a parameter to achieve the desired period or range.
Scenario 3 and solution: Now, let us look at a scenario where the tables have futuristic dates for sales value, forecast value, and future dates. So, how do we deal with the current time period or last time period situations?
One solution is to use a pyramid solution system date. But in order to use it, we have to make sure that the system date and system date of the pyramid are in sync. For this, we need a formula using either the PQL ( Pyramid Query Language) or MDX. The syntax for both languages is the same.
StrToMemeber([date].[month Name], DateFormat( DateTime(), “MMM-yy”) )
There is another generic but well-suited solution for this scenario. We can create a view at the DB level or model level using the SQL script. This can also be achieved by a date table that refreshes the date using ETL. Also, we should make sure to create the view table with Yes/No flags against all periods like previous, new, current, etc.
CASE
WHEN (dim_date.date = ((now())::date – '1 day'::interval)) THEN 'True'::text ELSE 'False'::text
END AS yesterday,
Same logic can be applied to other datetime values as per the need.