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?
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.
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) ];
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.
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 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:
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.
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.
