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
0 comments:
Post a Comment