BEx query to HANA View Conversion

[hari_balija]

Introduction :

In this blog, I am going to describe how SAP provided standard functionalities can be leveraged to migrate/convert BEx Queries to HANA views

HANA views can be generated automatically from the BW system for the below object types:

• SAP HANA-optimized InfoCubes


• DataStore objects


• InfoObjects


• queries as InfoProviders


• CompositeProviders


• local CompositeProviders in the BW Workspace and


• queries

For complete details on this topic, refer this SAP link

Now you may curious why I am writing this blog separately as SAP is providing option to generate HANA view automatically from the Bex Query. Yes, HANA view can be generated from the BEx query but there are so many limitations/pre-requisites for doing so. For example, below BW functions would be either unsupported or ignored.

• Filter on a hierarchy


• Constant selection


• Definitions on a cell


• Exception aggregation


• Hierarchy display settings


• Default values for characteristics


• Conditions


• Exceptions


• Local calculations

For complete list of Ignored and Unsupported BW functions please click here

In most of the real time Bex queries, at-least any one of these BW functions would be used for sure to meet business requirements. Hence most likely it would not be feasible to generate HANA views from the BW system directly for the Bex queries.

Problem Statement :

Now let me talk about the business requirement of my use case before getting into technical details.

During one of on-going assignments, SAP Analytics Cloud (SAC) dashboards were built on top of the BW system using BEx live connection (BEx live connection stands for Business Explorer reports connecting in real-time to the SAC dashboards, that provides real-time data). However, few other Business Warehouse (BW) systems in the landscape were not compatible due to lower BW support pack of BW 7.5, as BEx live connection requires BW 7.5 SP10+. Hence either all these SAC dashboards required re-work to convert the BEx live connection to a HANA view live connection or all other BW systems in landscape need to be upgraded to 7.5 SP10+.

Solution Proposal :

After comprehensive analysis, SAP recommended to upgrade all BW systems. But this approach would not only increase over all cost of the project but also disturb the live systems and hence total project timelines. An alternate approach is to migrate Bex queries to HANA views. With this solution, the issue has been addressed with no major additional cost and without disrupting the live systems

To migrate Bex queries to HANA views, generate HANA views from the underlying infoProviders of the Bex query and use them as base layer views to construct reporting layer HANA views based on these generated views. Create restricted and calculated columns on top of these generated views based on available Bex query elements definition. Also generated HANA views from master data Info objects and join them in final reporting view using STAR JOIN.

I want to highlight one of the Bex query feature here, due to which HANA view values were not getting matched with original Bex query values. The Aggregation property for one of the formula/CKF in Bex query is set to 'Summation' explicitly. So in the Bex report output, totals row for this column is getting displayed as summation of individual values instead of applying formula at the totals row. Please see below example for better understanding of this scenario.

	Input 1	Input 2	Formula = Input 1 * Input 2
	5	2	10
	6	7	42
Totals	11	9	99   52

To achieve this in HANA view, push this formula to one layer below and use aggregate node on top of that.

And create input Parameters based on stored procedures to achieve the functionality of Bex Variables with processing type 'Customer Exit'. Please be more cautious while replicating optional Bex variables to HANA views using Input Parameters. Because if no value is provided for the optional variable while executing the Bex query, the report output will display all the rows from the underlying InfoProvider without applying any filter value. But if no value is provided for optional Input Parameter in HANA view execution, no rows would be displayed in the output. To resolve this create one optional variable on that column and one more Input Parameter based on Stored Procedure and check if the value of the variable is BLANK then pass * to Input Parameter or else pass the manually entered Variable value to Input Parameter. Then use this Input Parameter in that specified column restriction.

Also level hierarchies can be created manually on Time Characteristics (Calday, Calweek, Calmonth, Quarter and Year) in HANA view to display them in hierarchical manner in SAC output. Please make ensure to enable the setting 'Enable SQL Access for Hierarchies' if the hierarchy variable is created in HANA view and need to be used in SAC report building.

Conclusion :

In this blog, we have learnt how to leverage SAP provided functionalities to migrate Bex Queries to HANA views successfully without disturbing the existing functionality.

 

Thank you. Please share if you have any comments, I will update the content accordingly to make it more enlightening.