Cube/Metadata Design Question

[vmanojrc30]

We are building a Consolidation application with 10 Base Entities and approx. 2000 Cost Centers. Each Cost Center is valid for only one Entity. In Other words a group of Cost Centers are valid for One Entity and the same group of Cost centers are NOT valid for any other entities.

Given this requirement is it best approach to create Separate cubes for the 10 Entities and apply Extensibility on Cost Center dimension with Valid Cost centers for the Entity OR Create One Cube and apply UD Constraint on Entity with the Group of Valid Cost Centers ?

Appreciate any thoughts or recommendation between these 2 options.

[T_Kress · Accepted solution]

It is hard to say in isolation, as there may be other factors to consider in regard to the overall design.  And the below is just my two cents, others may have different opinions.

Are you planning on a parent cube (where all these entities consolidate together) with the cost center dimension at a more summary level, e.g. maybe only 100 or so parent cost center groups in the parent cube?

Having a parent cube that is at a summary cost center level which references either:

1. 1 base entity cube where constraints have been applied per entity and appropriate cost centers
2. 10 base entity cubes where each entity only has its valid cost centers

I think it becomes a preference at that point between 1 and 2 because both options will control the DU size in the base cube and the parent cube will already be at a more summary level.  One key benefit with extensibility is controlling DU size and I believe #1 and #2 will help limit DU size as you will not have valid intersections for invalid combinations of entity and cost center.

The bigger question then becomes how do you want to maintain things?  If their base entities move around a lot or they frequently add new base entities, then having #1 may be preferred as that is let cube setup.  If they do not add new entities often but do add cost centers often, then maybe #2 is better so long as the customer understands how to maintain alternate constraint rollup groups.

Another question is around how do they want to report on cost center?  If you use #1 then when drilling to base you will definitely want to have sparse row suppression as true and suppress no data intersections (to avoid seeing all cost centers for all base entities).  If you use #2 then when drilling to base on entity you will only see each entity's relevant cost centers without needing suppression.  In both cases you will need to use the .Options functionality when drilling to get the correct cost center extended level.

I think you also have to consider other UDs and the account dimension when you consider extending vertically or not, and how.  For example, maybe there are other things that would push you to #2 with each base entity having its own base cube.

I think the key is having a parent cube with only summary or parent cost centers, e.g. 100 or so.  That will be key to overall DU size control and performance.  After that then #1 vs #2 may come down to other factors.

I think all else being equal, meaning if there is no other reason to have 10 separate sub-cubes, one per entity, then I would lean towards 1 base cube with cost center constraints by entity, and 1 parent cube.

Curious to hear others thoughts.

[kberry · Accepted solution]

I can offer four pieces of advice from my experience implementing this design:

(1) The parent cube design Teresa describes is really the only way to go. Without that design, the cost center dim will prevent records from combining during consolidation. The number of records in the top entity will be equal to the sum of records in every base entity. If this doesn't cause problems from the outset, it will leave you vulnerable to performance problems later when the application expands. Users haven't had a problem drilling to a base entity to view cost center detail.

