Line Item and High Cardinality 

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. In BW 2.0, this was also known as a line item dimension, in which case the characteristic responsible for the high cardinality was seen as a line item. 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- 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.

       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 in 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.

Note: In SAP BW 3.0, the term line item dimension from SAP BW 2.0 must a) have precisely one characteristic and b) this characteristic must have a high cardinality. Before, the term line item dimension was often only associated with a). Hence the inclusion of this property in the above.  Be aware that a line item dimension has a different meaning now than in SAP BW2.0.

However, we recommend that you use ODS objects, where possible, instead of InfoCubes for line items. See Creating ODS Objects.

Activities

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