TM1 Forum

This question pertains to handling cubes with lots of conditional statements and, if has been addressed, I've simply missed it.

The cube in question is a calculation cube for a staffing model. The users enter information into a input cube that essentially has Cost Centers, Slots, and Measures. The measures dimensions has items like Effective date, job title, base salary, job code, job status. There are a number of actions that can occur such as a transfer to another cost center or a position could be deactivated (an approved position, but not filled). There are dozens of these items which the user makes entries that determine calculation results. Each employee or position is entered into a single "Slot" in the appropriate Cost Center. If a CC has five staff positions, Slots 1-5 will have an entry.

The Calc cube (with dimensions Cost Center, Slots, Measures AND Months) then takes this information and spreads it out across the budget period on a monthly basis. So a staff member is promoted in April, then starting in April, that position calculates a new monthly salary, taxes, etc. Lots of conditional rules, lookup cubes and attributes used in this cube. Of course, the Calc cube is sparse as only a fraction of Cost Centers approach the Slot limit (125), most having fewer than 10 employees or positions. There are approximately 160 rules.

The Summary cube (dimensions; Cost Center, Months and Measures) simply summarizes the information from the Calc cube by Cost Center and Months

The question is how to best approach the rules in the calc cube. Is it better to STET cells at the beginning of the rules file and then write simpler rules OR repeat the same conditionals in every rule. For example, If the context month is less than the effective date, we do not want to calculate anything. That one conditional applies to every cell.

I approached this by writing a rule at the outset that STETS all cells with the context month less than the effective date and the following rules are much simpler. However, that kind of goes against the grain of published rule design which is STET small areas and then write rules to cover the bigger area.

Recommendations? Practical Experience?

Recommendations?

Simpler is (almost always) better, IMO.

That is why I took the approach I took. I have been very careful of overfeeding, so the feeders also have conditionals. I used an overfeed cube to eliminate the bulk of the overfeeding problems because of incorrect coding. But because of the conditionals, it is impossible to completely eliminate during the day, but the impact should be miniscule.

When it comes to doing a staffing model I find it best to separate the employee from the cost center, keeping all the employee related items cost center agnostic. Then, in the cube where you actually have the cost center, have a measure called "Count" where the count is either 0 or 1, based on the hire and termination dates, cost center assignments, transfer dates, etc., with respect to the position and month in question. Then all you have to do is take the salary, benefits, etc. for the position and multiply by the count. If an employee doesn't exist in this cost center for this month, the expense is zero, otherwise there is an expense. Your're putting the "conditional" into just one measure, not a whole bunch of them.

If feeding is a problem, you can turn the count into a range valued amount through a TI and then use the count as your feeder. This is actually a really good way to do this if you have a lot of employees. Having a bunch of conditional feeders on a cube like this can really make it a dog, as far as performance is concerned.

Hello

Tomok is right, feed starting from a count measure (1 or 0 - the "default value" in TM1).
When you maintain / update the employee dimension through TI, also populate this count measure during the TI process execution.

As a general tip (unrelated to the employee case but related to overfeeding): feeding like this is an interesting approach in forecast application too.
When loading Actuals data, populate an additional "feeder" measure in forecast cubes. Simply putting a 1 in the cell. Then use that 1 to feed forecast cells where are rull could pull over Actuals data.

Tomok,

Thank you very much for the feedback. Performance is something of an issue, mainly in loading views at the Enterprise level. Leaf and intermediate hierarchy loads are okay. The approach you describe is intriguing, so let me throw some additional constraints I am having to deal with.

First, I have rewritten the rules, adjusted dimensions, made usage of attributes and consolidations far more widespread. The original rules for the Calc cube numbered over 600 and virtually all of them had conditional statements. We're down to less than 170 rules and have greatly reduced the number of conditionals. I had also implemented a flag value analogous to your "Count."

This is a planning/budgeting application. The application owners (the Business) want real-time numbers. "I add an employee, I want to see the results in my cost center's bottom line immediately." There are over 5000 staff member and 800 cost centers. Approximately 400 managers interact with it. There are virtually no TI processes to move data between cubes (they are all rule driven) except for one that handles a particular chargeback item to avoid a continuous loop.

I know there are far larger organizations that use this for the same thing, I just want to know how they handle in regards to comparing to the constraints being faced here.

With regards to your suggestions, I see the obvious advantages, but not quite sure how you get from point A to point B to point C with it. For example, how do you manage employees being cost center agnostic without employees listed in a dimension? And at some point, they have to be tied to the cost center. I do not want to assume I know what you are doing.

Thanks, again!!!

My "agnostic" wording may have thrown you off. What I meant was you don't use the cost center dimension in the cube with all the attributes. The cost center assignment is just another attribute like salary, title, country, state, benefit choices, etc. I build most of my models with an employee dimension , that's what you use to assign the attributes. You can use just plain TM1 attributes but most of the time I create my own attributes cube because I want other dimensions in it too.

As to the real-time requirements, does it have to be so real-time that they can't be bothered to click on an action button after they add the employee? When I have clients that are being unreasonable like that I just tell them it can't be done without it. Technically, yes it can, but they'll be unhappy with the consequences of that decision so I make it for them.

Okay! I was thought that is what you meant.

The client is still using Contributor. We are using 10.1.1 and the update action button was not an option, but, as you said, they have to accept performance issues or not being 100% real time.

Moving on: so I have an attribute cube using an employee dimension. How do you build it so that the manager of CC 100 only sees CC 100 employees? In this scenario, how are adds, open positions handled?

Sorry to ask so many questions. I like hearing other's elegant solutions or ideas. It may or may not be perfect for this application, but certainly provides a bigger platform to expand on. I hate reinventing the wheel.

Thank you again.