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 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.
John, our BI analyst at Venus Pharmaceuticals from other recent blog posts, has an SAP BW4Hana 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.
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.
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.