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

[marcus_sommer]

64 M in a star-scheme fact-table and a base-expression like sum(Amount) + various set statements should perform quite fast. I wouldn't expect significantly delays on a current laptop and surely not against an adequate server environment - by using a "normal" pivot. If the aim is to display hundreds columns with thousands of rows and in the end a huge number of cells the calculation will need some time ... Further make sure that there no calculated dimensions within the chart.

Nevertheless there may potential to improve the performance, for example replacing the key-fields with autonumber, removing a row-formatting from the values, applying an appropriate round/floor/ceil() against the values.

[Lech_Miszkiewicz]

HI @ali_hijazi 

Understand. So there are couple of factor I would think:

1. What you are not telling us is If you are using any part of this expression as a master measure which is then re-used with outer set analysis?
   1. if you are - then fair enough
   2. if you are not - then I don't see a point of overcomplicating this as using individual expressions would work just fine and you can still use  KPI={$(vL.KPI.Effort.Spent.MD)} as part of your set analysis
      1. having it like you suggested would be my preferred way: 

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)

• Another point I would raise is that values you use in set analysis are not existing directly in your fact table I suppose. You are loading them as dimensions. Unless they are creating 1:Many relationship with the data (or could cause duplicates if moved to fact table) I would move those values directly to fact/link table and reference my set analysis directly from fact table
• This would then bring next point where effectively you could create more comprehensive flags to replace FLAG_ID={[0],[-1],[6],[7],[8]} with single flag for this scenario
• Next thing I want to raise is that pivot table object performance is not the same as straight table performance!!! Yeah - I know, it is controversial statement but it is proven by our internal tests. It is just way slower. Have you tried using straight table for this expression just to see how it would perform? Maybe we are focusing on wrong thing in this scenario and maybe it is what it is. 
  1. When looking at pivot table see if you have subtotals enabled or if you use indent mode and what layout you are actually requiring. We often substitute pivot tables with straight tables to gain performance where such substitution can be possible
• Last thing is: Is this Qlik cloud? if it is it may be that you are maxing the allocated engine capability which by default has rather unimpressive specs (I am guessing something like 16gb and 8 CPU core) which with large app may need dedicated engine and "large app" allocation enabled. This is just a thought.

cheers

Lech