Restricted Measure Issue in HANA

[Former Member]

Hello Experts,

I'm facing a challenge, wherein I need to do more than 1 exclusion on the same field but unfortunately it's not working.

HANA Rev: 62

Let's take  example:

I've a restriction on field ABGRU as shown in the screenshot

Now when I run the query, I still get data for Z3 and Z5 even though is it's excluded as shown below:

Then in that case, how ill you achieve this.

Is there something I'm missing ?

Thanks & regards,

Jomy

[Former Member]

Thanks. This thread was helpful.

[Former Member]

I think your issue is that this is "working as designed". The restricted measures window in the analytic view uses OR to combine lines. Therefore any record that is <> Z3 OR <> Z5 is included. Obviously no record can be both Z3 AND Z5, so nothing gets excluded from the result.

To get this to work you will need 2 restricted measures and a calculated measure. Calculated measure will be = ABGRU - RM_EXCLUDE_Z3 - RM_EXCLUDE_Z5.

Alternatively, you can use SQL in the Calculation View as suggested by others.

[justin_molenaur2]

Adam, I would agree with you as far as the OR part of the "include", but I would highly doubt that the "exclude" functionality was designed with an OR on purpose. In fact, since I saw this response tonight I checked the status of my open OSS message and found this response. There will be a fix put in place for a future revision.

So yes, there are now three possible workarounds on the table until revision 7x contains this fix

Regards,

Justin

>>>OSS Response<<<

We could see this behavior also in our development environment. The

reason why it does not work is that when deploy the model, the

restrictions are translated into expressions which are connected with

"OR" instead of "AND". In the create statement of the olap view, you

could actually see this.

I will inform you once we have a fix plan for this issue. As a

temporary workaround, you may manually drop and recreate the olap view

and manually modify the expression for the restricted measure.

Best regards,

SAP HANA Development Support

[Former Member]

Hi Justin,

Yes you are right. It is bug because if there are 10 specific excludes which are there in my case, you really dont want to create 10 different RM and a calculated measure on top of it.

Also I've noticed that RM inside the CM sometimes slows down the performance.

BTW, I did not understood SAP's reply,  "Manually drop and recreate the OLAP View and manually modifying the expression for RM"---What does that mean ?

Thanks & regards,

Jomy

[Former Member]

Working as DESIGNED, but yes I would agree that it's hard to imagine that this is what was intended. One other wrinkle to this discussion is related to ranges. Even on include, they do not work because of the OR clause. For example:

FIELD > 5

FIELD < 10

Does not give you the range 5-10 as you might expect. Instead, it does nothing to limit your results at all as each record will meet one or the other condition. Unless you have a discreet list of includes, you can have only one condition per restricted measure when using excludes or range operators.

[justin_molenaur2]

I wouldn't even go that far, more like "works as erroneously built"

I will add your example to the message so we can attempt to get a response on that too.

Regards,

Justin

[Former Member]

Hi Justin,

I tried to do what SAP suggested based on your reply.

a) I dropped the Column View of the Analytic View manually

b) Recreated the Column View Manually such that Generated 'OR' is replaced with 'AND'

This works like a charm 🙂

It's just that we need to manually create this view for any View Update till we get the fix instead of creating too many RM's and one Calculated Measure to achieve one particular stuff.

Thanks & regards,

Jomy

[rindia]

Hi Jomy,

How you recreated the column view manually?

Is it like you exported the view and modified the xml or different way?

It would be great to community if you can explain.

Regards

Raj

[Former Member]

Hi Raj,

Definitely.  Please find the steps below:

a) Goto the SYS_BIC schema and find the Column View for your Model. In my case it was a Analytical View

b) Now double click on the Column View and then Click on the second tab i.e. 'Create Statement'. Therein You can search for restriction specific to your Restricted Measure.

Here you can see Exclusions are having 'OR' Statement with Values Z3 and Z5 for field 'ABGRU' and hence results are wrong as shown below.

Basically I want everything except Z3 and Z5.

c) Now you can copy this SQL Code of this view and then drop the View via the following statement

Drop View "_SYS_BIC"."Test/TEST_DLVRY"

d) Now recreate the Procedure by pasting your copied code in SQL Console but just changing that OR to AND.

e) Now I re-ran my SQL and below is correct result.

I hope I was able to explain each and every step here.

Let me know if you need any additional info.

Thanks & regards,

Jomy

[rindia]

Thanks Jomy,

That was fair enough.

You might be aware that I created one document(http://scn.sap.com/docs/DOC-53218) for alternate solution for this. With your permission I want to add this to that document.

Could you please approve so that it will be useful to community till the bug is fixed.

Regards

Raj

[Former Member]

Hi Raj,

Yes Please and if possible mark this answer as correct or helpful whatever you feel so. This will help not only other members to find this thread where they can find some concrete conclusions.

Thanks & regards,

Jomy

[rindia]

Hi Jomy,

I modified my document accordingly. But I cannot close this thread as you are the owner.

Regards

Raj

[Former Member]

Thanks Raj!!!

It's a great help to the community.

Regards,

Jomy

[justin_molenaur2]

Hey all, I got a response back from SAP development. Looks like this fix will be included in Rev 75. And the range issue mentioned above, with this fix we can work around the issue that Adam raised as well.

Thanks,

Justin

Hi Justin,

The fix is ready from development and it will go through the testing

for delivery process. Based on current status, most likely it would be

available with rev75 planned to be released in May.

Best regards,

SAP Development

Hi Justin,

With the fix regarding "not equal to" in place, you may also be able

to use it together with > or < in order to specify the range condition

as a workaround.

Best regards,

SAP Development

[Former Member]

Thanks for updating...:)

