Performace steps

[#1]

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

[d_pranskus]

Hi

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

[#3]

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

[Oleg_Troyansky]

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.

[#5]

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

[Oleg_Troyansky]

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...

[sczetty]

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.

[#8]

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

[d_pranskus]

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

[#10]

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