Problem in creating Metrics based on a Metrics

[banda27]

Hi All,
I have a report in which few metrics are calculated using other metrics in the report. These metrics are created using ApplySimpe and CASE statements.

The problem is the SQL generated is very complicated and it is bigger that 64kb.

Eg: SELECT (des_amt * 20) as a
,(CASE WHEN (des_amt * 20) > 24
THEN des_des * 0.01
ELSE (des_amt * 20) ) as b
,(CASE WHEN (CASE WHEN (des_amt * 20) > 24
THEN des_des * 0.01
ELSE (des_amt * 20) ) > 50
THEN (des_amt * 20)
ELSE des_des * 0.01) as c
,......
goes on....

What I would like to see is
SELECT (des_amt * 20) as a
,(CASE WHEN a > 24
THEN des_des * 0.01
ELSE a ) as b
,(CASE WHEN b > 50
THEN a
ELSE b) as c
,.........

Can any one tell me which VLDB properties will help me to achieve this OR if there is any other way?

Thanks
-Sri

 

[chael]

Sri - there is no VLDB setting that applies to what you are doing.

You should consider building a table for what you are trying to calculate and then model it in...should be pretty simple...

des_amt | des_amt_case

This will simply the SQL greatly.

Chael

 

[chael]

One other suggestion...if you are on 7.2.x take a look at the BandingP function...

entaroadun knows the answer to this...look at his thread:
thread395-484865 luck since then entaroadun???

 

[entaroadun]

I haven't tried further with BandingP because of its inherent limitations (e.g. you can't choose between greater than and greater than or equal to when defining your range endpoints).

To answer banda's question:

MSI 7.2 has several different Case functions. Refer to the product documentation.

If this is not possible:

The SQL you want (the second set) is impossible. You cannot alias a column and then have another column refer to that alias.

The best you can hope for is separate passes for each calc. The first pass would get A. The second pass would base itself off of the first pass and get B. The third pass would get C... It would actually be a pretty efficient query.

Nothing is that easy. Try this approach.

Write your A metric with no ApplySimple.
Write three B metrics:
B part 1 would be des_des * 0.01 with a metric filter of A > 24.
B part 2 would be A with a metric filter of A <=24.
B would be B part 1 + B part 2.

Using this method, you should get one pass for A, three passes for B, three passes for C... It's not pretty, but it's definitely better than the repeated Case statements.

Let us know if this works.

 

[banda27]

Thank you chael & entaroadun for your help.

I have few more questions entaroadun.

1) We have MS 7.2.1. Can you please tell me where or in which manual the CASE functionality is explained?

2) How can I do separate passes for each calc?

Thanks You
Sri

 

[entaroadun]

1) Check the Analytical Functions reference. I have 7.2.2 and am looking at page 204. I believe 7.2.1 has the same book.

2) Just create the compound metrics that you've been doing:
A = des_amt * 20
B = Case(A > 24, des_des * 0.01, A)
...

Check the SQL it generates. Hopefully, it won't do the ugly nested CASE statements.

 

[banda27]

1) No, the manuals for 7.2.1 & 7.2.2 are different. 7.2.1 does not have CASE functionality.

2) Even after building the Metrics as compound metrics, the SQL generated is same nested....

Any way, Thank you for your help.