Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi all,
I have an application that calculate more kpi (approx 400) on different period.
I have a fact table (approx 3.000.000 rows) like this:
CODE | LOCATION | PERIOD | VALUE |
code1 | italy | Y2020 | 100 |
code1 | france | Y2020 | 200 |
Period can be a month (ex 012020) a quarter (ex Q12020), an half (H12020) or a Year (Y2020)
The prolem is that some KPI use more period in expression. Using a pivot, I actually don't use PERIOD in dimension and I'm using variable per PERIOD value. For example (vy1='Y2020'):
SUM({<CODE={'1231'}, PERIOD={"$(='1H'&(Right(vy1,4)-2))"} >}VALUE)+
SUM({<CODE={'2456'}, PERIOD={"$(vy1)"} >}VALUE)
This solution has a performance problem because 400 KPI calculated by expression like this need more memory (out of memory error).
So I need to use PERIOD in dimension in pivot but I don't know how.
The idea is use a expression like this:
SUM({<CODE={'1231'}, PERIOD_P={"1H2"} >}VALUE)+
SUM({<CODE={'2456'} >}VALUE)
where PERIOD_P=1H2 means first half (1H) two year ago (2)=1H2
How restructure model to use this approach?
Just from your description it's hard to guess what do you want to do. Ideally you have just a simple expression like sum(Value) and everything else happens on appropriate created dimension-values and/or selections from the users.
In regard to your mentioned overlapping period-values you may bundle them with The-As-Of-Table and/or some kind of flagging them - avoiding in each case variables/expressions in the period-condition. Means to end with expressions like:
sum(Value)
sum({< Flag = {1}>} Value)
sum(Value) * Flag
sum({< Flag = {1}, Period = p(Period) >} Value)
or similar.
- Marcus