In my previous blogs, I highlighted a common goal of many SAP users: finding a self-service BI tool that works directly on SAP. I also showed how Pyramid is that a tool, and how it seamlessly connects with and runs direct queries on SAP BW and HANA. In this blog, I will show how Pyramid takes those capabilities even further: building and running calculations directly on these two data sources.
What differentiates modern self-service BI tools from their legacy counterparts is their “point and click” and “drag and drop” functionality, shielding users from needing deeper technical expertise, such as writing code.
SAP BI tools like Business Objects and BEx are designed for technical users, the opposite of self-service—where even the simplest processes are highly technical and require vast amounts of experience. SAP’s Analytics Cloud, on the other hand, offers the promise of self-service analytics directly on BW and HANA. But as an immature product, it remains far behind in analytical functionality and sophistication.
Using third party tools that offer far more mature self-service capabilities replaces that headache with technologies that cannot fully work directly on SAP BW and HANA. They force users to extract, reload, and duplicate the data, thereby introducing data governance and security headaches.
The challenge then is to find a real self-service analytics platform with requisite functional depth that works directly on SAP BW and HANA. It’s even more difficult to find a platform that can build calculation logic that goes beyond ultra-simplistic mathematics.
Consider the following business problem: a business user is analyzing monthly sales data and wants to track when important milestones have been reached. In order to achieve this, she would like to see running totals of sales in her SAP HANA view indicating when a $1M milestone was achieved. Due to the ad-hoc discovery nature of her investigation, she might also want to track this on different levels of granularity, such as by quarter, by month, or by week.
With Pyramid, a user can use a direct query on SAP HANA to query and retrieve the results below in a simple matrix grid in three clicks, without having to extract the data into another database.
While this information provides sales information by month and year in an easy-to-follow grid, the user is not able to see when the $1M (or $2M and $3M) milestone was achieved. She needs the table to display running or cumulative totals to achieve that outcome.
Running totals in a single click
Creating running totals in many BI applications is often part of an ad-hoc reporting exercise that requires finicky coding and depends on developers and the associated software development lifecycle.
It’s a snap in Pyramid. With a single right click on any sales metric, a user can display dynamic “Cumulative Totals”—no code or data extractions required—on top of any query processed on SAP BW or HANA. The task doesn’t require the user to re-design the InfoCube or write highly technical BEx queries.
And when you swap out months or any other hierarchy—or add additional hierarchies—the running total is automatically recalculated. No extra code or steps is required. Now the user can easily see the key month targets highlighted below.
How do other tools compare?
On less sophisticated platforms, each calculation will require the user to write code specific to that combination of dimensions and measures she wants to view in her visual or report. This is normally beyond the capabilities of most consumers, and even some proficient end-users will require assistance from an experienced developer. And this doesn’t even address the headache of connecting to SAP BW or HANA to get the data in the first place! For many BI tools, this inefficiency (across specifications, documentations, development, testing, etc.) is quite common.
In our example, the developer will need to create three different variables for company, salesman, and branch, each with three different time options for quarter, month, and week. That’s nine separate variables, each requiring separate code. Yikes!
Just for color, here’s a sample of DAX code required to calculate running totals in Power BI (remember, the user would have copy and adjust this calculation nine times):
At this point, business users will either spend a large amount of time creating all the variables and code required for this solution, or decide that it is not worth the effort. When they reach this point, the most common alternative is Excel. And when users resort to Excel, it effectively defeats the purpose of having a centralized enterprise analytics and reporting strategy. Hello, data silos!
Conclusion: With Pyramid, calculate without code
With Pyramid users can create advanced calculations and logic that work directly on SAP BW and HANA. The logic, driven from a self-service paradigm, covers anything from basic calculations to sophisticated logical expressions.
To view Pyramid’s extremely powerful code-free solutions for other common business problems on any data source, including SAP BW and SAP HANA, see my previous blogs:
SAP and Pyramid Analytics
Real self-service BI for SAP
Self-service BI directly on SAP BW
Collaborative analytics for SAP BW and HANA
Time intelligence for SAP BW and HANA
Running Totals Made Easy for SAP HANA and BW
- Dashboards and report bursting directly off SAP BW and HANA
Realizing the power of parameters in SAP BW and HANA