Evaluate_AGGR Function for Sum over Partition By ...

3275611

Guys ,

am trying to achieve this ..

               SUM ( (E_373)) OVER (PARTITION BY E103195, (NP105)) AS NP104,

Need help where am trying to use

EVALUATE_AGGR('SUM (%1)  OVER  (PARTITION BY %2,%3)', "Fact - SCM AP Spend Summary"."Converted Amount","GL Period"."Period Year", "SCM Vendor Mapping"."Revised Vendor Name" )

When i use the simple logical query like ..

Sum("Fact - SCM AP Spend Summary"."Converted Amount" by "GL Period"."Period Year" ,"SCM Vendor Mapping"."Revised Vendor Name").

The result comes in no time  but the problem is until i use this "SCM Vendor Mapping"."Revised Vendor Name" in the query i get unwanted results ... and i dont want to add this column in the query . Now there are other calculated column based on "Revised Vendor" ..

So i thought of using Evalaute Aggr but the query adds a full outer join at the bottom and then it keeps running for ever.

WITH SAWITH0

     AS (  SELECT SUM (T355113.CONVERTED_AMT) AS c1,

                  T296961.PERIOD_YEAR AS c2,

                  T355156.REVISED_VENDOR_NAME AS c3

             FROM SCM_ACCOUNT_GROUP T353880        /* Dim_SCM_ACCOUNT_GROUP */

                                           ,

                  ORGANIZATIONS T296966                /* Dim_ORGANIZATIONS */

                                       ,

                  R3_GL_PERIOD T296961        /* Dim_R3_GL_PERIOD_GL_Period */

                                      ,

                  R3_AP_SPEND_SUMMARY T355113   /* Fact_R3_AP_SPEND_SUMMARY */

                                             ,

                  SCM_VENDOR_MAP T355156              /* Dim_SCM_VENDOR_MAP */

            WHERE (    T296966.ORG_ID NOT IN (5)

                   AND T353880.IFIS_GROUP NOT IN ('Out of Scope')

                   AND T355113.ORG_ID NOT IN (5)

                   AND T296961.PERIOD_NAME = T355113.PERIOD_NAME

                   AND T296966.ORG_ID = T355113.ORG_ID

                   AND T353880.ACCOUNT_ID = T355113.ACCOUNT_ID

                   AND T296961.PERIOD_SET_NAME = 'OPS Fiscal'

                   AND T355113.V3_VENDOR_ID = T355156.V3_VENDOR_ID

                   AND (T296961.PERIOD_YEAR IN (2014, 2015, 2090))

                   AND T353880.CATEGORY IS NOT NULL)

         GROUP BY T296961.PERIOD_YEAR, T355156.REVISED_VENDOR_NAME),

     SAWITH1

     AS (SELECT SUM (T355113.CONVERTED_AMT) AS c1

           FROM SCM_ACCOUNT_GROUP T353880          /* Dim_SCM_ACCOUNT_GROUP */

                                         ,

                ORGANIZATIONS T296966                  /* Dim_ORGANIZATIONS */

                                     ,

                R3_GL_PERIOD T296961          /* Dim_R3_GL_PERIOD_GL_Period */

                                    ,

                R3_AP_SPEND_SUMMARY T355113     /* Fact_R3_AP_SPEND_SUMMARY */

          WHERE (    T296966.ORG_ID NOT IN (5)

                 AND T353880.IFIS_GROUP NOT IN ('Out of Scope')

                 AND T355113.ORG_ID NOT IN (5)

                 AND T296961.PERIOD_NAME = T355113.PERIOD_NAME

                 AND T296966.ORG_ID = T355113.ORG_ID

                 AND T296961.PERIOD_SET_NAME = 'OPS Fiscal'

                 AND T353880.ACCOUNT_ID = T355113.ACCOUNT_ID

                 AND (T296961.PERIOD_YEAR IN (2014, 2015, 2090))

                 AND T353880.CATEGORY IS NOT NULL)),

     SAWITH2

     AS (  SELECT SUM (D2.c1) AS c1,

                  D1.c2 AS c2,

                  D1.c3 AS c3,

                  1 AS c4

             FROM SAWITH0 D1, SAWITH1 D2

         GROUP BY D1.c2, D1.c3),

     SAWITH3

     AS (  SELECT SUM (T355113.CONVERTED_AMT) AS c1,

                  T296961.PERIOD_YEAR AS c2,

                  T355156.REVISED_VENDOR_NAME AS c3

             FROM SCM_ACCOUNT_GROUP T353880        /* Dim_SCM_ACCOUNT_GROUP */

                                           ,

                  ORGANIZATIONS T296966                /* Dim_ORGANIZATIONS */

                                       ,

                  R3_GL_PERIOD T296961        /* Dim_R3_GL_PERIOD_GL_Period */

                                      ,

                  R3_AP_SPEND_SUMMARY T355113   /* Fact_R3_AP_SPEND_SUMMARY */

                                             ,

                  SCM_VENDOR_MAP T355156              /* Dim_SCM_VENDOR_MAP */

            WHERE (    T296966.ORG_ID NOT IN (5)

                   AND T353880.IFIS_GROUP NOT IN ('Out of Scope')

                   AND T355113.ORG_ID NOT IN (5)

                   AND T296961.PERIOD_NAME = T355113.PERIOD_NAME

                   AND T296966.ORG_ID = T355113.ORG_ID

                   AND T353880.ACCOUNT_ID = T355113.ACCOUNT_ID

                   AND T296961.PERIOD_SET_NAME = 'OPS Fiscal'

                   AND T355113.V3_VENDOR_ID = T355156.V3_VENDOR_ID

                   AND (T296961.PERIOD_YEAR IN (2014, 2015, 2090))

                   AND T353880.CATEGORY IS NOT NULL)

         GROUP BY T296961.PERIOD_YEAR, T355156.REVISED_VENDOR_NAME),

     SAWITH4

     AS (SELECT SUM (T355113.CONVERTED_AMT) AS c1

           FROM SCM_ACCOUNT_GROUP T353880          /* Dim_SCM_ACCOUNT_GROUP */

                                         ,

                ORGANIZATIONS T296966                  /* Dim_ORGANIZATIONS */

                                     ,

                R3_GL_PERIOD T296961          /* Dim_R3_GL_PERIOD_GL_Period */

                                    ,

                R3_AP_SPEND_SUMMARY T355113     /* Fact_R3_AP_SPEND_SUMMARY */

          WHERE (    T296966.ORG_ID NOT IN (5)

                 AND T353880.IFIS_GROUP NOT IN ('Out of Scope')

                 AND T355113.ORG_ID NOT IN (5)

                 AND T296961.PERIOD_NAME = T355113.PERIOD_NAME

                 AND T296966.ORG_ID = T355113.ORG_ID

                 AND T296961.PERIOD_SET_NAME = 'OPS Fiscal'

                 AND T353880.ACCOUNT_ID = T355113.ACCOUNT_ID

                 AND (T296961.PERIOD_YEAR IN (2014, 2015, 2090))

                 AND T353880.CATEGORY IS NOT NULL)),

     SAWITH5

     AS (SELECT SUM (D2.c1) OVER (PARTITION BY D1.c2, D1.c3) AS c4, 1 AS c5

           FROM SAWITH3 D1, SAWITH4 D2)

