The goal of this blog is to update you on the enhancements delivered since SAP BusinessObjects BI4.1 to leverage the SAP HANA platform. Please refer to the SAP 4.1 documentation to have more details.

This blog is covering several features introduced in SAP BI 4.1 Web Intelligence and Information Design Tool that will help you leverage your HANA investment. Please note that previous recommendations with BI 4.0 on top of SAP HANA still apply.

  • SAP BI 4.1 Universes on top of HANA: HANA Business Layer authoring enhancement, HANA Multi-view universes
  • HANA variables and input parameters with BI 4.1
  • HANA Database Ranking with BI 4.1
  • Query stripping Relational with HANA + Auto-refresh use case.

Automatic HANA universe generation (available since BI4.1 SP2)

From an authoring standpoint, the Information Design Tool (IDT) has been enhanced to facilitate Universes development on top of SAP HANA. IDT is now able to generate a HANA Business Layer automatically based on HANA Views.

The default process for creating a business layer on a data foundation containing SAP HANA views takes into account the metadata as defined in the SAP HANA information model. The New Business Layer wizard automatically creates the dimensions and attributes in each SAP HANA view in a business layer folder, and creates measures with the appropriate aggregation function.

You could do this manually prior to BI4.1 SP2. Please refer to this document for more details on the manual steps: Business Case for the BI4 Semantic layer and Web Intelligence on SAP HANA

The SAP HANA Business Layer option is available when you create a new IDT project

HANA BL 1.png

HANA BL 2.png

HANA BL3.png

The New SAP HANA Business Layer wizard automatically creates a data foundation and business layer based on selected SAP HANA views. When multiple SAP HANA views are present in the data foundation, any dimensions and attributes that are common to different views are created as a single business layer object, and special aggregate-aware objects are generated to make queries on multiple views possible.


In the following screenshot, we are selecting 2 distinct Analytic Views

HANA BL4.png

The HANA Business Layer is generated with the appropriate @Aggregate_aware function. By doing this, the Universe information engine will be able to generate the most optimal query to HANA. In this scenario, the Universe is leveraging aggregate awareness to access the HANA view in the most optimal manner.

HANA BL5.png

In the Data Foundation, a self-join is generated randomly for each Analytic view. The reason behind is to avoid joining HANA views with each other as that could impact performances. As you can see in the below screenshot, a “dummy” filter will be generated in the SQL statement and the 2 HANA views are not joined.

HANA BL6.png

The HANA Business Layer automatic generation will also configure the aggregate navigation’s incompatible objects for you. This will make sure the end user does not query incompatible objects from the query panel.

In the below screenshot, we can see that the incompatible objects were set automatically for each table.

  • Table foodmart/SALES_2006 is incompatible with SALES_2007 objects

HANA BL7.png

  • Table foodmart/SALES_2007 is incompatible with SALES_2006 objects

HANA BL8.png

