Announcements
cancel
Showing results for
Did you mean:
Partner - Creator

## DATA MODEL TO USE OTHER PERIOD IN DIMENSION

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

Community Browser