SAP BW and HANA users typically have massive amounts of data for analysis. And most analytical processes include date and time data. The challenge is finding the right analytical tools that let users build time-based calculations (on their own) using BW or Hana directly.
As explained in my previous blogs in this series (Collaborative analytics for SAP BW and HANA, Self-service BI directly on SAP BW, and Real self-service BI for SAP), the flexibility of Pyramid enables the direct querying of SAP data, bringing back results instantaneously from the either BW or HANA without requiring the extraction of the raw data and reloading of it into a proprietary database. Coupled with extremely powerful and sophisticated calculation options (along with time-based logic), Pyramid allows end users to get real self-service analytics with time intelligence working directly on SAP.
The key challenges of time calculations
Date-time groupings and “buckets”
The first and usually simplest component of time intelligence is to view amounts grouped in date “buckets” or periods. We can usually achieve this after identifying the existing date-time field on a data set—which is usually on the “grain,” or transactional detail of the stored data in the database. Once we have this date element (or elements if there are multiple dates), we can use time as a key element in all reporting.
While SAP BW InfoCubes and HANA Views often have the dates pre-bucketed for users, it’s possible that the user needs to make adjustments or build alternative buckets to those presented in the underlying data models in SAP.
To illustrate this, consider a grid of sale amounts per transactional date that has been converted to a column chart.
Seeing the data in this example at the individual day level is too granular. We need to add logical super groupings of dates and times to allow the reporting to show more useful period buckets. Using our example, it is difficult to discern performance over the course of the year. One obvious approach is to total the results by month, which we solve by creating a separate “bucket” for each month and summing all sales by each month.
Next, let’s assume we want to see the sales totals for 2017 and compare them to 2018. Here, not only do we want to see the data bucketed into annual figures, but we also want to see the absolute difference and the percentage change between the annual numbers.
This is the second component of time intelligence: creating calculations over a range of dates and comparing them against previous periods.
Often, this level of calculation is not included in the SAP BW InfoCube or HANA View. This is often the case because the exact calculation specification is best suited to the specific report—not to an entire data model. Historically, this type of problem was often solved with the now deprecated BEX reporting tool. However, BEX is far from self-service and accessible, and still poses significant challenges around report and data usage.
The above scenarios cut to the heart of what most analyses need to repeatedly do: visualize data over time and compare time periods. However, what seems a simple task can be difficult with the wrong analytics tool. This is compounded with SAP data stacks, since most tools that can perform some or all these functions require the data to be pulled out of BW or HANA first. So, while time intelligence is fixed in one arena, governance, security, data silos and a deployment mess is created in another.
A perfect example of this is the selection of a date in a parameter field, followed by calculations of year-to-date and month-to-date totals, based on the selected date. Qlik, Tableau, and Power BI are not able to translate this requirement into the required MDX for SAP BW or SQL/MDX for SAP HANA, forcing a total duplication of data in order to solve this requirement.
Pyramid’s time intelligence armada
When combined with Pyramid’s direct query capabilities, Pyramid’s array of highly effective and user-friendly time intelligence features is a game-changer. What’s more, these capabilities, made available via point-and-click options in a self-service usage model, work seamlessly for SAP BW and HANA data sources, covering the basics all the way through to highly sophisticated calculations and functionality.
The logic is built using SAP’s native querying languages, and it’s executed on the SAP server—so it produces both a secured and accurate result without extracting or duplicating data.
For more on Pyramid’s solution for date-time grouping and time buckets on all supported data sources including SAP, see the additional blogs below:
SAP and Pyramid Analytics
Real self-service BI for SAP
Self-service BI directly on SAP BW
Collaborative analytics for SAP BW and HANA
Time intelligence for SAP BW and HANA
Running Totals Made Easy for SAP HANA and BW
Dashboards and report bursting directly off SAP BW and HANA
Realizing the power of parameters in SAP BW and HANA