5.11 Check query per-formance and access plan
In another SAP GUI window, go to DBACockpit -> Performance -> SQL Cache.
On the Selection Criteria window, remove 100 from the Execution field, and enter the
“SELECT*FZSD_C01*” in the SQL Text field.
The following statement execution result is returned:
Double click on the statement, or click on the Explain button, you can get the following access plan:
Ques-tions:
1. What method is used to access the fact table? If it is an index access, what is the index being used? Hint – now it is using MDC block index based on CALMONTH dimension column.
2. Which dimension table is joined firstly to the fact table, what type of join?
3. What is the total cost of this access plan? Compared this cost with the result from the exercise 4.3.
Write down the Avg. Execution time, Buffer Quality, Rows Read, SQL Sorts and the total Estimated Costs to the Work Sheet on the final page of this hand book.
*Note* For more accurate comparison, especially to demonstrate the disk I/O cost sav-ing by ussav-ing MDC feature, you need to run the query in a “cold” buffer pool, which means the table data should not have been cached in the buffer pool. This will require comparing the result on the first query run on a newly started system. Therefore you might want to stop and restart your SAP system before running the query.
As you can tell, the query time is much faster after the fact table has been converted to MDC table. And the access plan indicates that the block index defined on
SID_0CALMONTH is used to select rows on the fact table.
5.12 Change settings in table RSADMIN.
Run program “SAP_RSADMIN_MAINTAIN” in transaction SA38.
Enter DB6_MDC_FOR_PSA in the OBJECT field, and YES in the VALUE field, choose INSERT, then click on Execute button.
5.13 Check settings in table RSADMIN.
Run transaction SE16 - Data Browser Browse table: RSADMIN
Restrict values in field “OBJECT” to “DB6*”
Entries starting with DB6 are DB2 for LUW related.
The following entries control MDC settings:
•1 DB6_MDC_FOR_PSA = YES/NO Æ default NO
•2 DB6_MDC_FOR_AGGREGATES = YES/NO Æ default YES
Unit 6 – Deep Compression for SAP BW
6.1 Check the Compression Details for InfoCube
Run transaction RSRV:
On the “Analysis and Repair of BI Objects” screen, expand “Tests in Transaction RSRV” Æ “All Elemetary Tests” Æ “Database” Æ Double-click “Data row compres-sion of InfoProviders…”. to select the test.
Double click on the row in the right side of the screen. A pop-up will appear.
6.2 Select the InfoProvider you want to check
Select the InfoProvider you want to test “ZSD_C01” and click “Transfer.”
6.3 Execute the test
Expand the row in the right side of the screen, high light the line “Info-Provider=ZSD_C01”, execute the test by pressing button “Execute.”
6.4 Check the result
The result shows the compression feature is NOT enabled for the F fact and E fact ta-bles of InfoCube ZSA_C01, and the F fact table size is 32.78MB.
6.5 Estimate Compression Ratio
Run transaction DBACockpit, go to Space Æ Single Table Analysis, enter table name
“/BIC/FZSD_C01” and hit Enter.
Select the tab “Compression Status”. It shows that this fact table is not enabled for compression.
Click “Compression Check” button, on the following screen, click on “Execute” but-ton.
Note: since this is a small table, it is OK to choose “Run in Dialog”. If it is a large ta-ble, it is better to run in a background job.
When it finishes, click on “Refresh” button, and the compression check result is shown:
It is estimated that 66% of pages will be saved.
6.6 Enable Compression
Click on “Compression On/Off” button, on the following screen, choose “Enable Compression and Run REORG”, click “Execute” button.
On the Schedule a New Action screen, click on “Execute Immediately” button.
6.7 Check the compression result
You can use transaction SM37 to check the Reorg job status. Once it is completed, refresh the Single Table Analysis screen. And you will see that it is compressed with 66% pages saved.
Flip to the tab “Table”, you will see the new table size is now 12864K.
Write down the Total Table Size, Total Index Size and No. of Pages with Data to the Work Sheet on the final page of this hand book.
You should notice that the table size and the No. of Pages with Data have decreased significantly.
6.8 Run BI
Run transaction RSRT, enter Query Name “ZSD_C01/BENCHMARK_QUERY_2”, click on “Execute + Debug”,
check flags “Do not use cache” and “Display statistics data” (under “Others” section), hit Continue button.
After the query results are displayed press “Back” button in the toolbar of the result window, the runtime statistics of the query is displayed.
Question: how much time is spent in the database (Data Manager) this time?
Write down the Duration of Data Manager to the Work Sheet on the final page of this hand book.
6.9 Check query per-formance and access plan
In another SAP GUI window, go to DBACockpit -> Performance -> SQL Cache.
On the Selection Criteria window, remove 100 from the Execution field, and enter the
“SELECT*FZSD_C01*” in the SQL Text field.
The following statement execution result is returned:
Double click on the statement, or click on the Explain button, you can get the access plan. Compare this access plan with the access plan you saw in exercise 5.11. They should be the same, except that the total estimated costs are different because of disk I/O cost saving on the compressed fact table.
Write down the Avg. Execution time, Buffer Quality, Rows Read, SQL Sorts and the total Estimated Costs to the Work Sheet on the final page of this hand book.
*Note* For more accurate comparison, especially to demonstrate the disk I/O cost saving by using MDC feature or compression feature, you need to run the query in a
“cold” buffer pool, which means the table data should not have been cached in the buffer pool. This will require comparing the result on the first query run on a newly
started system. Therefore you might want to stop and restart your SAP system before running the query.
As you can tell, the query time should be faster after the fact table has been com-pressed because of the savings on the disk I/O.
6.10 Change set-tings in ta-ble RSAD-MIN.
Run program “SAP_RSADMIN_MAINTAIN” in transaction SA38.
Enter DB6_ROW_COMPRESSION in the OBJECT field, and YES in the VALUE field, choose INSERT, then click on Execute button.
6.11 Check settings in table RSAD-MIN.
Run transaction SE16 - Data Browser Browse table: RSADMIN
Restrict values in field “OBJECT” to “DB6*”. You will see now DB6_ROW_COMPRESSION is set to YES.
Unit 7 – BI Operations
7.1 Com-press an InfoCube
Before starting the BI compression, let’s check the total number of records in F fact table and E fact table of InfoCube ZSD_C01. How to do that? Hint – use DBACockpit Single Table Analysis function.
Results: F fact should contain 160600 records, and E fact table contains 0 records.
Now move to DW Workbench screen.
Let’s check how many records was loaded in the request 14.
Run transaction RSA1 Æ Modeling Æ InfoProvider Æ Select “benchmark” Æ Select
“Benchmarkcube” and right-click Æ Select “Manage” Æ Select “Requests” tab.
Click on the Monitor icon on the line of request 14. You should see the detailed informa-tion of request 14.
Click on Details tab, and expand Extraction and Transfer menu:
You can see that there are 13640 records being extracted from the source file. Data was loaded in the package size of 1000. There were 14 data packages were created and loaded in BW.
Now go back to InfoProvider Administration screen, click on tab Collapse.
Enter the Job Name BI_COMPZSD_C01_REQ14, and Rquest ID “14”.
Before releasing the compression job, let’s turn on ST05 SQL trace.
Now click on Release button and it will schedule a background job. Click on the button Log to monitor the job status.
When the job is finished, go back to Requests tab, and click on Refresh button, you should see a check mark in the third column (Compression Status in InfoCube) on the line of Re-quest 14.
Go to the DBACockpit Single Table Analysis, Enter table name /BIC/FZSD_C01, do a RUNSTATS in dialog. When the runstats finishes, you should see the table cardinality is changed from 160.600 to 146.960, which means 13640 records has been deleted in F fact table.
Now check table /BIC/EZSD_C01. You should see its cardinality is 13640, which means 13640 records has been inserted into E fact table.
Go to the ST05 screen and deactivate the trace. Display the trace. You should be able to find the following statements in the Trace List screen:
The above INSERT and DELETE statements are key statements to this InfoCube compression process.
Move the cursor to the INSERT statement, and click on Explain button. You will see the full statement text and the access plan:
Try to understand this SQL statement. This is a INSERT…SELECT statement. It basically select records from F fact table based on the request ID and insert these records into E fact table.
Question: what index is used to select records from F fact table?
Answer: a MDC block index on Package dimension. Shown below:
Go back to Trace List screen, move the cursor to the DELETE statement, and click on Explain button. You will see the full statement text and the access plan:
Do you understand what this DELETE statement does?
7.2 Run the BI query
Now that we have some data being compressed into E fact table, we have InfoCube data stored in both F and E fact tables. How will this affect the query?
Run transaction RSRT, enter Query Name “ZSD_C01/BENCHMARK_QUERY_2”, click on “Execute + Debug”,
check flags ““Display SQL/BIA Query” (under “Data Manager” section), hit Continue button.
Firstly you will see the first SELECT statement that selects from F fact table;
click on the Back button, you will see the second SELECT statement that selects from E fact table; click on the Back button again, you will get the query result.
Therefore the query result is a merged result from two separate queries, one from F fact table, and the other from E fact table.
7.3 Enable MDC for aggregates
Run program “SAP_RSADMIN_MAINTAIN” in transaction SA38.
Enter DB6_MDC_FOR_AGGREGATES in the OBJECT field, and YES in the VALUE field, choose INSERT, then click on Execute button.
After this, you can run SE16 to display this parameter in table RSADMIN.
7.4 Create an aggre-gate
On the InfoProvider Administration screen of ZSD_C01, click on Rollup tab.
Click on Aggregates button to maintain aggregates of this InfoCube.
On the following screen, click on Create by Yourself button.
On the Maintenance for Aggregate screen, Click on the Create New Aggregate button.
Enter the aggregate description as below:
and hit Enter.
Drag Sales Area dimension and Time dimension from the left window onto the newly
cre-ated aggregate ZSD_C01_SALES_TIME on the right window, as below:
Click on the Save button at the top of the window to save the aggregate definition. Then click on the Activate and Fill button.
Click on Start button, and choose Immediately on the pop-up message box:
Click on the Jobs button and you will be directed to the Job Overview screen. On the Job
Overview screen, when the job BI_SAGR* job is completed, click on the Job Log button, you will see the following job logs. From this job log, you can tell how the aggregate is filled:
7.5 Check aggregate fact tables
Use DBACockpit -> Space -> Single Table Analysis to check the table and index structure and statistics of aggregate F table /BIC/F100004 and /BIC/F100004.
You should see that the aggregate fact table inherit the MDC definition of the InfoCube fact table. Therefore the aggregate fact table also has package and calmonth defined as MDC dimension.
7.6 Run BI query again
Since now we have the aggregate defined on both SALES AREA and TIME dimension, the original query we have run should select data from the aggregate rather than from the infocube. We can verify this by running the query again.
Run transaction RSRT, enter Query Name “ZSD_C01/BENCHMARK_QUERY_2”, click on “Execute + Debug”,
check flags ““Display SQL/BIA Query” (under “Data Manager” section), hit Continue button.
Now you will see the SELECT statement that selects from the aggregate E fact table /BIC/E100004.
click on the Back button then this query will be executed and the query result will be dis-played.
Work Sheet
MDC table Compressed MDC table
MDC table Compressed MDC table
MDC table Compressed MDC table
MDC table Compressed MDC table