TM1 Rules Query
Posted: 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:
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.
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);
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.