Building calculations directly on SAP BW and HANA

June 16, 2020 | David Gordon

Last updated: February 07, 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.

In previous blogs, I explored how the flexibility of Pyramid enables direct querying of SAP data, bringing back results directly from BW or HANA without extracting and reloading raw data into a proprietary database. With its rich features and ease of use, Pyramid allows end-users to get real self-service analytics with time intelligence working directly on SAP.

This functionality is accentuated greatly by a fully functional and open framework for building any type of analytic calculation function in Pyramid, which runs natively and directly on BW or HANA.

The problem

Users wishing to make direct queries on their existing SAP database are severely hampered when attempting to perform mathematical and set-based logic. Third-party software applications like Qlik, Tableau, and Power BI are unable to deliver the advanced MDX or SQL queries needed for more sophisticated calculations and logic. As such, users are often forced to extract and load the source SAP data into their tool’s proprietary database to access the numerical operations required to craft business analysis.

In doing so, they almost fully negate the investments made into SAP BW or HANA: breaking the chain of custody and governance through the duplication of data; sacrificing the powerful features such as parameters and advanced business logic found in BW and HANA, and decentralizing the source of the truth.

The solution

Pyramid solves this problem by offering users full MDX or SQL function libraries to resolve the mathematical and set-based logic needed for analysis. It does so through its “Pyrana” query engine, which works directly on SAP BW or HANA. All logic designed in Pyramid is executed as part of the direct querying process—without any functional or performance penalties. Additionally, the self-service function builders and editors are available via an intuitive point-and-click interface. End-users can write calculations without needing to update or adjust the BW and HANA models or databases.

Business case

Ron, the BI Manager for Natural Textiles, wants to create several reports and dashboards from his SAP BW platform, analyzing data across different months. He wants to select a date and then automatically calculate month-to-date, quarter-to-date, and year-to-date sales figures. In addition, he would like to see what percentage of year-to-date sales figures was attributable to the last months’ sales. For further analysis, he would like to view this by product category—and occasionally by product sub-category—without having to create new calculations and reload data.

In previous attempts with three other tools (Qlik, Power BI, and Tableau) directly querying the BW source was entirely impossible. Ron was forced to upload data before doing any local calculations on the data. Ron then tried Pyramid. He was astounded by the simplicity and power of the solution. He was particularly pleased with the fact that he did it himself in about three minutes.

In the dashboard below, Ron was able to seamlessly set up multiple calculations for month-to-date, quarter-to-date, and year-to-date sales—all on one dashboard—using a single date field as a parameter to drive the results. The calculations were made without a single line of code, running a direct query against the SAP BW data, without having to download or manipulate any data. What’s more, by dynamically modifying the date, all calculations were made seamlessly, directly on the SAP data.

The Date Parameter provides an easy, intuitive method for selecting a date on the fly. What’s more, all calculations are based on the selected date. No complex manipulations are required.
The month-to-date, quarter-to-date, and year-to-date calculations are provided as ready-built functions based on the selected date. All calculations are done via direct query of the SAP BW database. As these are created as custom members, they are measure-agnostic, so analysis can be done using any measure. That’s real power!
Another customer member is created with a simple point-and-click to group various items together; no need to enter any formulae.

Ron then continued to build out more formulas to calculate the month-to-date as a percentage of the year-to-date total. Pyramid’s Formulate tool allowed easy point-and-click creation of formulas with built-in wizards. In addition, for a more detailed view, he dropped the sub-category column on his dashboard. Pyramid automatically recalculated all amounts, without having to reload data to a local data source. The modified query ran directly and seamlessly against the SAP data.

The formula to calculate the month-to-date percentage of year-to-date is created intuitively by clicking on the fields. Once Ron creates the required function, it is then applied to the report or dashboard.
An additional subcategory is seamlessly added via the drop and drag interface. All queries are run against the original SAP BW database. Calculations are made by Pyramid automatically adjusting the query to cater to the new requirements.

Pyramid calculations vs BEx

In a final twist, Ron realized that Pyramid’s self-service logic-building capabilities are commensurate with many of the functions and capabilities found in BEx. However, instead of having to get his IT developers to create BEx queries for each nuanced reporting requirement, he built it himself in Pyramid without the extra BEx functionality—providing a far more flexible, accessible, and quicker approach to advanced querying directly in SAP BW.

This, it turns out, is a game changer, especially now that BEx is being deprecated by SAP.

In summary

Pyramid’s “Pyrana” querying engine generates complete and full MDX and/or SQL queries for a huge armada of calculations, formulations, and sets. And while they can be devised and built in Pyramid, they are executed and run natively in SAP BW or HANA—without losing the benefits and features of those analytic engines, data security, or performance.

This provides a winning formula for self-service to organizations on SAP BW and HANA.

For those that want to re-read our SAP-related posts, see the following links:

Pyramid’s scalable environment is capable of processing large amounts of data using direct access for all calculations
Sleek self-service with drag-and-drop capabilities for collaboration, time intelligence, and context calculations
Pyramid’s ability to automate the scheduling and distribution of pixel-perfect dashboards in a variety of formats, sliced per user’s view.
Time-dependent hierarchies, and support for multiple data connections to SAP BW, each with different parameterization.
Support for ragged hierarchies, numerical formats, multiple currencies, and captions

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: