High cardinality and line dimenson

[Former Member]

Hi

Are High cardinality and line dimenson both are dependent?

my understanding is that if the dimenson is more than 10% size of the fact table then we go for line dimenson. Highcardinality should be given only if the dimenson is more than 10% of fact table.By choosing line dimenson,fact table will be directly linked to sid table as there will be no dimenson table. Does it mean if I choose line dimenson, I can't go for high cardinality as there is no dimenson? Please let me know the relationship the between the two?

Thank you

Sriya

[Former Member · Accepted solution]

Hi

Check this also

Raja

[Former Member]

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

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.

For example a dimension having Sales Doc number and Sales Org can be set as High Cardinal as Sales Doc number will occur many times.

Hope this helps

Raja