TM1 Rules - Request your guidance please!

Post Reply
srp313
Posts: 26
Joined: Mon Jul 10, 2017 10:02 am
OLAP Product: TM1
Version: 10.2
Excel Version: 2016

TM1 Rules - Request your guidance please!

Post by srp313 »

Hi Forum members,

Requesting your valuable guidance as i am not skilled enough.
We have a cube named EMS_Employee Salary_HC_FTE cube . This cube is a rule driven cube for multiple scenarios.
Requirement was to add a new "Job Level" dimension to the existing cube. Here Job Level denotes the designation levels like 01, 02, M1, M2 etc..

I have used Performance Modeller to add the new dimension Job Level and have mapped existing data in the cube to a element "All Job Level" under "Job Level" dimension (mainly for data which is is not calculated based on rules).

Other rule driven intersections have been completely broken.

And i am finding it difficult to ascertain where to put the new dimension Job Level in the below Rules. One important point to note here is this cube also gets data from another cube EMS_Employee Details -> which contains "Employee Details" dimension -> which in turn contains the same "Job Level" data as an element.

Please i request your help in starting with the change in Rules, i do not know how and where to begin.

Rules are mentioned below:

Code: Select all

#**************************************************************************************************
# Calculation for Head Count
#**************************************************************************************************
['Working',HRAccounts:'Status',{'Plan','Current Budget','Current Forecast','What If','Actual','BC Lab','Workforce Lab','Guideline Request','Reorganizations'}] =
N:IF(DB('EMS_Employee Details',!Scenario,!Year,! Month,!Division,!Employee,'Job Type') =NUMBR(!Job Type),
				(DB('EMS_Employee Details',!Scenario,!Year,!Month,! Division,!Employee,'Status'))*1,STET);

['Working',HRAccounts:'800100','Job Type':{'1','2'},{'Plan','Current Budget','Current Forecast','What If','Actual','BC Lab','Workforce Lab','Guideline Request','Reorganizations'}] = N:IF(DB('EMS_Employee Details',!Scenario,!Year,!Month,! Division,!Employee,'Job Type') =NUMBR(!Job Type),
			[HRAccounts:'Status']*1,STET);

['Working',HRAccounts:'800300','Job Type':{'4','5','40','41','7','8','9','46','47','99'},{'Plan','Current Budget','Current Forecast','What If','Actual','BC Lab','Workforce Lab','Guideline Request','Reorganizations'}] = N:IF(DB('EMS_Employee Details',!Scenario,!Year,!Month,! Division,!Employee,'Job Type') =NUMBR(!Job Type),
			[HRAccounts:'Status']*1,STET);

['Working',HRAccounts:'801100','Job Type':  {'10','11','12','20','21','24','30','31','32','42','43','44','19','25','26','18'},{'Plan','Current Budget','Current   Forecast','What If','Actual','BC Lab','Workforce Lab','Guideline Request','Reorganizations'}] = N:IF(DB('EMS_Employee Details',!Scenario,!Year,!Month,! Division,!  Employee,'Job Type') =NUMBR(!Job Type),
			[HRAccounts:'Status']*1,STET);

['Working',HRAccounts:'804100','Job Type':{'13','23','33','45'},{'Plan','Current Budget','Current Forecast','What   If','Actual','BC Lab','Workforce Lab','Guideline Request','Reorganizations'}] = N:IF(DB('EMS_Employee Details',!Scenario,!Year,!Month,! Division,!Employee,'Job Type') =NUMBR  (!Job Type),
			[HRAccounts:'Status']*1,STET);


#**************************************************************************************************
# Calculation for FTE
#**************************************************************************************************
['Working',HRAccounts:'800101','Job Type':{'1'},{'Plan','Current Budget','Current Forecast','What If','Actual','BC Lab','Workforce Lab','Guideline Request','Reorganizations'}] = N:IF(DB('EMS_Employee Details',!Scenario,!Year,!Month,! Division,!Employee,'Job Type') =NUMBR(!Job Type),
			[HRAccounts:'Status']*1,STET);

