Ragged hierarchies and data formats in SAP BW

June 11, 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 attempting to use third-party self-service BI tools on an SAP BW implementation, many customers are often highly frustrated that critical features like ragged hierarchies, numerical formats, multiple currencies, and captions do not work.

In previous blogs I discussed Pyramid’s superior support for features common to both SAP BW and HANA, where I addressed:

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.

In this blog, I will discuss how Pyramid, in contrast to most other tools, provides support for SAP BW’s classic analytical features like ragged hierarchies, data formats, multiple currencies, and alternative captions without needing to export the data and or other backend manipulations and hacks.

The challenges

Ragged hierarchies

Ragged hierarchies are common in financial structures like profit and loss statements where items with amounts can appear on different hierarchical levels. SAP BW supports this requirement, but third-party BI tools expect all items to be on the same level, and thus do not support this all-too-common occurrence. Pyramid allows items in hierarchies to exist on different levels, thus retaining critical hierarchical logic.

Numerical formats and multiple currencies

SAP BW can apply scaling factors to metrics. It can also include settings to reverse value signs. Units of measure (for example, 230 KG) and currency formatting (for example, $2,300 or 4,000 AUD) are also heavily used. With most other tools, all formatting is ignored, and data is displayed in its ambiguous “un” formatted way. In Pyramid, all formatting choices are reflected in the reports and visualizations as they appear in BW.

Captions

SAP BW users are sometimes more familiar with various names and properties of values rather than the standard captions in a dimension (like cost center “100” vs “US Sales”). In most other tools, alternatives are not available. In Pyramid, with a simple point and click, you can change the field display to any of the alternate fields held in SAP BW.

Business case

Andrew, the CIO of International Cycle Company, wanted to revitalize his BI department with modern dashboards and self-service analytics. In his initial round of analyzing BI tools using a proof-of-concept project, he included various self-service tools. In the end, he rejected many solutions because:

None supported his financial data that was based on a classic multi-level, ragged chart of accounts hierarchy.
His cubes contained numerous numerical formats for multiple currencies.
Many of his attributes had alternative captions like account number, cost center number, etc., as well as names.

When he opened his cubes in Pyramid, he was pleased to see that all his requirements were met, without having to lift a single finger!

Ragged hierarchies

In the profit and loss statements, within the Operating Cash Flow hierarchy, Depreciation & Amortization appears on the third level. In contrast, within the Net Income hierarchy, Discounts appears on the fifth level. Trade Sales appear on the sixth level. All are displayed accurately within Pyramid without having to artificially “push them down” to appear on the same level.

The three items with red frames all exist on different hierarchical levels. Pyramid accurately depicts them as they occur in a BW InfoCube.

Numerical formats and multiple currencies

Customers’ sales figures appear in SAP BW as a gross amount with the applicable currency. The currency (which disappears in other BI tools) must be displayed, and an additional calculated column must appear where the amount is converted into Euros. Where a sum of items is not logically possible, like the aggregation of amounts with different currencies, a clear indication must be made to show that the addition is not applicable.

All amounts are displayed with currencies as they appear in the BW InfoCube
All amounts converted to euros
Gross Amount calculated, with a * for the currency to indicate addition is not applicable

Numerical formatting with calculations

Another common financial reporting requirement is to see trading companies as a calculated group. For example, the Russian Oil and Mexican Electronic Trading companies should appear in a group with a sub-total without having to download the data first. The direct query must formulate and resolve this calculation seamlessly.

A custom group member of the sum of Russian Electronic and Mexican Oil Trading Companies created in Pyramid via point and click. All definitions and calculations are done directly on the BW server—no data is downloaded on a local database.

Captions

In the Commercial Balance Sheet report, users would like to view the numerical names for the Cost Center, as it appears in SAP BW, not the long descriptive field.

With a simple point and click, Andrew can change the Cost Center column to its technical name, or to any of the alternate fields held in SAP BW.

Summary

In this blog, I have demonstrated how Pyramid exploits SAP BW’s various analytic features directly, providing a fusion of the BW technology with Pyramid’s modern, feature-rich BI capabilities. While many third-party tools can query BW, they are generally missing key elements that are critical to BW’s analysis, usage, and deployment. This makes Pyramid a natural self-service option for SAP BW, without the drawbacks.

In my next blog, I will address how Pyramid uniquely supports full logical, mathematical functions.

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: