Combined Dimensions

[tgaluskapatterson]

I am currently trying to improve performance of the cube listed below. The question I have is can I improve performance if I combined the AR_Territory dimension with the AR_Customer dimension. Every customer has a territory so if i combined these to create one dimension called AR_CustomerTerritory will it improve performance? This would remove one of the intersections of data in the cube. Any thoughts?

Cube Setup
AR_Accounts-Memory Used 20KB, # of Elements 16
AR_Aging-Memory Used 16KB, # of Elements 13
AR_Territory-Memory Used 61448KB, # of Elements 33359
AR_Measure-Memory Used 13KB, # of Elements 2
AR_LoadVersion-Memory Used 32KB, # of Elements 46
AR_Customer-Memory Used 139272KB, # of Elements 370093

[Wim Gielis]

Yes, that's an improvement.
Create roll-ups for the Territories within that (very) large Customers dimension.
Do you mean that you have more than 33,000 territories ?

[lotsaram]

Cube Setup
AR_Accounts-Memory Used 20KB, # of Elements 16
AR_Aging-Memory Used 16KB, # of Elements 13
AR_Territory-Memory Used 61448KB, # of Elements 33359
AR_Measure-Memory Used 13KB, # of Elements 2
AR_LoadVersion-Memory Used 32KB, # of Elements 46
AR_Customer-Memory Used 139272KB, # of Elements 370093

Is that actually the cube setup? The exact cube setup? I can't imagine that such a cube would not have one or possibly more time dimensions and a measure dimension! The request for assistance guidelines are quite specific (but not pedantic) about information you should provide.

However even if providing all needed information a question of this nature is pretty difficult to answer without detailed knowledge of both the particular model and what exactly you mean by "performance". In general TM1 handles sparsity very well. Although combining the dimensions might give an improvement in a properly designed cube I wouldn't have thought both dimensions would really cause that much trouble BUT you need to test for your model. Having territory as a rollup of customer AND as a separate dimension possibly adds value (how else do you cope with customers changing territories over time). Other likely candidates I would look at than combining the dimensions are:
- the rules and whether they are properly optimized
- does the cube contain only numeric data or string data as well
- has dimension order of the cube been optimized

[tgaluskapatterson]

We recently completed a cube re-design on this cube. We combined the Territory and Customer dimensions which had 33,714 elements and 377,634 elements into one TerritoryCustomer dimension with 570,642 elements. This significantly reduced the load time of the cube from about 30 seconds to about 17-18 seconds. We also had users that were trying to pull data and were receiving a memory exceeded error. Now that the dimensions are combined we no longer have this issue. On larger cubes it appears reducing the number of data points or intersections greatly improves performance and memory usage. Looks like this is a good practice however if anyone thinks this is a bad practice please let me know? Thanks!

[Wim Gielis]

Hello

Performance is very important, but so is functionality (obviously). Look again at Lotsaram's post - if customers can change territories, it could change the cube architecture by adding a dimension.

[tgaluskapatterson]

Good point. We tested a customer change in our dev environment and it appeared to work just fine. If a customer changed territories the data loaded in the new month created a new "TerritoryCustomer" field which is what I would expect. Thanks for the input.