Multiple Cubes or Measure Entity

Center of Excellence

1.   Abstract

Using a measure as a dimension in a Cube should always be weighted based on the aim. The balance between the Cube size and Cube proliferation, how the measure will be shown in a layout, and any calculation between different measures.

Board exploits a multidimensional and a multi-Cube database. When designing the data model, the developer must evaluate the benefit from the trade-off between a few Cubes with many dimensions versus more Cubes with fewer dimensions. Pros and cons of database maintenance, performance, and usability must also be considered.

2.   Context

In some cases, requirements for reporting or planning might create a dilemma for developers: either define the measure as an Entity included in a Cube or to separate the measures in two or more Cubes.

Both solutions have strengths and weaknesses and could be the preferred solution for specific cases.

3.   Content

Before we start, let’s have a look at an example requirement:

The measures in this report are partly loaded from a data source and partly ad-hoc calculations that need to be performed in Board.

The following two approaches can be used to develop this in Board.

Approach 1 – Develop as individual info-Cubes

• Each measure is stored in an individual info-Cube
• The layout of this report is aligned vertically (no dimensions by row)
• Measures calculations are done with block algorithms

Approach 2 – Develop with Measure Entity as a dimension

• Each measure is an item of the Entity “Measure”
• The Entity measure is used in a single info-Cube
• The layout of this report is displayed horizontally. The dimension measure is by row.
• Measures calculations are achieved using rules

The strengths and weaknesses of both approaches can be summarized as follows:

Approach 1 – Develop as individual info-Cube

Approach 2 – Develop with Measure Entity as a dimension. 

3.1 One of the main decision drivers: data entry requirements

The data entry requirements are often the main driver of the decision above.

In practice, the developer will follow approach 1, especially in application/modules where the data entry is performed on a single measure and broken down by specific details which are applicable only to that particular measure.

In contrast, the developer should follow approach 2 when the data entry requirement is to input multiple key figures at the same time and they all share the same dimensionality.

For example, let’s imagine an FP&A application in which the scope is to plan the following measures: 

In this example, the first four measures are entered individually and are the object of a dedicated detailed planning. They also involve specific details which are not applicable to other measures (Net sales and COGS are by product, Personnel costs are by employee, CAPEX and depreciation are by asset.) For these reasons, the most likely preferred approach should be Approach 1 – Develop as individual info-Cube.

However, you’ll notice that the last two measures are planned in combination and share the same details (for example account and cost center). For these reasons, the better approach is Approach 2 – Develop with Measure Entity as a dimension.

As you can see in the example above, inside the same application we’ve used both approaches.

4. Conclusion

As mentioned before, both approaches can be a solution based on the requirements. Reporting requirements therefore could drive the choice of one of the approaches.

As a recommended best practice, approach 1–develop as individual info-Cube, is best when:

• The dimensionality of the values is not similar
• The values stored are mostly used for calculation purposes, either data flows or column algorithms (potentially even in combination with different values)
• The values are mostly used in stand-alone reporting

As a recommended best practice, approach 2 – Develop with measure Entity as a dimension is best used when:

• The dimensionality of the values is equal or very similar
• The values make sense in a stand-alone way, where no calculation is needed to reach the reporting value.
• Entity members need to be used in a rule.
• Data-entry is required vertically.

 

Center of Excellence

Thanks @Andries de Vries for the insights on this topic!