Cube definition insights

Center of Excellence
edited April 2023 in Best Practices

1. Abstract

Insights on cubes definition and options.

2. Context

The following best practices are applicable every time a new cube needs to be created in a data model.

3. Content

Some limitations and constraints but also tips and guidelines from best practices and real-life cases.

  • Cube’s dimensions can range from one to 32. Generally, the number of dimensions of a Cube should not exceed 7. A Cube with more than 7 dimensions our experience suggests that can be difficult for end-users to understand and use and could generate performance challenges. Before creating a Cube with more than 7 dimensions, consider revising your data model to reduce the number of dimensions.
  • A Cube can have up to 256 versions. Generally, most Cubes only have a single version. Add other versions only when needed for instance for reporting optimization. When secondary versions are needed, generally a few versions are sufficient. If you have more than 3 versions, analyze whether or not each version is needed since each additional version will increase the size of the data model and memory consumption.
  • It is not possible to create a cube with a fully dense structure (at least 1 dimension needs to be set as sparse).
  • In the case of a numeric cube choose between a Single or a Double Precision. Once a precision (double or single) is chosen is suggested to keep it consistent across all data model's cubes unless is necessary for specific requirements
  • Single precision is the recommended data type to use for numeric Cubes, providing an excellent trade-off between accuracy and space required. A Single-data type Cube is a numeric Cube with up to seven decimal places. It's the most commonly used data type. In a single cube type, every cell occupies half size of a double one but numbers that need more than 4 bytes to be saved will be rounded.
  • A double-data type Cube is a numeric Cube with up to 14 decimals. A double data type is used for complex calculations, such as allocations, that aren't typically rounded. Double precision cube type occupies 8 bytes for each cell and provides you additional accuracy useful for calculation involving decimal precision (i.e. driver-based allocation)
  • Note that rounding occurs only when writing data to a single cell of the Cube, therefore if your data source provides data in single precision or in an 8-character field you should use this data type. When used in procedures and reports, single-precision Cubes are aggregated using double-precision variables therefore virtually no aggregation occurs.