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

[Lech_Miszkiewicz]

Totally agree with @marcus_sommer .

i have been personally in the wrong boat of creating link tables instead of concatenated single fact. I learned my lesson and now i always use concatenated, single fact table model where all facts are in single table with the flags used in set analysis as well as the actual field distinguishing fact rows which i normally add to set analysis too to narrow down number of rows expression is run against. 

cheers

Lech

[ali_hijazi]

Hello @Lech_Miszkiewicz @marcus_sommer  @marksouzacosta 
sorry the name of the table is misleading
the linktable table is actually is a concatenation of 4 fact tables; I should have called it FACT for example.

the concatenated fact tables all got the same structure (project_key, month_key, employee_key, currency_id, scenario_id, flag_id..)
these keys/ids are linked to the surrounding dimension tables (chek screen shot below):

the measure is always sum(AMOUNT) ; if we want to calculate the total spent mandays we write sum({<KPI={[spent_mandays]}>}AMOUNT)
if we want to caluclate the service cost for example we write sum({<kpi={cost1,cost2,cost3}>}AMOUNT)
other costs may have conditions on the flag_id, or portfolio id (part of the project_history table)
....
the fact tables contain data related to COST, EXPENSE, REVENUE, and MANDAYS
the heavy calculation is related to the calculation of the Margins (Operating, Direct, and Contribution)
the calculation of the direct margin is complex it is the sum of costs + revenue + expense

The contribution margin is Direct Margin + another set of costs specified by KPI, flag_id and other conditions related to projects, with OR operator between those conditions
then I got to calculate the direct margin % which is the direct margin / revenue

so expressions would look something like
sum({< filters...> + <filters_2> + <filters_3>...>} AMOUNT) ;
sum({< filters...> + <filters_2> + <filters_3>...>} AMOUNT)/sum({< filters...> + <filters_5> + <filters_6>...>} AMOUNT)

the "linktable" or the "facts" table has around 64M records

so when in a sheet we want to display the costs, expenses, mandays, and margins in a pivot table
the response is taking "ages"

how can I improve performance? can it be by caluclating the revenue in the script for example? but this would add so many rows, and we might end with over 100M records and this number will increase by time

[ali_hijazi]

hello @marksouzacosta 
the flag_id corresponds to a group of data
I want the "actual" man-days spent (I have actual data (scenario=actual) and budget or forecast data where scenario would be either budger or FCS#1, FCST#2, FCST#3

portfolio id 1 = 22 means the clients mandays i.e. on the clients' projects (not technical or internal,...)
and the flag_id in the above values 
the flag_id, and the KPI fields are in the "linktable" or the "fact" table
the portfolio field is in the PROJECT_HISTORY table 
the scenario field is in the SCENARIO table

the name of the central table is misleading ; it is the concatenation of 4 fact tables ; I should have called it FACT

so this is a star schema what else can be done!!!!

[ali_hijazi]

hello @Lech_Miszkiewicz 
this is what I have done
the LINKTABLE in the middle is actually a concatenation of 4 fact tables
maybe the name is misleading
but all the tables surrounding this table are dimensions