High Cardinality Flag in conjuntion with a Line Item Dimension

[Former Member]

I cannot find any documentation on the significance of setting the high-cardinality flag in conjunction with a line item dimension.

It is clear that a line item dimension does not create a dimension table. The high-cardinality flag optimizes indexes on large dimension tables (more than 20% the size of the fact table).

With that said, it would seem that the high-cardinality flag is irrelvant for a line item dimension (no DIM table). In the system (7.0), you can turn on high-cardinality and the line item flag simultaneously.

Does anyone know what this does from a technical standpoint?

[Former Member]

High cardinality is a signal to the underlying database, Oracle and maybe others, not to create a bitmap index. Bitmap indexes are very fast for retrieval, but are only appropriate where there is low-cardinality (few unique values, like male or female)

An example of high cardinality would be employee id number.

[Former Member]

hi,

line item dimention ment for improving the performence purpose purpose .If you dim automatically it will generate sids ,in this case when you run the query it degrades the performence .the relation for line item is 1:N relation .If you put both tick marks that relations will be N:N relations.

thanks,

vis

[Former Member]

Hi,

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.

Hopeful it helps to understand,

thanks

vissu.

[Former Member]

Just to clarify my note:

I am clear on line items dimensions and the high cardinality flag when used separately.

However, in the system, you can set them together and that does not make sense to me since a line item dimension has no DIM table.

What happens when you use them together (if anything)?

[Former Member]

Hello,

Its the way indexs are created when a cardinality flag is set and nothing else.

it has different effect depdending upon the Database used and behaves differently if Oracle is used or SQL is used.

For a Non Oracle system this flag has no effect on index type of query performace but it improves data loading.

For non oracle system this flag is used to do some internal checks and should be used in non oracle system and here they have no negative impact.

For an Oracle system a cube has a Bitmap index by default for reading purpose on all the dimension column in fact table which is best for reading

if you select this flag then B-tree index are created instead of Bitmap index on that dimension...this will effect the query reading and the reading will worse if you set this and therefore you should not use it in the case of Oracle systems and if the object is going to be used in the query.

Thanks

Ajeet

[former_member185837]

Ajeet Singh wrote:

 For an Oracle system a cube has a Bitmap index by default for reading purpose on all the dimension column in fact table which is best for reading 
if you select this flag then B-tree index are created instead of Bitmap index on that dimension...

To further clarify what Robert Jerome was asking, the high cardinality flag influences indexing on fact tables, not on dimension tables. Hence, this parameter is effective even when the dimension is line item--i.e. when there is not a physical dimension table at all.

Although a line item dimension does not have a physical dimension table (the dimension table exists, but it is a view on the SID master data table), the fact table has a column for the foreign key of such a dimension. Being the dimension line item, this column stores the characteristic's SIDs.

BW indexes each dimension's foreign key in the fact tables--whether the dimension is line item or not. For instance, [this picture|http://wiki.sdn.sap.com/wiki/download/attachments/19688/high_card_dim_01.jpg] shows two indexes defined on the foreign keys of two line item dimensions.

If the dimension is not flagged as having high cardinality (default), then a bitmap index is used. On the other hand, if you set the high cardinality flag, a B-tree index is used.

[This second picture|http://wiki.sdn.sap.com/wiki/download/attachments/19688/high_card_dim_02.jpg] shows how the foreing keys for the two line item dimensions of the previous example are indexed. The first line item dimension has not high cardinality, therefore a bitmap index is used on the dimension's foreing key. Instead, since the second line item dimension has high cardinality, a B-tree index is used.

Hope this helps understand why the InfoCube editor allows you to set the high cardinality flag also when the line item parameter is flagged.

Regards,

Davide