Summary Cubes - good or bad practice?

[fleaster]

Hi all,
So the situation is, we have a very detailed GL cube* and are considering feeding this to a Summary Cube**, either via rules/feeders or static values TI load.

*Detailed cube: 16 dimensions or so, with millions of elements in hierarchies of 20+ levels - is currently the basis for all reporting, analysis & forecasting, and is 1.5 GB large (with another 2-3 GB of feeders). The performance of the cube is reasonable, as long as the view is not overly large.

**Summary cube: <10 dimensions, thousands of elements in hierarchies of 5+ levels, where these are replicated/simplified from the detailed dimension - will likely be 10 MB large

The purpose of the new summary cube is to allow the user to:
1. peform efficient data dumps into Excel, where they have models/dashboards intended to be distributed to non-TM1 users
2. do quick topside adjustments/data spreads
3. collect topside commentary
4. perform faster analysis in the Summary Cube, with an optional drill back to the detailed GL cube
5. potentially encourage users to do all their reporting out of the Summary Cube, and only use the detailed GL cube for drill/analysis

The other option was to output a .csv file from the detail cube (with appropriate dimensionality), which users can then import into their Excel models.

...am interested in people's thoughts on these approaches, and what experiences you've had.

thanks!

Matt

P.S. some key considerations are: performance, user-friendliness and overhead (from needing additional reconciliations to align 2x cubes, backfeeding adjustments to detail cube etc)

[lotsaram]

Generally in-my-own-personal-opinion I think that summary cubes are a good thing to have in any TM1 system that has large cubes because also in-my-own-personal-opinion the number 1 criteria for usability and user acceptance of any system is performance so queries have to be fast, it is the most important consideration. This does imply or require duplication of data and any time you duplicate data you need to be careful that there are no errors and that there are built-in checks that everything reconciles.

If performance is already adequate then there's no need, except perhaps in situations to present a simplified reporting cube with reduced dimensionality and physically separate this from the "analysis" cube which has many more dimensions to support a more detailed set of requirements. (Sometimes having a simple cube just makes people happy.)

If you are thinking you can have a rule-driven summary cube with reduced dimensionality and still achieve performance gains, forget it. It won't happen as in the background the detailed data is being summarized on the fly whenever the small cube is queried. You need to pre-load the summarized data in advance to get the performance gains.

I have come across a lot or people who really, and I mean REALLY don't like the idea of duplicating data in a summary cube but if the end justifies the means OK by me.

[fleaster]

Thanks for the feedback ...so I guess this covers the "read" aspect of a summary cube, but how about the "write" aspect (e.g. for adjustments and forecasting)?

e.g. would you have users write data to the Detailed cube, which then flows on to the Summary cube... or would you allow users to write topside adjustments to the Summary cube, which then backfeeds to the Detailed cube?

[rmackenzie]

would you have users write data to the Detailed cube, which then flows on to the Summary cube... or would you allow users to write topside adjustments to the Summary cube, which then backfeeds to the Detailed cube?

Sounds like you're not clear on the requirement - what if some users want to adjust at the detail level and others want to adjust at the summary level? Are you interested in prioritising one set of needs, over another? Is there a real need for the cubes to reconcile or are the 'topside' adjustments just a set of overlays that not everyone needs to, or can see?

[fleaster]

Hi Robin,
Now that you mention it, it's probably only a select group of users (that have a country consolidation role) that will need to see the overlays... however, not sure if this will change in future.

I guess that's probably the crux of the matter - we want to make the process as flexible as possible in order to future proof it...

...also in terms of Detail reconciling to Summary - I personally think for the sake of data integrity, that they should match (else we may get alot of users asking why X is different from Y etc)

M.

[rmackenzie]

So if you avoid rules for the reasons of performance (see Lotsa's post) then you're left with TI to push numbers from Detail to Summary. However if you are allowing for adjustments in one or more cubes (plus potential overlays for special audiences) then you're not really ever going to be able to guarantee that one cube equals the other. In all data management systems there's a constant trade-off between flexibility and integrity - you just have to find the right balance. E.g. if you get everyone to agree that the cubes only reconcile on Thursday lunchtimes and it's OK for them to be adjusted for the rest of the time, then maybe that's an OK balance for you?

[fleaster]

yes that's a fair point - I don't think the users have any expectations that the cubes will be aligned in real time, but they should be ok to wait for a "daily run" to get them reconciled.

thanks for the feedback