TM1 Forum

I'm pretty new to TM1 and this problem has me stumped. Any help would be appreciated.
2 Cubes: Employee Stats and Costs. To somewhat simplify, Employee Stats has Employee, Month and EmpMeasures dimensions. Employee dimension has the OfficeID attribute and is arranged in a department hierarchy, the EmpMeasures dim has the salary costs. The Costs cube has Month, PLMeasures dimensions. Currently, I'm able to get the department level consolidated values of employees' salaries from the Employee Stats to the Costs cube PLMeasure (e.g. AP and AR department salaries are shown in the Finance Cost element of the PLMeasure dim). Here comes the new requirement, only salary values for employees with a certain OfficeID (e.g. 1 and 5) need to be included in the consolidated values in the Costs cube. In other words, my DB statements pulling consolidated salaries from the Department elements in the Employee Stats cube need to be filtered by the OfficeID attribute.

I'd say you have more or less 2 options. 1/ create hierarchies in the employee dimension by office and in the P&L Measure cube look up the specific hierarchy values to haul the costs in or 2/ create an intermediate calculation cube that has some other means of differentiating cost by office.

Thanks lotsaram!
I thought about 1) but that would be difficult to maintain since the hierarchies can't be based on dynamic subsets or can they? 2) may be an option, but then again, I'm looking at both the maintenance and consolidation issues, I think...