TM1 Rules Query

Post Reply
DavidP
Posts: 4
Joined: Mon Nov 05, 2018 3:42 pm
OLAP Product: TM1 Planning Analytics
Version: 2.0
Excel Version: 2016 32 bit

TM1 Rules Query

Post by DavidP » Tue Nov 06, 2018 12:30 pm

Good Afternoon TM1ers,

I have a rule query in Architect that is probably not complicated but I cannot work it out.

I have a cube 'Revenue Staging by Flight'
with dims:

Time by Day
Revenue X3 Account Code
Revenue X3 Cost Centre
Aircraft Reg
Flights
Airports
Departing & Arriving
Revenue Detail

I have a cube 'RP Financial Reporting by Flight'
with dims:

Plan Version
Time by Day
RP Cost Group
Aircraft Reg
Airports
Flights
Departing & Arriving
RP by Flight Measures

I have 3 Mapping cubes:
RP X3 Account Code Mapping
with Dims:

X3 Account Code
RP X3 Account Code Mapping

RP X3 Cost Centre Mapping
with Dims:

X3 Cost Centre
RP X3 Cost Centre Mapping

RP Cost Group Mapping
with Dims:
RP X3 Account Code Group
RP X3 Cost Centre Group
RP Cost Group Mapping

In order to narrow down our account codes and cost centres, each account code is assigned to an account code group, each cost centre is assigned to a cost centre group and each account code group and cost centre group is assigned to a 'Cost Group' effectively narrowing down 30,000 combinations into 20 Cost Groups.

I am trying to create a rule to move the data from the Revenue Staging by Flight cube into the RP Reporting by Flight cube and mapping each combination into its cost group.

So far the rule in the RP Financial Reporting by Flight cube is:

Code: Select all

#Revenue
['Actual','RPFF01']=N: IF(ATTRS('RP Cost Group',!RP Cost Group,'Name') @=DB('RP Cost Group Mapping','RPA025','RPC003','RPG001'),
DB('Revenue Staging by Flight',!Time by Day,'Total Revenue X3 AC','Total Revenue X3 CC',!Aircraft Reg,!Flights,!Airports,!Departing & Arriving,'Total Revenue'),0);
This is a static rule that doesn't really do anything. It is looking for Cost Groups that match the Ticket Revenue mapping in the Cost Group mapping cube (but is only looking by the ticket revenue account code group and the commercial services cost centre group) and then pulls the entire Revenue Staging by Flight cube. Once it picks up the Cost Group mapping, I want to then trace the account codes that are mapped to that Account Code Group, I then want to trace the Cost centres that are mapped to the Cost Centre Group and from the Account Codes and Cost Centres that satisfy these groupings, pull the relevant Revenue Staging by Flight data. I assumed I could create a link from the cost group through to the account codes and cost centres.

I believe from what I have worked out is that you cannot cycle through dimensions that don't exist in the cube that the rules are in (which may be obvious). I have looked through each rule function and the TM1 rules guide but could not find a solution. I have attached screenshots of the mapping cubes.

I have two questions:
1) Is it possible to make the code dynamic so that it will cycle through all the Account Code and Cost Centre groupings?
2) How can I then refer to those Account Code and Cost Centre groupings when pulling the data from the Revenue Staging by Flight cube?

Any help is greatly appreciated, thanks.
Attachments
Mapping cubes.PNG
Mapping cubes.PNG (136.14 KiB) Viewed 474 times
Last edited by DavidP on Thu Nov 15, 2018 4:02 pm, edited 1 time in total.

DavidP
Posts: 4
Joined: Mon Nov 05, 2018 3:42 pm
OLAP Product: TM1 Planning Analytics
Version: 2.0
Excel Version: 2016 32 bit

Re: TM1 Rules Query

Post by DavidP » Thu Nov 15, 2018 2:31 pm

Hi,

Just bumping this.

lotsaram
MVP
Posts: 3148
Joined: Fri Mar 13, 2009 11:14 am
OLAP Product: TableManager1
Version: PA 2.0.x
Excel Version: Office 365
Location: Switzerland

Re: TM1 Rules Query

Post by lotsaram » Thu Nov 15, 2018 9:45 pm

DavidP wrote:
Tue Nov 06, 2018 12:30 pm
I have two questions:
1) Is it possible to make the code dynamic so that it will cycle through all the Account Code and Cost Centre groupings?
2) How can I then refer to those Account Code and Cost Centre groupings when pulling the data from the Revenue Staging by Flight cube?
To answer 1/ & 2/. TM1 does have procedural code in TurboIntegrator but rules behave in much the same way as formulas in Excel. There is no equivalent concept of iterating in rules like Generate in MDX. However I am near certain that in this case you don't need to iterate or cycle. There's a much easier way of cumulating values in TM1, namely use dimension structure. At the leaf level of your account codes and cost centres you have defined the mapping to account code group and cost centre group. What you also need is to use these mappings to build consolidations in these dimensions to roll the leaves up to the group nodes. Then in the cube at cost group level simply use the mapping held at cost group level to refer to the appropriate consolidations in the account and cost centre dimensions in the Revenue Staging by Flight cube.

