HANA Input Parameters with Multi-Values to Filter Calculation Views

(1)

SAP COMMUNITY NETWORK

HANA Input Parameters with

Multi-Values to Filter Calculation

Views

Applies to:

SAP BusinessObjects BI platform 4.0.

Summary

This document provides information and guidelines about HANA calculation views with input parameters and its consumption in relational universes. Input parameters are used to filter the calculation view beforehand: users can dynamically select one or more values.

Author: Didier Mazoué

Company: SAP

Created on: May 27, 2013

Author Bio

Didier Mazoué is an expert product manager in the semantic layer team at SAP Business Objects Enterprise Information Management. Didier is the expert on most major OLAP databases, SAP NetWeaver BW, HANA, and relational databases. Before joining the product group in 2005, Didier was a strategic pre-sales field representative specializing in closing large database deals. He currently resides in France and works out of the SAP Lab in Levallois-Perret.

(2)

Audience

This document is intended for HANA designers and universe designers who have a working knowledge of building HANA views using HANA Studio and universes using the Information Design Tool.

Purpose

The document is based on several customers‟ requirements asking for filtering a calculation view beforehand. The customers‟ requirements are: select one or multiple values from an attribute with the ability to manually enter values.

This document presents hint and tips for creating a relational universe (data foundation), to support HANA input parameters with multiple values. The document also focuses on the calculation view creation.

Requirements

As described in the document purpose, the main requirement is to filter the calculation views before creating it.

In HANA Studio the only way to do it is to create a calculation view using SQL script to filter the data. The only way for filtering a calculation view is to create an input parameter but unfortunately input parameters only accept single value.

The requirement is to let the uses select one or multiple values from an attribute list of values, potentially manually enter values and also do a search on the database. The latter is motivated by the size of the list of values: some of the attributes contain hundreds of thousands distinct values.

Calculation view definition

Because input parameters only support one value, the values selected by the user must not be enclosed with quotes like this: Value 1, Value 2, Value 3.

It means that the SQL expression must be able to analyze the content of the input parameter in order to filter the data.

The solution is to use the SQL function LOCATE.

Definition:

Returns the position of a substring needle within a string haystack. Returns 0 if needle is not found within haystack. Returns NULL if haystack or needle is NULL, or if haystack is an empty string. As the user can select one or multiple values each value is separated by a comma and enclosed with parenthesis like this. (Value 1,Value 2,Value 3).

Finally the input parameter value is always enclosed by single-quotes and will look like this: „(Value

1,Value 2,Value 3)‟.

So the SQL WHERE clause the calculation view needs to analyze the input parameter content to detect if a

selected value is one of the attribute values.

Here is a calculation view script based on the “Foodmart” schema where the input parameter is based on the attribute PRODUCT_CATEGORY from the attribute view PRODUCT..

(4)

Calculation view definition SELECT "COUNTRY" AS "CUSTOMER_COUNTRY", "STATE" AS "CUSTOMER_STATE", "CITY" AS "CUSTOMER_CITY", "FAMILY" AS "PRODUCT_FAMILY", "DEPARTMENT" AS "PRODUCT_DEPARTMENT", "CATEGORY" AS "PRODUCT_CATEGORY", "SUBCATEG" AS "PRODUCT_SUBCATEGORY", "PRODUCT" AS "PRODUCT", "STORE_COUNTRY" AS "STORE_COUNTRY", "STORE_STATE" AS "STORE_STATE", "STORE_CITY" AS "STORE_CITY", "TYPE" AS "STORE_TYPE", "STORE" AS "STORE", "YEAR" AS "YEAR", "QUARTER" AS "QUARTER", "MONTH" AS "MONTH", SUM("STORE_SALES") AS "STORE_SALES", SUM("STORE_COST") AS "STORE_COST", SUM("UNIT_SALES") AS "UNIT_SALES" FROM "_SYS_BIC"."foodmart/SALES_2007" GROUP BY "COUNTRY", "STATE", "CITY", "FAMILY", "DEPARTMENT", "CATEGORY", "SUBCATEG", "PRODUCT", "STORE_COUNTRY", "STORE_STATE", "STORE_CITY", "TYPE", "STORE", "YEAR",

(5)

"QUARTER", "MONTH"

WHERE : CATEGORY = '(*)' OR

( (locate( :CATEGORY, ','||"PRODUCT_CATEGORY"||',') != 0) OR (locate( :CATEGORY, ','||"PRODUCT_CATEGORY"||')') != 0) OR (locate( :CATEGORY, '('||"PRODUCT_CATEGORY"||',') != 0) OR (locate( :CATEGORY, '('||"PRODUCT_CATEGORY"||')') != 0) );

