which approach is faster

[ali_hijazi]

Hello I got a dashboard with star schema and a one fact/linktable with 64M records so far

I have the following expression:

sum

(

    {

        <

            KPI={$(vL.KPI.Effort.Spent.MD)}

            >

        }

        AMOUNT

    )

this expression is put in a variable named vL.Spent.Mandays because I'm using it in severa charts that have different / addiotnal filters defined:
for example

{<
FLAG_ID={[0],[-1],[6],[7],[8]}
,SCENARIO_TYPE={[ACTUAL]}
,[Portfolio Level 1 ID]={22}
,TASK_NAME=-{'9.0-SAAS TECHNICAL ALLOCATION'}

>}
$(vL.Spent.Mandays)

the field FLAG_ID is in the linktable above
the field SCENARIO is linked to linktable via a key and is in the table SCENARIO_TYPE table
the field [Portfolio Level 1 ID] is in the table PROJECT_HISTORY
and the field TASK_NAME is located in the table DIM_TIMESHEET


this expression is causing the pivot table to respond very slowly
is it because of the variables I'm using in the expressions
do I need to write it as follows:

sum

(

    {

        <

            KPI={$(vL.KPI.Effort.Spent.MD)}

,FLAG_ID={[0],[-1],[6],[7],[8]}
,SCENARIO_TYPE={[ACTUAL]}
,[Portfolio Level 1 ID]={22}
,TASK_NAME=-{'9.0-SAAS TECHNICAL ALLOCATION'}

            >

        }

        AMOUNT

    )

but in this way I won't reuse the variable vL.Spent.Mandays

[marcus_sommer]

I wouldn't expect significantly differences in regard to the performance if the set conditions are on the inner or outer side of the aggregation and needing maybe very slightly more resources with they were mixed. Also not really in regard if they were within a variable or not because it's just a reference which is evaluated before the calculation started.

Much more related will be the data-model which is ideally a real star-scheme which means a single fact-table with n surrounding dimension-tables. The shown link-table approach will add a lot of overhead to run through all tables over the link-table to build the virtual table of the required dimensional context for the chart-aggregations. IMO is the data-model the bottleneck ...

[ali_hijazi]

Hello @MAR 
you said that the link-table adds a lot of overhead
the thing is that we have 4 fact tables all got the structure
the difference is in the values of two fields flag_id and KPI
KPI has values such as SPENT_MANDAYS, SERVICE_COST, INVOICED_LICENSE, ....
and each KPI is related to several flag_id values
so the above sample expression that I shared gets the spent mandays for allocations specified by the flag_id 
and the additional filters
what would be the best practice regarding the schema

[marksouzacosta]

Hi @ali_hijazi,

The variables are not the problem, like @marcus_sommer said.

The points of concern in your expressions are the red ones:

sum

(

    {

        <

            KPI={$(vL.KPI.Effort.Spent.MD)}

,FLAG_ID={[0],[-1],[6],[7],[8]}
,SCENARIO_TYPE={[ACTUAL]}
,[Portfolio Level 1 ID]={22}
,TASK_NAME=-{'9.0-SAAS TECHNICAL ALLOCATION'}

            >

        }

        AMOUNT

    )

You are comparing entire field sets. I would review those two sentences and also look for improvements in the design of your Data Model to better support your expressions.

Regards,

Mark Costa