We can test the results by creating a query with a dimension “Product” and 2 measures (“Store Sales” from the SALES_2006 HANA View and “Store Sales” from the SALES_2007 HANA View.

In the below screenshot, the Universe will generate two separate queries hitting the 2 HANA Views with a full outer join performed on the client side.

HANA BL9.png

HANA BL10.png

As a result, the query will generate a result set from both SQL flows in the same table whenever possible.

HANA BL11.png

The goal of this functionality is to enable ad-hoc WebIntelligence reporting on top of HANA. The end user doesn’t have to know which or how many HANA views are being accessed and the Universe will generate the most optimal and performing query transparently.

HANA variables and input parameters (available since BI 4.1 SP3)

Variables and input columns defined in the SAP HANA information model are now included in the data foundation. When refreshing the data foundation, new, deleted, and updated variables in the data source are taken into account.

HANA IP1.png

In Web Intelligence, SAP HANA universes behave like any other relational UNX universe; HANA variables and input parameters in SAP HANA information models are associated with the corresponding tables in the data foundation.

HANA IP2.png

When you run a query that includes HANA variables and input parameters in the Query Panel or when a document is refreshed, prompts appear that require you to specify values for those variables and parameters. The values available in the prompts come directly from the HANA source

HANA IP3.png

HANA Database Ranking (available since BI4.1 SP3)

You can now set the ranking of data in an SAP HANA universe. Queries based on objects with universe-level ranking will take less time to fetch data. You can use HANA Analytic functions such as RANK() and PERCENT_RANK for ranking filters

HANA Rank1.png

  • SQL generation for RANK()

HANA rank 3.png

  • SQL generation for PERCENT_RANK()

HANA Rank 4.png

Query Stripping Relational (available since BI4.1 SP2)

Query stripping is now available for relational universes (including universes on SAP HANA) as well as OLAP universes. Query stripping is a reporting feature that can be used to optimize performance by automatically rewriting the query to retrieve only objects included in the report. It is used only by SAP BusinessObjects Web Intelligence.

The following steps will show you how to configure and enable query stripping on relational Universe (in this example a HANA Universe)

In the Business Layer Query options, check the Allow query stripping option and publish the HANA Business Layer

QS1.png

In the Web Intelligence query panel> Query properties, check “Enable query stripping”

QS2.png

In the Web Intelligence document property, check “Enable query stripping”

QS3.png

Create a report to test the query stripping. In the screenshot below, the dimension in bold are stripped and are not included in the SQL statement anymore unless you add them in the report canvas.

QS4.png

If you drag and drop a stripped dimension into the report canvas, you’ll get a #REFRESH message instead of the dimension’s data. This is normal as the dimension is not in the SQL anymore, you have to refresh the dataprovider manually by clicking “Refresh”

QS5.png

If you want to perform this action more transparently for the end user, you can enable the Automatic Refresh feature available since BI 4.1 :

In the CMC go to Application > Web Intelligence > Properties > Automatic Refresh

autoR1.png

In the document property, enable the “Automatic Refresh”

autoR2.png

Auto-refresh  works only when delegated measures are involved in the report

autoR4.png

If you now drag and drop a stripped dimension into the report canvas, a refresh will automatically run so you don’t have to manually click on the refresh button.

To report this post you need to login first.

10 Comments

You must be Logged on to comment or reply to a post.

  1. Prashanth Chinta

    We are going to have Hana first time in our company soon, looks like I need to upgrade to 4.1 SP2+, can’t wait to try this myself,  well explained,  thanks.

    (0) 
  2. Prabhdeep Kaur

    Hi Guys,

     

    I am creating Universe using IDT on top of HANA views. Till now we were using “Business Layer” and not ” SAP HANA Business Layer”.

     

    When I try using this option , it asks me to enter the name of Business layer and it automatically takes the name of Data Foundation layer. But when I click next , don’t see the relational connection at all .I see an empty list

     

    Is there any thing wrong with my connection , though it works fine with normal Business layer.

     

    Any idea ?

     

    Regards

    Prabh

    (0) 
  3. Prabhdeep Kaur

    Thanks Romanic..!!!

     

    I have created my Universe selecting two Views from HANA. I can see the self joins created , aggregate  awareness in Dimensions, context for each view and Non-Compatible objects defined by the system automatically.

     

    Now if I create a query in IDT and pick measures from each view and some dimensions, I can only see data for one of the view fields and for the others no data at all.

     

    I have checked the other view , It has valid data but doesn’t show above.

    What else that I need to do here ?

     

    Regards

    Prabh

    (0) 
    1. Romaric SOKHAN Post author

      Hi Prabh,

      If the contexts are correct, I don’t see why you wouldn’t have 2 SQL flows if you select measures from 2 different HANA Views. Which version of IDT are you using?

      (0) 
  4. Rodrigo Caparroz

    Hi Romaric, great post!

     

    Do you know a way to set a default value automatically on one of these HANA variables on IDT?

    I’m able to see my variables, but I need to set that the Input_to parameter must receive the actual date.

     

    Fecha_TO.JPG

     

    Best,

     

    Rodrigo.

    (0) 
  5. Sukanta Panigrahi

    Hi Romaric,

     

    I followed the same steps of your post ‘ Automatic HANA universe generation (available since BI4.1 SP2)’. But while generating the query its is showing error like Aggregate Awareness resolution failed. Have checked properties of data foundation and the option ‘Multiple SQL Statement for each measure’ is enabled. Can you please let me know what would the the issue. Screenshot attached for your reference.Aggregate Aware Resolution Failed.jpg

    (0) 

Leave a Reply