Rules and defining Areas partially twice
-
- Posts: 10
- Joined: Thu Aug 12, 2010 11:24 am
- OLAP Product: TM1, MS Analysis Services
- Version: 9.51
- Excel Version: 2003 - 2007
- Location: Dortmund, Germay
- Contact:
Rules and defining Areas partially twice
Hey everyone,
i've got a question regarding rules and how they work, if one defines an area partially twice. I've got two rule lines (in this order):
['Europe','Article 1','2010']=N:0;
['Article 1', '2010']= N:1
Shouldn't the first line overwrite the second for all dimension elements, that are element 'Europe'? And all other elements of this dimension should be calculated by the second line?
Regards,
Roger
i've got a question regarding rules and how they work, if one defines an area partially twice. I've got two rule lines (in this order):
['Europe','Article 1','2010']=N:0;
['Article 1', '2010']= N:1
Shouldn't the first line overwrite the second for all dimension elements, that are element 'Europe'? And all other elements of this dimension should be calculated by the second line?
Regards,
Roger
-
- MVP
- Posts: 214
- Joined: Tue Nov 11, 2008 11:57 pm
- OLAP Product: TM1, CX
- Version: TM1 7x 8x 9x 10x CX 9.5 10.1
- Excel Version: XP 2003 2007 2010
- Location: Hungary
Re: Rules and defining Areas partially twice
"Shouldn't the first line overwrite the second for all dimension elements, that are element 'Europe'? And all other elements of this dimension should be calculated by the second line?"
Do you mean that Europe is a C level element in one of the dimensions?
If yes, then the first rule line will never work.
Kind Regards,
Peter
Do you mean that Europe is a C level element in one of the dimensions?
If yes, then the first rule line will never work.
Kind Regards,
Peter
Best Regards,
Peter
Peter
-
- Posts: 10
- Joined: Thu Aug 12, 2010 11:24 am
- OLAP Product: TM1, MS Analysis Services
- Version: 9.51
- Excel Version: 2003 - 2007
- Location: Dortmund, Germay
- Contact:
Re: Rules and defining Areas partially twice
Erm, no
Imagine the first dimension as a flat dimension with no hierarchic structure. Let's say the Elements are Europe, Northern America, Asia, Africa
Here is the real code:
Imagine the first dimension as a flat dimension with no hierarchic structure. Let's say the Elements are Europe, Northern America, Asia, Africa
Here is the real code:
Code: Select all
[{'87216000','87219000','87225000','87250000','87276000','87279000','87116000','87119000','87121000','87150000','87176000','87179000',
'87316000','87319000','87329000','87350000','87376000','87379000','87516000','87519000','87579000','88519000','87490000'},'Ist',
{'Haushalt','Zähler','Gewerbe','Landwirtschaft','Agenturen Autogas','Eigenhändler Autogas','Sonstige Autogas','Staplergas','Brenngas',
'Global','Industrie','Air Liquide','Bau','Gastronomie','Sonstige','OBI','Max Bahr','Hellweg','VST','Aerosol','Beteiligungen','ZG Allgemein',
'Rest Infrastruktur','Infrastruktur','Overhead','Abgrenzungen'},
'Verteilung nicht spartengenauer FiBu-Daten'] = N:0;
['Ist',{'Haushalt','Zähler','Gewerbe','Landwirtschaft','Agenturen Autogas','Eigenhändler Autogas','Sonstige Autogas','Staplergas','Brenngas','Global','Industrie','Air Liquide',
'Bau','Gastronomie','Sonstige','OBI','Max Bahr','Hellweg','VST','Aerosol','Beteiligungen','ZG Allgemein','Rest Infrastruktur','Infrastruktur','Overhead'},
'Verteilung nicht spartengenauer FiBu-Daten']=
N:(
DB('Finanzdaten', !Jahre, !Monate, 'Hilfssparten', !Kostenstellen, !Kostenarten, 'Ist')
- DB('Finanzdaten', !Jahre, !Monate, 'AV', !Kostenstellen, !Kostenarten, 'Ist')
)
*
(
DB('Spartenergebnis', !Jahre, ELPAR('Monate',!Monate,1), !Sparten,
IF(ELPAR('Kostenarten',!Kostenarten,1)@='Abgrenzungskonten',ELPAR('Kostenarten',!Kostenarten,2),ELPAR('Kostenarten',!Kostenarten,1)), ELPAR('Kostenstellen',!Kostenstellen,1), !Datenarten, 'Originär + Direkt + Overhead')\
(
DB('Spartenergebnis', !Jahre, ELPAR('Monate',!Monate,1), 'Alle Sparten',
IF(ELPAR('Kostenarten',!Kostenarten,1)@='Abgrenzungskonten',ELPAR('Kostenarten',!Kostenarten,2),ELPAR('Kostenarten',!Kostenarten,1)), ELPAR('Kostenstellen',!Kostenstellen,1),!Datenarten, 'Originär + Direkt + Overhead')-
DB('Spartenergebnis', !Jahre, ELPAR('Monate',!Monate,1), 'Abgrenzungen',
IF(ELPAR('Kostenarten',!Kostenarten,1)@='Abgrenzungskonten',ELPAR('Kostenarten',!Kostenarten,2),ELPAR('Kostenarten',!Kostenarten,1)), ELPAR('Kostenstellen',!Kostenstellen,1),!Datenarten, 'Originär + Direkt + Overhead')
)
);
-
- MVP
- Posts: 214
- Joined: Tue Nov 11, 2008 11:57 pm
- OLAP Product: TM1, CX
- Version: TM1 7x 8x 9x 10x CX 9.5 10.1
- Excel Version: XP 2003 2007 2010
- Location: Hungary
Re: Rules and defining Areas partially twice
The first statement assigns the value 0 to the Europe / Article 1 / 2010 cells.
It takes precedence over the second statement, which specifies that all other countries / Article 1 / 2010 should be 1.
['Europe','Article 1','2010']=N:0;
['Article 1', '2010']= N:1
It takes precedence over the second statement, which specifies that all other countries / Article 1 / 2010 should be 1.
['Europe','Article 1','2010']=N:0;
['Article 1', '2010']= N:1
Best Regards,
Peter
Peter
-
- Posts: 10
- Joined: Thu Aug 12, 2010 11:24 am
- OLAP Product: TM1, MS Analysis Services
- Version: 9.51
- Excel Version: 2003 - 2007
- Location: Dortmund, Germay
- Contact:
Re: Rules and defining Areas partially twice
So my example should work. But it does not. Any hint?
-
- MVP
- Posts: 214
- Joined: Tue Nov 11, 2008 11:57 pm
- OLAP Product: TM1, CX
- Version: TM1 7x 8x 9x 10x CX 9.5 10.1
- Excel Version: XP 2003 2007 2010
- Location: Hungary
Re: Rules and defining Areas partially twice
Create a simple N level view with some test element.
Use the rule tracer (at right click) to see which rule is valid for the miscalculated cell.
Use the rule tracer (at right click) to see which rule is valid for the miscalculated cell.
Best Regards,
Peter
Peter
-
- Posts: 10
- Joined: Thu Aug 12, 2010 11:24 am
- OLAP Product: TM1, MS Analysis Services
- Version: 9.51
- Excel Version: 2003 - 2007
- Location: Dortmund, Germay
- Contact:
Re: Rules and defining Areas partially twice
Already did. It's the second line, although the element would be Europe. In my case its one of the adressed cost types. I'm puzzled...
-
- MVP
- Posts: 214
- Joined: Tue Nov 11, 2008 11:57 pm
- OLAP Product: TM1, CX
- Version: TM1 7x 8x 9x 10x CX 9.5 10.1
- Excel Version: XP 2003 2007 2010
- Location: Hungary
Re: Rules and defining Areas partially twice
Please specify the cell which is miscalculated:
Eg.: ['87216000','Ist','Haushalt','Verteilung nicht spartengenauer FiBu-Daten']
Eg.: ['87216000','Ist','Haushalt','Verteilung nicht spartengenauer FiBu-Daten']
Best Regards,
Peter
Peter
-
- Posts: 10
- Joined: Thu Aug 12, 2010 11:24 am
- OLAP Product: TM1, MS Analysis Services
- Version: 9.51
- Excel Version: 2003 - 2007
- Location: Dortmund, Germay
- Contact:
Re: Rules and defining Areas partially twice
There are a lot other rule lines, which set the values of othe cost types to zero. Here are the coordinates of one example, i have found.
Year: 2010
Month: 6
Type of Cost: 87276000
Branch: Zähler
cost unit: 1950
level of allocation: Verteilung nicht spartengenauer FiBu-Daten
data type: Ist
value: 11237.11
Year: 2010
Month: 6
Type of Cost: 87276000
Branch: Zähler
cost unit: 1950
level of allocation: Verteilung nicht spartengenauer FiBu-Daten
data type: Ist
value: 11237.11
-
- MVP
- Posts: 214
- Joined: Tue Nov 11, 2008 11:57 pm
- OLAP Product: TM1, CX
- Version: TM1 7x 8x 9x 10x CX 9.5 10.1
- Excel Version: XP 2003 2007 2010
- Location: Hungary
Re: Rules and defining Areas partially twice
Based on the sample you provided is seems to me that it should be 0.
2 questions:
- are you sure that the first rule line saved in the cube at all? Could you check in the rule editor especially if you use XRU files for rule maintenance.
- are you sure that the rule which set the value 11237.11 is the second rule line in your example? If you have many rule lines then it is important to know that the value is really calculated by the second rule not a 3rd one.
2 questions:
- are you sure that the first rule line saved in the cube at all? Could you check in the rule editor especially if you use XRU files for rule maintenance.
- are you sure that the rule which set the value 11237.11 is the second rule line in your example? If you have many rule lines then it is important to know that the value is really calculated by the second rule not a 3rd one.
Best Regards,
Peter
Peter
-
- Posts: 10
- Joined: Thu Aug 12, 2010 11:24 am
- OLAP Product: TM1, MS Analysis Services
- Version: 9.51
- Excel Version: 2003 - 2007
- Location: Dortmund, Germay
- Contact:
Re: Rules and defining Areas partially twice
Yes, i'm absolutely sure, that it is the second line of code i provided. it's distinctively the second line.
I also started asking myself, if it is maybe a probem caused be the sheer length of the rule. There are about 3000 lines by now. i saved the rule with the old editor and also the new editor, just to make sure, if there's a difference in saving behaviour. I also checked the *.rux-File. The lines, which set the value to zero because of a given type of cost are there, so it seems, they were saved.
I also started asking myself, if it is maybe a probem caused be the sheer length of the rule. There are about 3000 lines by now. i saved the rule with the old editor and also the new editor, just to make sure, if there's a difference in saving behaviour. I also checked the *.rux-File. The lines, which set the value to zero because of a given type of cost are there, so it seems, they were saved.
-
- Posts: 10
- Joined: Thu Aug 12, 2010 11:24 am
- OLAP Product: TM1, MS Analysis Services
- Version: 9.51
- Excel Version: 2003 - 2007
- Location: Dortmund, Germay
- Contact:
Re: Rules and defining Areas partially twice
Things are getting really strange. I did an upgrade to FP2, then restarted the services. Now sometimes the values are zero, sometimes not. I created a TI Process with a SaveDataAll;, which runs as a job every 30 minutes.
Looks like a bug or something connected to cubes with large rules?
Looks like a bug or something connected to cubes with large rules?
-
- Posts: 32
- Joined: Wed May 28, 2008 11:41 am
- OLAP Product: TM1
- Version: 6 and 2.5 to 10.2.2
- Excel Version: 2007 2010
- Location: Northern England
- Contact:
Re: Rules and defining Areas partially twice
HI there,
Is there a ru$ file in the data directory, as this has an impact of rules you save, appear to be saving properly but don't actually change?
More thoughts as they come to me - it may take a while
Cheers
Neil
Is there a ru$ file in the data directory, as this has an impact of rules you save, appear to be saving properly but don't actually change?
More thoughts as they come to me - it may take a while
Cheers
Neil
-
- Posts: 10
- Joined: Thu Aug 12, 2010 11:24 am
- OLAP Product: TM1, MS Analysis Services
- Version: 9.51
- Excel Version: 2003 - 2007
- Location: Dortmund, Germay
- Contact:
Re: Rules and defining Areas partially twice
Hey,
i checked and found no *.ru$ File, only *.rux
Nonetheless it seems to me, that FP2 has fixed this odd behaviour
Regards,
Roger
i checked and found no *.ru$ File, only *.rux
Nonetheless it seems to me, that FP2 has fixed this odd behaviour
Regards,
Roger