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

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

[#3]

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