Show TOC

Line Item and High CardinalityLocate this document in the navigation structure

Use

When compared to a fact table, dimensions ideally have a small cardinality. However, there is an exception to this rule. For example, there are InfoCubes in which a characteristic Document is used, in which case almost every entry in the fact table is assigned to a different Document. This means that the dimension (or the associated dimension table) has almost as many entries as the fact table itself. We refer here to a degenerated dimension.

Generally, relational and multi-dimensional database systems have problems to efficiently process such dimensions. You can use the indicators Line Item and High Cardinality to execute the following optimizations:

  1. Line Item: This means the dimension contains precisely one characteristic. This means that the system does not create a dimension table. Instead, the SID table of the characteristic takes on the role of dimension table. Removing the dimension table has the following advantages:

    • When loading transaction data, no IDs are generated for the entries in the dimension table. This number range operation can compromise performance precisely in the case where a degenerated dimension is involved.

    • A table - possibly having a very large cardinality - is removed from the star schema. As a result, the SQL-based queries are simpler. In many cases, the database optimizer can choose better execution plans.

    Nevertheless, it also has a disadvantage: A dimension marked as a Line Item cannot subsequently include additional characteristics. This is only possible with normal dimensions.

    Recommendation

    We recommend that you use DataStore objects, where possible, instead of InfoCubes for line items. See Creating DataStore Objects.

  2. High Cardinality: This means that the dimension is to have a large number of instances (that is, a high cardinality). This information is used to carry out optimizations on a physical level depending on the database platform. Different index types are used than is normally the case. A general rule is that a dimension has a high cardinality when the number of dimension entries is at least 20% of the fact table entries. If you are unsure, do not select a dimension having high cardinality.

Activities

When creating dimensions in the InfoCube maintenance, flag the relevant dimension as a Line Item/ having High Cardinality.