topic Performace steps in QlikView

Performace steps

Mon, 12 Apr 2010 15:23:25 GMT

Hi,

I have a report which is taking very long to get results:

I have added the following dimensions:

1.=if(MONTH_NUMBER=calendar_month_num and calendar_year=YEAR_NUMBER , Monthly_Budget)

2.=if(MONTH_NUMBER=calendar_month_num and calendar_year=YEAR_NUMBER,LY_Monthly_Budget)

3.=if(MONTH_NUMBER=calendar_month_num and calendar_year=YEAR_NUMBER,MTD_Percent_To_Budget)

4.=if(MONTH_NUMBER=calendar_month_num and calendar_year=YEAR_NUMBER,LY_MTD_Percent_To_Budget)

I have selected the option suppress when value is null but it takes like 2-3 mins to get the results.Pls suggest any performance improving techniques.Enclosed is my data model and report.

Thanks,

Swetha

Performace steps

d_pranskus — Mon, 12 Apr 2010 15:37:49 GMT

My sugestion is to move conditions to expression and use set analysis, something like this

SUM({$<MONTH_NUMBER={$(=calendar_month_num)}, YEAR_NUMBER={$(=calendar_year_num)}>} [SomeAmountField])

Cheers

Darius

Performace steps

Mon, 12 Apr 2010 16:06:08 GMT

Thanks a lot Darius.

I have only one final question.The result is represented as columns but I want to get as only one value instead of so many values .And alos am unable to drag it under storenumber.pls suggets if its possible.Pls find the enclosed result as result of the suggested answer.

Thanks,

Swetha

Performace steps

Oleg_Troyansky — Mon, 12 Apr 2010 18:13:31 GMT

Your best solution is to link your OrderDate calendar to the Order Date, instead of using the "island calendar". Then you won't have to use so may IF conditions in your dimensions.

Performace steps

Mon, 12 Apr 2010 18:21:35 GMT

Hi Oleg,

I do not need Orderdate table at all.I can even remove that table completely the problem is with store_flas_1 not with orderdate.

Thanks,

Swetha

Performace steps

Oleg_Troyansky — Mon, 12 Apr 2010 20:07:57 GMT

Swetha,

your data structure needs some help... It looks like you keep both the detailed data (Orders), along with Summarized data (Store_Flash_1), linked only by store number, and the dates and months are scattered across those tables. You need to build a structure in which your detailed data is linked to the summarized data in such a way that each date is also linked to the corresponding Year, Month, etc...

Performace steps

sczetty — Mon, 12 Apr 2010 20:31:39 GMT

Swetha,

I also think that the Store_Flash_1 table is the problem. Looks like this is a monthly store budget table, yet it has daily data that would be better left to QV to calculate (such as month to date figures). If you just had Store Number, Year, Month and Budget in the table, you could use set analysis to identify the correct year and month to calculate the values for the metrics (YTD, MTD, LYTD, LMTD, etc.)

I suggest that you combine the store table with the store budget table as a single table that all the store budgets and attributes (since store attributes can also change monthly; i.e. same store flag, manager name, etc.). You would then key this table with store number, year and month. For the sake of improved performance, I suggest you link it to the Order Header table, not the Order Detail.

Performace steps

Tue, 13 Apr 2010 13:50:27 GMT

Thanks everyone for valuable suggestions.Here are my concerns regarding your suggestions.

1.Pls forget about my orderdate table.thats just hanging and I would remove as I do not need it at all.

2.All my requirement is to combine year and month of store_flash_1 table with calendar table but its forming synthetic keys thats the reason am doing yearnumber and monthnumber using if statements in teh report level:(the store_flash_1 is an aggregated table with monthlyincome,lastyeraincome etc for only last day of each month)

3.Is there any ways to combine the year and month of store_flash_1 with calendar table.

I hope am clear in explaining my situation.Pls suggest.

Thanks,

Swetha

Performace steps

d_pranskus — Tue, 13 Apr 2010 14:06:50 GMT

Synthetic keys are not big issue, you could create one field, i.e. YearMonth in both tables and then connect them to each other. Then delete Year and Month from fact table. To calculate YearMonth use following:

Year * 100 + Month as YearMonth

But my opinion is the same as others it is beter to have non agregated data in application

Performace steps

Tue, 13 Apr 2010 17:50:56 GMT

I understand that aggregated table is not a suggested one but my requirement is like that .store_flash data at non aggregated level has 30 or 31 entries for each month.So I have to take the last entry(30 or 31) dependending on month.If I link year month it would gime all the entries for amonth.So,I hve used a aggregated table there.Pls suggest if theres any other alternative to resolve this.

Thanks

Performace steps

d_pranskus — Tue, 13 Apr 2010 21:09:34 GMT

If you have Master Calendar table which contains all date dimensions, you can flag all last days of month with a flag in data loading script (for example IF(DAY(DateField+1) = 1, 1, 0) AS LastDay). Then you can use set analysis to limit just those records where corresponding record in Master Calendar has LastDay=1. You can use expression similar to this:

SUM({$<LastDay = {1}>} SomeAmountField)

Set analysis functionality is very powerfull in such situations, when you want to redefine selections for particular expression.

Cheers

Darius

Performace steps

Tue, 13 Apr 2010 21:33:11 GMT

Hi Darius,

Set analysis did work for me as you have suggested initially and the performnace is also excellenet.

But I need the data enclosed in doc1(pls see the first screenshot)but am getting differently if use set analysis (pls see scrrenshot2).

Thanks,

Swetha