2251147 - BI4.x Bad performance in BO Client Tools when retrieving data or list of valiues from a HANA View with many joins

Symptom

• Performance issue in WebI, when refreshing the LOV prompt coming from HANA View with many joins 
• BO Client Tools are refreshing the List Of Values very slowly
• Issue is reproducible in Information Design Tool as well
• Values are retrieved much faster when working in HANA Studio or in Analysis for OLAP

Environment

• SAP BusinessObjects Business Intelligence 4.x
• SAP BO reports based on universe from SAP HANA View
• SAP HANA JDBC connection

Reproducing the Issue

1. Create a HANA View with many joins
2. Add an Input Parameter or Hana Variable to the view in HANA Studio
3. Create a universe in IDT based on this view
4. Create a query in IDT or in WebI
5. Observe, that the BO Client tools are retrieving the list of values much slower than AOLAP or HANA Studio
6. When analyzing the generated SQL realize that these tools are retrieving the list of values from the view instead of the HANA master table

Cause

• SAP BO tools using universe based on a Hana view will execute the View and not the master tables
• In case having huge amount of joins in the view, refreshing the LOV will scan through all the joins and retrieves the values much slower
• HANA Studio and the AOLAP application has a direct access to the HANA DB, therefore the values will be retrieved from master tables directly

Resolution

1. Always make sure, that the SAP HANA and the SAP BO version you are using is on a supported SP/Patch level. (See SAP Knowledge Base Article 2251246)
2. In order to get the values directly from the master tables you should follow the steps written in the following document when Hana Input Parameters/Variables in SAP BO:
   http://scn.sap.com/docs/DOC-27676
3. A derived table should be created, and the prompt should be replaced by the 'PLACEHOLDER' word
4. By assigning to  a dedicated parameter created in IDT to the @Prompt, the end user will be prompted before executing the report
5. When creating the Parameter the values should be picked up from master tables directly, therefore LOV's will be retrieved from the master tables much faster and not from the HANA View
6. Otherwise the BO Client tool will scan the whole universe and all the associated HANA-joins through to get the data

See Also

2251246 - BI4.x How can I make sure, that I'm working in an environment which is fully supported by SAP BusinessObjects and SAP HANA

Keywords

webi, webintelligence, information design tool, universe, slow, refresh, prompt, appear, retrieve, calculation view, hana view, hana, bi, rich client, idt, slowness, bad, performance, affected, parameters, @prompt, tables