Self-service BI directly on 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.

Direct query

A direct query is a query that connects directly to the data source without first copying the data into the local data model technology. It does not consume extra memory and utilizes the power of the source environment hosting the source data. More importantly, the cardinal enterprise principles of security and governance are not sacrificed when data is accessed by direct query and the centrality of the business logic remains intact.

Direct query on SAP BW

Direct querying of SAP BW, for third parties, must be executed via the MDX language. BI vendors like Power BI, Qlik, and Tableau make basic MDX direct queries to the BW environment. However, their capabilities are limited to simple queries because they do not support deep, MDX logic functionality. This severely limits the scope of analytics that these tools can execute. For commonly used queries involving member calculations and set/list formulations, like year-to-date totals, month-on-month percentage growth rate, totals, filters, sorts, etc., these tools effectively force users to load the raw SAP data into their internal data engines to resolve queries.

Goodbye to security. Goodbye to governance.

To top it all, users lose all the inherent power and logic built into SAP BW cubes (and SAP HANA views) that have been developed and optimized to match the underlying SAP ERP application.

Goodbye to many SAP investments.

Pyramid’s Approach

Pyramid’s query engine, PYRANA, operates natively in either MDX or SQL. This means it can fully expose the full depth and breadth of the BW engine (MDX) or HANA engine (MDX or SQL) by working directly against these technologies—without any loss of analytic functionality. This is a game-changer. Users can fully leverage the original capabilities of BW and HANA without losing self-service functionality, without compromising governance and data security, and without data duplication and redundancy. It’s real self-service BI that works directly on SAP.

It's real self-service BI that works directly on SAP.

Business Case

John, our BI analyst at Venus Pharmaceuticals from other recent blog posts, has an SAP BW/4HANA warehouse that loads ten million transactions per month from the SAP ERP application. He would like to perform a simple current-month versus 12-month average calculation for the top 10 states. He wants to flip between queries, analyzing different months each time he analyses a different slice of the data.

Venus Pharmaceuticals’ existing SAP tool gives him little self-service functionality to build visualizations the way he prefers. He doesn’t want to use (the now deprecated) BEx, because it’s both too complicated and missing all the visualization capabilities. He also cannot realistically export the data into a third-party alternative because the data footprint is too large, the data is considered “strictly confidential,” and the special roll-up logic of the data elements in BV is too hard to replicate.

Data table 

John decides to use Pyramid because he can build his report and visualization directly on BW, avoiding all the headaches. No data is ingested or duplicated, all server-side logic remains intact, and complex calculations are rendered correctly via MDX, including the ones he designed—point and click—in Pyramid.

Within a few minutes after connecting to the BW infoCube, John builds the following report. It allows him to pick a month from a slice drop down that injects itself into a 12-month calculation to compare to the same month’s actual figures.

Summary Calculations 

Incidentally, Pyramid generates all the “messy” MDX logic behind the scenes; John simply uses the point-and-click formula tools in Pyramid. Importantly, it’s written in Pyramid without manual code and then executed on the BW server as if it were defined in the relevant InfoCube. John thus achieves the best of both worlds: self-service, sophisticated business logic without extracting the data.

Average(Range(Lag([data].[dateKey full month name].@[735741be-8e37-4cc2-9fcc-472ff6629133],12),lag([data].[dateKey full month name].@[735741be-8e37-4cc2-9fcc-472ff6629133],1)))

In summary, Pyramid provides the perfect front-end analytics tool to complement a company’s investment in SAP. It ensures data and business logic remains in SAP engines. It ensures that data remains secure, obviating the need for extraction outside the security perimeter—while still letting the data analyst build analytic content on his/her own. Importantly, it avoids data duplication and significantly improves governance. Direct queries unleash the full power of SAP’s underlying querying engines.

In my next blog, I will explore Pyramid’s drag and drop capability for collaboration, time intelligence, and context calculations in more detail.


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