Possible to change variables with rules?

[oleisbest]

Hi,

I have two different cubes where both cubes use the same dimension, "Year". The two cubes are updated from two different sources. The first source gives the dimension "Year" values like 2009, 2010 etc. The second source gives the dimension the value "t+1" - which means it’s this year + one = 2011.

Is it possible to write a rule that changes all the variables after the source is updated in the cube from "t+1" to 2011, "t+2" to 2012 and so on?

Thanks in advance!

[Andy Key]

Put an alias on your year dimension where the alias is t+1 for 2011, t+2 for 2012 etc. Don't forget to update this when you start a new year.

Or depending on how quickly your second system changes from t+1 = 2011 to t+1 = 2012 at year end you could use a rule to populate the alias.

[lotsaram]

If you have 2 different definitions of "year" from different data sources it might also be a good idea to have separate year dimensions in TM1 to accommodate this. Then you wouldn't have this issue!

[oleisbest]

Put an alias on your year dimension where the alias is t+1 for 2011, t+2 for 2012 etc. Don't forget to update this when you start a new year.

Or depending on how quickly your second system changes from t+1 = 2011 to t+1 = 2012 at year end you could use a rule to populate the alias.

But this solution will only change the element name, it will not change the value of the data.

[Jeroen Eynikel]

Andy,

it is unclear to me what exactly you are asking.

As I understand it you have two year dimensions, one containing elements like 2009, 2010,... the other one having elements like T+1, T+2,...

Are you trying to have an alias on the T+1,... elements that is rule generated, or are you trying to have a rule dynamically shift data for the T+X elements, i.e. as of 1/1/2011 the data mapped to T+1 needs to change automatically to data from 2012?

Jeroen

[oleisbest]

Andy,

it is unclear to me what exactly you are asking.

As I understand it you have two year dimensions, one containing elements like 2009, 2010,... the other one having elements like T+1, T+2,...

Are you trying to have an alias on the T+1,... elements that is rule generated, or are you trying to have a rule dynamically shift data for the T+X elements, i.e. as of 1/1/2011 the data mapped to T+1 needs to change automatically to data from 2012?

Jeroen

Hi,

I will try to make myself a bit clearer. I have two data sources. The sources are exported to two different cubes (later I would like to compare these two data sources). These cubes have the following dimensions (se picture below):



My problem is:The data from source 1 has the data values t+1, t+2 and so on for the dimension year, instead of 2010, 2011 etc. I would like to change the data values from t+1, t+2 etc to numbers. I would like TM1 to change the data value t+1 to 2011 for all instances.

HÃ¥kon

[Jeroen Eynikel]

HÃ¥kon,


I am still not entirely clear on whether we are talking about attributes to the year dimension or data in a 'real' cube.

That being said since you talk about different datasources I am assuming you are using TI to load the data.

You can basically do one of two things:

1: convert T+1 to (f.i.) 2011 in the TI process so that it stores 2011 instead of T+1. (You'll have to define a new variable and a formula to derive 2011 from t+1 (and the sysdate probably or you could refer to a lookup cube fi).

2: with rules you cannot change a cell where data was loaded. Data is either loaded/inputted or calculated but not both. What you can do is create a second measure and use a rule on that one that derives the year in 'normal' format from the measure where you are currently storing the T+X items.