The SQL WHERE clause analyzes all possible user answers including “*” if the user select all values.

Universe

The universe designer needs to use Information Design Tool to create the universe.

The first action is to create a relational connection on the HANA server then create a data foundation on top of the new created connection.

Then drag and drop the calculation view (in “SYS_BIC_” schema) in the data foundation. To support the calculation view, the data foundation needs to be enriched with the addition of:

 a derived table

 a parameter

 a list of values

Parameter definition

A parameter needs to be created in the data foundation.

Parameters can be created and edited in the PARAMETERS AND LIST OF VALUES pane of the data foundation

(6)

First solution

If the requirement is to let the users entering search criteria to filter the database before displaying the list of values, a parameter must be created as following:

The parameter options are the following:

 Not based on a list of values: manual entry

 Single value

 Default value = *

Setting “*” as default value do not force the user to enter a value when prompted. It allows running the query without filtering the calculation view.

(7)

Second solution

If the requirement is to let the users manually enter the categories (full name and not search criteria) to filter the database before displaying the list of values, a parameter must be created as following:

The parameter options are the following:

 Not based on a list of values: manual entry

 Multiple values

 Default value = *

Setting “*” as default value do not force the user to enter a value when prompted. It allows running the query without filtering the calculation view.

(8)

List of values definition

A list of values needs to be created in the data foundation.

List of values can be created and edited in the PARAMETERS AND LIST OF VALUES pane of the data

foundation editor.

For performance reasons, it is important to build the SQL on top of the dimension table or the attribute view contain the product values.

Defining a list of values on an analytic view or a calculation view can manipulate too much data. First solution

If the requirement is to let the users entering search criteria to filter the database before displaying the list of values, the list of values must be a SQL list of values and references the parameter defined in the first solution of parameters definition:

SELECT '*' AS PRODUCT_CATEGORY FROM SYS.DUMMY UNION

SELECT @Prompt(Search categories) AS PRODUCT_CATEGORY FROM SYS.DUMMY UNION

SELECT DISTINCT "PRODUCT_CATEGORY" FROM "FOODMART"."PRODUCT_CLASS" WHERE "PRODUCT_CATEGORY" LIKE CASE

WHEN @Prompt(Search categories) = '*' THEN '*************' ELSE '%' || @Prompt(Search categories) || '%'

END

ORDER BY 1

As you can see the list of values will return:

 Always a “*” for allowing users to select it as the placeholder for “All values”.

 The value entered by the user if the value is a category and not a search criteria

 The categories filtered by the search criteria using the LIKE SQL function. This is another reason to use the dimension table or the attribute view.

You can notice that if the user has enter “*” as search criteria it is replaced by something that will return no value. This construction is dependent of the size of the list of values:

 If the list of values is very large , the customers don’t want that all values are displayed in the list for performance reason

 If the list of values is small, then all values can be displayed in the list and we can replace the SQL expression WHEN @Prompt(Search categories) = '*' THEN '*************'

by WHEN @Prompt(Search categories) = '*' THEN '%': all values will be retrieved from the dimension.

(9)

Second solution

If the requirement is to let the users manually enter the categories (full name and not search criteria) to filter the database before displaying the list of values, the list of values must be a SQL list of values and references the parameter defined in the second solution of parameters definition:

SELECT '*' AS PRODUCT_CATEGORY FROM SYS.DUMMY UNION

SELECT DISTINCT "PRODUCT_CATEGORY" FROM "FOODMART"."PRODUCT_CLASS"

WHERE "PRODUCT_CATEGORY" IN @Prompt(Enter categories) ORDER BY 1

As you can see the list of values will return:

 Always a “*” for allowing users to select it as the placeholder for “All values”.

 The categories filtered by the user selection.

Derived table definition

Once the parameter and the list of values have been defined, we need now to create the derived table in order to use prompt the user to select values from the list of values and add the results in the input parameter placeholder.

First solution

If the requirement is to let the users entering search criteria to filter the database before displaying the list

of values, the data foundation must contain an @prompt based on the first list of values definition:

Derived table definition

SELECT "CUSTOMER_COUNTRY", "CUSTOMER_STATE", "CUSTOMER_CITY", "PRODUCT_FAMILY", "PRODUCT_DEPARTMENT", "PRODUCT_CATEGORY", "PRODUCT_SUBCATEGORY", "PRODUCT", "STORE_COUNTRY", "STORE_STATE", "STORE_CITY", "STORE_TYPE", "STORE", "YEAR", "QUARTER", "MONTH", SUM("STORE_SALES") as STORE_SALES, SUM("STORE_COST") as STORE_COST, SUM("UNIT_SALES") as UNIT_SALES FROM "_SYS_BIC"."foodmart/CALC_SALES_FILTER_BEFORE_LOCATE"