SELECT D1.c1 AS c1, D1.c2 AS c2, D1.c3 AS c3

  FROM (SELECT DISTINCT 0 AS c1, D2.c4 AS c2, D1.c1 AS c3

         FROM SAWITH2 D1 FULL OUTER JOIN SAWITH5 D2 ON D1.c4 = D2.c5) D1

WHERE ROWNUM <= 5000001..

Any thoughts Please help !!

FTisiot

Hi

Isn't what you are asking for just a Level based measure?

SUM ( (E_373)) OVER (PARTITION BY E103195, (NP105)) AS NP104

Thomas Dodds

Agreed ... this is a level-based measure

3275611

Thanks for the Answer thomas.

I agree this looks like an LBM

So i need Converted Amt  by Period and By Vendor and i can achieve this ... setting levels in RPD ..

Now please suggest

I have have more custom metrics and attribute columns which needs to use this Sum("Converted Amount)  by Year , Vendor".

So what makes more sense ...

creating a LBM  or individual metrics using evaluate function ....

In the mean while am giving a shot to this ...

Sudhir ...

Thomas Dodds

Build once - use many ... this is what makes sense!  Why build many?

Any logical measure to use the LBM will be correct ... any dimensional attribute to filter the LBM will work according to the logical hierarchies and the business model. 

^-- this is KEY; it all depends on your model

3275611

Sure Thomas i agree it depends on a model  ... will get back with LBM test...

But just an FYI, and am confused too .. whether its syntax or something else.

EVALUATE('SUM (%1) OVER  (PARTITION BY %2,%3)' , "Fact - SCM AP Spend Summary"."Converted Amount","GL Period"."Period Year", "SCM Vendor Mapping"."Revised Vendor Name" ) --- This does not work -- not the

--- Error being State: HY000. Code: 42015. [nQSError: 42015] Cannot function ship the following expression: EVALUATE. (HY000)

and

EVALUATE_AGGR('SUM (%1) OVER  (PARTITION BY %2,%3)' , "Fact - SCM AP Spend Summary"."Converted Amount","GL Period"."Period Year", "SCM Vendor Mapping"."Revised Vendor Name" ) ---- This keeps running for ever ...

Evaluate_Aggr('SUM (%1) BY %2,%3)' , "Fact - SCM AP Spend Summary"."Converted Amount","GL Period"."Period Year", "SCM Vendor Mapping"."Revised Vendor Name" ),,, Throws a from clause error ...

Any inputs will be great what s being missed here ..

Thomas Dodds

EVALUATE('SUM (%1) OVER  (PARTITION BY %2,%3)' , "Fact - SCM AP Spend Summary"."Converted Amount","GL Period"."Period Year", "SCM Vendor Mapping"."Revised Vendor Name" ) --- This does not work -- not the

--- Error being State: HY000. Code: 42015. [nQSError: 42015] Cannot function ship the following expression: EVALUATE. (HY000)

^-- you need to use _AGGR because of SUM being aggregation

EVALUATE_AGGR('SUM (%1) OVER  (PARTITION BY %2,%3)' , "Fact - SCM AP Spend Summary"."Converted Amount","GL Period"."Period Year", "SCM Vendor Mapping"."Revised Vendor Name" ) ---- This keeps running for ever ...

^-- precisely why this approach is not the best -- I have no insight into your database setup and why you have bad performance

Evaluate_Aggr('SUM (%1) BY %2,%3)' , "Fact - SCM AP Spend Summary"."Converted Amount","GL Period"."Period Year", "SCM Vendor Mapping"."Revised Vendor Name" ),,, Throws a from clause error ...

^-- mal-formed analytic function

Try in Answers:

New column formula = SUM("Fact - SCM AP Spend Summary"."Converted Amount" BY "GL Period"."Period Year" AND "SCM Vendor Mapping"."Revised Vendor Name")

^-- still not my recommended way to go

Joel

Hi @Thomas Dodds shouldn't that be:

New column formula = SUM("Fact - SCM AP Spend Summary"."Converted Amount" BY "GL Period"."Period Year" ,"SCM Vendor Mapping"."Revised Vendor Name")

The list of dimensions to partition the fact are comma separated and yes, I do agree totally with you that this should be deployed in the repository with aggregations and content levels all pre-defined.

Thomas Dodds

good catch!