Running totals made easy for SAP HANA and BW

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.

Code-free calculations

What differentiates modern self-service BI tools from their legacy counterparts is their “point and click” and “drag and drop” functionality, shielding users from needing deeper technical expertise, such as writing code.

Third-party tools that profess to offer more mature self-service capabilities cannot fully work directly on SAP BW and HANA. They force users to extract, reload, and duplicate the data, thereby introducing data governance and security headaches.

The challenge, then, is to find a real self-service analytics platform with requisite functional depth that works directly on SAP BW and HANA. Finding a platform that can build calculation logic beyond ultra-simplistic mathematics is even more difficult.

Business case

Consider the following business problem: a business user is analyzing monthly sales data and wants to track when important milestones have been reached. In order to achieve this, she would like to see running totals of sales in her SAP HANA view indicating when a $1M milestone was achieved. Due to the ad-hoc discovery nature of her investigation, she might also want to track this on different levels of granularity, such as by quarter, by month, or by week.

With Pyramid, a user can use a direct query on SAP HANA to query and retrieve the results below in a simple matrix grid in three clicks without having to extract the data into another database.

While this information provides sales information by month and year in an easy-to-follow grid, the user is not able to see when the $1M (or $2M and $3M) milestone was achieved. She needs the table to display running or cumulative totals to achieve that outcome.

Running totals in a single click

Creating running totals in many BI applications is often part of an ad-hoc reporting exercise that requires finicky coding and depends on developers and the associated software development lifecycle.

It’s a snap in Pyramid. With a single right click on any sales metric, a user can display dynamic “Cumulative Totals”—no code or data extractions required—on top of any query processed on SAP BW or HANA. The task doesn’t require the user to re-design the InfoCube or write highly technical BEx queries.

And when you swap out months or any other hierarchy—or add additional hierarchies—the running total is automatically recalculated. No extra code or steps is required. Now the user can easily see the key month targets highlighted below.

How do other tools compare?

On less sophisticated platforms, each calculation will require the user to write code specific to that combination of dimensions and measures she wants to view in her visual or report. This is normally beyond the capabilities of most consumers, and even some proficient end-users will require assistance from an experienced developer. And this doesn’t even address the headache of connecting to SAP BW or HANA to get the data in the first place! For many BI tools, this inefficiency (across specifications, documentation, development, testing, etc.) is quite common.

In our example, the developer will need to create three different variables for company, salesman, and branch, each with three different time options for quarter, month, and week. That’s nine separate variables, each requiring a separate code. Yikes!

Just for color, here’s a sample of DAX code required to calculate running totals in Power BI (remember, the user would have to copy and adjust this calculation nine times):

At this point, business users will either spend a large amount of time creating all the variables and code required for this solution or decide that it is not worth the effort. When they reach this point, the most common alternative is Excel. And when users resort to Excel, it effectively defeats the purpose of having a centralized enterprise analytics and reporting strategy. Hello, data silos!

Conclusion: With Pyramid, calculate without code

With Pyramid, users can create advanced calculations and logic that work directly on SAP BW and HANA. The logic, driven by a self-service paradigm, covers anything from basic calculations to sophisticated logical expressions.

To view Pyramid’s extremely powerful code-free solutions for other common business problems on any data source, including SAP BW and SAP HANA, see my previous blogs:

• Pyramid’s powerful, simple, and code-free tool to uncover 80/20 insights
• Code-free calculations for commonly used metrics

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. Each post contains specific examples to illustrate key functionality:

1. Real self-service BI for SAP
2. Self-service BI directly on SAP BW
3. Collaborative analytics for SAP BW and HANA
4. Time intelligence for SAP BW and HANA
5. Running Totals Made Easy for SAP HANA and BW
6. Dashboards and report bursting directly off SAP BW and HANA
7. Realizing the power of parameters in SAP BW and HANA
8. SAP BW Time-Dependent Hierarchies
9. Ragged Hierarchies and Data Formats in SAP BW
10. Building Calculations directly on SAP BW and HANA
11. Building data models into SAP HANA