How to perform BETWEEN operator in Restricted measure in an Analytic View

[Former Member]

Hello Experts,

Suppose if we've to perform G/L Account restriction for any amount field between 80000 to 89999 then how shall we achieve this functionality ?

As of now there is no between operator  in SPS6 for restricted measure.

In addition a condition like below also doesnt work :

G/L Account < 80000 Exclude

G/L Account >89999 Exclude

Then what are the options to make it work.

Thanks & regards,

Jomy

[rama_shankar3 · Accepted solution]

You can have a calculation script view to restrict this data using SQL. As needed you can have a paramater table where restriction values are stored for calculation view.

Hope this helps.

Rama

[Former Member · Accepted solution]

Hi Rama,

I definitely would not like to do this in Calc View because of obvious reasons and we all know that nobody shall enter the area of Scripted Calculation View unless it is really really required because it just eats out on performance a lot.

Really appreciate if you can have mechanism in Analytical View.

Regards,

JOmy

[Former Member · Accepted solution]

How large is your dataset?

A few ideas and things to consider:

1) The best-performing solution would be to create a generated column that returns a single integer value (i.e. 1) for valid conditions, and 0 otherwise. Then you could use this field for your restricted column.

2) The logic would be easy to create in a calculated column. Performance may suffer though, hard to say without further details.

3) You could think of the problem as an "intersection" of two datasets: The first dataset includes measures where GL > 8000, and the second dataset is GL < 8999. So, in a CalcView, you'd include the same dataset in two source nodes, then filter as above with projection nodes, then do an "efficient intersection" similar to here (http://scn.sap.com/docs/DOC-41219 can also be done graphically), and then combine the resulting measure with the rest of your data via UCV. This would very likely be an "overkill" approach.

4) It's not true to say that scripted Calculation Views have bad performance. In fact, I've seen a case where a well-crafted SQLScript CalcView gets better performance than an Analytic View for a specific query profile against a star-schema type of structure. This is the exception, and not the rule - but want to clarify that there's nothing *inherently* slow about scripted CalcViews.

Cheers,

Jody

[Former Member · Accepted solution]

Hey Jody,

Thanks for your reply!!!

Can you please elaborate on your option (1), as it sounds interesting ?

My experience with other options-

Option - 2 Calculated measure involving restricted measure not only increase the maintainability by 3 times but also degrades the performance

Option - 3 I would really like to do this in ANV view as we already around 7-8 projections and now if we again have so many different projections for different G/L account ranges then it really messes up the Calc View

Option 4 -  For our requirement scripted Calc view doesn't suit at all and hampered the performance a lot !!

Regards,

Jomy

[Former Member · Accepted solution]

A generated columns is a physical column in HANA, which is calculated every time data is inserted into the table. You could use it for a restricted measure.

It has low overhead which is great for performance (on insert/update/delete).

Any time the table is dropped and re-loaded though, the definition of the generated column needs to be executed again.

(It can cause problems for ABAP Accelerator when poorly written ABAP (or any application layer code) expects a base table structure that's been changed from such generated columns.)

Here's sample code. See all of the details in the HANA SQL guide.

-- sample table
CREATE COLUMN TABLE FACT_TAB (GL INTEGER);


-- create generated column to use for restricted measure
ALTER TABLE FACT_TAB ADD ("IS_GL_IN_8000s" SMALLINT GENERATED ALWAYS AS CASE WHEN GL <= 89999 AND GL >= 80000 THEN 1 ELSE 0 END);


-- create test data
INSERT INTO FACT_TAB VALUES (25);
INSERT INTO FACT_TAB VALUES (85000);
INSERT INTO FACT_TAB VALUES (90000);


-- check results
SELECT * FROM FACT_TAB;

[Former Member · Accepted solution]

Nice Approach, but I'm using a different approach.

I've created a Table with which will be filled by a procedure with all G/L accounts and corresponding Text.

Now Use this Table to join with the Fact Table .

This seems to be really good  in terms of Performance.

anyways, Thanks for your replies.

Thanks & regards,

Jomy