topic Re: Really Slow Graph in QlikView

Really Slow Graph

Tue, 10 Sep 2013 21:28:27 GMT

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()
)
)
)
)
)
)
)
)
)
)
)
)

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

Re: Really Slow Graph

paul_scotchford — Tue, 10 Sep 2013 22:50:44 GMT

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.

Re: Really Slow Graph

michael_anthony — Wed, 11 Sep 2013 05:55:55 GMT

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.

Re: Really Slow Graph

Wed, 11 Sep 2013 15:58:58 GMT

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.

Re: Really Slow Graph

Wed, 11 Sep 2013 16:03:50 GMT

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.

Re: Really Slow Graph

rajeshvaswani77 — Wed, 11 Sep 2013 17:48:51 GMT

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

Re: Really Slow Graph

Wed, 11 Sep 2013 17:54:15 GMT

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

Re: Really Slow Graph

rajeshvaswani77 — Wed, 11 Sep 2013 18:00:23 GMT

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

Re: Really Slow Graph

Peter_Cammaert — Wed, 11 Sep 2013 20:33:21 GMT

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

Re: Really Slow Graph

paul_scotchford — Wed, 11 Sep 2013 21:28:16 GMT

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

Re: Really Slow Graph

michael_anthony — Wed, 11 Sep 2013 23:33:16 GMT

Are you able to post the qvw - even a cut down version?

What is the MetricFlag? I assume it's a variable the user selects to control what presents in the graph, which would also suggest it is a single value. You might need to consider multiple graphs based on that selection - and multiple formula's. So Graph1 would only show if MetricFlag = 1, and the COMPUTEDMETRIC_1 formula would be trimmed down to only include relevant parts. Graph2 would only show if MetricFlag = 2, and again the formula COMPUTEDMETRIC_2 would only need to include relevant parts.

Does mean a lot more setup in your qvw, but users shouldn't notice, and may allow you to retain the flexibility of not needing to pre-calculate results.

Re: Really Slow Graph

Fri, 13 Sep 2013 18:18:23 GMT

Thanks Peter. This sounds promising.

I already had an inline table setup with the Metric Name and the Metric Number. This table is used to create a list box for the user to select a metric to display in the graph and set the MetricFlag variable based on the metric number.

I added a column to the table with the computed metric formula. I named the field ComputedMetric, and temporarily removed my original variable that had the same name. I left the expressions in the ComputedBench variable and the chart dimension exactly the same as it was before since I named the field the same as the old ComputedMetric variable. The graph shows no data.

Do I need to setup my inline table or the expressions in the benchmark variable differently to make this work? Here is the code for my inline table:

METRIC_T:

LOAD * INLINE [
Metric_Selected, Metric_Num, ComputedMetric
Total Expense per Adjusted Patient Day, 2, sum(NormTotalExpenseEVS) / sum(OP_TEMP_EVS.ADJPATIENTDAYS)
];

Re: Really Slow Graph

Fri, 13 Sep 2013 18:22:11 GMT

Rajesh - Thanks. Our IT department handles our data warehouse. Unfortunatly, I don't have direct access to it, but if I am unable to find another solution I will bring this up to our IT department.

Re: Really Slow Graph

Fri, 13 Sep 2013 18:29:14 GMT

Paul -

I know we have a data warehouse that we ping from, but we don't have any pre-calculate fields other than at the single month level right now. Since I'm not part of IT, how much work would it be for the IT department to set the pre-calcs up? Is this something that would be simple for them to implement or would it be a large project? They would have to do a calculation for every possible month combination for each profit center.

I am hoping to solve it on my end, but if the inline table and creating separate graphs doesn't work this may be the route I have to go.

Thanks,

Shannon

Re: Really Slow Graph

Fri, 13 Sep 2013 18:40:51 GMT

Michael,

Thanks for the reply. Due to the nature of the info in the QVW I am not able to post the file here.

The MetricFlag is a variable set based on the user's selection in a list box. The list box is based off of an inline table (see my reply to Peter for an example of the code in the inline table ) and drives which metric is displayed in the graph. I only included one line of the inline table in the post. The list box is set to only allow one choice, so using Sum below works. Here is the code for MetricFlag:

=if(GetSelectedCount(Metric_Selected)<> 1 And GetFieldSelections(LOS)='Food', 101,
if(GetSelectedCount(Metric_Selected)=1, Sum({<Metric_Selected = {"= Metric_Selected = GetFieldSelections((Metric_Selected))"}>} Metric_Num),
2
)
)

I am going to try putting the formulas in an inline table first. If that doesn't work, I am going to try individual graphs to see if it speeds things up.

Thanks,

Shannon

Re: Really Slow Graph

paul_scotchford — Sun, 15 Sep 2013 21:47:43 GMT

Sure, but in our case we (IT) maintain the Data Warehouse , we also develop our QV solutions for the Enterprise, which means we can oversee data governance, especially for user Self Service BI (i.e. Models and any required specific precalcs) we deal in large data, hence its important that IT are heavily involved in development.

Yes everyone here is making great suggestions to help solve your performance problem, I would still be reiterating the argument for greater involvement of your DW.

Cheers

Paul

Re: Really Slow Graph

Mon, 16 Sep 2013 14:33:37 GMT

Peter - I got the metrics from the inline table working, and it's really fast. Thank you so much!