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:
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
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
8 | |
7 | |
5 | |
4 | |
4 | |
4 | |
4 | |
3 | |
3 | |
3 |