There are several forum discussions and blogs that talks about partitioning and its impact on query performance and yet, there is a lot to explore and the fun never stops. This blog explains the intricacies of physical partition and aims at proving how the query reads from the partition.
- Range Partition - Where to find and prove? That's what this blog talks about.
Case description:Objective is to Improve the SAP BW query performance by using Range partition. You have followed all the best practices in data modeling but the query response is slow.
0.1.
You have tried
Aggregates - It couldn't help much as the summarization ratio < 5 for your queries. 0.1.
You have tried
Pre-calculation - It is practically impossible as the authorization is based on Customer account hierarchy, say the requirement is with banking where each user should be authorized to just his account number and you have thousands of such users. 0.1.
You are now ‘banking' on +Partitioning
</li></ul><p>_Let's Do It :_+ BWCUBE is the cube under scrutiny. You have already collected the RSRT query statistics for your slow queries before doing the partitioning for benchmarking. Say, you have now completed the partitioning based on 0CALMONTH; the data is loaded and ready for reporting. Attached is the screenshot of the partitioned cube.
!
https://weblogs.sdn.sap.com/weblogs/images/251913262/Cube.JPG|height=427|alt=image|width=666|src=htt...!
You check the
TCode RSRT query statistics again hoping that partitioning has done the magic for you - at least I hoped so when I completed it. Normally you would see significant improvement after partitioning. But in my case - Nope! No improvement in the *QDBTIME. *So, now we end up with the question +- how do I verify and prove that my query is actually using the physical partition?
Okay, Lets trace. </p><p>SID_0CALMONTH is the ‘partitioning field' that would have been newly included in your E Fact table. You first check TCode SE14 --> E Fact table of cube BWCUBE --> Storage Parameters and that gives you the list of partitions (names) that have happened on the cube. '/BIC/<Cube name> <SID of the partitioning characteristic>' is the SAP naming convention for partitions. Okay- so, partitions do exist on the cube!.But this doesn't tell you if the cube has been partitioned the way you want - does each partition hold the data for the appropriate calendar month? </p><p>You have to now explore TCode DB02 --> Detailed Analysis --> Provide the E Fact table name. Attached is the screenshot of DB02 Analysis. </p><p>!https://weblogs.sdn.sap.com/weblogs/images/251913262/DB02.JPG|height=420|alt=image|width=585|src=htt...!</p><p>The Column ‘Object' lists down the partitions for the E Fact table of cube /BIC/BWCUBE. The Column ‘Kbytes' gives the size of the partition. If you haven't done compression of the cube yet, you will sure see only 0 KB on all partitions. Compression is the pre-requisite. Note: The cube only has data for year 2008 and therefore there is no data for 2009 partitions.</p><p>Fair enough. Still, you will appreciate the partition only after you confirm that the count of records in each partition matches with the count of records in the cube (E Fact table) for each of calendar months, right? Choose one of the partitions, say, /BIC/EBWCUBE200805 and click on ‘Detailed Analysis' tab above.Attached is the screenshot of the Detailed Analysis. </p><p>!https://weblogs.sdn.sap.com/weblogs/images/251913262/DB02detailed.JPG|height=419|alt=image|width=563...!</p><p>The *Rows+* Total 1,310,200 is the count of data records of this partition. You can now check the count of data records in your E Fact table for the calendar month
200804. Bingo! It matches. (200804 is not a typo error. That's the way the partitions are named. Explore ‘high_value' of the partition)
*Are we there yet? *Cool! The Cube is partitioned as expected. Go to
RSRT ‘Execute and Debug', choose ‘
Display SQL Query','
Display Run Schedule', ‘
Display Statistics Data', ‘+Do not use Cache'. +Run your query and checkout the Execution plan of the SQL statement. There will be a section for SQL Statement and below that is the Execution plan. Attached is the screenshot of the execution plan.
!
https://weblogs.sdn.sap.com/weblogs/images/251913262/Executionplan.JPG|height=481|alt=image|width=57...!</body>