This should be a pretty easy problem to solve. Consolidation via dimensional structure is something TM1 does exceedingly well.
Please place all requests for help in a public thread. I will not answer PMs requesting assistance.

DavidP
Posts: 4
Joined: Mon Nov 05, 2018 3:42 pm
OLAP Product: TM1 Planning Analytics
Version: 2.0
Excel Version: 2016 32 bit

Re: TM1 Rules Query

Post by DavidP » Thu Nov 29, 2018 4:36 pm

Hi Lotsaram, thanks for your reply.

Is it possible to amend dimension structure without using a T.I?

I have now built a T.I that maps account codes and cost centres to their cost groups that works fine. I was looking to do this within rules as as there are a few T.I's I have that are only mapping data with no other function.

Thanks,

Wim Gielis
MVP
Posts: 1842
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1
Version: PAL 2.0
Excel Version: 2016
Location: Brussels, Belgium
Contact:

Re: TM1 Rules Query

Post by Wim Gielis » Thu Nov 29, 2018 4:50 pm

DavidP wrote:
Thu Nov 29, 2018 4:36 pm
Is it possible to amend dimension structure without using a T.I?
Dimensions can be changed manually but TI is a much better solution, for obvious reasons.
DavidP wrote:
Thu Nov 29, 2018 4:36 pm
I have that are only mapping data with no other function.
That’s not bad at all. What is it that you dislike about this solution ?
Best regards,

Wim Gielis

Excel Most Valuable Professional, 2011-2014
http://www.wimgielis.com ==> 105 TM1 articles and a lot of custom code
Newest blog article: Looping over input files

DavidP
Posts: 4
Joined: Mon Nov 05, 2018 3:42 pm
OLAP Product: TM1 Planning Analytics
Version: 2.0
Excel Version: 2016 32 bit

Re: TM1 Rules Query

Post by DavidP » Fri Nov 30, 2018 11:02 am

Hi Wim,

I was trying to ensure that this model was not reliant on too many processes as I preferred the model to be fully automated and not to have any buttons to press for the user.

However, after thinking about this again, I put too much emphasis on it being an automated model, than actually delivering the build. Also I can bolt on some T.I's to existing ones and nest sub-processes within processes so there will only be 1 or 2 buttons for the user to run.

I used a simple code to do the mapping:

Code: Select all

pACMap = CellGetS('RP X3 Account Code Mapping', vCOA, 'RPA001');
pCCMap = CellGetS('RP X3 Cost Centre Mapping', vCC, 'RPC001');
pCostGroupMap = CellGetS('RP Cost Group Mapping', pACMap,pCCMap, 'RPG001');

IF(CellIsUpdateable(cCubeNameTarget,'Current Forecast',pYearTo,vPrd,pCostGroupMap)=1);

 CellIncrementN(vValue,cCubeNameTarget,'Current Forecast',pYearTo,vPrd,pCostGroupMap);

ENDIF;
Thanks,

Wim Gielis
MVP
Posts: 1842
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1
Version: PAL 2.0
Excel Version: 2016
Location: Brussels, Belgium
Contact:

Re: TM1 Rules Query

Post by Wim Gielis » Fri Nov 30, 2018 11:56 am

DavidP wrote:
Fri Nov 30, 2018 11:02 am
Hi Wim,

I was trying to ensure that this model was not reliant on too many processes as I preferred the model to be fully automated and not to have any buttons to press for the user.

However, after thinking about this again, I put too much emphasis on it being an automated model, than actually delivering the build. Also I can bolt on some T.I's to existing ones and nest sub-processes within processes so there will only be 1 or 2 buttons for the user to run.

I used a simple code to do the mapping:

Code: Select all

pACMap = CellGetS('RP X3 Account Code Mapping', vCOA, 'RPA001');
pCCMap = CellGetS('RP X3 Cost Centre Mapping', vCC, 'RPC001');
pCostGroupMap = CellGetS('RP Cost Group Mapping', pACMap,pCCMap, 'RPG001');

IF(CellIsUpdateable(cCubeNameTarget,'Current Forecast',pYearTo,vPrd,pCostGroupMap)=1);

 CellIncrementN(vValue,cCubeNameTarget,'Current Forecast',pYearTo,vPrd,pCostGroupMap);

ENDIF;
Thanks,
Good !
Best regards,

Wim Gielis

Excel Most Valuable Professional, 2011-2014
http://www.wimgielis.com ==> 105 TM1 articles and a lot of custom code
Newest blog article: Looping over input files

Post Reply