simplify formula

[#1]

Hello,

My processor is to slow when I use the formulea:

  sum

({<Source={Sales}, GL_1={VOL}>}
if(InYearToDate(EffectiveDate,'01-'&PITPeriod&'-'&PITYear,0,4),
Data))

The database is over 50 million record. Structure as in the attachment.

Does anyone know a why to de-stress my processor?

René

[Gysbert_Wassenaar]

PITPeriod and PITYear are in a data island. That means the expression is calculated over a cartesian product. If there is only one row in that table then you could create a flag field in the script so the if statement can be replaced in your expression.

Load x,y,z,

-1*InYearToDate(EffectiveDate,'01-'& peek('PITPeriod')&'-'& peek('PITYear'),0,4) as IsYTD

from ...;

The expression could then change to sum(({<Source={Sales}, GL_1={VOL}, IsYTD={1}>}Data)

[#3]

Thanks,

Not sure what you mean by if there is only one row in the PITYear and PITPeriod data island. There are over 3000 rows in that table.

I use it so users can select a period to view the data.

For exsample see the aftachment.

[Gysbert_Wassenaar]

Well, that data island is most likely responsible for a large part of your performance troubles since it means you need that expensive if statement. Your probably far better off with a master calendar that is properly associated with your other tables. See this discussion: Tutorial - Using Common Date Dimensions and Shared Calendars

[vincent_ardiet · Accepted solution]

Hi,

First try to create a variable which contains the value of PITYear and PITPeriod.

If it's still not good.

Then, you can try to create another field which you can use in a set analysis instead of a If.

As you want that your year starts in April, you can add a CorrectedYear in your model (which is 2012 for an effectivedate before April else 2013), and just compare this field with PITYear in a set analysis.

Regards,

Vincent

[#6]

Thanks! Off course creating a variabel based upon the PIT selection was the answer.

Makes sense now...