Percentages vs Quantum - Best practices

[ashishkmr1]

Hi All,
I am a rookie and need both design as well as best practices guidance in this case. We have a finance application for a retail enterprise. Most of the cost line items are tracked at monthly basis while stock and sales are at daily basis. So there are two category of cubes that we have, the first (for cost line items) has P&L ITEM, YEAR, MONTH, STORE, SCENARIO dimensions and the second category (for sales, margin and stock) has data at P&L ITEM, YEAR, MONTH, DAY, STORE, PRODUCT, SCENARIO dimensions. All the data is tracked in quantums (absolute numbers). Now the requirement is to show both the quantum as well as, as a percentage of sales. What are the best practices around this? So apart from having 'Actual','Plan' etc in my Scenario dimension should I also have 'Actual %', 'Plan %' elements in there or should I have separate cubes for percentages or should it be something else?
Also, the second part of this is apart from showing up the percentages, we also have to enable planning at % level. For example, the planning will be done by quantums and the % (derived from this quantum and as % of sales) are available. As the last part of the process they want to change the % (lets say from 10 to 12) and then want to see the same change travel back to quantums. This becomes like a circular reference since the % in the first place were derived from quantums.
Any help on this front will be greatly appreciated.

Hi Admin - Similar to FAQs, do we have a Best Practices link also some where?

Thanks,
Ashish

[ashishkmr1]

Hmmm...one week and no response

[lotsaram]

This is quite a generic question with many possible answers. TM1 is a very flexible modelling tool with many ways to do this.

"Best Practice" may be different things to different people but in general for this kind of reporting issue avoid separate cubes, there is simply not need and many drawbacks (more fragmented experience for users, more difficult to write reports, rules more complicated, etc.) Basically you have 2 choices: 1/ have additional measures for amount and % or 2/ have an additional dimension with elements for amount and %. The choice of which is the best will really depend on how many measures you have. If you have a lot of measures then you won't want to duplicate each one.

On another point it isn't clear whether you have your measure that are available at daily level also in your monthly cube. If you don't then this would certainly be a very good idea.

[tomok]

Hmmm...one week and no response

Complaining about the lack of FREE help is certainly not going to endear yourself to anyone. Do you think the TM1 community has an obligation to answer all questions posted in this forum?

[Alan Kirk]

Hmmm...one week and no response

Complaining about the lack of FREE help is certainly not going to endear yourself to anyone. Do you think the TM1 community has an obligation to answer all questions posted in this forum?

The icon he chose was "sad", not one of the angry ones, so I didn't read it as a "complaint". I don't think there's any harm in following up an unanswered question after a week, especially given that the audience can ebb and flow over that period and the original post may not have been seen at the time by someone who had the time to answer it.

But as things stand I think Lotsa has answered it as best it can be for the moment.

[Martin Ryan]

Hi Admin - Similar to FAQs, do we have a Best Practices link also some where?

As alluded to by lotsaram Best Practices is a bit difficult to pin down in TM1 because it is capable of such different types of application. TM1's flexibility is a fantastic strength, but it does mean that a lot of development knowledge is down to experience rather than book learning or reading a scholar's view of best practice.

There is a lot of support on here so please do keep trying to tap it. I can't speak for others but I didn't respond to your initial post because my eyes glazed over at a big block of text. Lack of coffee at the time of reading probably didn't help either.

Martin

[ashishkmr1]

Hmmm...one week and no response

Complaining about the lack of FREE help is certainly not going to endear yourself to anyone. Do you think the TM1 community has an obligation to answer all questions posted in this forum?

@tomok If you hate me for being sad that I did not get a solution, so be it. I am not here to win any popularity contest. Any how, as Alan said, I was in need of immediate help and even after a week when I did not get a response, I just expressed myself.

@lotsaram - Second option in your response was what I was more inclined to when I had raised this question. But adding another dimension in the cubes means a lot of (data transfer) work and as I mentioned, I am a rookie, so I wanted a sort of confirmation that it is a good approach. Just one more question - I do have some cubes that have daily measures. Does it mean I should not be using one of the 2 options mentioned in your response? Does it involve a different approach?

Thanks,
Ashish

[Alan Kirk]

@lotsaram - Second option in your response was what I was more inclined to when I had raised this question. But adding another dimension in the cubes means a lot of (data transfer) work and as I mentioned, I am a rookie, so I wanted a sort of confirmation that it is a good approach. Just one more question - I do have some cubes that have daily measures. Does it mean I should not be using one of the 2 options mentioned in your response? Does it involve a different approach?

In short, I would say no. If you go for a single cube approach (and I agree with Lotsaram that this is the preferred option) then you simply have your cube at daily level, and assign all of your daily data by day and your monthly data to a "representative" day, typically the last day of the month.

You don't even need to have separate dimensions for Year, Month and Day as you have at the moment if you don't want to, you could conceivably have a single time dimension (with days as the N level elements) and use consolidations to add them into months, quarters, halves, years etcetera. However the question of how many time dimensions to use in a cube often causes bar fights to start in this place. Accordingly rather than recommend a specific practice I'm just going to point you to the FAQ thread where you'll find links to a number of cube design issue threads, including time dimension ones.