Skip to Content
Author's profile photo Raj Kumar S

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.

VBAK91.jpg

                    Incorrect result                                                                         

VBAK92.jpg

                          Correct result

VBAK93.jpg

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.

VBAK1.jpg

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;

VBAK2.jpg

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.

VBAK3.jpg

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:

VBAK4.jpg

Activate the view and run the query:

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

GROUP BY ERNAM;

VBAK5.jpg

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

VBAK6.jpg

Activate the view and run the query:

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

GROUP BY ERNAM;

VBAK8.jpg

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”.

  R1.jpg

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.

  R2.jpg

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

  R3.jpg

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.

VBAK9.jpg

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

VBAK10.jpg

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

VBAK11.jpg

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”)

VBAK12.jpg

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;

VBAK13.jpg

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

VBAK14.jpg

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.

Assigned Tags

      15 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Srinivasulu Reddy Tanguturi
      Srinivasulu Reddy Tanguturi

      Hi raj

      Very nice document , its very useful.

      Thank you

      Srinivas

      Author's profile photo Former Member
      Former Member

      Good one..

      Author's profile photo Raj Kumar S
      Raj Kumar S
      Blog Post Author

      Thanks Srinivas, Nageshwar.

      Author's profile photo SIVA P
      SIVA P

      Good document..

      Author's profile photo Azeem Quadri Mohammed Abdul
      Azeem Quadri Mohammed Abdul

      Thanks.

      Author's profile photo Raj Kumar S
      Raj Kumar S
      Blog Post Author

      Thanks Azeem

      Author's profile photo Former Member
      Former Member

      Very Nice Document Raj....

      Thanks

      Ravi

      Author's profile photo Raj Kumar S
      Raj Kumar S
      Blog Post Author

      Thanks Ravi

      Author's profile photo Former Member
      Former Member

      Nice work Sir, Thank you..

      Author's profile photo Swaroop kumar
      Swaroop kumar

      thanks for sharing.

      Author's profile photo Raj Kumar S
      Raj Kumar S
      Blog Post Author

      Thanks Ashwin and Swaroop

      Author's profile photo ramesh vankudoth
      ramesh vankudoth

      Nice document Raj.....

      Author's profile photo Former Member
      Former Member

      Good one!!

      Author's profile photo Mayank Jaiswal
      Mayank Jaiswal

      Nice Doc 🙂

      Author's profile photo Former Member
      Former Member

      Hi ,

      Any One please send the proper document on SAP ECC 6.x to SAP BODS 4.x Data Migration.