['Working',HRAccounts:'800301','Job Type':{'4','5','40','41','99'},{'Plan','Current Budget','Current Forecast','What   If','Actual','BC Lab','Workforce Lab','Guideline Request','Reorganizations'}] = N:IF(DB('EMS_Employee Details',!Scenario,!Year,!Month,! Division,!Employee,'Job Type') =NUMBR  (!Job Type),
			[HRAccounts:'Status']*1,STET);

['Working',HRAccounts:'801101','Job Type':{'10','18','20','25','30','42'},{'Plan','Current Budget','Current   Forecast','What If','Actual','BC Lab','Workforce Lab','Guideline Request','Reorganizations'}] = N:IF(DB('EMS_Employee Details',!Scenario,!Year,!Month,! Division,!  Employee,'Job Type') =NUMBR(!Job Type),
			[HRAccounts:'Status']*1,STET);

['Working',HRAccounts:'800102','Job Type':{'2'},{'Plan','Current Budget','Current Forecast','What If','Actual','BC Lab','Workforce Lab','Guideline Request','Reorganizations'}] = N:IF(DB('EMS_Employee Details',!Scenario,!Year,!Month,! Division,!Employee,'Job Type') =NUMBR(!Job Type),
			IF(DB('EMS_Employee Details',!Scenario,!Year,!Month,!Division,! Employee,'Scheduled Hours per   week')>=37.5 &
			     DB('EMS_Employee Details',!Scenario,!Year,!Month,!Division,! Employee,'Scheduled Hours per   week')<=40,
			[HRAccounts:'Status']*1,(DB('EMS_Employee Details',!Scenario,!Year,!Month,!Division,! Employee,'Scheduled   Hours per week')/40)*[HRAccounts:'Status']),STET);


['Working',HRAccounts:'802302','Job Type':{'7','8','9','46','47'},{'Plan','Current Budget','Current Forecast','What   If','Actual','BC Lab','Workforce Lab','Guideline Request','Reorganizations'}] = N:IF(DB('EMS_Employee Details',!Scenario,!Year,!Month,! Division,!Employee,'Job Type') =NUMBR  (!Job Type),
			IF(DB('EMS_Employee Details',!Scenario,!Year,!Month,!Division,! Employee,'Scheduled Hours per   week')>=37.5 &
			     DB('EMS_Employee Details',!Scenario,!Year,!Month,!Division,! Employee,'Scheduled Hours per   week')<=40,
			[HRAccounts:'Status']*1,(DB('EMS_Employee Details',!Scenario,!Year,!Month,!Division,! Employee,'Scheduled   Hours per week')/40)*[HRAccounts:'Status']),STET);


['Working',HRAccounts:'802502','Job Type':{'11','12','19','21','26','31','32','43','44','24'},{'Plan','Current Budget','Current   Forecast','What If','Actual','BC Lab','Workforce Lab','Guideline Request','Reorganizations'}] = N:IF(DB('EMS_Employee Details',!Scenario,!Year,!Month,! Division,!  Employee,'Job Type') =NUMBR(!Job Type),
			IF(DB('EMS_Employee Details',!Scenario,!Year,!Month,!Division,! Employee,'Scheduled Hours per   week')>=37.5 &
			     DB('EMS_Employee Details',!Scenario,!Year,!Month,!Division,! Employee,'Scheduled Hours per   week')<=40,
			[HRAccounts:'Status']*1,(DB('EMS_Employee Details',!Scenario,!Year,!Month,!Division,! Employee,'Scheduled   Hours per week')/40)*[HRAccounts:'Status']),STET);



