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.

Shopping Basket

MicroFocus Vertica Analytics Platform delivers speed, scalability, and built-in machine learning that today’s most analytically intensive workloads demand, whether in the Public Clouds, On-Premises, on Hadoop, or any Hybrid combination. Vertica’s SQL Data Warehouse is trusted by the world’s leading data-driven companies, including Cerner, Etsy, Intuit, Uber and more to deliver speed, scale and reliability on mission-critical analytics. Vertica combines the power of a high-performance, massively parallel processing SQL query engine with advanced analytics and machine learning so you can unlock the true potential of your data with no limits and no compromises. We are a certified System Integration and reseller partner of Vertica and have a strategic alliance to develop industry-specific solutions using this Award-winning Columnar Database in the APAC region.

We have extensive experience with the entire product suite having successfully completed over 50 implementations in the USA/Europe/Asia Pacific across different industries and still continue to support a few key customers Globally.

As a Future-ready and complete, enterprise-grade analytics platform, Pyramid is a compelling option for organizations. Pyramid offers an integrated suite for modern Analytics and Business Intelligence requirements. It has a broad range of analytical capabilities, including data wrangling, ad hoc analysis, interactive visualization, analytic dashboards, mobile capabilities and collaboration in a governed infrastructure. It also features an integrated workflow for system-of-record reporting. Its Augmented features such as Smart Discovery, Smart Reporting, Ask Pyramid (NLQ), AI-driven modelling, automatic visualizations and dynamic content offer powerful insights to all users, regardless of skill level and the adaptive augmented analytics platform covers the entire data life cycle out-of-the-box, from ML-based data preparation to automated insights and automated ML model building. Pyramid is especially useful for the customer who is in urgent need to get more value out of their existing SAP BW and SAP HANA investments. Without any data extraction or duplication, Pyramid offers best-in-class functionality and performance that preserves the security and governance inherent in the SAP platform. We are a Strategic System Integration and Reseller partner of Pyramid Analytics.