How to…. Handling multiple Exclude Restrictions in Analytic View

This document is prepared based on HANA SPS 07 revision 70.

This is a workaround for the product bug. In Higher revisions or versions, you may not reproduce the bug if it is fixed.

Problem description:

When we apply multiple restrictions to exclude same attribute in Restricted column, Analytic view is not returning proper values.

                    Incorrect result                                                                         

                          Correct result

This document shows how to achieve the solution within the Analytic view.

Recreating the problem:

Table VBAK is used in this document.

———————————————————————————————————–

If VBAK table is not loaded, then you can use the below DDL,

which includes only few columns with sample data.

————————————————————-

CREATE COLUMN TABLE “ECC”.”VBAK” (“VBELN” NVARCHAR(10),

“ERDAT” NVARCHAR(8), “ERNAM” NVARCHAR(6), “NETWR” DECIMAL(15,2) CS_FIXED,

PRIMARY KEY (“VBELN”));

insert into “ECC”.”VBAK” values(‘101101′,’20140313′,’BU01’,100);

insert into “ECC”.”VBAK” values(‘101102′,’20140313′,’BU01’,300);

insert into “ECC”.”VBAK” values(‘101103′,’20140313′,’BU02’,100);

insert into “ECC”.”VBAK” values(‘101104′,’20140313′,’BU02’,200);

insert into “ECC”.”VBAK” values(‘101105′,’20140313′,’BU03’,400);

insert into “ECC”.”VBAK” values(‘101106′,’20140313′,’BU03’,100);

insert into “ECC”.”VBAK” values(‘101107′,’20140313′,’BU04’,100);

insert into “ECC”.”VBAK” values(‘101108′,’20140313′,’BU04’,100);

insert into “ECC”.”VBAK” values(‘101109′,’20140313′,’BU05’,100);

insert into “ECC”.”VBAK” values(‘101110′,’20140313′,’BU05’,150);

———————————————————————————————————–

Create Analytic view (AN_VBAK) and activate the view.

Execute the below query in SQL console (replace package name EDW1 with your package name)

SELECT ERNAM, SUM(NETWR) FROM “_SYS_BIC”.”EDW1/AN_VBAK”

GROUP BY ERNAM;

Apply restriction to exclude BU02.

For this we can create Restricted column (NETWR_RC_BU02) based on measure “NETWR” and apply restriction on attribute “ERNAM” and apply

operator “Not Equal” and check the include box, but such operator is not yet provided by SAP.

An alternate would be to use operator “Equal” and uncheck the Include box, and now it will act as Exclude or Except restriction.

Create the restriction to exclude BU02 as shown below:

Activate the view and run the query:

SELECT ERNAM, SUM(NETWR), SUM(NETWR_RC_BU02) FROM “_SYS_BIC”.”EDW1/AN_VBAK”

GROUP BY ERNAM;

Observe null value above which is expected behaviour. Now add one more restriction to exclude BU03.

Activate the view and run the query:

SELECT ERNAM, SUM(NETWR), SUM(NETWR_RC_BU02) FROM “_SYS_BIC”.”EDW1/AN_VBAK”

GROUP BY ERNAM;

Now you can see the abnormal behaviour. It is not displaying null values for BU02, BU03.

Having more than 1 restriction is not handling properly by HANA. in below solution let us take 3 restrictions and will see how to handle such situations.

Solution:

This is a workaround on a product bug. Use Approach 1 while Approach 2 can be meant for educational purpose only.

Approach 1:

1. Navigate to your view under “Column View” of schema “_SYS_BIC”. Double click the view to open the view definition, click on tab “Create Statement”.

 

2. Search for restriction, specific to your restricted measure. You will find that operator “OR” will be used for the restricted attribute.

Copy the entire view.

 

3. Drop the view using below statement in SQLConsole:

DROP VIEW “_SYS_BIC”.”Raj/AN_VBAK”;

4. Paste the code copied from the view and modify the code by replacing “OR” with “AND” as shown in above figure

restriction='(“ERNAM” != ”BU02” AND “ERNAM” != ”BU04”)’),

5. Execute the query.

SELECT ERNAM,  SUM(NETWR_RC_BU02) FROM “_SYS_BIC”.”Raj/AN_VBAK”

WHERE ERNAM LIKE ‘BU%’

GROUP BY ERNAM

 

Note: Whenever you do the changes to the Analytic view and validate and activate from your own package this will be overridden with restricted operator as “OR”. So manually you need to change the operator to “AND”.

Approach 2:

1. Create a new Restricted column (NETWR_BU02) with only one restriction as shown below. You may delete the restriction which is created before (NETWR_RC_BU02) or modify by renaming it and deleting the second restriction.

2. Create another restricted column (NETWR_BU03) to restrict BU03 as shown below

3. Create another restricted column (NETWR_BU04) to restrict BU04 as shown below

4. Create one Calculated column (NETWR_BU) in which the above three restricted columns will be used with expression as

Expression used in calculated column
if(isnull(“NETWR_BU02”) OR isnull(“NETWR_BU03”) OR isnull(“NETWR_BU04″),NULL,”NETWR”)

Activate the view and run the SQL query:

SELECT ERNAM, SUM(NETWR_BU), SUM(NETWR_BU02), SUM(NETWR_BU03), SUM(NETWR_BU04) FROM “_SYS_BIC”.”EDW1/AN_VBAK”

GROUP BY ERNAM;

We can hide the restricted columns NETWR_BU02, NETWR_BU03, NETWR_BU04 as these are used in calculated column for calculation purpose only.

Now do the data preview of final analytic view

After reading this thread (Restricted Measure Issue in HANA), Initially I came with Approach 2 and later Approach 1 is added based on OSS response from SAP HANA Development support. Thanks to Justin Molenaurand Jomy Joy.

Thank you for your time.