[justin_molenaur2]

		Got a response from SAP on this.
		Hi Justin, The fix for this issue has been released with SPS08 rev80. It will be also included in rev74.1 which is coming soon. For the interval, please use exclude with > or <, until the operator is available in next studio releases. Best regards, SAP HANA Development

Happy HANA,

Justin

[rindia]

Jomy,

Here is the solution.

http://scn.sap.com/docs/DOC-53218

Regards

Raj

[justin_molenaur2]

Here is what I'll say - I can appreciate the solution (really is a technical workaround) presented by Raj, but this is really a workaround for a bug (in my opinion). I just opened OSS message 246011 in order to get a straight answer from Development.

If the INCLUDE works as expected, the inverse of that should also work.

Summary: using restricted measures in an Analytic View works fine when INCLUDING multiple values for a given column. However, when EXCLUDING multiple values, this does not appear to work.

Create table

CREATE COLUMN TABLE "MOLJUS02"."TEST_RKM" ("UNIT" NVARCHAR(4),

        "VALUE" DECIMAL(15,

       2) CS_FIXED) UNLOAD PRIORITY 5 AUTO MERGE

Insert Data

INSERT INTO "MOLJUS02"."TEST_RKM" VALUES ('AB', 100.00);

INSERT INTO "MOLJUS02"."TEST_RKM" VALUES ('BC', 100.00);

INSERT INTO "MOLJUS02"."TEST_RKM" VALUES ('DE', 100.00);

Create Restricted Measure with an INCLUDE on two values of column “UNIT”

Create Restricted Measure with an EXCLUDE on two values of the column “UNIT”

Data Preview

The INCLUDE Restricted Measure works as expected, however the EXCLUDE Restricted Measure does not actually exclude the two values defined in the view.

I'll update back when I get a response.

Regards,

Justin

[rindia]

Thanks Justin,

Hope this will be fixed by SAP in coming revision or SPS.

Regards

Raj

[Former Member]

Thanks Justin!!

yes you are right, this is definitely a bug and needs to be addressed. I cant even take the approach which Raj mentioned because I was talking to my client and he clearly we should not workaround a bug and since we've around 10 exclude on a single field. Hence creating 10 different restricted measure and creating a Calculated column will be costly in terms of maintenance as well as performance as well.

Lets see what SAP has to say on this.

Thanks & regards,

Jomy

[rindia]

Hi Jomy,

For single restriction, exclude will work but for multiple restrictions exclude not working on same attribute.

If there would be "Not Equal" operator had made the task easier.

One alternate could be using calculation view.

Regards

Raj

[Former Member]

Hi Raj,

This is what I've noticed but how to achieve this using the Calculation View.

Can you please elaborate ?

Thanks & regards,

Jomy

[justin_molenaur2]

You could achieve this in a projection or expression node with the filter expression.

"ABGRU" not in ('A', 'B')

OR

"ABGRU" != 'A' AND "ABGRU" != 'B'

Regards,

Justin

[justin_molenaur2]

That seems like a bug to me, I would advise to open an OSS message on this to get it resolved in a future release. Has anyone tried this on a SPS07 revision?

Regards,

Justin

[former_member182302]

Hi Justin,

I tried on Rev 70. It didn't work for me either. Seems like this Bug is still OPEN

Regards,

Krishna Tangudu

[former_member186082]

Hi Jomy,

Have you tried restricing on single value? Is that working?

Did you try checking Include for both the values and try running the query?

Regards,

Chandra.

[Former Member]

Hi Chandra,

Single value Exclude works.

Single value Include works.

Multiple Value Single Field exclude DO NOT WORK.

Multiple Value  Single FIeld include WORKS.

Thanks & regards,

Jomy

[former_member182302]

Hi Jomy,

Am on rev 68.

It seems to work fine i kept a restriction like this shown below:

And i don't see salary values for AMER in the output below as shown:

Regards,

Krishna Tangudu

[Former Member]

Hi Krishna,

It's working for you because you have a single restriction on that same field.

Suppose there would have restriction of :

REGION EQ AMER    Exclude

REGION EQ APAC    Exclude

Then none of teh restrictions would have worked, because it seems there is a bug as of now, wherein HANA do not consider it as OR operation but treat it as AND for a "Multi-Value Single Field Restriction", unless and until there are other ways to solve this.

Thanks & regards,

Jomy

[former_member182302]

I see what you are saying Joy. Adding an update to you am working on Rev 70 and i see the same behavior..

So i think only using Script based calculation view is the way to handle this or also depending on the reporting solution like BO should be able to handle this..

Anyways it seems like a bug which must be addressed.

Regards,

Krishna Tangudu