Skip to Content
Technical Articles

BEx query to HANA View Conversion

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.

4 Comments
You must be Logged on to comment or reply to a post.
  • There is a requirement to replicate the functionality of Bex to HANA view

    We are facing issue while replicating the exception aggregation from Bex to HANA view.

    The respective attributes columns has been enabled for “KEEP FLAG” but still values are not matching.

    Approved capital (USD) (This is defined as exceptional aggr based on 0PROJECT) = Approved capital * Exchange Rate(This is derived as formula variable in BEx)

    Approved capital is matching but value in USD not matching even though flag is enabled

    Need immediate assistance on issue

     

     

    • Hi Lokeswara,

      Please make ensure that ‘KEEP FLAG’ property is enabled for all the columns which defines exception criteria. There could be additional attribute along with 0PROJECT to define exception aggregation in Bex query. As Keep flag will ensure to add all these columns in the group by clause even though they are not requested in the reporting query.

      And make ensure Projection node would be the default node in your view

      Add aggregation node on underlying table/view where all the required exception aggregate attributes are available. Create required Calculated column here.

      Hope this should work as expected.

  • CONV_CAP_SPENT_USD IS SHOWING CORRECT VALUE AS PER BEX BUT WHENEVER APP_CAPITAL_USD IS DRILL DOWN , VALUES NOT MATCHING

    BOTH OF THEM ARE EXCEP AGGREGATION

    1.APP_CAPITAL_USD  : APP_CAPITAL * EXCH_RATE(0PROJ_EXCH_RATE)

    ONLY FINAL COLUMN IS EXCEP AGGR

    2.CONV_CAP_SPENT_USD : RESTRCTIONS ON EXCHANGE RATE(A,C) + CAD AMOUNT(0WBS_AMOUNT)

    TRIED DIFF WAYS TO RECONCILE VLAUES WHEN BOTH ARE SELECTED ONCE AT A TIME BUT ITS NOT WORKING

    2.SEMNATIC FOR CURRENCY CODE CONVERSION WILL NOT WORK ON PROJECTION NODE (SINCE ITS DEFAULT IN VIEW DUE T OEXCEP AGGR)

    ANY ALTERNATIVE WAY TO ACHIEVE THIS SINCE EVEN THOUGH OPTION EXISTS BOTH SOURCE & TARGET CURRENCY SHOWING SAME VALUE (IT MEANS CONVERISON NOT HAPPENING)

    REGARDS

    NAVNEEET DARAK