DATA MODEL TO USE OTHER PERIOD IN DIMENSION

[nboccassini]

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?

[marcus_sommer]

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