Ragged Hierarchies and Data Formats in SAP BW

Pyramid is an official member of the SAP® PartnerEdge® open ecosystem. Pyramid 2020 is certified by SAP to interoperate with SAP BW/4HANA. Pyramid 2020 is pending certification by SAP to interoperate with SAP HANA

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:

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 for 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, the Depreciation & Amortization appears on the third level while within the Net Income hierarchy Discounts appears on the fifth level. Trade Sales appears on the sixth level. All displayed accurately within Pyramid without having to artificially “push them down” to appear on the same level.

Ragged hierarchies accounting grid

The three items with red frames all exist on different hierarchical levels. Pyramid accurately depicts them as they occur in an 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.

Multiple currencies converted

  1. All amounts are displayed with currencies as they appear in the BW InfoCube
  2. All amounts converted to euros
  3. 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.

Multiple currencies converted

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

Caption all

  1. 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:

  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