['Working',HRAccounts:'804102','Job Type':{'23','33','45'},{'Plan','Current Budget','Current Forecast','What   If','Actual','BC Lab','Workforce Lab','Guideline Request','Reorganizations'}] =  N:IF(DB('EMS_Employee Details',!Scenario,!Year,!Month,! Division,!Employee,'Job Type')   =NUMBR(!Job Type),
			IF(DB('EMS_Employee Details',!Scenario,!Year,!Month,!Division,! Employee,'Scheduled Hours per   week')>=37.5 &
			     DB('EMS_Employee Details',!Scenario,!Year,!Month,!Division,! Employee,'Scheduled Hours per   week')<=40,
			[HRAccounts:'Status']*1,(DB('EMS_Employee Details',!Scenario,!Year,!Month,!Division,! Employee,'Scheduled   Hours per week')/40)*[HRAccounts:'Status']),STET);


#--- Monthly base salary ----
#**************************************************************************************************
# 11/14/2016    *****  Payroll changes Start
#**************************************************************************************************
['Working','600300','Job Type':  {'1','2','4','5','7','8','9','40','41','46','47','10','18','20','25','30','42','11','12','19','21','24','26','31','32','43','44'},{'Plan','Current Budget','Current Forecast','What If','BC Lab','Workforce Lab','Guideline Request','Reorganizations'}]   =  N:IF(NUMBR(!Year)<= 2016,IF(DB('EMS_Employee Details',!Scenario,!Year,!Month,! Division,!Employee,'Job Type') =NUMBR(!Job Type),
				[HRAccounts:'Status']*(DB('EMS_Employee Details',!Scenario,!Year,!Month,!Division,!Employee,'Annual Salary')\12),STET),CONTINUE);

['Working','600300','Job Type':  {'1','2','4','5','7','8','9','40','41','46','47'},{'Plan','Current Budget','Current Forecast','What If','BC Lab','Workforce Lab','Guideline Request','Reorganizations'}]   =  N:IF(NUMBR(!Year)>=2017,IF(DB('EMS_Employee Details',!Scenario,!Year,!Month,! Division,!Employee,'Job Type') =NUMBR(!Job Type),
				[HRAccounts:'Status']* (DB('EMS_Employee Details',!Scenario,!Year,!Month,!Division,!Employee,'Annual Salary')\12),STET),CONTINUE);


['Working','600300','Job Type': {'10','11','12','18','19','20','21','24','25','26','30','31','32','42','43','44'},{'Plan','Current Budget','Current Forecast','What If','BC Lab','Workforce Lab','Guideline Request','Reorganizations'}]   =  N:IF(NUMBR(!Year) >=2017,IF(DB('EMS_Employee Details',!Scenario,!Year,!Month,! Division,!Employee,'Job Type') =NUMBR(!Job Type),
		 		  [HRAccounts:'Status']*
				     ((DB('EMS_Employee Details',!Scenario,!Year,!Month,!Division,!Employee,'Annual Salary'))/
        		        		    ((DB('EMS_Workforce Drivers',!Year,'January'))+(DB('EMS_Workforce Drivers',!Year,'February'))+(DB('EMS_Workforce Drivers',!Year,'March'))+
         				    (DB('EMS_Workforce Drivers',!Year,'April'))+(DB('EMS_Workforce Drivers',!Year,'May'))+ (DB('EMS_Workforce Drivers',!Year,'June'))+  (DB('EMS_Workforce Drivers',!Year,'July'))+(DB('EMS_Workforce Drivers',!Year,'August'))+
         				    (DB('EMS_Workforce Drivers',!Year,'September'))+(DB('EMS_Workforce Drivers',!Year,'October'))+ (DB('EMS_Workforce Drivers',!Year,'November'))+(DB('EMS_Workforce Drivers',!Year,'December'))))*(DB('EMS_Workforce Drivers',!Year,!Month)),STET),CONTINUE);

# **** Feeders Begin ****

Feeders;

['Working',HRAccounts:'Status',{'Plan','Current  Budget','Current Forecast','What If','BC Lab','Workforce Lab','Guideline Request','Reorganizations'},'Job Type Total']   => DB('EMS_Employee Details',!Scenario,!Year,!Month,! Division,!Employee,'Status');

#---Feeders for HeadCount----

['Working',HRAccounts:'status','Job Type':{'1','2'},{'Plan','Current Budget','Current Forecast','What If','Actual','BC Lab','Workforce Lab','Guideline Request','Reorganizations'}] => [HRAccounts:'800100'];

