How to build a WEBI on a HANA ANALYTICAL view with parameters?

[Former Member]

How to create a WEBI report based on a HANA analytical view that has mandatory PARAMETERS?

Introduction: This short document shares how one can achieve potential performance improvements on large volume analytical views by passing in parameters to the HANA analytical views so the filters can be pushed to the lowest level possible. (Pushing filters to the lowest possible level is one of the best practices so that the aggregation of the data takes place on a smaller subset of data rather than aggregating a large subset of data and then filtering)

At this point this document only shares how to pass a SINGLE PARAMETER and does not cover passing of multiple parameters on multiple dimensions.

Step 1: Create a HANA view that has a Mandatory parameter as shown in the following steps

• Create a HANA view of your choice in the HANA studio modeler perspective that takes in a mandatory paramter. In this example we will use the already created HANA VIEW “AN_ODP_FLOW_U_P”  as our example. This view gives the order and billing information and some of the dimensions of interest include customer, sales org.. etc and we will define a parameter for CUSTOMER input.
• The parameter P_CUSTOMER is created at the Data Foundation level and is tied to the SOLDTO attribute in the data foundation.
• Please note the syntax of $$<parameter_name>>$$ as you will need to use that syntax in testing of your HANA view. In this example since the parameter is called P_CUSTOMER we need to put the value as $$P_CUSTOMER$$

Finally here is the SQL console statements you can use as reference to test your own HANA views that has parameters.

SELECT * FROM "_SYS_BIC"."sial.bicont-dev.sales.order_flow/AN_ODB_FLOW_U_P"

(PLACEHOLDER."$$P_CUSTOMER$$" => '0049476774' );

Note: This syntax will change  a little bit when using in IDT BOBJ layer

At this point we assume you have a HANA view with parameter that has been tested in the HANA studio and ready to start building the Information Design Tool BOBJ artifacts.

Step 2: Create a universe using BOBJ IDT toolset.

• Create a short cut to the connection of interest into the project
• Create a new data foundation layer
• Insert a derived table. (Note: Derived table are those typically you can build using SQL statements.)
• In the edit window of the derived table enter the follow SQL statement

SELECT * FROM "_SYS_BIC"."sial.bicont-dev.sales.order_flow/AN_ODB_FLOW_U_P"

('PLACEHOLDER'=('$$P_CUSTOMER$$' ,@Prompt('Enter P_CUSTOMER','A')))

Note: The above syntax is different than used in the HANA studio to test the analytical view.

Also note the @Prompt is a function call and its parameters all of some default value and some meaning

Please ensure you review the documentation on the @Prompt function to understand its parameters

• Create a parameter called P_CUSTOMER and then validate the syntax and test the DERIVED table with show values options and it should prompt you for a PARAMATER to be entered and each time you can enter a different values for the customer and test that it indeed fetches the data record for that customer
• Make sure the universe is published to a folder where WEBI layer can access it.

Step 3: Create a WEBI layer

• Login to the launch pad and create a new REPORT
• Use universe as a source and specify the universe published in step 2 as the source
• Create a VARIABLE in the WEBI report and in this example I am going to call it “Test_Customer”
• I am still not sure how does this VARIABLE get the co-relation to the P_CUSTOMER parameter in IDT and in term to the P_CUSTOMER parameter in the analytical view. In my thoughts the only link between these 3 entities is the customer no column.
• We need to still test how this will behave if there are multiple parameters like start date and end date etc?