Skip to Content
Author's profile photo Former Member

index in Line item and high cardinality dimensions

Line item and high cardinality dimension

The info cube uses bit map index and the B tree index. The creation of the indices depends on underlying database also. This document demonstrates and explains the indices which are created when a line item dimension and the High cardinality dimension check boxes are selected while creating a dimension in the cube.

             

Line Item dimension:  If this check box is checked, it implies that only one characteristic can be added to this dimension. Once a dimension becomes a line item, the system does not create a dimension table for it and the SID table itself is used as the dimension table.

The Line item dimension is considered as high cardinality dimension and hence B-tree indices (non-unique) are created it by the system by default. Though you get an option of high cardinality also in the properties of the dimension but checking that does not make any difference, as a line item dimension is meant to handle a high cardinality.

High cardinality dimension: Checking this check box tells the system that this dimension has high cardinality and the system creates a B-tree index for this, as B tree index is more suited in high cardinality than the Bitmap index which the system generates by default and uses for general dimensions.

Following are certain scenarios in which the Bit map and B tree indices are created:

  1. If nothing is done to the dimensions, i.e.  no LID and the HCD is selected: The system generates the Bitmap index in Oracle and B-tree Index in MS SQL server for the fact tables.

Dimension tables:

B-tree (Unique) –primary index in Oracle

B-tree (non-unique)-secondary Index in Oracle

B-tree (Unique, clustered) – Primary index in MS SQL

B-tree (non-unique, non-clustered)- secondary index in MS SQL

  1. If you check the Line item dimension only:  The system generates the B tree (non-unique) index in Oracle and B-tree (Non unique and non-clustered)
  2. If both the LID and the HCD are checked: The system generates B-tree index. Checking both does not have any effect on the type of index created.

/wp-content/uploads/2013/09/scn_278865.png

                                               

BIT MAP Index :

The Bit map index is used when the no. of distinct values in a column in very less. In columns with the low cardinality, this index has been found to improve the performance dramatically

CARDINALITY = total number of distinct values / total number of rows

The BIT map index performs optimally if the cardinality is less than 0.01.

Following is as illustration of the BIT map index:

Let us consider that we have a column which contains the designations that cab held by an employee in an organization, The following Bit map table is created by the system:

DESIGNATION

M

SA

A

C

Manager(M)

1

0

0

0

Senior associate(SA)

0

1

0

0

Associate(A)

0

0

1

0

Consultant(C)

0

0

0

1

Manager(M)

1

0

0

0

Senior associate(SA)

0

1

0

0

Manager(M)

1

0

0

0

Associate(A)

0

0

1

0

Associate(A)

0

0

1

0

Associate(A)

0

0

1

0

Suppose the query is run for Associate and manager, the following table is formed after the Boolean operation:

MANAGER

ASSOCIATE

RESULT

1

0

1

0

0

0

0

1

1

0

0

0

1

0

1

0

0

0

1

0

1

0

1

1

0

1

1

0

1

1

The logical ‘OR’ operation is carried out between the ‘manager’ and the ‘associate’ and the rows which have 1 in them are shown in the query.

Assigned tags

      16 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Former Member
      Former Member

      Hi Saurabh,

      Very nice blog. Thanks for sharing.

      Thanks

      Aakash

      Author's profile photo Former Member
      Former Member
      Blog Post Author

      Hi Akash,

      Thanks a lot for the comments.

      Regards,

      Saurabh

      Author's profile photo CH Raman
      CH Raman

      Really nice presentation and useful doc.

      Thanks for sharing and making.

      Thanks

      Raman

      Author's profile photo Former Member
      Former Member
      Blog Post Author

      Hi Ram,

      Thanks for the comment, I found very less material on the web about this, so thought to compile.

      Regards,

      Saurabh

      Author's profile photo Martin Grob
      Martin Grob

      Nicely made thanks for sharing

      Martin

      Author's profile photo Former Member
      Former Member
      Blog Post Author

      Hi Martin,

      Thanks a lot.

      Regards,

      Saurabh

      Author's profile photo Former Member
      Former Member
      Blog Post Author

      Hi suman,

      I believe I was correct, The system does not create a dimension table for LID and uses the SID table of the charateristic as dimension table. Thus a dimensional table which would have a very high cardinality is removed from the star schema and the excess baggage of creating DIMIDs with every load is also done away with.

      following link of SAP would help you.

      http://help.sap.com/saphelp_nw04/helpdata/en/a7/d50f395fc8cb7fe10000000a11402f/content.htm

      I think I understood you correctly.

      Regards,

      Saurabh

      Author's profile photo Suman Chakravarthy K
      Suman Chakravarthy K

      Hi Suarabh,

      My mistake. I misunderstood your definition. You are right. Dim table will not be created and SID table will be directly linked to Fact table.

      Regards,

      Suman

      Author's profile photo Former Member
      Former Member

      Very Nicely explined.

      Author's profile photo Anshu Lilhori
      Anshu Lilhori

      Hi,

      I have read so many documents on the same but i must say this is the best one..Reason proper definition of everything..

      Graphical representation definitely makes your document more stronger and easy to understand and analyze.

      Keep up the good work.

      Regards,

      AL

      Author's profile photo Former Member
      Former Member

      Hi Saurabh,

      Wonderful blog. Very nicely explained.

      Regards,

      Ankeet

      Author's profile photo Kamal Mehta
      Kamal Mehta

      Nicely done .

      Very well presented.

      Thanks

      Author's profile photo Sebastien Francal
      Sebastien Francal

      Hi,

      You wrote that

      "The Line item dimension is considered as high cardinality dimension and hence B-tree indices (non-unique) are created it by the system by default. Though you get an option of high cardinality also in the properties of the dimension but checking that does not make any difference, as a line item dimension is meant to handle a high cardinality."

      but is that actually the case???

      I read several contradicting posts about this, including this one from Lars Breddemann where he says

      "The fallacy the customer and I became victims of was to believe that flagging a dimension as line item automatically includes the setting for the B*Tree index. After all this totally makes sense, doesn't it? Unfortunately, but correctly, the BW developers separated these two functions and provided two flags for infocube dimensions that can be set independently."

      Can anyone confirm either post???

      Regards,

      Author's profile photo Lars Breddemann
      Lars Breddemann

      Sebastian, be careful not to mix up BW on Oracle and BW on MS SQL Server.

      The indexing strategies are highly different for both.

      In fact if I remember correctly both statements are true - each on the platform it refers to.

      Cheers,

      Lars

      Author's profile photo Sebastien Francal
      Sebastien Francal

      Hi Lars, and thanks for your quick reply!

      My question is specifically for BW on Oracle: is flagging a dimension as Line Item but not as High Cardinality the same as flagging both, or not?

      Regards,

      Author's profile photo Lars Breddemann
      Lars Breddemann

      It's not the same thing, as I have written in my meanwhile ancient blog post,