['Working',HRAccounts:'status','Job Type':{'4','5','40','41','7','8','9','46','47','99'},{'Plan','Current Budget','Current   Forecast','What If','Actual','BC Lab','Workforce Lab','Guideline Request','Reorganizations'}] => [HRAccounts:'800300'];

['Working',HRAccounts:'status','Job Type':  {'10','11','12','20','21','24','30','31','32','42','43','44','18','19','25','26','18'},{'Plan','Current Budget','Current   Forecast','What If','Actual','BC Lab','Workforce Lab','Guideline Request','Reorganizations'}] => [HRAccounts:'801100'];

['Working',HRAccounts:'status','Job Type':{'13','23','33','45'},{'Plan','Current Budget','Current Forecast','What   If','Actual','BC Lab','Workforce Lab','Guideline Request','Reorganizations'}] =>[HRAccounts:'804100'];

#---Feeders for FTE----

['Working',HRAccounts:'Status','Job Type':{'1'},{'Plan','Current Budget','Current Forecast','What If','Actual','BC Lab','Workforce Lab','Guideline Request','Reorganizations'}] => [HRAccounts:'800101'];

['Working',HRAccounts:'Status','Job Type':{'4','5','40','41','99'},{'Plan','Current Budget','Current Forecast','What If','Actual','BC Lab','Workforce Lab','Guideline Request','Reorganizations'}] =>[HRAccounts:'800301'];

['Working',HRAccounts:'Status','Job Type':{'10','18','20','25','30','42'},{'Plan','Current Budget','Current   Forecast','What If','Actual','BC Lab','Workforce Lab','Guideline Request','Reorganizations'}]=>[HRAccounts:'801101'];

['Working',HRAccounts:'Status','Job Type':{'2'},{'Plan','Current Budget','Current Forecast','What If','Actual','BC Lab','Workforce Lab','Guideline Request','Reorganizations'}] =>[HRAccounts:'800102'];

['Working',HRAccounts:'Status','Job Type':{'7','8','9','46','47'},{'Plan','Current Budget','Current Forecast','What   If','Actual','BC Lab','Workforce Lab','Guideline Request','Reorganizations'}] => [HRAccounts:'802302'];

['Working',HRAccounts:'Status','Job Type':{'11','12','19','21','26','31','32','43','44','24'				},{'Plan','Current Budget','Current   Forecast','What If','Actual','BC Lab','Workforce Lab','Guideline Request','Reorganizations'}] => [HRAccounts:'802502'];

['Working',HRAccounts:'Status','Job Type':{'13','23','33','45'},{'Plan','Current Budget','Current Forecast','What   If','Actual','BC Lab','Workforce Lab','Guideline Request','Reorganizations'}] => [HRAccounts:'804102'];

#---Feeders for Merit----

['Working','Job Type':{'1','4','5','40','41','2','7','8','9','46','47','10','20','30','42','18','25','11','12','21','24','31','32','43','44','13','23','33','45','19','26'},{'Plan','Current Budget','Current Forecast','What If','BC Lab','Workforce Lab','Guideline Request','Reorganizations'},HRAccounts:'Status']  =>['600310'];

#---Feeders for VariablePay----

['Working',HRAccounts:'Status','Job Type':{'1','2','4','5','40','41','7','8','9','46','47','10','20','30','42','11','12','21','24','31','32','43','44','18','25','19','26'},{'Plan','Current Budget','Current   Forecast','What If','BC Lab','Workforce Lab','Guideline Request','Reorganizations'}] =>['600320'];

#---Feeders for VariablePay Accrual----

['Redistribution',HRAccounts:'Status','Actual','Job Type':{'1','2','4','5','40','41','7','8','9','46','47','10','20','30','42','11','12','21','24','31','32','43','44','18','25','19','26'}] =>['600320'];

#---Feeders for other Benifits----

