Design Consideration for Capex budgeting module

[harrytm1]

Hi all,

We have been developing modules that require users to enter data at line items and then monthly data e.g. depreciation will be calculated by rules. Usually, the module makes up of two cubes: one is a Capex Details cube where it does not contain any time dimension, and a Capex cube where monthly data is calculated with either continuous or discreet time dimension(s).

We have come across a few posts in this forum that highlighted potential performance issue for intercube rules and feeders.

Q1: We are wondering whether it is better to combine both cubes into just one cube. Users will enter capex data for each line item into an element such as "No Month" in the Month dim (if it is a continuous time dim). Then the monthly data will be calculated in the same cube since it contains a time dim. Is one cube design better that 2 cubes?

Q2: If this is done through rules, then overfeeding will happen since it will be "No Month" feeding into all other month elements. Am I right to say that, unless we use Conditional Feeders (which I have no idea how to write), overfeeding will result.

Q3: Alternatively, we can also use TI to calculate the monthly data instead of rules. The question is, if we allow users to trigger the TI, will other users be affected when it is running? We should be running PI in this case. Since users enter data into "No Month" element while TI populates data into month elements, will this prevent any locking issue?

[Martin Ryan]

This is exactly how my capex model works. It's all in the one cube with the driver information in "No Month". I don't think that's necessarily "better" than two cubes in any technical sense, but it is tidier.

There is overfeeding when it comes to the calcs, but it's not a big cube, so it's not a drama. I don't think it's worth the time to improve it with conditional feeders. Unless you have thousands of lines of capex I'd suggest the same will be true for your model.

Personally I'd steer clear of TI because the calcs should all be real time. But as for your specific question I expect any process would be so fast that it would not register on the users' radar.

Martin

[harrytm1]

Thanks, Martin, for the reply.

[mattgoff]

We have the two cube CapEx setup. It's more efficient, but it's also a PITA since the users do most of their work in the $ cube which lacks useful metadata (most importantly: project name). As a result, people almost never use the cubeviewer for CapEx-- only the templates which merge data. If I were to start from scratch, I'd probably design it with one cube. In general, CapEx cubes are pretty small (unless you happen to be in a very capital-intense company) so the inefficiency of a single cube probably has little real-world effect on performance but is a lot easier to use.

Matt

[Harvey]

Like Matt, I have used the two-cube approach to capex many times, and prefer it.

I find combining cubes is generally tidier for a developer, but more confusing for a user. Sure, you can hide all that with a good input template, but having two or more cubes doesn't impact an identical input template!

I made the decision a while ago to never assume that users wouldn't log in an examine the cubes using server explorer, or, perhaps even prefer this technique to using input templates or websheets, and it has impacted my design choices significantly. Now I always strive to make the cubes self explanatory and as "browsable" as possible.

One thing I noticed in the previous posts is the mention that you'd have to feed all months. This is true if you only intend to project one year, as in the case of a budget, but for capex projections this will not suffice.

In that case, you'll have to feed all years up to the maximum useful life of the item purchased, or to the maximum projection years, whichever is smallest.

You can do this with conditional feeders, but you mentioned you don't know how to write them, so here's an explanation.

A conditional feeder has two conditions 1) feed the cube as normal with a regular DB formula OR 2) feed a cube that doesn't exist, hence no cube at all.

Yes, there is no option to feed nothing.

This is achieved by putting a regular "IF" statement around the first parameter of the DB formula, which is the cube name. Hence, you'd end up with a feeder of the form:

[] => DB(
IF(
[feeder condition],
'Capex Cube',
''),
... );

It looks like a hack when you first encounter it but this is the way it's done.

Hope that helps a little!