Time intelligence for SAP BW and HANA

April 23, 2020 | David Gordon

Last updated: February 16, 2023

Pyramid is an official member of the SAP® PartnerEdge® open ecosystem and is fully certified by SAP on SAP BW/4HANA, SAP HANA, and SAP Netweaver. The Pyramid Platform is also available on the SAP Store.

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 data 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 data 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.

Date-time calculations

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:

Date-time grouping capabilities let users quickly “bucket” time intelligence periods.
How to bucket time intelligence periods to quickly visualize grouping capabilities.
Custom date-time calculations amplify Pyramid’s dynamic time intelligence capabilities.
Date lists and ranges give users pinpoint control over date-time parameters within Pyramid.

SAP and Pyramid Analytics

Pyramid solves this entire problem with a complete solution that offers real self-service working directly on SAP BW and HANA. How? By delivering best-in-class functionality and performance on SAP BW without extracting or duplicating data, Pyramid preserves the full analytic power of the SAP engines, as well as the inherent security and governance.

Please explore our Pyramid + SAP Blog Series to learn how Pyramid supports our customers on SAP and setup a demo today.

Each post contains specific examples to illustrate key functionality: