Report Design Help

[mattgoff]

I'm trying to create a report with a list of employees who have a Hire Date (stored as yyyy-mm-dd) in a user-specified period (yyyy-mm). I was planning to do this with an Active Form and MDX, and have now found two posts from tomok and lotsaram that indicate my approach will not work. I was trying to use MDX to filter the Employee dim based on a cube string value stored in the Employee cube:

Code: Select all

Cube: Employee
Dims: Employee, Cost_Center, Employee_m

{FILTER(
   {TM1FILTERBYLEVEL( 
      {TM1SUBSETALL( [Employee] )}
   , 0)},

   LEFT([Employee].([Cost_Center].[01.102.5065],[Employee_m].[Hire Date]),7)="2006-07")}

This works fine when I have a single Cost_Center (department) selected as above (01.102.5065) but fails when I try to apply it to all members (by removing [Cost_Center].[01.102.5065]). The MDX Primer seems to show an example of this, but I assume it limited to CurrentMember which won't work in my case since I need to iterate through a different dim (MDX is run on Employee dim, need to iterate through Cost_Center dim).

So, what are my alternatives to create a dynamic report? It doesn't have to be an Active Form. Is Drill an option? I haven't used Drill for anything other than relational drill-through or to another cube-- can a build the logic into TI and have the results returned in a dialog similar to relational drill?

Thanks,
Matt

[tomok]

Instead of using MDX to filter the Employee dimension why not create a measure in the cube that holds a "1",via rule, when the hire date matches the user-specified period. You could then create a zero-suppressed active form report with the Employee dimension as the rows. Of course, this will only work for one person at a time unless you get extra creative and add the }Clients dimension to the mix.

[mattgoff]

Instead of using MDX to filter the Employee dimension why not create a measure in the cube that holds a "1",via rule, when the hire date matches the user-specified period. You could then create a zero-suppressed active form report with the Employee dimension as the rows. Of course, this will only work for one person at a time unless you get extra creative and add the }Clients dimension to the mix.

That might get the job done, but I'm not sure how sustainable it would be. In theory, more than half of my users (those with employee cube access) would need this report, and due to deadlines there's a good chance they'd be running it at the same time. An employee can only exist in one Cost_Center at a time (yes, I know, sparse!), so I also considered a process to copy leaf-level data into the top consolidation. I don't think there's a realtime need, so I could just stick it into my big overnight maintenance chore. If it turned out there was a real-time need I could fall back to a rule (or run it once an hour or some other compromise).

Matt

[lotsaram]

Hi Matt

I wouldn't construe the post of mine that you linked to as any evidence that you can't filter based on a string value or range of values even with MDX to drive an active form. Just that if the string is contained in a cube it works best for 2D cubes since there is then only 1 variable since you need to treat all other dimensions effectively as titles and specify them to be able to pull a value out to work in the filter.

But for what you want to do why does the start date string need to be stored as a cube value at all?? Why not just have the user enter it directly in the report and pass the string to the MDX? You also don't need to substring the yyyy-mm-dd date string as you can use > and < operators and AND in the MDX Filter function so you could drive it based on hired between month x & y and not just month x alone.

I take it the "hire date" to filter on is not necessarily the hire date but the start date in cost center YXZ? So that the problem is that you have to solve for the intersection of employee / cost center then date? Assuming this assumption is correct and that the report is cost center specific you could solve it by having a "last transfer date" attribute in the employee dimension then do the MDX in 2 parts 1/ construct a list of employees for cost center XYZ THEN filter on the last transfer date attribute of employee.

[mattgoff]

I wouldn't construe the post of mine that you linked to as any evidence that you can't filter based on a string value or range of values even with MDX to drive an active form. Just that if the string is contained in a cube it works best for 2D cubes since there is then only 1 variable since you need to treat all other dimensions effectively as titles and specify them to be able to pull a value out to work in the filter.

Hm, well I've apparently exhausted my MDX ability because I (like tomok) spent as much time as I had experimenting with different syntax and could not get it to work. From everything I can see, at least in TM1, when using MDX to generate a subset you must specify a single element for every dim (other than your "target" dim) on the filter. I'd love to hear that I'm wrong, but I'm proceeding with Plan B.

But for what you want to do why does the start date string need to be stored as a cube value at all?? Why not just have the user enter it directly in the report and pass the string to the MDX? You also don't need to substring the yyyy-mm-dd date string as you can use > and < operators and AND in the MDX Filter function so you could drive it based on hired between month x & y and not just month x alone.

The start date for the query doesn't need to be stored as a cube value, that was just one possible workaround. If the above is true and you must supply a single Cost_Center element to the filter, by moving the filter measure to a numeric you could use the top-level consolidation (set a flag via a rule at the leaf and automatically shows in the consolidation). Since the number of possible query targets is large (in our case, around 120 periods), it doesn't make sense to have individual (and growing) dedicated flags-- to avoid this you could have one flag cell and write the rule to look at a control cell to identify which period is flagged. There are obvious flaws with extending this to many users running simultaneous queries.

I knew MDX could evaluate datestrings, but I never bothered to see if our format (the TM1 standard yyyy-mm-dd) was acceptable. In any case, we will never need to do multi-period reporting (at least for now, shockingly enough requirements have been known to change), so the substring approach works for now.

I take it the "hire date" to filter on is not necessarily the hire date but the start date in cost center YXZ? So that the problem is that you have to solve for the intersection of employee / cost center then date? Assuming this assumption is correct and that the report is cost center specific you could solve it by having a "last transfer date" attribute in the employee dimension then do the MDX in 2 parts 1/ construct a list of employees for cost center XYZ THEN filter on the last transfer date attribute of employee.

In our case Hire Date is the original hire date at the company. If an employee transfers, the data is wiped from the old dept after it's written to the new dept. This is all automated as part of my HRIS import. We used to leave the data in the old dept indefinitely and just set FTE=0, but we ran into privacy issues where a manager would get promoted and his replacement could see his previous salary. So, we now leave individual employee history to the HRIS system and only retain enough employee data in TM1 to facilitate forecasting. I went so far as to eliminate the version dim from the Employee cube as well.

The lack of transfer date does mean that transfers are invisible to a user reporting from the cube (an employee just "disappears" from his or her old dept and appears in the new). This is mitigated by an email alert I send out to each user summarizing changes made to their specific depts after each weekly refresh (logging during import written to csv, post-processed and emailed with perl)-- so the users will have a heads-up but cannot explicitly report on it within TM1. I've had on the bottom of my bin list a project to add a TM1-specific measure a la your Last Transfer Date and just plug in the current date when I detect a transfer (the "real" date is not passed from HRIS, and I'm not sure it's worth the effort to get it added). We're relatively small (3,000 worldwide employees), so transfers are rare enough to keep it low on the bin list.

Back to the issue at hand, your suggestion basically summarizes what I'm trying to do. The problem is that I need the report to return a list of all employees with a start date matching the query period in all depts (or at least all those a user can access). The MDX is tripping up when I try to get it to do anything above a leaf dept since the filter measure is a string (hence the workaround above).

Plan B is more or less working now and should meet our needs. The need for this specific report was based on the need to drill into the a summarization cube which contains the following measures:

• Hire-Complete (new hire started)
• Hire-Committed (new hire offer made/accepted but not yet started)
• Hire-Planned (new hire req open, no offer made/accepted)
• Hire-Forecasted (new hire planned, req not yet open)
• Term-Complete (termination complete)
• Term-Planned (termination planned)
• Attrition-Forecasted (the "know-that-we-don't-know" measure, accounts for future resignation vacancy)

This cube is currently populated via a process which is part of my overnight maintenance chore. Since every employee is guaranteed to exist in only one dept (even in the case of transfers, as described earlier), I added some code to copy key measures (Start Date, Term Date, HireName, Status, ReqID) in the Employee cube from the leaf to the top-level consolidation at the same time. Then, I can specify this consolidation in my MDX and build the active report off it. In fact, it's probably good that both the sumarry data in the cube and the drill/detail data are both batch-job generated as it avoids drilling into the detail and seeing a count mismatch. The process runs very fast, so if we needed it faster I'd have no issue adding it to my Hourly Refresh chore.

