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'];