This property specifies the dimension order of the actual cube file. When a cube is built, dimensions are arranged in decreasing order of cardinality, with the highest cardinality arranged first. This property overrides the cardinality order. Changing the order can affect cube size, cuboid count, and query performance.
Default value and behavior:
By default, the dimension order is computed dynamically during a full cube build. The dimensions are arranged in decreasing order of cardinality.
Dimensions can be reordered using the properties dialog box on the Kyvos web portal to define the order. This property enables users to move a lower cardinality dimension into higher precedence in the cube structure. Dimensions not selected in this property will be arranged based on the decreasing order of their cardinality.
Cube: If the property is set on a cube, then the value will override the connection level value for that cube’s build job.
Comes into effect:
A full cube build is required for this to take effect.
Dependencies and related properties:
This property has a significant impact on both the size of a cube as well as the query performance. The query performance improves for dimensions which are selected at the higher order. However, the cuboid size and cube build time increase resulting in slower query performance when higher cardinality dimensions are placed at lower order.
General recommendations include:
- Dimensions should be arranged in descending order by cardinality (this is the default behavior).
- Frequently filtered dimensions should have higher precedence.
- Frequently referenced dimensions should have higher precedence.
When working with Olympics data, the Athlete dimension has the highest precedence due to its highest cardinality. However, since other dimensions are queried and filtered more frequently, such as Country or Event, these dimensions can be moved higher to improve query performance. But, this may result in a larger cube.