Summary
Symptom
FAQ note on the SAP_INFOCUBE_DESIGNS report.
If dimension tables are too large then they can cause badly performing table joins on database level, as well as poor query performance. As the data volume grows and the data allocation changes over the time, this SAP_INFOCUBE_DESIGNS check should be executed regularly on the BW system, using tcode SE38.
Other termsIf dimension tables are too large then they can cause badly performing table joins on database level, as well as poor query performance. As the data volume grows and the data allocation changes over the time, this SAP_INFOCUBE_DESIGNS check should be executed regularly on the BW system, using tcode SE38.
SAP_INFOCUBE_DESIGNS, infocube size, dimension table, fact table, performance, compression
Solution
====== Purpose ======
The output of SAP_INFOCUBE_DESIGNS shows the database tables of the InfoCubes on the system, the number of records in these tables & the ratio of the dimension table size to the fact table size.
For each infocube you will see Rows and Ratio for all the dimension tables and fact tables.
Rows indicate the number of rows in that Dimension or Fact table.
Ratio (% in the last column) indicates the number of rows in that table divided by number of rows in the fact table.
As a standard, the ratio of rows in Dimension table to rows in Fact table should not be more than 10%.
If it is more than 10% then you will see the line highlighted in red colour in this report and you should consider redesigning that particular dimension. (The red highlight will only show up for cubes with more than 30.000 rows to avoid false alarms).
Example:
ZSDTEST /BIC/DZSTEST1 rows: 777.750 ratio: 61 % <-red
::
ZSDTEST /BIC/FZSTEST rows: 1.269.233 ratio: 100 %
To rectify this, you can do one of two things (for both of these solutions you will need to empty the cube before making the change):
a) If there are too many characteristics in that dimension, you can create more dimensions (if possible) and move some of the characteristics into the new dimension. This will split some of the data in the dimension table making it smaller.
b) If there is a characteristic that has a lot of data in this particular cube, you can move it into a dimension on its own and mark the dimension as a line item dimension. This stores that characteristic value in the fact table as apposed to the dimension table and helps improve performance on the infocube.
====== Compressed or Not compressed? ======
If everything is in the E-fact table (i.e. for /BI0/E<infocube name> ratio = 100%) therefore that InfoCube is compressed.
Example:
0QM_C10 /BI0/E0QM_C10 rows: 81.006 ratio: 100 % <-
0QM_C10 /BI0/F0QM_C10 rows: 99 ratio: 0 %
If everything is in the F-fact table (i.e. for /BI0/F<infocube name> ratio = 100%) therefore that InfoCube is NOT compressed.
Example:
0CRM_C04 /BI0/E0CRM_C04 rows: 0 ratio: 0 %
0CRM_C04 /BI0/F0CRM_C04 rows: 221 ratio: 100 % <-
====== Large TCT tables ======
Tables for Technical Content (TCT) InfoCubes are delivered large & thus usually show up as red in the output of SAP_INFOCUBE_DESIGNS. Therefore they can be ignored.
Example:
0TCT_C01 /BI0/D0TCT_C013 rows: 2.533 ratio: 0 %
0TCT_C01 /BI0/D0TCT_C018 rows: 31 ratio: 0 %
0TCT_C01 /BI0/D0TCT_C019 rows: 604.010 ratio: 18 % <-red
0TCT_C01 /BI0/D0TCT_C01P rows: 779 ratio: 0 %
0TCT_C01 /BI0/D0TCT_C01T rows: 1.008 ratio: 0 %
0TCT_C01 /BI0/E0TCT_C01 rows: 0 ratio: 0 %
0TCT_C01 /BI0/F0TCT_C01 rows: 3.289.233 ratio: 100 %
====== Other technical information ======
Report SAP_INFOCUBE_DESIGNS reads the number of records from the database table DBA_TABLES. If you feel that the report shows wrong data, please check the corresponding entry in DBA_TABLES. Have a look at the fields LAST_ANALYZED and NUM_ROWS. These will reflect the data you see in SAP_INFOCUBE_DESIGNS.
The output of SAP_INFOCUBE_DESIGNS shows the database tables of the InfoCubes on the system, the number of records in these tables & the ratio of the dimension table size to the fact table size.
For each infocube you will see Rows and Ratio for all the dimension tables and fact tables.
Rows indicate the number of rows in that Dimension or Fact table.
Ratio (% in the last column) indicates the number of rows in that table divided by number of rows in the fact table.
As a standard, the ratio of rows in Dimension table to rows in Fact table should not be more than 10%.
If it is more than 10% then you will see the line highlighted in red colour in this report and you should consider redesigning that particular dimension. (The red highlight will only show up for cubes with more than 30.000 rows to avoid false alarms).
Example:
ZSDTEST /BIC/DZSTEST1 rows: 777.750 ratio: 61 % <-red
::
ZSDTEST /BIC/FZSTEST rows: 1.269.233 ratio: 100 %
To rectify this, you can do one of two things (for both of these solutions you will need to empty the cube before making the change):
a) If there are too many characteristics in that dimension, you can create more dimensions (if possible) and move some of the characteristics into the new dimension. This will split some of the data in the dimension table making it smaller.
b) If there is a characteristic that has a lot of data in this particular cube, you can move it into a dimension on its own and mark the dimension as a line item dimension. This stores that characteristic value in the fact table as apposed to the dimension table and helps improve performance on the infocube.
====== Compressed or Not compressed? ======
If everything is in the E-fact table (i.e. for /BI0/E<infocube name> ratio = 100%) therefore that InfoCube is compressed.
Example:
0QM_C10 /BI0/E0QM_C10 rows: 81.006 ratio: 100 % <-
0QM_C10 /BI0/F0QM_C10 rows: 99 ratio: 0 %
If everything is in the F-fact table (i.e. for /BI0/F<infocube name> ratio = 100%) therefore that InfoCube is NOT compressed.
Example:
0CRM_C04 /BI0/E0CRM_C04 rows: 0 ratio: 0 %
0CRM_C04 /BI0/F0CRM_C04 rows: 221 ratio: 100 % <-
====== Large TCT tables ======
Tables for Technical Content (TCT) InfoCubes are delivered large & thus usually show up as red in the output of SAP_INFOCUBE_DESIGNS. Therefore they can be ignored.
Example:
0TCT_C01 /BI0/D0TCT_C013 rows: 2.533 ratio: 0 %
0TCT_C01 /BI0/D0TCT_C018 rows: 31 ratio: 0 %
0TCT_C01 /BI0/D0TCT_C019 rows: 604.010 ratio: 18 % <-red
0TCT_C01 /BI0/D0TCT_C01P rows: 779 ratio: 0 %
0TCT_C01 /BI0/D0TCT_C01T rows: 1.008 ratio: 0 %
0TCT_C01 /BI0/E0TCT_C01 rows: 0 ratio: 0 %
0TCT_C01 /BI0/F0TCT_C01 rows: 3.289.233 ratio: 100 %
====== Other technical information ======
Report SAP_INFOCUBE_DESIGNS reads the number of records from the database table DBA_TABLES. If you feel that the report shows wrong data, please check the corresponding entry in DBA_TABLES. Have a look at the fields LAST_ANALYZED and NUM_ROWS. These will reflect the data you see in SAP_INFOCUBE_DESIGNS.
Header Data
| Release Status: | Released for Customer |
| Released on: | 04.01.2011 11:04:33 |
| Master Language: | English |
| Priority: | Recommendations/additional info |
| Category: | FAQ |
| Primary Component: | BW-BEX-OT-DBIF Interface to Database |
| Secondary Components: | BW-WHM-DBA-ICUB InfoCubes |
Affected Releases
Release-Independent
Related Notes
No comments:
Post a Comment