Cube Limit

[miked]

Hi All,

I have a bit of an extreme question here.  Is there any limit, whether system or logically, that there are too many cubes in an application?  My example (which I have simplified here for illustrative purposes) relates to UD1 for Cost Centers where we have 150 entities all having unique and different cost centers.  Since they each have 20 unique Cost Centers, that's potentially 3,000 Cost Centers in one dimension which feels big and very sparse. 

Thinking through options for how to approach, one option we are weighing is to add a separate cube for each entity and assign a separate UD1 dimension to each cube / entity so they would only have 20 Cost Centers to contend with.  As it rolls up the entity structure, it would consolidate to a Summary cube that has one member called 'Total Cost Center' thereby shrinking the data unit in the top cube.

Has anyone dealt with these high numbers of cubes before?  My gut tells me be that this could be troublesome but sometimes it seems like others are more liberal in their cube assignments.

Curious your thoughts?  Also, if in another example, there were 2,000 entities rather than 150, would that change your opinion?  Is there a line here?

Thx,

Mike

[RobbSalzmann · Accepted solution]

Given the relationship of cost centers to entities you describe, where any given cost center belongs to only one entity, I see cost centers naturally rolling up to entities and would keeps them all in the same dimension type.  

With each cost center relating to only its entity, there most likely isn’t a reporting or data entry use case that would benefit from intersecting these using different dimension types.  

I would roll them up to their entity based on the info here.  I don’t think you give up much flexibility with single member sparsity by keeping things fairly simple. 

[RobbSalzmann · Accepted solution]

Given the relationship of cost centers to entities you describe, where any given cost center belongs to only one entity, I see cost centers naturally rolling up to entities and would keeps them all in the same dimension type.  

With each cost center relating to only its entity, there most likely isn’t a reporting or data entry use case that would benefit from intersecting these using different dimension types.  

I would roll them up to their entity based on the info here.  I don’t think you give up much flexibility with single member sparsity by keeping things fairly simple. 

[miked]

Thx Robb.  I don't necessarily want to explode my entity hierarchy into 1500 entities.  But just curious if you have any thoughts on number of cube limits regardless of the use case.  Are you aware of limits?

Thx,

Mike

[JackLacava]

Under the hood, Cubes are basically just another dataunit dimension. So I'd say you should (roughly) apply the same considerations to them as you do to Entities.

[miked]

ok understood.  and have you heard of successful projects that have 100+ cubes or more?

[JackLacava]

Man, we struggle to get customers to use 2 or 3 cubes instead of their big bad monocubes, let alone 100... 😂

[JackLacava · Accepted solution]

To me, this sounds like you need a "Register", a custom table like you have in Marketplace solutions, keeping records for cost centers; then you can pump aggregated data in the cube, or even leave everything in that custom table (or in Stage, as an Attribute) and just blend data on reports. You could leverage BiBlend to do aggregations if you have complex hierarchies.

Data specific to one or two Entities makes for bad UDs. They're more like Attributes, or custom data.

This said, 3000 members in one UD might not be the end of the world, if the rest of your hierarchies is smallish and your rules are well-built.

[miked]

yes, in fact, the real use case is that they ultimately just need custom descriptions on their cost centers so we are exploring all options.  Another thought is to just have 20 generic cost centers that all entities load into and use a lookup table and XFBR string to return the custom description applicable to each entity/UD1 combination.

Sometimes extending cubes is quite liberally talked about almost as if it's like an alternate hierarchy.  but i tend to be more conservative.

[RobbSalzmann]

How many cubes are you thinking?  I think we all would agree that after a while too much of anything can adversely impact maintainability, performance, or useability.  Which two of these is important to your client?

In aviation we have an expression: "With enough thrust, anything will fly". 🙂

[miked]

The idea we were exploring was a seperate cube for each entity for which there are 120 now...but that would grow by 10 or so a year.  I totally agree on the concerns.  We're still scoping it out but yes, all 3 of those factors are important and come into play.

My sense is 100+ cubes seems over the top and the lookup table option might be more sensible.  Only drawback to lookup / XFBR's is the user would need to know how to use them when creating a custom quickview on the fly.

[Big_Rick_CPM]

In addition to other responses, I would consider a simple 2 cube approach (TopEntity cube and BaseEntity cube). In the TopEntity cube, you have a can have a summary UD1 dimension that stops at TotalCostCenter. In the BaseEntity cube, you can extend UD1 to include all cost centers.

From there, you should then connect your base entity to your top entity cubes via any entity structure that makes sense for reporting (e.g. there is likely a clean break where having cost center detail is needed in reports). Next, to prevent every entity from seeing every UD1, just make sure you use entity constraints to limit where data is valid.  By default, everything is set to Root, but you can explicitly define which UD members are valid for specific entities this way. 

[miked]

Thx Rick.  yes that is similar to what we think we will end up doing (summary, detail cube).   However, I still think we want to avoid having the 3000 UD1 members even if we leverage constraints.  Having that many members in a dimension just makes drilling in Excel slower especially if it's done in combination with another dimension with a couple hundred other members.  But I agree with your thinking and am a strong proponent of constraints as well.  Thx

[Big_Rick_CPM]

If it helps, I have a client with this design/similar issue with sparsity and around 75k members in UD1 with no performance issues on drill downs in excel/app. The key is to make sure you have suppression on before drilling down on a quick view/cubeview. 

By using the constraints, the "Suppress invalid" option will work nicely for suppressing out combinations automatically. Also make sure you look into sparse row suppression settings which can help cube view performance. 

[miked]

Thx Rick. That's impressive.  We have seen performance issues in the past on some apps with the combination of a few dimensions having just a few hundred members when drilling on more than one at a time.  Even with suppression turned on.  We shall see on this one.  Thx for the reply...much appreciated.

[kberry]

We have also used Rick's approach with success on a very large UD dimension. We first experimented with a 450-cube approach. It worked fine, but the two-cube approach Rick describes is easier to implement and maintain.

We use two other techniques to limit drill-through volume. We set Display Member Security linked to the entity on each UD member. Users only see the cost centers for the entities to which they have access. Also, on cube views and other reports we limit the cost center members using Where clauses, An example is U4#Root.Base.Where(Name Contains |CVEntity|). In our case the entity name was embedded in the cost center, but you could also place it on a text field. Both approaches limit our reliance on suppression, which seems to help performance. 

[miked]

Thank you!