So, my immediate need seems to be solved, but if I'm wrong about TM1 MDX being unable to complete this task as originally planned I'd love to hear about it. I'm sure this kind of problem will come up again....

Matt

[rmackenzie]

Hi Matt,

Dims: Employee, Cost_Center, Employee_m

An employee can only exist in one Cost_Center at a time (yes, I know, sparse!)

I realise my suggestion is a bit awkward to implement because it is a change in structure, but you could just have a single Employee_By_CC dimension which rolled employees up to cost centres, maintaining the existing structure in Cost_Center; and storing the date as a string attribute which opens up the use of filtering by attribute. Then, your MDX, for a single cost centre, is pretty simple:

Code: Select all

{FILTER(
  {TM1FILTERBYLEVEL(
    {TM1DRILLDOWNMEMBER( {[Employee_By_CC].[YOUR_COST_CENTER]}, ALL, RECURSIVE )}, 
    0)
  },
  LEFT([Employee_By_CC].[Hire Date],7)="2006-07")
}

Perhaps, if you need a cube, because this is a source of a drill-through to another cube, then you could still have the cube but with the Employee_By_CC and Employee_m dimensions. With the hire date stored at a 2d intersection would still allow a small variant of the dynamic subset code above. Either way, you would have removed the problem of having to iterate over cost centre.

Regards your other point:

From everything I can see, at least in TM1, when using MDX to generate a subset you must specify a single element for every dim (other than your "target" dim) on the filter. I'd love to hear that I'm wrong

It’s not always the case that you need to specify elements for every dimension when using a cube reference. Take this example, using the planning sample database:

Code: Select all

 { ORDER( {TOPPERCENT( {TM1FILTERBYLEVEL( {TM1SUBSETALL( [plan_chart_of_accounts] )}, 0)}, 75.000000, [plan_BudgetPlan].([plan_version].[FY 2004 Budget],[plan_exchange_rates].[local],[plan_source].[budget],[plan_time].[Jan-2004]))}, [plan_BudgetPlan].([plan_version].[FY 2004 Budget],[plan_exchange_rates].[local],[plan_source].[budget],[plan_time].[Jan-2004]), BDESC) }

Note there’s no elements specified for plan_department and plan_business_unit.

HTH
Robin

[mattgoff]

Hi Robin,

Thanks for the additional perspective. My concern with your suggestion would be that I'd still need a periodic process to examine the Employee cube and build the Employee_by_CC hierarchy. If I'm going to have a process, I might as well not add a dimension and stick with my original workaround. Here's what I added to the Data tab of an existing process which walked the Employee cube (one employee per datasource row) to calculate the summary metrics used in the report:

Code: Select all

vParentCC = ELPAR ( 'Cost_Center' , vCost_Center , 1);

WHILE ( vParentCC @<> '' );

     CellPutS( vStartDate , 'Employee' , vEmployee , vParentCC , 'Hire Date' );
     CellPutS( vTermDate , 'Employee' , vEmployee , vParentCC , 'Term Date' );
     CellPutS( vStatus , 'Employee' , vEmployee , vParentCC , 'Status' );
     CellPutS( vHireName , 'Employee' , vEmployee , vParentCC , 'HireName' );
     CellPutS( vReqID , 'Employee' , vEmployee , vParentCC , 'ReqID' );

     vParentCC = ELPAR ( 'Cost_Center' , vParentCC , 1 );

END;

Two big assumptions required for the above to work, and both are true for my model:

1. Employees can only exist in one Cost_Center at a time.
2. Each Cost_Center can only have one parent (multiple hierarchies not allowed).

The first is non-negotiable for this approach to work. The second could be worked around if not true by more sophisticated copying code than I have used.

Not ideal (still wish I could avoid copying extra data around), but it works perfectly from the end-user perspective. My test code on dev had this little routine running in a few ms, so it's negligible to process overhead-- no problem running it hourly if really necessary.

On the MDX issue, it might be that the limitation I describe is associated with the FILTER function.

Matt