['Working',HRAccounts:'Status','Job Type':   {'1','2','4','5','7','8','9','10','11','12','18','19','20','21','24','25','26','30','31','32','40','41','42','43','44','46'  ,'47'},{'Plan','Current Budget','Current Forecast','What If','BC Lab','Workforce Lab','Guideline Request','Reorganizations'}] =>['610240'];

['Working',HRAccounts:'Status','Job Type':   {'1','2','4','5','7','8','9','10','11','12','18','19','20','21','24','25','26','30','31','32','40','41','42','43','44','46'  ,'47'},{'Plan','Current Budget','Current Forecast','What If','BC Lab','Workforce Lab','Guideline Request','Reorganizations'}]=>['610200'];

['Working',HRAccounts:'Status','Job Type':   {'1','2','4','5','7','8','9','10','11','12','13','18','19','20','21','23','24','25','26','30','31','32','33','40','41','42'  ,'43','44','45','46','47'},{'Plan','Current Budget','Current Forecast','What If','BC Lab','Workforce Lab','Guideline Request','Reorganizations'}]=>['610320'];

['Working',HRAccounts:'Status','Job Type':   {'1','2','4','5','7','8','9','10','11','12','13','18','19','20','21','23','24','25','26','30','31','32','33','40','41','42'  ,'43','44','45','46','47'},{'Plan','Current Budget','Current Forecast','What If','BC Lab','Workforce Lab','Guideline Request','Reorganizations'}]=>['610310'];

['Working',HRAccounts:'Status','Job Type':   {'1','2','4','5','8','9','10','11','18','19','20','21','25','26','30','31','40','41','42','43','46'},{'Plan','Current   Budget','Current Forecast','What If','BC Lab','Workforce Lab','Guideline Request','Reorganizations'}]=> ['610120'];

['Working',HRAccounts:'Status','Job Type':   {'1','2','4','5','7','8','9','10','11','12','18','19','20','21','24','25','26','30','31','32','40','41','42','43','44','46'  ,'47'},{'Plan','Current Budget','Current Forecast','What If','BC Lab','Workforce Lab','Guideline Request','Reorganizations'}]=> ['610170'];

#**************************************************************************************************
# 11/14/2016    *****  Payroll changes Start
#**************************************************************************************************

#['Working','Status','Job Type': {'1','2','4','5','7','8','9','40','41','46','47','10','18','20','25','30','42','11','12','19','21','24','26','31','32','43','44'},{'Plan','Current Budget','Current Forecast','What If','BC Lab','Workforce Lab','Guideline Request','Reorganizations'}]   => ['600300'];
['Working',HRAccounts:'Status','Job Type':{'1','2','4','5','7','8','9','40','41','46','47','25','42','10','11','12','18','19','20','21','24','25','26','30','31','32','42','43','44'},{'Plan','Current Budget','Current Forecast','What If','BC Lab','Workforce Lab','Guideline Request','Reorganizations'}]   => ['600300'];

#**************************************************************************************************
# 11/14/2016    *****  Payroll changes End
#**************************************************************************************************

['Working',HRAccounts:'Status','Job Type': {'13','23','33','45'},{'Plan','Current Budget','Current Forecast','What If','BC Lab','Workforce Lab','Guideline Request','Reorganizations'}]   => ['604100'];

['Working',HRAccounts:'Status', 'Job Type':  {'1','2','4','5','8','9','10','11','18','19','20','21','25','26','30','31','40','41','42','43','46'},{'Plan','Current    Budget','Current Forecast','What If','BC Lab','Workforce Lab','Guideline Request','Reorganizations'}] => ['610280'];

['Working',HRAccounts:'Status',{'Plan','Current  Budget','Current Forecast','What If','BC Lab','Workforce Lab','Guideline Request','Reorganizations'}] => ['610300 - FICA - Medicare'];

['Working',HRAccounts:'Status',{'Plan','Current  Budget','Current Forecast','What If','BC Lab','Workforce Lab','Guideline Request','Reorganizations'}] => ['610300 - FICA'];


