Sunday, December 15, 2013

InfoCube Performance Tuning Options


Compression

InfoCube aggregates data. Let’s say the data records loaded on day 1 are:


On day 2 the following records are loaded to the cube.


Now the cube will not store the records in this manner.

The first record of the day 1 and day 2 loads only contains first characteristic Customer (A001), UOM (KG) and Currency (PKR) as similar but you have two different characteristics which are Product ID (P02) and Product Group (PG02). So if at least one different characteristic is available when two records are compared data will be not aggregated by InfoCube.

But if you observed, the 2nd record of both loads have same characteristics, so in this case cube will aggregate the records.
 

After the 2nd load the above data can be found in cube. Here the 2nd and last record has same characteristics only the Request ID is different, if we remove the Request ID then we will have only 5 records left as the same records will be aggregated.
Removing the Request ID and moving the records from F fact table to E fact table is called compression. Request ID belongs to Data Package dimension table.

F table (/BIC/F(cube name))---------> Compression --------> E table (/BIC/E(cube name))

1)      Right click your InfoCube select Manage and from the Contents tab click Fact table button and click Execute
2)      The Request tab shows the list of requests for which data are not yet compressed note the highest request id, go to Collapse tab, provide the highest request id in collapse section, all the request below this request id will also be compressed. Click Release button
3)      You can monitor this job in SM37 or on the Request tab
4)      Check again the contents of fact table from Content tab, now you will find 0 records in F table
5)      To check contents of E table go to SE11 and enter  /BIC/E ZIC_CTRN and click Display button, click Contents icon on application toolbar, click Execute, you will find that data is aggregated
Thus compression saves space and improves performance of query execution
6)      Go to InfoCube-->Manage, select your cube in top section of screen and click Contents button on application toolbar, click Execute, you will see that Request ID field is empty 

Aggregates

Aggregates are baby cubes where we avoid some characteristics but not key figures. Let’s we have the following data in our InfoCube.


If we want to see what the sale of P01 is in Month of Jan, the above format is fine for this type of query. But developer observed that user is not executing reports on monthly rather yearly bases, so the above format of data is not suitable for yearly report, because it involves the a lot of calculation by summing key figures for months.

The yearly report should be like as shown below, i.e. at more aggregated level then the above
 

This format is called the aggregate of the above InfoCube as we avoided the Cal Month characteristic. Next let’s say we want to see the sale of single product, in this case we can avoid the Cal Year characteristic also


Aggregates increase reports speed but it put extra burden of maintenance and periodic data load. The report will first search for aggregate if aggregate is not found then it will use the InfoCube to pull data from
The concept of updating the aggregates is called Rollup. 

The loading of data from SourceSystem to DataSource is termed as InfoPackage

SourceSystem ---> InfoPackage --->DataSource

The loading of data from DataSource to Data Target (InfoCube) is termed as DTP

DataSource--->DTP--->Data Target

While the loading of data from InfoCube to aggregate is termed as Rollup

Cube ---> Rollup --> Aggregates

1)      Right click on your InfoCube, select Manage, go to Rollup tab, click Aggregates, click Create by Yourself in pop up
2)      To create an aggregate which give high level report of customer data, drag and drop Customer ID to the right section of the screen provide Short (Aggr: for Customer) and Long description (Aggregate for Customer).
3)      Select Aggregate and click Activate from the application toolbar, click the Start in pop up to fill the data in aggregate, click Immediate
4)      In the right section of screen, the Status (S) and Fill (F) will show status as green.
5)      Let’s create an aggregate with two attributes, click create icon on application toolbar, provide Short (Aggr: Product) and Long description (Aggregate for Product).
6)      Add Product ID, and Product Group to this aggregate, and activate it, click Start, then Immediate
7)      To see the contents of aggregate, select it in the right section and click Contents icon on application toolbar, then click Execute
8)      If the aggregate is not required for a period of time you can de-activate it by clicking on Deactivate icon on application toolbar

So if the report is based on customer or product data will be pull from these aggregates first.  If user is complaining about report execution is taking long, then you can create an aggregate to boost the report performance


Line Item Dimension

When we create a dimension, two performance tuning options are offered: Line Item Dimension and High Cardinality.

When a Line Item Dimension is set, SAP BW doesn’t create a dimension table for it, instead directly connects the fact table with the SID table of characteristic, this feature is used when characteristic have large number of distinct values, and thus to avoid joins b/w SID, dimension tables


When the above data is loaded in cube we have:


§  No. of joins we have here are:
·         Master Data table ----is connected to---- SID table ----is connected to---- Dimension table ----is connected to----Fact table

§  We can reduce the joins by making a direct join b/w SID table and Fact table as
·         Master Data table ----is connected to---- SID table ----is connected to----Fact table

§  By reducing joins we increase the performance of query, So I can replace the SID-C column of Customer Dim with Dim-C in Fact table, this type of join is called Line Item Dimension

 

§  But note we cannot replace DIM-P in fact table as we there are two SID tables (SID-P and SID-PG) associated with Dim-P, Therefore; Line Item dimension feature can be used with dimension which has one characteristic
§  It is good practice to use Line item when we have only one characteristic dimension

High Cardinality

The High Cardinality flag changes the index type to B-Tree, in cases where distinct values are high and
dimension table is at least 20% of the size of fact table. By default SAP BW creates bitmap index on each 
column of dimension. It is mostly used with Oracle DB, as other DBs don’t support bitmap index
 







No comments:

Post a Comment