(2) Add the entity name to the cost center name or a text field so you can use Where clauses (UD1#root.Base.Where(Text1 Startswith XXX) to identify the cost centers for an entity on reports. It makes a big difference in performance. Suppression is very inefficient and doesn't create a great experience for users.  

(3) Consider using Display Member Group security to hide the cost centers not relevant to a user. This makes things less confusing and prevents users from performing unnecessary ad hoc drill downs on the entire cost center dimension.

(4) I experimented with single vs. multiple cubes and didn't find a difference. The single cube design is simpler and constraints are more easily modified than extensibility, so I go with one cube.

 For anyone not familiar with the cube design Teresa describes, it is as follows:

• extend the UD1 cost center dimension type into two dimensions, one with cost centers and one with parent members
• create two cubes, one with base entities and one with parent entities
• assign the lower cost center dimension to the cube with base entities so that cost center detail is available in base entities
• assign the upper UD1 dimension to the cube with parent entities so only summary information is available in parent entities
• link the base entities in the first cube to the second cube and place the base entities under their parent
• during consolidation OneStream will automatically summarize the cost center detail in base entities into UD1 parent members in parent entities

[T_Kress · Accepted solution]

It is hard to say in isolation, as there may be other factors to consider in regard to the overall design.  And the below is just my two cents, others may have different opinions.

Are you planning on a parent cube (where all these entities consolidate together) with the cost center dimension at a more summary level, e.g. maybe only 100 or so parent cost center groups in the parent cube?

Having a parent cube that is at a summary cost center level which references either:

1. 1 base entity cube where constraints have been applied per entity and appropriate cost centers
2. 10 base entity cubes where each entity only has its valid cost centers

I think it becomes a preference at that point between 1 and 2 because both options will control the DU size in the base cube and the parent cube will already be at a more summary level.  One key benefit with extensibility is controlling DU size and I believe #1 and #2 will help limit DU size as you will not have valid intersections for invalid combinations of entity and cost center.

The bigger question then becomes how do you want to maintain things?  If their base entities move around a lot or they frequently add new base entities, then having #1 may be preferred as that is let cube setup.  If they do not add new entities often but do add cost centers often, then maybe #2 is better so long as the customer understands how to maintain alternate constraint rollup groups.

Another question is around how do they want to report on cost center?  If you use #1 then when drilling to base you will definitely want to have sparse row suppression as true and suppress no data intersections (to avoid seeing all cost centers for all base entities).  If you use #2 then when drilling to base on entity you will only see each entity's relevant cost centers without needing suppression.  In both cases you will need to use the .Options functionality when drilling to get the correct cost center extended level.

I think you also have to consider other UDs and the account dimension when you consider extending vertically or not, and how.  For example, maybe there are other things that would push you to #2 with each base entity having its own base cube.

I think the key is having a parent cube with only summary or parent cost centers, e.g. 100 or so.  That will be key to overall DU size control and performance.  After that then #1 vs #2 may come down to other factors.

I think all else being equal, meaning if there is no other reason to have 10 separate sub-cubes, one per entity, then I would lean towards 1 base cube with cost center constraints by entity, and 1 parent cube.

Curious to hear others thoughts.

[kberry · Accepted solution]

I can offer four pieces of advice from my experience implementing this design:

(1) The parent cube design Teresa describes is really the only way to go. Without that design, the cost center dim will prevent records from combining during consolidation. The number of records in the top entity will be equal to the sum of records in every base entity. If this doesn't cause problems from the outset, it will leave you vulnerable to performance problems later when the application expands. Users haven't had a problem drilling to a base entity to view cost center detail.

(2) Add the entity name to the cost center name or a text field so you can use Where clauses (UD1#root.Base.Where(Text1 Startswith XXX) to identify the cost centers for an entity on reports. It makes a big difference in performance. Suppression is very inefficient and doesn't create a great experience for users.  

(3) Consider using Display Member Group security to hide the cost centers not relevant to a user. This makes things less confusing and prevents users from performing unnecessary ad hoc drill downs on the entire cost center dimension.

(4) I experimented with single vs. multiple cubes and didn't find a difference. The single cube design is simpler and constraints are more easily modified than extensibility, so I go with one cube.

 For anyone not familiar with the cube design Teresa describes, it is as follows:

• extend the UD1 cost center dimension type into two dimensions, one with cost centers and one with parent members
• create two cubes, one with base entities and one with parent entities
• assign the lower cost center dimension to the cube with base entities so that cost center detail is available in base entities
• assign the upper UD1 dimension to the cube with parent entities so only summary information is available in parent entities
• link the base entities in the first cube to the second cube and place the base entities under their parent
• during consolidation OneStream will automatically summarize the cost center detail in base entities into UD1 parent members in parent entities

[vmanojrc30]

Thanks @T_Kress @kberry for your advice.

I do have one follow up question with the Parent - Base Cube design.

1. Do I need to separate out Business rules that runs on Base Entities LC , Base Entities PC , Elims and Parent Entities (if any) and assign them separately to the respective Cube OR having One BR and assign it to Parent Cube would work (as the Base Cube is linked)?Will the Base Entity Cube have reference to it Parent to run any rules on the Parent Currency or Elim?

Thanks

Manoj

[T_Kress · Accepted solution]

I would have one business rule and in your business rule you put your IF statements to limit rules on running on base entities, parent currency, local currency, translated, etc.  That is a good practice anyway to have IF statements limiting when certain rules are triggered.  And then that one BR would be assigned to both the parent and base cubes.

[LeeB]

Have you considered having the Cost Centres in the Entity Dimension?  
You are going to create a lot of sparsity by having a 2,000 member UD1 dimension used only in part by each Entity.