['Working','Cummulative Salary',{'Plan','Current  Budget','Current Forecast','What If','BC Lab','Workforce Lab','Guideline Request','Reorganizations'},'Job Type Total'] =>
				DB('EMS_Employee Salary_HC_FTE',!Scenario,!HRVersion,!Year,ATTRS('Month',!  Month,'NextMonth'),!Currency,!Job Type,!Division,!Employee,'Cummulative Salary');


['Working','Monthly Salary',{'Plan','Current  Budget','Current Forecast','What If','BC Lab','Workforce Lab','Guideline Request','Reorganizations'}] => ['Cummulative Salary'];

['Working','Cummulative Salary-FICA',{'Plan','Current  Budget','Current Forecast','What If','BC Lab','Workforce Lab','Guideline Request','Reorganizations'},'Job Type Total']   =>
				DB('EMS_Employee Salary_HC_FTE',!Scenario,!HRVersion,!Year,ATTRS('Month',!  Month,'NextMonth'),!Currency,!Job Type,!Division,!Employee,'Cummulative Salary-FICA');


['Working',HRAccounts:'Status',{'Plan','Current  Budget','Current Forecast','What If','BC Lab','Workforce Lab','Guideline Request','Reorganizations'}] => ['Cummulative Salary-FICA'];

['Working',HRAccounts:'Status',{'Plan','Current  Budget','Current Forecast','What If','BC Lab','Workforce Lab','Guideline Request','Reorganizations'}] => ['FICA Eligible Salary Amount'];

['Working',HRAccounts:'Status',{'Plan','Current  Budget','Current Forecast','What If','BC Lab','Workforce Lab','Guideline Request','Reorganizations'}] => ['401K Eligible Salary Amount'];
Best Regards,
srp
Top
srp313
Posts: 26
Joined: Mon Jul 10, 2017 10:02 am
OLAP Product: TM1
Version: 10.2
Excel Version: 2016

Re: TM1 Rules - Request your guidance please!

Post by srp313 »

I would greatly appreciate on any kind pointers to start with.
Best Regards,
srp
Top
Edward Stuart
Community Contributor
Posts: 248
Joined: Tue Nov 01, 2011 10:31 am
OLAP Product: TM1
Version: All
Excel Version: All
Location: Manchester
Contact:
Contact Edward Stuart

Re: TM1 Rules - Request your guidance please!

Post by Edward Stuart »

Please i request your help in starting with the change in Rules, i do not know how and where to begin.
What error message(s) are you getting when you open/ save the rules?
Top
User avatar
Steve Rowe
Site Admin
Posts: 2416
Joined: Wed May 14, 2008 4:25 pm
OLAP Product: TM1
Version: TM1 v6,v7,v8,v9,v10,v11+PAW
Excel Version: Nearly all of them

Re: TM1 Rules - Request your guidance please!

Post by Steve Rowe »

Hi,

Any reference in a square bracket can remain unchanged.
Any reference to the rebuilt cube with a DB will need to be changed so that it either has !Job Level or an explicit Job Level element in the correct position.
Any references to external cubes with Job Level can have their explicit reference changed to !Job Level.

This might get the rules to compile, but may not give the results you expect.
With the greatest respect to you and the role you are doing for your organisation you really should not be making the level of change you are attempting with your current understanding of the product. Appreciate you might not have had a say in the matter though!
Technical Director
www.infocat.co.uk
Top
bgregs
Posts: 77
Joined: Wed Sep 12, 2018 11:19 am
OLAP Product: TM1 / Planning Analytics
Version: 2.0
Excel Version: 2016

Re: TM1 Rules - Request your guidance please!

Post by bgregs »

In regards to the question:
Please i request your help in starting with the change in Rules, i do not know how and where to begin.
Start here: https://www.ibm.com/support/knowledgece ... ent_N91157

As Steve pointed out, you may want to get some extra advice on site with you. I would also be concerned about mapping all historic data under a single element (i.e. All Job Level). Unless the business fully understands the impact of that decision, it will make forecasting and trending over history a nightmare in the future.....
Top
Post Reply

Return to “IBM TM1, Planning Analytics, PAx and PAW”