Really Slow Graph

[#1]

HI,

I've created a stacked bar chart.  It works correctly, but it takes a considerable amount of time to execute.  The chart is dynamic, and has to work for any possible combination of time periods that the user selects, so I have to do the computations on the fly inside of the graph. I included the code from the chart dimension and the variables.

I suspect the problem is that the ComputedMetric variable is executed multiple times inside of the ComputedBench variable and that the ComputedBench variable is executed multiple times inside of the conditional statment in the chart dimension.

1. Any suggestions on how to make the code execute faster?

2.  Can I make this execute quickly with variables or would I be better off creating a function in VB script?

3. If I need to create a function, do you know of any good training resources?

************************************************************************************************************************************

CHART DIMENSION

  =AGGR(if($(ComputedBench)='25th', '25th', if($(ComputedBench) = '50th', '50th', if($(ComputedBench) = '75th', '75th', if($(ComputedBench) = 'Over 75th',

'Over 75th')))), Year ,FacLocationFood)

*************************************************************************************************************************************

*************************************************************************************************************************************

ComputedBench VARIABLE

if(MetricFlag < 100,
     if(MetricFlag = 2 OR (MetricFlag = 3 And EVS_DOM_METRIC = 'APD'),
          if(($(ComputedMetric)<> 0 AND $(ComputedMetric) <> Null()),if($(ComputedMetric) < [TotExp/APD -          25th], '25th', if     ($(ComputedMetric) < [TotExp/APD -50th], '50th', if($(ComputedMetric) < [TotExp/APD -75th], '75th',

           'Over 75th'))), NULL()),
     Null()
     ),
if(MetricFlag >= 100 And MetricFlag < 200,
     if(OP_TEMP_FOOD.DIETICIANS = 'Yes',
          if(MetricFlag = 101,
               if(($(ComputedMetric)<> 0 AND $(ComputedMetric) <> Null()),if($(ComputedMetric) <

                     [Food Total Expense/TMEDietitians - 25th], '25th', if($(ComputedMetric) < [Food Total Expense/TMEDietitians - 50th],

                    '50th', if($(ComputedMetric) < [Food Total Expense/TMEDietitians - 75th], '75th', 'Over 75th'))), NULL()),
          if(MetricFlag = 102,
                 if(($(ComputedMetric)<> 0 AND $(ComputedMetric) <> Null()),if($(ComputedMetric) <

                 [Food Total Expense/APDDietitians - 25th], '25th', if($(ComputedMetric) < [Food Total Expense/APDDietitians - 50th],

                 '50th', if($(ComputedMetric) < [Food Total Expense/APDDietitians - 75th], '75th', 'Over 75th'))), NULL()),

     NULL()
     )
),

if(MetricFlag = 101,
    if(($(ComputedMetric)<> 0 AND $(ComputedMetric) <> Null()),if($(ComputedMetric) < [Food Total Expense/TME - 25th],

     '25th', if ($(ComputedMetric) < [Food Total Expense/TME - 50th], '50th', if($(ComputedMetric) < [Food Total Expense/TME - 75th],

     '75th', 'Over 75th'))), NULL()),
if(MetricFlag=102,
     if(($(ComputedMetric)<> 0 AND $(ComputedMetric) <> Null()),if($(ComputedMetric) < [Food Total Expense/APD - 25th],

     '25th', if ($(ComputedMetric) < [Food Total Expense/APD - 50th], '50th', if($(ComputedMetric) < [Food Total Expense/APD - 75th],

     '75th', 'Over 75th'))), NULL()),
NULL()
)
)
)
)

)
**************************************************************************************************************************************************

ComputedMetric VARIABLE

if(MetricFlag = 1 OR (MetricFlag = 3 And EVS_DOM_METRIC = 'SQFT'),
     ((sum(NormTotalExpenseEVS) / avg(OP_TEMP_EVS.NETSQUAREFEETCLEANED)) / SelectedMonthCount) * 12,
if(MetricFlag = 2 OR (MetricFlag = 3 And EVS_DOM_METRIC = 'APD'),
     sum(NormTotalExpenseEVS) / sum(OP_TEMP_EVS.ADJPATIENTDAYS),
if(MetricFlag = 4 OR (MetricFlag = 6 AND EVS_DOM_METRIC = 'SQFT'),
     ((Sum(OP_TEMP_EVS.TOTHRSWORKED) / avg(OP_TEMP_EVS.NETSQUAREFEETCLEANED)) / SelectedMonthCount) * 12,
if(MetricFlag = 5 OR (MetricFlag = 6 AND EVS_DOM_METRIC = 'APD'),
     Sum(OP_TEMP_EVS.TOTHRSWORKED) / Sum(OP_TEMP_EVS.ADJPATIENTDAYS),
if(MetricFlag = 101,
     if(sum(NormTotExpFood)<>0 AND sum(OP_TEMP_FOOD.ARTTOTMEALEQUIV) <>0,
          sum(NormTotExpFood) / sum(OP_TEMP_FOOD.ARTTOTMEALEQUIV),
     NULL()),
if(MetricFlag = 102,
     if(sum(OP_TEMP_FOOD.NORMTOTEXPPERADJPATIENTDAY)<> 0,
          sum(NormTotExpFood) / sum(OP_TEMP_FOOD.ADJPATIENTDAYS),
     NULL()),
if(MetricFlag=103,
     sum(NormNetExpFood) / sum(OP_TEMP_FOOD.ARTTOTMEALEQUIV),
if(MetricFlag = 104,
     sum(NormNetExpFood) / sum(OP_TEMP_FOOD.ADJPATIENTDAYS),
if(MetricFlag = 105,
     sum(OP_TEMP_FOOD.TOTHRSWRKD) / sum(OP_TEMP_FOOD.ARTTOTMEALEQUIV),
if(MetricFlag = 106,
     sum(OP_TEMP_FOOD.TOTHRSWRKD) / sum(OP_TEMP_FOOD.ADJPATIENTDAYS),
if(MetricFlag = 107,
     sum(OP_TEMP_FOOD.FOODEXPACTUAL) / sum(OP_TEMP_FOOD.ARTTOTMEALEQUIV),
if(MetricFlag=108,
     sum(OP_TEMP_FOOD.FOODEXPACTUAL)/ sum(OP_TEMP_FOOD.ADJPATIENTDAYS),
     NULL()
)
)
)
)
)
)
)
)
)
)
)
)

*************************************************************************************************************************************************

[paul_scotchford]

How much data are you importing to the model ?

Is there scope to do some pre-computations in the load script to your QVD ? If so you maybe able to

reduce the amount of computation in you metrics. 

[michael_anthony]

Can the variables be determined at the document level rather than within the chart.  My understanding of what your doing with the variable substitution into your expression is putting the full formula into each cell of your chart.

If the ComputedBench and/or ComputedMetric variable are the same for all cells, if you put a "=" at the start of the variable it should calculate the result once and then use the result in the cells.

[#4]

Paul - There are approximatly 11,000 records.

In the graph, I need to compute a single combined metric for whatever time period the user iselects.  So if a user selects January - June, I need to compute the metric and bench it based on 6 months of data, and if the user were to select February & March I would need to compute the metric and bench it based on two months of data.  Since there are so many possible combinations that the user could select, I don't think I would be able to do any of the calculations in the script.

[#5]

Michael - Thanks for the suggestion.  I gave this a try.  What ended up happening is that the variables didn't update every time they should of.  I ended up with 100% of accounts in a single bucket.

[rajeshvaswani77]

Hi,

The way out of this is, you could consider building a snapshot that will have data for the possible date selections. Alternatively you could try to create a balance between a snapshot and your expression.

thanks,

Rajesh Vaswani

[#7]

Thanks for the reply.  How do I build a snapshot? Do you know of any good training resources?

[rajeshvaswani77]

Ideally what you are doing here should have been done in a data warehouse with pre-calculated values ready for use. To create a snapshot you would have to convert this logic to a script(rather than expression you have now) . Say your data is from 1 jan 2013 till today. Then you will have to pre calculate the value and keep them ready for all the days from 1 jan 2013 till today. The chart will fetch and show the values that are already there and will happen just seamlessly. You would have to loop through for every possible combination and build you snapshot. Based on your requirement the snapshot would remain as is and need to do the loading only once and stay as a QVD or it could be that you have to do this all days. Creating a full snapshot would be expensive and take time, however your charts would display data on the fly as the values are already there.

At a low level you might end up with a for loop and the logic inside having a peek function.

thanks,

Rajesh Vaswani

[Peter_Cammaert · Accepted solution]

Amongst a lot of other things, the complex IF-constructs are killing you.

How about creating an INLINE table with two columns:

     MetricFlag

       contains a row for each value from 1 to 108

     FormulaUsedInComputedMetric

          contains the formula to be applied for this MetricFlag

Use regular QlikView associative behavior to apply a formula linked to a single MetricFlag immediately instead of walking the ComputedMetric construct 1000s of times.

Peter

[paul_scotchford]

Hi Shannon,

There's a few suggestions in the thread heading down a good path, I favour mostly in the direction of a

Data Warehouse / Mart. 11K rows is pretty small. The snapshot idea may work, but we always do

Data Warehouse backend (SQL Server), e.g.. My inventory row count 2013 YTD is 250Mil, I do a lot of complex precalc in the Load [ DW ---> QVD's] that way I can keep my calcs small in the Dashboard which keeps my User experience performant.

I would (if I were you) explore a datamart, following a standard Star Schema (Dims and Fact(s) ) , analyse how

you will get the data into a QVD.

Try that first , a big problem can be broken down into smaller ones to provide a good solution

cheers