Conditionally sum data

    This site uses cookies. By continuing to browse this site, you are agreeing to our Cookie Policy.

    • Conditionally sum data

      Hi All,
      I am trying to use a rule to conditionally sum data
      Eg. I may have cube layout as below
      Two Dimensions
      Employees
      Person1
      Person2
      Person3
      Person4

      Employee Data
      Cost Centre No.
      Basic Pay
      Overtime
      Total Pay for Cost Centre
      Total Overtime for Cost Centre



      What I would like is to create a rule that would calculate the Total Pay for Cost Centre
      eg. if Person1 and Person3 belongs to the same cost centre, then Total Pay for Cost Centre element should show me the total of the Basic Pay of Person1 and Person2.

      I am aware that it would be better to have Cost centre as a dimension but there is a risk of someone's data being entered in multiple cost centres if a person is reassigned to another cost centre
    • I have had that thought but the root problem I am having is that my company has have being doing a lot of restructuring in the past couple of years. This has resulted in persons being reassigned to other cost centres during the year.
      The structure you propose still leaves a Person being inadvertently assigned to multiple Cost Centre. I am talking about a company with >100 cost centres, > 2000 staff plus the cost centre structure with about 4-5 levels.

      The post was edited 1 time, last by chayden ().

    • If I understand you correctly, you want an employee belonging to only one cost center.. and if that cost center changes.. all data changes accordingly.

      Holger's suggestion should work, but you'd need to rebuild your entire cube after a dimension change.

      Ultimately your source transactional data is where you need enforce your requirement of one cost center per employee. Assuming you are using ETL server for loading your cube...
    • Slowly Changing Dimension

      Hi,
      it looks like a Kimball "slowly changing dimension" type2, where you want to "retain the history".

      If Palo takes data from a relational data warehouse, this can be handled there with no problem (you create a new dimension member with the same 'natural key' and a new 'warehouse key'; using the warehouse key to join the fact table naturally partitions its data over time), so the employee, e.g., is under both the Center1 and the Center2, with its costs under Center1 for the time it was there, and under Center2 for the other period.

      But we also have some problem when modeling this into Palo, since Dimensions do not explicitely have member key and a member name, just the member name, that has to be unique along the whole dimension: so the only solution is to suffix the member name each time it changes its parent. In your case, it could be John Doe when under Cost Center 1, John Doe_2 when under Cost center 2. All but elegant, but it should work.

      Hope this helps,
      kind regards
      RQ
    • Conditionally sum data

      Hi All,
      I have looked at all the comments and thanks very much. what realquo said about;
      it looks like a Kimball "slowly changing dimension" type2, where you want to "retain the history".
      is exactly it. I decided that I will create/add a cost centre dimension to the cube and put in place some procedure to verify if a person is assigned to multiple cost during a year that the months in that year does not overlap. Maybe later I can create a dashboard to highlight these things.
      Thanks again for all the help.

      The post was edited 1 time, last by chayden ().

    • Hi All,
      It's me again. I have created the dimension with the cost centre dimension but I have added some rules but the performance is poor. Below is a list of rules connected to cube in question. I have narrowed done the problem rule to ['OT-Numerator']. If I replace PALO.DATA with PALO.MARKER I get 0 for the values. I would appreciate any thoughts ;(

      ['Overtime'] = N:IF(LEFT(!'Employee',1) == "3",0,IF(LEFT(PALO.DATA("OM_new","Employee Payroll",!'Year',"Fixed",!'Cost Centres',!'Employee',"Employment Type"),1) == "C",0,PALO.DATA("OM_new","Employee Payroll",!'Year',!'Months',!'Cost Centres',!'Employee',"OT-Numerator") / PALO.DATA("OM_new","Employee Payroll",!'Year',"All Months",!'Cost Centres',"All","OT-Denominator")))

      ['OT-Numerator'] = N: PALO.DATA("OM_new","Employee Payroll",!'Year',"All Months",!'Cost Centres',"All","Basic Pay") * PALO.DATA("OM_new","CC Overtime Rate",!'Cost Centres',!'Year') * PALO.DATA("OM_new","Employee Payroll",!'Year',!'Months',!'Cost Centres',!'Employee',"Basic Pay")

      ['OT-Denominator'] = N:IF(LEFT(!'Employee',1) == "3",0,['Basic Pay'])

      ['Basic Pay'] = N:SUM(1,IF(LEFT(['Grade','Fixed'],1) == "1",PALO.DATA("OM_new","Assumptions",!'Year',"Supervisors(Salary Increases)"),IF(LEFT(['Grade','Fixed'],1) == "2",PALO.DATA("OM_new","Assumptions",!'Year',"Clerical (Salary Increases)"),IF(LEFT(['Grade','Fixed'],1) == "3",PALO.DATA("OM_new","Assumptions",!'Year',"Hourly Paid(Salary Increases)"),IF(LEFT(['Grade','Fixed'],1) == "4",PALO.DATA("OM_new","Assumptions",!'Year',"Managers(Salary Increases)"),IF(LEFT(['Grade','Fixed'],1) == "5",PALO.DATA("OM_new","Assumptions",!'Year',"Confidential Secretary(Salary Increases)"),IF(LEFT(['Grade','Fixed'],1) == "6",PALO.DATA("OM_new","Assumptions",!'Year',"Directors(Salary Increases)"),1))))))) * ['Salary'] * VALUE(['% OM','Fixed'])

      The post was edited 3 times, last by chayden ().