('PLACEHOLDER' = ('$$category$$', '@Prompt('Select categories','K',first solution',multi,constrained,persistent,{'*'})'))

GROUP BY

"CUSTOMER_COUNTRY", "CUSTOMER_STATE",

(10)

"CUSTOMER_CITY", "PRODUCT_FAMILY", "PRODUCT_DEPARTMENT", "PRODUCT_CATEGORY", "PRODUCT_SUBCATEGORY", "PRODUCT", "STORE_COUNTRY", "STORE_STATE", "STORE_CITY", "STORE_TYPE", "STORE", "YEAR", "QUARTER", "MONTH"

The derived table contains a prompt based on the list of values named “first solution”.

The prompt data type is “K”: it means that the values selected by the user are not enclosed by single quotes.

The prompt allow the user to select multiple values but only in the list (parameter constrained). This is a restriction of “K” data type to avoid SQL injection.

A default value (*) is added in the prompt definition: this allows the validation of the derived table because prompt needs to be solved beforehand.

The prompt expression is enclosed by single-quotes to ensure that HANA will validate the derived table SQL expression.

Second solution

If the requirement is to let the users manually enter the categories (full name and not search criteria) to

filter the database before displaying the list of values, the data foundation must contain an @prompt

based on the second list of values definition: Derived table definition

SELECT "CUSTOMER_COUNTRY", "CUSTOMER_STATE", "CUSTOMER_CITY", "PRODUCT_FAMILY", "PRODUCT_DEPARTMENT", "PRODUCT_CATEGORY", "PRODUCT_SUBCATEGORY", "PRODUCT", "STORE_COUNTRY", "STORE_STATE", "STORE_CITY", "STORE_TYPE", "STORE", "YEAR", "QUARTER", "MONTH", SUM("STORE_SALES") as STORE_SALES, SUM("STORE_COST") as STORE_COST, SUM("UNIT_SALES") as UNIT_SALES FROM "_SYS_BIC"."foodmart/CALC_SALES_FILTER_BEFORE_LOCATE"

('PLACEHOLDER' = ('$$category$$', '@Prompt('Select categories','K',second solution',multi,constrained,persistent,{'*'})'))

(11)

SAP COMMUNITY NETWORK © 2013 SAP AG 11 GROUP BY "CUSTOMER_COUNTRY", "CUSTOMER_STATE", "CUSTOMER_CITY", "PRODUCT_FAMILY", "PRODUCT_DEPARTMENT", "PRODUCT_CATEGORY", "PRODUCT_SUBCATEGORY", "PRODUCT", "STORE_COUNTRY", "STORE_STATE", "STORE_CITY", "STORE_TYPE", "STORE", "YEAR", "QUARTER", "MONTH"

There is no difference in the derived table SQL expression between the first and second solutions except that the list of values referenced in the prompt are different..

Preview calculation view data

For both solutions we have create two prompts: one with the parameter and the second using @prompt

expression.

We have created a prompt dependency also called cascading prompts: the @prompt defined in the

derived table depends on the parameter created in the data foundation and referenced by the list of values.

So the user must have to first answer to the parameter and the select values in the list of values

(12)

(13)

(14)

(15)

(16)

(17)

(18)

(19)

(20)

SAP, R/3, SAP NetWeaver, Duet, PartnerEdge, ByDesign, SAP BusinessObjects Explorer, StreamWork, SAP HANA, and other SAP products and services mentioned herein as well as their respective logos are trademarks or registered trademarks of SAP AG in Germany and other countries.

Business Objects and the Business Objects logo, BusinessObjects, Crystal Reports, Crystal Decisions, Web Intelligence, Xcelsius, and other Business Objects products and services mentioned herein as well as their respective logos are trademarks or registered trademarks of Business Objects Software Ltd. Business Objects is an SAP company.

Sybase and Adaptive Server, iAnywhere, Sybase 365, SQL Anywhere, and other Sybase products and services mentioned herein as well as their respective logos are trademarks or registered trademarks of Sybase Inc. Sybase is an SAP company. Crossgate, m@gic EDDY, B2B 360°, and B2B 360° Services are registered trademarks of Crossgate AG in Germany and other countries. Crossgate is an SAP company.

All other product and service names mentioned are the trademarks of their respective companies. Data contained in this document serves informational purposes only. National product specifications may vary. These materials are subject to change without notice. These materials are provided by SAP AG and its affiliated companies ("SAP Group") for informational purposes only, without representation or warranty of

HANA Input Parameters with Multi-Values to Filter Calculation Views