bw query tuning
DESCRIPTION
hhTRANSCRIPT
1
Empowering Workshop
TEWA50BW Query Tuning
Version 2005 / 1.09
2
SAP AG 2005, TEWA50 / 2
General information
Target group:
� SAP Business Information Warehouse project team members
� SAP BW consultants
� SAP BW administrators
Duration:
� Two days
Prerequisites:
� Experience with SAP Business Information Warehouse
� Knowledge of the SAP BW data model
� Access to your SAP BW system
3
SAP AG 2005, TEWA50 / 3
Course Goals
This course will prepare you to:
Understand the concept of aggregates
Find queries that can be tuned with aggregates
Use the OLAP Cache efficiently
Fill and monitor the OLAP Cache
Create suitable aggregates
Reduce the time needed for aggregate maintenance
4
SAP AG 2005, TEWA50 / 4
Course Objectives
At the conclusion of this workshop, youwill be able to:
Explain the concept of aggregates
Find queries that can be tuned with aggregates
Use the OLAP Cache efficiently
Fill and monitor the OLAP Cache
Create suitable aggregates
Maintain aggregates efficiently
5
SAP AG 2005, TEWA50 / 5
1 Basics About Aggregates
2 How to Detect Tuning Potential
3 How to Tune Query Performance
5 Basics ofAggregate Maintenance
1.1 Aggregates
1.2 Aggregate Design Basics
1.3 How to Create Aggregates
Overview 1: Basics About Aggregates
4 Basics about OLAP Cache
6 How to TuneAggregate Maintenance
6
SAP AG 2005, TEWA50 / 6
1 Basics About Aggregates
2 How to Detect Tuning Potential
3 How to Tune Query Performance
5 Basics ofAggregate Maintenance
Overview 2: How to detect Tuning Potential
4 Basics about OLAP Cache
6 How to TuneAggregate Maintenance
2.1 BW Statistics
2.2 Check for Missing Aggregates
2.3 Influences on AggregateUsage
7
SAP AG 2005, TEWA50 / 7
1 Basics About Aggregates
2 How to Detect Tuning Potential
3 How to Tune Query Performance
5 Basics ofAggregate Maintenance
Overview 3: How to Tune Query Performance
4 Basics about OLAP Cache
6 How to TuneAggregate Maintenance
3.1 How to Tune Query Performance on Query level
3.2 How to Tune Query Performance on Cube level
8
SAP AG 2005, TEWA50 / 8
1 Basics About Aggregates
2 How to Detect Tuning Potential
3 How to Tune Query Performance
5 Basics ofAggregate Maintenance
Overview 4: OLAP Cache
4 Basics about OLAP Cache
6 How to TuneAggregate Maintenance
9
SAP AG 2005, TEWA50 / 9
1 Basics About Aggregates
2 How to Detect Tuning Potential
3 How to Tune Query Performance
5 Basics ofAggregate Maintenance
Overview 5: Basics of Aggregate Maintenance
4 Basics about OLAP Cache
6 How to TuneAggregate Maintenance
5.2 Rollup of Aggregates
5.3 Changerun
5.1 General Strategy
10
SAP AG 2005, TEWA50 / 10
1 Basics About Aggregates
2 How to Detect Tuning Potential
3 How to Tune Query Performance
5 Basics ofAggregate Maintenance
Overview 6: How to Tune Aggregate Maintenance
4 Basics about OLAP Cache
6 How to TuneAggregate Maintenance
6.1 Aggregate Maintenance time
6.3 Useless Aggregates
6.4 Basis Aggregates
6.5 Aggregate Check tools
6.2 Aggregate Hierarchy
11
SAP AG 2005, TEWA50 / 11
Course Contents
Unit 1 Basics about Aggregates
Unit 2 How to detect Tuning Potential
Unit 3 How to tune Query Performance
Unit 4 The OLAP Cache
Unit 5 Basics of Aggregate Maintenance
Unit 6 How to tune Aggregate Maintenance
Preface
Conclusion
Appendix
12
SAP AG 2005, TEWA50 / 12
1 Basics About Aggregates
2 How to Detect Tuning Potential
3 How to Tune Query Performance
5 Basics ofAggregate Maintenance
1.1 Aggregates
1.2 Aggregate Design Basics
1.3 How to Create Aggregates
Unit 1: Basics About Aggregates
4 Basics about OLAP Cache
6 How to TuneAggregate Maintenance
13
SAP AG 2005, TEWA50 / 13
1 Basics About Aggregates
2 How to Detect Tuning Potential
3 How to Tune Query Performance
5 Basics ofAggregate Maintenance
1.1 Aggregates
1.2 Aggregate Design Basics
1.3 How to Create Aggregates
Unit 1.1: Aggregates
4 Basics about OLAP Cache
6 How to TuneAggregate Maintenance
14
SAP AG 2005, TEWA50 / 14
Contents
� Aggregate Design Basics
� How to Create Aggregates
� Usage of Aggregates
Objectives
At the end of this unit you will be able to:
� Explain the concept of aggregates
� Explain why aggregates are necessary
� Use aggregates wisely
Aggregates
15
SAP AG 2005, TEWA50 / 15
An InfoCube Star Schema
Fact Table
Dimensions
Characteristics
Month
Year
Day
City Region Country
Product Product group
Sales person
Division
Distribution channel
Sales organization
Tim
e D
ime
ns
ion
Product Dimension
Region DimensionS
ale
s O
rgD
ime
ns
ion
� This slide illustrates the star schema of an InfoCube. An InfoCube is a multi-dimensional reporting scenario built out of characteristics and key figures.
� Physically, an InfoCube is a set of database tables that are related to one another in a star schema. This is a very common technique in data warehousing and many database vendors have tuned their various DBMS products to recognize star schema scenarios by providing appropriate techniques, such as star joins and bitmap indexing schemes for the
processing of queries on star schemas.
� The boxes in this slide represent various tables. In the center of a star schema lies the fact table, which contains a huge amount of data. The fact table holds all the information
on the key figures. All the other tables are smaller. The dimension tables hold information about group related characteristics. The master data tables also hold information about attributes like the color or the price of a product.
� In this example, there are dimensions on time, region, product and sales organization. Thus the fact table holds information on sales figures, profit, costs etc. per day, product,
city and sales organization.
� Each query on such a star schema uses a certain subset of these relationships.
16
SAP AG 2005, TEWA50 / 16
A Typical Query
Month
Day
City Region
Product Product group
Sales person
Division
Distribution channel
Sales organization
Tim
e D
ime
ns
ion
Product Dimension
Region DimensionS
ale
s O
rgD
ime
ns
ion
CountryGermany
Year1999
Relevantfacts
Relevant facts aftersummarizationon the database
� The slide shows a typical query. It shows sales data for 1999 for the country Germany. The sales data is summarized. The query is not concerned with details of products and products group. It is also not concerned with details of days, months, cities, regions or thesales organization. This query uses the illustrated subset of the original star schema.
� Typically, a DBMS query optimizer creates an execution plan that uses filters on year and country to derive the area of the fact table that includes the relevant data for answeringthe query. Despite the fairly restrictive filters, this area can still be huge, as details of a
day, month, city, region, product and sales organization level still have to be summarized.
� Processing such a query can cause a performance problem. To solve this problem, you
can define a suitable aggregate.
17
SAP AG 2005, TEWA50 / 17
A Suitable Aggregate
Fact Table
Dimensions
Characteristics
Year
Country
Tim
e D
ime
ns
ion
Region Dimension
Relevantfacts
Relevant facts aftersummarizationon the database
� Like a query, an aggregate constitutes a subset of the star schema of the relatedInfoCube. However, it uses its own private fact table and possibly its own dimensiontables. In this example, aggregates can discard certain levels of details, such as day and city, or the sales organization, and keep data at a summarized level. So an aggregate'sfact table can be much smaller. In the above example, processing the query on the
aggregate rather than on the original InfoCube brings an immediate performance benefit.
� Since an aggregate does not contain all the detailed information from the original InfoCube, it cannot replace that InfoCube. However, a small number of well-defined
aggregates can substantially improve the performance of the standard queries that usersexecute.
� The usage of aggregates is totally transparent to the users. Only the OLAP processor
decides whether to access the cube or to use an aggregate.
18
SAP AG 2005, TEWA50 / 18
Aggregates: Definition
Aggregates are subsetsof fact table data where
summary datais stored as part of new, transparent InfoCubes
� An Aggregate is a rollup of fact data where a total value is sufficient and no detailed information is needed.
� So aggregates are like InfoCubes except that they summarize or aggregate data from an InfoCube.
� When you use an Aggregate, the summarization it represents does not need to be doneduring runtime.
19
SAP AG 2005, TEWA50 / 19
Aggregates: Concept
Data transfer from InfoCube to query (revenue grouped by month)
InfoCube
Number of recordsread on thedatabase
Records transferred to BW instanceafter being
summarized on the database
Aggregate
Month Material Revenue
July Hammer 10
July Nail 20
August Hammer 10
August Nail 20
Month Revenue
July 30
August 30
Month Material Revenue
July Hammer 10
July Nail 20
August Hammer 10
August Nail 20
Month Revenue
July 30
August 30
Month Revenue
July 30
August 30
Flow with aggregate
Flow without aggregate
� The aggregate is stored as a new, transparent cube.
� The aggregate is used to reduce the volume of data read while querying.
� The end result, the rows transmitted from the database to the BW instance, is the samein both cases.
� The tables are simplified examples only. Normally, both the InfoCube and the aggregatefact table contain the facts (in this example, the revenue) and pointers to the master data(in this example, the month). To keep this example simple, the pointers are replaced by
the master data.
� The aggregate contains the data already summarized by month. The aggregate has the
same structure as an InfoCube, with transparent database tables and a fact table at thecenter surrounded by dimension tables.
� InfoCube access: to find the required information, all four rows have to be read.
� Aggregate access: Since the aggregate contains data precalculated for the month, only
two rows have to be read.
� InfoCube access: The four rows have to be summarized to two rows, since the query isonly interested in revenue per month and not in material.
20
SAP AG 2005, TEWA50 / 20
Aggregates: Goal
To reduce the cost of query retrieval by
reducing the amount of data that must be read
on the database
� The high cost of query retrieval comes from increased volume transferred from (selectedon) the database, long running queries, and more hardware resources consumed.
� So Aggregates contain redundant information, but accelerate the access to thatinformation.
� With respect to performance, the aggregates’ role is similar to that of database indexesfor database tables.
21
SAP AG 2005, TEWA50 / 21
Databasedisks
DBMSprocesses
Data buffer
Expensive SQL statements in theShared SQL Area
1 user is causinga long running
query
Missing or Inappropriate Aggregates
Several userswaiting for
CPU Resources
Several userswaiting for
memory
Severalusers waiting for
physical I/Oload
� The main limited resources on the database server are:
�CPU capacity
�Memory capacity
�Number of physical I/O operations that can be performed efficiently
� Improper or unnecessary use of the database buffers results in displacements of other
data blocks, and affects overall system performance.
� A query from a poorly tuned aggregate, even one with adequate performance, may
adversely affect the performance of other queries in the system.
� If aggregates are missing, the user who started the query must wait. Although the user
itself might be satisfied with the runtime of his ‚long running query‘ as he does not need the data immediately, he is allocating resources which are shared between all users. So this long running query has a large impact on the overall performance of the system.
22
SAP AG 2005, TEWA50 / 22
1 Basics About Aggregates
2 How to Detect Tuning Potential
3 How to Tune Query Performance
5 Basics ofAggregate Maintenance
1.1 Aggregates
1.2 Aggregate Design Basics
1.3 How to Create Aggregates
Unit 1.2: Aggregate Design Basics
4 Basics about OLAP Cache
6 How to TuneAggregate Maintenance
23
SAP AG 2005, TEWA50 / 23
Aggregates can be created Aggregates can be created
On time-dependent navigational attributes
On hierarchy levels where the structure is time-dependent
For BasicCubes
On dimension characteristics
On navigational attributes
On hierarchy levels
New !
New !
New !
New !
New !
New !
New !
New !
BW 3.0x Possible Info Objects
� Aggregates can not be created for
- MultiProviders
- RemoteCubes
- ODS-Objects
NEW
NEW
NEW
24
SAP AG 2005, TEWA50 / 24
Aggregates: Different Aggregation Levels
Defining AggregatesDefining Aggregates
**
FF
HH
Group according to characteristic or attribute value
Filter according to fixed value
Group according to nodes of a hierarchy level
� Different type of aggregation can be used. An example of each type follows:
�* Group according to characteristic or attribute value
�F Filter according to fixed value
�H Group according to nodes of a hierarchy level
25
SAP AG 2005, TEWA50 / 25
Grouped by Characteristic
CountryCountry CustomerCustomer SalesSales
USAGermanyUSAAustriaAustriaGermanyUSA
Winsoft Inc.InternetworksFunny Duds Inc.InternetworksThor IndustriesFunny Duds Inc.Winsoft Inc.
1015510102025
Fact Table: Sales DataFact Table: Fact Table: Sales DataSales Data Aggregate Tables: Sales DataAggregate Tables: Aggregate Tables: Sales DataSales Data
Country *Country Country **
CountryCountry SalesSales
403520
USAGermanyAustria
� From the aggregate (country *), you can read out data for queries about:
�Sales in the different countries
�Overall sales
� Because there is no information about customer available in the aggregate, the aggregate
cannot be used for queries about:
�Sales for a special customer
� If you need information about a special customer, you have to access the InfoCube.
26
SAP AG 2005, TEWA50 / 26
Grouped by Characteristic: Technical View
Fact Table: Sales DataFact Table: Sales DataAggregate Tables: Sales DataAggregate Tables: Sales Data
Country *Country *
/BI0/F<CUBENAME>
/BI0/E<CUBENAME>
/BI0/F<CUBENAME>
/BI0/E<CUBENAME> /BIC/F<1XXXXX>
/BIC/E<1XXXXX>
/BIC/F<1XXXXX>
/BIC/E<1XXXXX>
Customer
/BI0/D<CUBENAME>1
No linkany more
/BI0/D<CUBENAME>2
Shared
dimension
table
Fact Table
Dimensions
Characteristics
Country
InfoCube Aggregate
� Technically speaking, an aggregate is a separate InfoCube with its own fact table and dimension tables.
When an aggregate is created, it is given a 6-digit number <1NNNNN> that starts with a "1". The table
name for an aggregate is derived in the same way from this number as InfoCube table names from
InfoCube names.
� For example, if an aggregate has the technical name 100001, its fact tables are called /BIC/E100001 and
/BIC/F100001. Its dimensions have the table names /BIC/D100001P, /BIC/D100001T, and so on.
� Dimension tables can be shared between an InfoCube and an aggregate. In this example, dimension 2 (the
country dimension) is shared between the InfoCube and the aggregate. It is not necessary to create a new
dimension table. A link to this dimension table is created in the aggregate fact table.
� Dimensions are only shared if all characteristics of the InfoCube-dimension are also used in the aggregate.
Otherwise a new dimension table will be created for the aggregate.
� There is no longer a link from the new aggregate to the dimension customer (dimension 1), since the
aggregate does not contain any information about the customer.
� There are two fact tables: the F-fact table and the E-fact table. If you upload data into an InfoCube, it is
always written into the F-fact table. If you compress the data, the data is shifted from the F-fact table to the
E-fact table. The F-fact tables for aggregates are always empty, since aggregates are compressed
automatically.
� After a changerun the F-fact table can have entries as well as when you use the functionality ‚do not
compress requests‘ for Aggregates.
27
SAP AG 2005, TEWA50 / 27
Using Filters
CountryCountry CustomerCustomer SalesSales
USAGermanyUSAAustriaAustriaGermanyUSA
Winsoft Inc.InternetworksFunny Duds Inc.InternetworksThor IndustriesFunny Duds Inc.Winsoft Inc.
1015510102025
Fact Table: Sales DataFact Table: Fact Table: Sales DataSales Data Aggregate Tables: Sales DataAggregate Tables: Aggregate Tables: Sales DataSales Data
Country F, GermanyCustomer *
Country Country F, GermanyF, Germany
Customer Customer **
CountryCountry SalesSales
1520
GermanyGermany
CustomerCustomer
InternetworksFunny Duds Inc.
� From the aggregate (country F, Germany; customer *), you can read out data for queries about:
�Sales in Germany for different customers
�Overall sales in Germany
� Because there is no information about other countries than Germany available in the aggregate, the aggregate cannot be used for queries about:
�Sales for all customers in the USA
�Sales for all customers in Austria
�Sales for a special customer
�Overall sales
� Aggregates with filters are only useful for queries with the same filter.
� If you need summarized information about a special customer, you have to access the
InfoCube, because the customer can have sales in different countries.
28
SAP AG 2005, TEWA50 / 28
Shared
dimension
table
Using Filters: Technical View
Fact Table: Sales DataFact Table: Sales Data Aggregate Tables: Sales DataAggregate Tables: Sales Data
Country F, GermanyCustomer *
Country F, GermanyCustomer *
/BI0/F<CUBENAME>
/BI0/E<CUBENAME>
/BI0/F<CUBENAME>
/BI0/E<CUBENAME> /BIC/F<1XXXXX>
/BIC/E<1XXXXX>
/BIC/F<1XXXXX>
/BIC/E<1XXXXX>
Customer
/BI0/D<CUBENAME>1
/BI0/D<CUBENAME>2
Shared
dimension
table
Country
InfoCube Aggregate
� Dimension tables that are identical are shared between an InfoCube and an aggregate.
� In this example, both dimension tables (country and customer) are shared between theInfoCube and the aggregate. It is not necessary to create a new dimension table forcountry, although only one value (Germany) is used.
29
SAP AG 2005, TEWA50 / 29
/BI0/F<CUBENAME>
/BI0/E<CUBENAME>
/BI0/F<CUBENAME>
/BI0/E<CUBENAME>
/BI0/D<CUBENAME>1
Customer
/BI0/XCUSTOMER
Navigational attributeIndustry for Customer
Explanation of Navigational Attributes
DIM for Customer Sales
Fact Table: Sales DataFact Table: Sales Data
Dimension Table: CustomerDimension Table: Customer
DIM for customer SID
1234
1231234
1015510102025
SID Table: CustomerSID Table: Customer
Customer Name
1234
Winsoft Inc.Funny Duds Inc.InternetworksThor Industries
SID
1234
DIM for Country
1112222
Industry
TechnologyConsumer ProductsTechnologyChemical
Fact Table
Dimensions
Characteristics
SID Table
� Navigational attributes are stored in the SID table (/BI0/XCUSTOMER). They are stored in the master data tables. The master data tables are independent of the InfoCube. This is shown by the name of the tables, which contain the name of the InfoObject, not the name of the InfoCube.
� If a query concerns sales by industry, the query must first access table /BI0/XCUSTOMER to find out which customer belongs to which industry. So if a query contains a navigational attribute, it must access the master data table.
30
SAP AG 2005, TEWA50 / 30
Using Navigational Attributes
Aggregate Tables: Sales DataAggregate Tables: Aggregate Tables: Sales DataSales Data
Customer__Industry *Customer__Industry *Customer__Industry *
Customer__IndustryCustomer__Industry SalesSales
602510
TechnologyConsumer ProductsChemical
TechnologyConsumer ProductsTechnologyChemical
IndustryIndustryCustomerCustomer
Winsoft Inc.Funny Duds Inc.InternetworksThor Industries
Navigational Attribute forCharacteristic Customer
NavigationalNavigational Attribute Attribute forfor
CharacteristicCharacteristic CustomerCustomer
CountryCountry CustomerCustomer SalesSales
USAGermanyUSAAustriaAustriaGermanyUSA
Winsoft Inc.InternetworksFunny Duds Inc.InternetworksThor IndustriesFunny Duds Inc.Winsoft Inc.
1015510102025
Fact Table: Sales DataFact Table: Fact Table: Sales DataSales Data
� From the aggregate (Customer__Industry *), you can read out data for queries about:
�Sales grouped by industries
�Overall sales
� Because there is no information about customer available in this aggregate, the
aggregate cannot be used for queries about:
�Sales grouped by customer
�Sales for a special customer
� Because there is no information about country available in this aggregate, the aggregate
cannot be used for queries about:
�Sales grouped by country
�Sales for a special country
31
SAP AG 2005, TEWA50 / 31
Using Navigational Attributes: Technical View
Aggregate Tables: Sales DataAggregate Tables: Sales Data
Customer__Industry *Customer__Industry *
/BI0/F<1XXXXX>
/BI0/E<1XXXXX>
/BI0/F<1XXXXX>
/BI0/E<1XXXXX>
Customer__Industry
/BI0/D<1XXXXX>1
/BI0/XCUSTOMER
Navigational attributeIndustry for Customer
No linkany more
/BI0/F<CUBENAME>
/BI0/E<CUBENAME>
/BI0/F<CUBENAME>
/BI0/E<CUBENAME>
/BI0/D<CUBENAME>1
Customer
� The Navigational Attribute is turning to a Characteristic, because it is now part of a dimension. The master data table is no longer accessed for information about the navigational attribute Customer__Industry. The information is stored in the dimension table itself. So in this aggregate, the navigational attribute is stored in the same way as a
characteristic. During query execution, this reduces the number of database joins by one.
� A new dimension has been created, which contains the navigational attribute Customer__Industry. Navigational attributes always contain a double-length underscore
in the technical name. Since the dimension country (dimension 1 of the InfoCube) does not contain any information about industry, a new dimension is created. The name of the database table of this dimension is /BI0/D<1XXXXX>1.
� There is no longer a link to the dimension tables of the InfoCube, since the aggregate
does not contain information about the country and the customer.
32
SAP AG 2005, TEWA50 / 32
Using Hierarchies
CountryCountry CustomerCustomer SalesSales
USAGermanyUSAAustriaAustriaGermanyUSA
Winsoft Inc.InternetworksFunny Duds Inc.InternetworksThor IndustriesFunny Duds Inc.Winsoft Inc.
1015510102025
Fact Table: Sales DataFact Table: Fact Table: Sales DataSales Data Aggregate Tables: Sales DataAggregate Tables: Aggregate Tables: Sales DataSales Data
Country H, Level 2Country Country H, Level 2H, Level 2
CountryCountry SalesSales
4055
AmericaEurope
EuropeEurope AmericaAmerica
GermanyGermany AustriaAustria USAUSA
Hierarchy for CountryHierarchy for CountryHierarchy for Country
Time-independent hierarchies are storedoutside thedimension, in a table called /BI0/ICOUNTRY
AllAllLevel 1
Level 2
Level 3
� Aggregate (country H, level 2) may be used for queries about:
�Sales for Europe
�Sales for America
�Sales for All (Overall sales)
�Sales for all countries ordered by the country hierarchy up to level 1 or 2
� If the hierarchy has double leafs such that some characteristic value appear more than once in the hierarchy or if the hierarchy has the attribute "no restnode" (so that the
remaining node is explicitly hidden), the overall query cannot use the hierarchy aggregate.
� Aggregates with a hierarchy are useful for queries that use nodes of the hierarchy as a
filter or that use the hierarchy as a presentation hierarchy. For exceptions, see SAP Note 198568.
� The level of the desired nodes must be less than or equal to the level in the aggregate.
33
SAP AG 2005, TEWA50 / 33
Using Hierarchies: Technical View
Aggregate Tables: Sales DataAggregate Tables: Sales Data
Country H, Level 2Country H, Level 2
/BI0/ICOUNTRY
/BIC/F<1XXXXX>
/BIC/E<1XXXXX>
/BIC/F<1XXXXX>
/BIC/E<1XXXXX>
Hierarchyfor country
/BIC/D<1xxxxx>2
Hierarchy for country
No linkany more
Country
/BI0/D<CUBENAME>2
Fact Table: Sales Data
/BI0/F<CUBENAME>
/BI0/E<CUBENAME>
/BI0/D<CUBENAME>1
Customer
No linkany more
� The Hierarchy for Country on level 2 is turning to a Characteristic, because it is now part of a dimension. The master data table is no longer accessed for information about the hierarchy on level 2. The information is stored in the dimension table itself. In this aggregate, this hierarchy information behaves like a normal characteristic. During query
execution, this reduces the number of database joins by one.
� A new dimension has been created, which contain the hierarchy information for country on level 2. Since the dimension country (dimension 1 of the InfoCube) does not contain any information about the hierarchy, a new dimension is created. The name of the
database table of this dimension is /BIC/D<1XXXXX>2.
� There is no longer a link to the dimension tables of the InfoCube, since the aggregate
does not contain information about the country and the customer.
34
SAP AG 2005, TEWA50 / 34
When should you use time dependent objects?
Perspective 1:
Hierarchy in 1999
Perspective 2:
Hierarchy in 2000
Result 1 Result 2
Data for a specificperiod
��������
� Time-dependent navigational attributes (and time-dependent hierarchies) can be used to report from different perspectives on the same data. If you define queries, you can usethe navigational attribute or hierarchy in its newest version or in any older version youlike.
35
SAP AG 2005, TEWA50 / 35
CountryCountry MonthMonth SalesSales
USAGermanyUSAItalyItalyGermanyUSA
01/200001/200002/200002/200003/200003/200003/2000
1015510102025
Fact Table: Sales DataFact Table: Fact Table: Sales DataSales Data Aggregate Tables: Sales DataAggregate Tables: Aggregate Tables: Sales DataSales Data
Country H, Level 3Country Country H, Level 3H, Level 3
CountryCountry SalesSales
??
??
AllAll
EuropeEurope AmericaAmerica
E. NorthE. North E. SouthE. South North AmericaNorth America
ItalyGermany USA
Perspective 2:
Hierarchy in 2000
Time-Dependent Hierarchies
?
AllAll
EuropeEurope AmericaAmerica
GermanyGermany ItalyItaly USAUSA
����
����
Perspective 1:
Hierarchy in 1999
����
����
� Which perspective should be used?
� You can only specify the hierarchy and the hierarchy level when an aggregate is created. You cannot specify a validity time or a version number. For this reason, time-dependenthierarchies cannot be used in aggregates.
36
SAP AG 2005, TEWA50 / 36
Time-Dependent Navigational Attributes
CountryCountry CustomerCustomer SalesSales
USAGermanyUSAItalyItalyGermanyUSA
1015510102025
Fact Table: Sales DataFact Table: Fact Table: Sales DataSales Data
Winsoft Inc.InternetworksFunny Duds Inc.InternetworksThor IndustriesFunny Duds Inc.Winsoft Inc.
TechnologyConsumer ProductsTechnologyChemical
IndustryIndustryCustomerCustomer
Winsoft Inc.Funny Duds Inc.InternetworksThor Industries
Navigational Attribute forCharacteristic Customer
NavigationalNavigational Attribute Attribute forfor
CharacteristicCharacteristic CustomerCustomer
Perspective 1:
Attributes in 1999
Consumer ProductsConsumer ProductsTechnologyChemical
IndustryIndustryCustomerCustomer
Winsoft Inc.Funny Duds Inc.InternetworksThor Industries
Navigational Attribute forCharacteristic Customer
NavigationalNavigational Attribute Attribute forfor
CharacteristicCharacteristic CustomerCustomer
Perspective 2:
Hierarchy in 2000
Aggregate Tables: Sales DataAggregate Tables: Aggregate Tables: Sales DataSales Data
Customer__Industry *Customer__Industry Customer__Industry **
Customer__IndustryCustomer__Industry SalesSales
??10
Consumer Prod.TechnologyChemical
?����
����
����
����
� Which perspective should be used?
� You can only specify the navigational attribute used. You cannot specify a validity time ora version. For this reason, time-dependent navigational attributes cannot be used in aggregates.
37
SAP AG 2005, TEWA50 / 37
year ∈∈∈∈
{2000, 2001}
citysales person
Revenue by sales persons comparing years 2000 and 2001, where sales person is a time dependent navigation Attribute
from city
Typical Query with time dependency (1)
38
SAP AG 2005, TEWA50 / 38
Typical Query with time dependency (2)
Master Data Table: CountryMaster Data Table: Country
Walther31.12.99991.1.2001Wien
Muth31.12.99991.1.2000Paris
Beck31.12.99991.1.2001Berlin
Walther31.12.20001.1.2000Berlin
Beck31.12.20001.1.2000Wien
Sales PersonValid toValid fromCity
City Year Revenue
WienWienParisParisBerlinBerlin
200020012000200120002001
110 €130 €200 €190 €150 €150 €
Fact Table: Sales DataFact Table: Sales Data
Time dependent navigational Attribute: Sales Person
Query Result Set Query Result Set Query Result Set Query Result Set
with actual with actual with actual with actual keydatekeydatekeydatekeydate
Sales P. Year Revenue
BeckBeckMuthMuthWaltherWalther
200020012000200120002001
110 €150 €200 €190 €150 €130 €
Query Result Set with Query Result Set with Query Result Set with Query Result Set with
keydatekeydatekeydatekeydate 08/31/200108/31/200108/31/200108/31/2001
Sales P. Year Revenue
BeckBeckMuthMuthWaltherWalther
200020012000200120002001
150 €150 €200 €190 €110 €130 €
� Sales Person is a time dependent navigational Attribute of City
� We run a Query which looks for:
Revenue by sales persons comparing years 2000 and 2001 (without/with key date 31.08.2001) .
� Without Keydate you will get the following allocation:
2000: 2001:
Beck = Wien Berlin
Muth = Paris Paris
Walther = Berlin Wien
� At keydate 31. August 2001 you have the following allocation:
Beck = Berlin
Muth = Paris
Walther = Wien
so you calculate the revenues for the different citys with the situation at the keydate.
� The result shows, that without keydate sales person Walther looks like an unsuccessfull sales person, as he
has decreased his revenue from 150€ to 130€. But this is due to changing the city. The better comparable
result for the query returns if you use a key date, where you can see that Walther has increased the
revenue from 110€ to 130€.
� So if you use time dependent navigational attributes in queries you often want to see the results of the
situation at a special key date. This can be any date, but often it will simply be today – so you want to see
the revenue at the current situation.
39
SAP AG 2005, TEWA50 / 39
Fact Table: Sales DataFact Table: Sales Data Aggregate Tables: Sales DataAggregate Tables: Sales Data
City Revenue
240 €300 €390 €
WienBerlinParis
City Year Revenue
WienWienParisParisBerlinBerlin
200020012000200120002001
110 €130 €200 €190 €150 €150 €
Aggregate Tables: Sales DataAggregate Tables: Sales Data
Aggregate Tables: Sales DataAggregate Tables: Sales Data
Sales Person Revenue
300 €390 €240 €
BeckMuthWalther
City *CityCity **
Sales Person *Year *Keydate 31.08.2001
Sales PersonSales Person **
YearYear **
KeydateKeydate 31.08.200131.08.2001Sales Person *Keydate 31.08.2001
Sales PersonSales Person **
KeydateKeydate 31.08.200131.08.2001
Sales P. Year Revenue
BeckBeckMuthMuthWaltherWalther
200020012000200120002001
150 €150 €200 €190 €110 €130 €
BW 3.0x Aggregates with keydate (1)
� If you want to use time dependent navigational attributes or hierarchies in an aggregate, you have to specify a keydate for which this aggregate is calculated.
� Only if the query uses the same keydate, the aggregate can be used.
� If you have a changing keydate (like today), you have to recalculate the aggregate
regulary.
40
SAP AG 2005, TEWA50 / 40
Aggregates with a time-dependent component (navigational attribute or hierarchy) are evaluated for a keydate (similar to the keydate of a query)
The keydate can be� a BEx variable which is filled via a SAP- or User- Exit
� a fixed date
When filling an aggregate, the keydate variable is processed
If time-dependent components come in, queries can only use aggregates with the same keydate
Process “Adjustment of Time-Dependent Aggregates“ fits data of the aggregates with variables for the keydate to the changes of the keydate
Important for the use of aggregates is not the variable but the processed keydate.
BW 3.0x Aggregates with keydate (2)
� The variables used in aggregates should be the variables used in queries for the keydate
� Example: A query uses time-dependent attributes and the keydate is the variable “Current Date”(0DAT) then the aggregate with time-dependent attributes should also be defined with the variable “Current Date” (0DAT). The process “Adjustment of Time-Dependent
Aggregates” should be included in a process chain which is executed daily.
� When defining a process chain (Transaction RSPC), the process “Adjustment of Time-
Dependent Aggregates” can be found under „Other BW Processes“.
� If you recalculate the query with process “Adjustment of Time-Dependent Aggregates“ in a process chain (e.g. for the keydate = current day), you have to verify that all servers
where the job can run have the same date / time stamp.Additionally you have to ensure that the process run after midnight to get the right date,
and if you have users in different time zones, you eventually need aggregates with same content, but different keydates.
41
SAP AG 2005, TEWA50 / 41
Useful only for key figures with aggregation SUM, MIN, MAX (not AVG)
Possible Aggregation (SUM, MIN, MAX)
CountryCountry MonthMonth SalesSales
USAGermanyUSAItalyItalyGermanyUSA
01/200001/200001/200001/200002/200002/200002/2000
1015510102025
Fact Table: Sales DataFact Table: Fact Table: Sales DataSales Data
MaterialMaterial
HammerNailsNailsNailsHammerNailsNails
Aggregate Tables: Sales DataAggregate Tables: Aggregate Tables: Sales DataSales Data
Month *Material *
MonthMonth **
Material Material **
SalesSales
10301045
MonthMonth MaterialMaterial
01/200001/200002/200002/2000
HammerNailsHammerNails
Aggregate Tables: Sales DataAggregate Tables: Aggregate Tables: Sales DataSales Data
Month *Material *
MonthMonth **
Material Material **
SalesSales
10101022,5
MonthMonth MaterialMaterial
01/200001/200002/200002/2000
HammerNailsHammerNails
SUM
AVG
AVG result is only valid for exactlythe combination stored in theaggregate (month and material)
You cannot get correct results foraverage on month or material alone
� You can create aggregates only for key figures with aggregation SUM, MIN, MAX.
� Aggregates with aggregation AVG (average) would be valid only for the exactcombination of involved characteristics, hierarchies, and navigational attributes. In thisexample, they would be valid only for this combination of month and material.
42
SAP AG 2005, TEWA50 / 42
Flat Aggregates Concept
If an aggregate has less or equal then 14 components, each component is put into a separate dimension (“Flat Aggregates”)
The dimensions (except of the package and unit) are marked as “Line Item”
F, E
S
Y X S
Better Aggregate Loading Performance
Better Aggregate Query Performance
� Flat Aggregates do no longer contain Dimensiontables, all dimensions are handled like line item dimension, which means the SID is directly stored in the fact table of the Aggregate.
� “Flat Aggregates” are filled and rolled up without loading the data into the application server since no DIMID’s need to be created anymore for the Dimension Tables. Therefore a much better aggregate rollup and change run times were observed.
� Creating of Aggregates as well as Rollup and Changerun is done via DB Insert as select from parent aggregate or from Info Cube.
� Querying data from Flat Aggregate has also shown a better query run time performance.
� Flat aggregates are already used in BW 2.X for other database than Oracle. Starting with
BW version 3.0A it is also used for BW systems with Oracle databases.
43
SAP AG 2005, TEWA50 / 43
Aggregates – No Compress
Default Option: compress after rollup
� Advantage:
optimal query performance
� Disadvantage:
no deletion of requests out of the cube without completely deleting and recreating the aggregate
44
SAP AG 2005, TEWA50 / 44
1 Basics About Aggregates
2 How to Detect Tuning Potential
3 How to Tune Query Performance
5 Basics ofAggregate Maintenance
1.1 Aggregates
1.2 Aggregate Design Basics
1.3 How to Create Aggregates
Unit 1.3: How to Create Aggregates
4 Basics about OLAP Cache
6 How to TuneAggregate Maintenance
45
SAP AG 2005, TEWA50 / 45
Administrator Workbench
/NRSA1
� The above screen shows how to get to from screen Administrator Workbench: Modelling
to maintain aggregates.
�Select the InfoCube you are interested in
�Right-click the InfoCube
�From the drop-down menu, choose Maintain aggregates
� Only one user at a time can use aggregate maintenance in change mode.
46
SAP AG 2005, TEWA50 / 46
Aggregate Maintenance Screen
/NRSDDV
Double-click on theInfoCube you are
interested in
� You can also use the transaction RSDDV and double-click on the InfoCube you are interested in. You can also change the aggregates version:
�M (for modified) represents the current situation: you can create, change, and delete aggregates.
�A (for active) displays all existing aggregates but you cannot create, change, or delete aggregates.
� If you want to check your aggregate design but do not want to stop another user from creating new aggregates, choose A for Aggregates Version. Otherwise you lock the
aggregates of this InfoCube for all other users.
47
SAP AG 2005, TEWA50 / 47
Available Characteristics/Attributes
TechnicalTechnical namename of of thethe
dimensiondimension
Long text Long text forfor thethe
dimensiondimension
TechnicalTechnical namename of of thethe
characteristicscharacteristics//attributesattributes
Long text Long text forfor thethe
characteristicscharacteristics//attributesattributes
� If you go to the aggregate maintenance screen before any aggregates have beendefined, a dialog box appears that asks you to choose one of the following:
�Create the aggregates manually
�Let the system suggest aggregates
48
SAP AG 2005, TEWA50 / 48
Creating Aggregates
To enter characteristics / attributes foraggregates, use drag-and-drop
To change the aggregation level, right-clickon the characteristic
Check whether thedefinition is correct
� There is an automatic check for correctness:
�If the aggregate definition is correct, you see a green traffic light.
�If there are warnings or problems, you see a yellow or red light.
� The time dimension (in this slide, dimension 0APO_C02T) offers the following feature. Ifyou include a specific time characteristic in the aggregate, time characteristics that canbe derived from this characteristic are added automatically. In this example, if you use
calendar year/quarter in the aggregate, the calendar year is added automatically.
� This feature does not increase the quantity of data for the aggregate. For example, you need this feature to build a year aggregate from this aggregate, or to use this aggregate in queries that need year values.
� If you use compound objects, the necessary characteristics are added to the aggregate. For example, if object APO order is compound with APO planning version, APO planning
version is added automatically whenever APO order is included in the aggregate. APO order requires APO planning version but APO planning version can be added alone.
49
SAP AG 2005, TEWA50 / 49
Converting SIDs for Fixed Values
SID
RSDDV
?
� If you use RSRT or RSRTRACE to detect missing aggregates you get only the SID valuefor fixed values
� In RSDDV when you want to create an aggregate you get only the value of thecharacteristic
� We have to convert SID = 3 to the right Country of navigational attribute0VC_CUS1__0VC_COUN manually
50
SAP AG 2005, TEWA50 / 50
Converting SIDs for Fixed Values II
RSDCUBE
� Use transaction RSDCUBE enter the InfoCube name 0BWVC_C09 and push Display
� Doubleclick on the selected characteristics 0VC_CUS1
51
SAP AG 2005, TEWA50 / 51
Converting SIDs for Fixed Values III
� If we don‘t use a navigational attribute, but a normal characteristic, you can directlydoubleclick on the SID-table of the characteristic /BI0/SVC_CUS1
� To get to the SID table of the attribute, select tab-strib Attributes to show all navigational
attributes
� Doubleclick on the selected attribute 0VC_COUN
52
SAP AG 2005, TEWA50 / 52
Converting SIDs for Fixed Values IV
� Doubleclick on the SID-table of attribute /BI0/SVC_COUN
� Select button Contents
53
SAP AG 2005, TEWA50 / 53
Converting SIDs for Fixed Values V
� Enter SID = 3 and push Execute
� Now you can see the Value DE = GERMANY which is related to SID = 3
54
SAP AG 2005, TEWA50 / 54
Indicates that the aggregatedefinition still has to be saved
Activate and fillthe aggregate
Activation and Filling (1)
Save aggregatedefinition
� The symbol in column Save shows that the aggregate definition still has to be saved. To save it, choose Save. As soon as the aggregate definition is saved, the symbol in columnSave vanishes.
� The aggregate is not yet activated and filled. To activate and fill it, choose Activate and fill
(F6).
55
SAP AG 2005, TEWA50 / 55
Activation and Filling (2)
� The aggregate tables are created automatically in the SAP dictionary and on the database. These tables are still empty. When they are created, column Status shows a green traffic light and column Filled shows a red traffic light.
� In the dialog box, mark the aggregates to be filled. Several aggregates can be filled at the same time in one background job. In the background job, the aggregate(s) is (are) filled with data. The aggregates are sorted internally. If possible, smaller aggregates are
filled from larger ones.
� If you cancel the filling, the aggregate remains active, but the database tables remain empty and cannot be used for reporting.
56
SAP AG 2005, TEWA50 / 56
Activation and Filling (3)
� If you change the definition of an aggregate after it is activated, the status of thisaggregate turns to yellow. To activate the changes, activate and fill the aggregate again.
� Until the aggregate is activated and filled again, it remains active in the former version.
57
SAP AG 2005, TEWA50 / 57
Aggregates Maintenance BW 3.0x
Var keydateVar keydate
Processedfor 16.8.2001
Processedfor 16.8.2001
Not time-Dependent
Not time-Dependent
� Path to screen: Admin-Workbench > InfoCubes > right mouse click on InfoCube > Maintain aggregates ...Or transaction RSDDV
� When adding the first time-dependent component (attribute or hierarchy) to an aggregate, the user is asked for a keydate
� Choose „CALENDAR“ on the selection screen for the keydate to select a fixed day
� The variable can be changed via the context menue
� The field “Keydate” is filled only for filled aggregates
58
SAP AG 2005, TEWA50 / 58
1.
2.
Creating Aggregates withtime dependent Navigational Attributes (1)
Transaction RSDDV
1. Add Time Dependent InfoObject to Aggregate Definition
2. Select Variable or fixed value for the keydate
� If a time dependent attribute is added to an aggregate definition you need to select a variable or fixed value for the keydate. The keydate can be
� either a BEx variable which is filled via a SAP- or User- Exit
� Or a fixed calendar date.
� The variables used in aggregates should be the variables used in queries for the keydate
For example a query which uses time-dependent attributes and the keydate is the variable “Current Date” (0DAT) then the aggregate with time-dependent attributes should also be defined with the variable “Current Date” (0DAT). The process “Adjustment of
Time-Dependent Aggregates” should be included in a process chain which is executed daily.
59
SAP AG 2005, TEWA50 / 59
3.
Creating Aggregates withtime dependend Navigational Attributes (2)
3. An Aggregate with time dependend Attribute is defined
� Finally the Aggregate with the time dependend Attribute is defined. In the Aggregate Definition Display a new “Properties” section is added. There you find information if for the keydate a variable or fixed value was selected and with what keydate the aggregate was processed. The keydate information is empty as long as the aggregate was not filled at least once.
60
SAP AG 2005, TEWA50 / 60
Creating Aggregates withtime dependend Navigational Attributes (3)
4.
4. When filling an aggregate, the keydate variable is processed.
61
SAP AG 2005, TEWA50 / 61
Use F4 help to select a tablespace
Tablespaces Used
� Normally the aggregate is located in the same tablespace as the InfoCube where it belongs to.
� In BW 2.0B, you can determine the data type and therefore the tablespace for the aggregates of an InfoCube. To do so, in aggregate maintenance choose Extras > Change
Data Type for Aggregates. If the data type is not determined, the data type of the InfoCube is used, and the InfoCube’s tablespace is used when the aggregate’s tables
and indices are created.
�Depending on the database and on disk layout considerations, assigning an aggregate’s objects to a
user-defined tablespace can improve performance.
� With some databases, you can create user-defined buffer pools (caches) for specific
tablespaces. This allows the aggregate tables to be buffered separately. Separate tablespaces can be useful for backup, administration, and reorganizations.
62
SAP AG 2005, TEWA50 / 62
Additional InfoObjects Added Automatically
� Time dimension
If you add an InfoObject (such as 0CALMONTH), all InfoObjects that can bederived from this InfoObject are added automatically (for example, 0CALQUARTER, 0CALYEAR)
� Compound objects
� Exceptional aggregation
While you add InfoObjects to an aggregate, additional InfoObjectsmay be added automatically by the system in the following cases:
� Time dimension:
�If you include a time characteristic in an aggregate, time characteristics that can be derived from this
characteristic are added automatically.
� Compound objects:
�If you use compound objects, any necessary characteristics are added to the aggregate.
� Exceptional aggregation:
�If you use exceptional aggregation for one of the key figures of the InfoCube, the reference
characteristic is added automatically.
� In the example shown, 0CALDAY is added. Exceptional aggregation forces 0CALDAY in
the aggregate and all time characteristics that can be derived from it are addedautomatically. Fiscal year variant is added because it is compounded to 0FISCPER
(Fiscal year / period).
� If you create your aggregate, you should also implement all characteristics that can bederived from the characteristic you want to implement e.g. Customer / Customergroup.
63
SAP AG 2005, TEWA50 / 63
1 Basics About Aggregates
2 How to Detect Tuning Potential
3 How to Tune Query Performance
5 Basics ofAggregate Maintenance
Unit 2: How to detect Tuning Potential
4 Basics about OLAP Cache
6 How to TuneAggregate Maintenance
2.1 BW Statistics
2.2 Check for Missing Aggregates
2.3 Influences on AggregateUsage
64
SAP AG 2005, TEWA50 / 64
Contents
� Business Information Warehouse statistics
� Missing Aggregates
Objectives
At the end of this unit you will be able to:
� Describe how information is collected in BW
� Check for missing aggregates
How to Detect Tuning Potential
65
SAP AG 2005, TEWA50 / 65
Query Performance: Split-Up
As of BW 2.0, queries can be split up over several aggregates, which are accessed in serial
InfoCube
Aggregate 2Aggregate 1
Query
DB Access DB Access DB Access
� When a query is executed or query navigation takes place, the OLAP engine splits the request into several database queries. The system then looks for the best possible aggregate for each of the database queries.
� Every access to the different aggregates or to the InfoCube is done in serial.
� The goal is to reduce the access to the large InfoCube to a minimum.
� Example with 3 different columns on the excel sheet:
� First column is a non-cummulative value, second column is a detailed view and third column is an
overview.
A) Without splitting: all data needs to be accessed from the infocube
runtime for one query to the infocube = 40 sec.
=> total runtime = 40 sec.
B)Split-Up into 3 db-queries (like in the screen above):
runtime db-query 1 to the infocube = 15 sec (more selective)
runtime db-query 2 to Aggregate 1 = 5 sec
runtime db-query 3 to Aggregate 2 = 2 sec
=> total runtime = 22 sec
� In BW 1.2B, you can only use one aggregate during each navigation.
66
SAP AG 2005, TEWA50 / 66
Query on MultiCubes – PARALLEL – NEW
The Cubes are accessedin parallel,
the aggregates of onesub-query serial
Sub-Query III
15 sec 1 sec 3 sec 2 sec 2 sec
BasisCubeI
Query on MultiCube
Sub-Query II
AggregateBasisCube
III
Sub-Query I
=>15 sec runtime
Aggregate IBasisCube
II
Aggregate IIBasisCube
II
Aggregate IIIBasisCube
II
� One dialog process per Sub-Query necessary -> relevant regarding system resources
� The note 629541 and 630500 are relevant concerning the switch ‘parallel‘ – ‘serial‘
� The slowest Sub-Query is decisive for the overall query run time.
67
SAP AG 2005, TEWA50 / 67
Query on MultiCubes - NOPARALLEL
Using the NOPARALLEL–option in table RSADMIN:
the BasisCubes are accessed in serial, but more than one aggregate per Infocube
can be used
Sub-Query III
15 sec 1 sec 3 sec 2 sec 2 sec
BasisCubeI
Query on MultiCube
Sub-Query II
AggregateBasisCube
III
Sub-Query I
=>23 sec runtime
Aggregate IBasisCube
II
Aggregate IIBasisCube
II
Aggregate IIIBasisCube
II
� To change the processing of MultiCubes to use several aggregates, you have to change the settings of table RSADMIN
�Enter the MultiCube with option NOPARALLEL in table RSADMIN using report SAP_RSADMIN_MAINTAIN
�Every BasisCube is now accessed in serial
�You need less system resources
�The OLAP engine now splits every sub-query into several database queries, so different aggregates (or the InfoCube) can be
accessed for every BasisCube
�You can only switch for all queries on those MultiCube to NOPARALLEL
�You have to enter every single MultiCube into table RSADMIN
�See SAP Note 327876
� Runtime of the query is now the sum of all accesses to the different Aggregates or InfoCubes, in this example (15+1+3+2+2) => 23 seconds.
� You can only find out by testing if access to the different MultiCubes is faster by using thisNOPARALLEL option or not.
� If you use RSRT: MultiCubes are accessed always in serial, so if you want to use thosesuggested aggregates also for your queries, you have to switch the cube in RSADMIN to
NOPARALLEL.
� As of BW 3.x it is possible to combine those options, so that you can have parallel access to the
BasisCubes with usage of different aggregates per Cube.
68
SAP AG 2005, TEWA50 / 68
Query on MultiCubes – NOPARALLEL option
Reasons for using NOPARALLEL option:
� Note 629541 (’MultiProvider: Parallel Processing‘)
� Resources (one dialog process for each BasisCube)
� Note 630500 “Maximum size of the interim result with MultiProvider”: The parallel processing of a MultiProvider query is terminated as standard as soon as the interim result exceeds 30,000 rows. The query is then automatically restarted and processed sequentially. This gives the impression that a sequential processing is faster than a parallel processing. Note 607164 discusses this phenomenon.The limit of 30,000 rows can be changed by an RSADMIN parameter as described in
note 630500.
69
SAP AG 2005, TEWA50 / 69
1 Basics About Aggregates
2 How to Detect Tuning Potential
3 How to Tune Query Performance
5 Basics ofAggregate Maintenance
Unit 2.1: BW Statistics
4 Basics about OLAP Cache
6 How to TuneAggregate Maintenance
2.1 BW Statistics
2.2 Check for Missing Aggregates
2.3 Influences on AggregateUsage
70
SAP AG 2005, TEWA50 / 70
Tools for Analysis of Query Performance
Queries of BW STATISTICS
Using table RSDDSTAT as InfoSource
Table RSDDSTAT
Function module RSDDCVER_RFC_BW_STATISTICS
BW Statistics
Collecting informationfrom table RSDDSTAT
BW Workload Analysis – ST03
� To analyze query performance, the following tools can be used:
�Table RSDDSTAT
�Queries of BW statistics InfoCube (BW statistics use table RSDDSTAT and other tables as
InfoSources)
�Function module RSDDCVER_RFC_BW_STATISTICS (this function module collects data from table
RSDDSTAT).
�ST03 for BW (starting with 4.6C basis support package 20)
71
SAP AG 2005, TEWA50 / 71
BW StatisticsMultiCube
Queriesor ODBO
Queriesor ODBO
aggregatesaggregates
warehousemanagement
warehousemanagement
Table RSDDSTAT
Analysis by queries
(in workbooks)
Table RSDDSTATAGGR
Table RSDDSTATWHM
Info-Source
Info-Source
Turn on/off logging for each InfoCube
Updaterules
Updaterules
Overview
Function module
RSDDCVER_RFC_BW_STATISTICS
Info-Source
Info-Source
Info-Source
Info-Source
Updaterules
Updaterules
Updaterules
Updaterules
� When executing queries, different statistical data about the execution of these queries is entered and stored temporarily at the end of each navigation step. This also occurs when using the ODBO (OLE DB for OLAP) interface. The data is stored in table RSDDSTAT. Additional data is collected when filling and rolling up aggregates and during master data
activation. This data is stored in table RSDDSTATAGGR.
� The data is transferred from the InfoSources (tables RSDDSTAT and RSDDSTATAGGR
and others) into the InfoCube “BW statistics.“
�In BW 2.0X or higher, this data is uploaded via the Administrator Workbench.
�In BW 1.2B, or if you have migrated the BW Statistics Cube from BW 1.2B to BW 2.0, you cannot use
the administrator workbench to upload data. Instead, use report RSDDK_STA_WRITE_IN_CUBE.
� Function module RSDDCVER_RFC_BW_STATISTICS gathers data from the database
table RSDDSTAT and summarizes and displays this data.
� Tabelle RSDDSTATCOND ???
72
SAP AG 2005, TEWA50 / 72
Turning on Statistics
Choose Tools > BW Statistics for Infocubes
Can be turned on/off for OLAP/WHM
You can delete old data from database tables (buffers)
You can store new settings permanently
� It only takes a short time to enter and save the BW Statistics data. However, the dataset for larger installations can become quite large. You can deactivate both entering and saving data separately for each InfoCube as well as for OLAP and Warehouse Management.
�use transaction RSA1 and choose Tools > BW statistics for InfoCubes.
� Make sure that you delete old data for periods that no longer interest you.
� The flag in column OLAP turns on/off BW statistics data collection for query execution and aggregate maintenance.
� The flag in column WHM turns on/off BW statistics data collection for loading of data.
� Data from table RSDDSTAT is not deleted automatically, you have to do this manually.
� Löschjob für Statistikdaten vorhanden ????
73
SAP AG 2005, TEWA50 / 73
In transaction SE16, enter tablename RSDDSTAT and table contents (F7)
Using Table RSDDSTAT
YYYYMMDDHHMMSS
� In the selection screen of the data browser (transaction SE16) you can select for specificcriteria (choose Settings > Fields for selection). A common criterion is the start time of thequeries. This start time has the format YYYYMMDDHHMMSS (year, month, day, hour, minute, second).
� Welche Zeit ist das ??? UTC ???
� Table RSDDSTAT consists of many columns. To restrict which columns should be
displayed, choose Settings > List format and select fields.
74
SAP AG 2005, TEWA50 / 74
Choose Settings ���� User parameters (F6)
Settings of Table RSDDSTAT
Set flag to display query names
� The conversion exit flag has to be set to get the technical query name in field QueryID. Ifthe flag is not set, a 25-digit BW internal QueryID is displayed.
�Example: 9OG6XFHBXSP4PSMHZI9774WHH
� You can also specify the width of the output list and the maximum number of hits.
75
SAP AG 2005, TEWA50 / 75
Fields in Table RSDDSTAT (1)
Query name Runtime Category Database timeRecords selected on the database
Records transferred from the database to the OLAP
� The screenshot above shows the most important fields for tuning queries withaggregates.
� Each query navigation (first screen, navigations, drilldowns) creates an entry in table RSDDSTAT.
�In BW 1.2B, one navigation creates only one entry in table RSDDSTAT.
� For Multicubes only one entry is written per navigation step.
76
SAP AG 2005, TEWA50 / 76
Fields in Table RSDDSTAT (2)
InfocubeNumber of recordsread on thedatabase
(QDBSEL)
Records transferredafter being summarizedon the database
(QDBTRANS)
Start 2s 4s 6s 8s 10s 12s 14s 16s 18s 20s
Total query runtime (QRUNTIMECATEGORY)
End
Database time (QDBTIME)
Total runtime of query
� The query runtime (QRUNTIMECATEGORY) is stored as a runtime category in table RSDDSTAT. There are different categories:
�From 1 to 10 in steps of 1
�From 10 to 100 in steps of 10
�From 100 to 1000 in steps of 100
�Above 1000 in steps of 1000
� Examples:
�Runtime category 20: the query took between 10 and 20 seconds
�Runtime category 4: the query took between 3 and 4 seconds
77
SAP AG 2005, TEWA50 / 77
Fields in Table RSDDSTAT (3)
Navigation step of the query
Read mode of the query
Name of the aggregate used (blank if no aggregate has been used)
Time spent in the OLAP processor (ABAP coding)
Time spent for frontendcommunication (network / frontend time)
� Explanation of the most important fields:
�NAVSTEPUID: If there are several entries for one navigation in table RSDDSTAT, all of those entries
belonging to the same navigation share the same NAVSTEPUID.
�QueryID: Technical name of the query. Format: <InfoCube name>/<Query name>
�QAGGRUSED: Aggregate used (if any) during query execution
�QNACHLESEN: Read mode of the query
�QRUNTIMECATEGORY: See previous page
�QNAVSTEP: The navigation steps of a QSESSION are numbered sequentially. The number shows if
statistical information was collected for the first execution of a query (1) or for drilldowns or navigations
(greater than 1).
�QDBSEL: Number of rows selected/read on the database
�QDBTRANS: Number of rows transferred to the OLAP processor
�QTIMEDB: Time used to find the relevant data on the database and to pass it to the OLAP processor
�STARTTIME: Query start time. Format: YYYYMMDDHHMMSS
� For detailed information, see SAP Note 130696.
78
SAP AG 2005, TEWA50 / 78
Fields in Table RSDDSTAT (4)
79
SAP AG 2005, TEWA50 / 79
Analyzing Query Performance
� As well as displaying the information as a chart, you can also display it as a table.
80
SAP AG 2005, TEWA50 / 80
Using RSDDCVER_RFC_BW_STATISTICS
Enter time frame you are interested in (default one week)
Single test (F8)
Execute (F8)
� Function module RSDDCVER_RFC_BW_STATISTICS gathers data from table RSDDSTAT. This data is summarized at InfoCube level or at query level.
� To start the function module, in transaction SE37, enter the function module name and choose Single Test (F8).
� In section Import Parameters, the default time frame is one week. So long as TIME_FROM is before TIME_TO, you can enter any time frame you want.
81
SAP AG 2005, TEWA50 / 81
Values for all queries of all InfoCubes are summarized
Query runtimes summarized for all InfoCubes
Query runtimes summarized on query level
Results from RSDDCVER_RFC_BW_STATISTICS
� The results can be displayed at different levels of detail:
�To get values summed up for the total system, choose export parameter
E_S_USAGE_BY_INFOCUBE. The values of all InfoCubes are summarized to a total value.
�To get values summarized for each InfoCube, choose table E_T_USAGE_BY_INFOCUBE.
�To get values summarized for each query, choose table E_T_USAGE_BY_QUERY.
� You can get this information also using the Earlywatch Alert report.
82
SAP AG 2005, TEWA50 / 82
Usage by InfoCube
InfoCube name
Summarized runtime of all queries belonging to this InfoCube (in seconds)
� To view the runtime of all queries belonging to a specific InfoCube, select table E_T_USAGE_BY_QUERY and choose Single entries (Shift + F7).
83
SAP AG 2005, TEWA50 / 83
Table RSDDSTAT
BW statistics InfoCube
RSDDCVER_RFC_BW_STATISTICS
Actuality of statistics
Always up-to-date
Actuality dependent on Upload Frequency
Always up-to-date
Handling Not user friendly –tuning of queries
User friendly: drilldown and own queries possible, exception reporting
Not user friendly
Granularity Single query execution
Summarized for InfoCubes/Queries
Summarized for InfoCubes/Queries
Effect of long running queries
No effect Will increase average value
Will increase average value
Advantages / Disadvantages
� The different ways to display statistics about query runtime have different advantagesand disadvantages.
� To tune specific queries, use table RSDDSTAT. Since no average are calculated, a fewlong running queries do not falsify the query response time.
� The BW Statistics InfoCube offers many more possibilities. Calculation of averages canlead to a false picture of response times. For example, a few long running queries due to
poor selections can cause high average runtimes.
� Table RSDDSTAT and function module RSDDCVER_RFC_BW_STATISTICS areavailable for analyzing without doing a rollup of data into the BW Statistics InfoCube.
84
SAP AG 2005, TEWA50 / 84
ST03 – Workload Analysis - BW Specific
1
� To view BW Workload Analysis click on Administrator and select Expert Mode.
85
SAP AG 2005, TEWA50 / 85
EarlyWatch - Alert
•Queries by total workload•Queries by DB Load•Top time Queries by average runtime•Top time queries per single execution
Performance of your queries
Query name Avg. Runtime (s) DB time (s) OLAP time (s) Frontend time (s)
Total 16,1 11,8 0,5 3,6
ZCOSTCENTER1 22,9 17,8 0,6 4,2
0CCA_C01_Q0015 6,1 0,7 1,4 3,9
0CCA_C01_Q0007 5,4 2,7 0,2 2,0
ZP_IND_CONSISTENZE 4,6 1,6 0,2 2,7
Task type Navigation
steps
Runtime > 20
seconds in %
Avg. runtime
(s)
Avg. time
OLAPINIT (s)
Avg. time
OLAP (s)
Avg. time DB
(s)
Avg. time
Frontend (s)
All Queries 48 27 16,1 0,3 0,5 11,8 3,6
� You get a weekly overview over the performance of your queries.
� The statistical values are separated in 5 areas:
• Total overview• Queries by total workload• Queries by DB Load• Top time Queries by average runtime• Top time queries per single execution
� For details to EarlyWatch Alert, see SAP Note 215416
86
SAP AG 2005, TEWA50 / 86
All Fields in Table RSDDSTAT(I)
STATUID Unique key to identify the records in the database
SESSIONUID One session gets always the same session ID
NAVSTEPUID Every navigation step gets his ID
INFOCUBE Technical name of the InfoCube
HANDLE Internal identifier
QUERYID Technical name of the query
PAGEID Webquery: Identifier of the HTML page
UNAME User name
QAGGRUSED Name of the used aggregate
QNACHLESEN Read mode of the query
OLAPMODE Type of OLAP access
DBSELTP Readmode of the database
QRUNTIMECATEGORY Total time for one NavStep as category
QNAVSTEP Number of the NavStep to identify the order
QNUMOLAPREADS Number of OLAP reads per NavStep
QDBSEL Number of records read on the database
QDBTRANS Number of records transferred from database to OLAP
QNUMCELLS Number of cells transferred to the frontend
QNUMRANGES Number of formatting areas transferred to the frontend
RECCHAVLREAD Number of records read from master data / text
87
SAP AG 2005, TEWA50 / 87
All Fields in Table RSDDSTAT(II)
QTIMEOLAPINIT Time used to initialise a query; e.g. time to generate the query
QTIMEOLAP Time which is spend in the OLAP processor
QTIMEDB Time which is spend on the database + network DB<->APPL
QTIMEVARDP Time which user has spend for inserting values for variables
QTIMEUSER Wait time of user between different NavSteps
QTIMECLIENT Time which is spend on the frontend + network APPL<-> Frontend
TIMECHAVLREAD Time for reading master data / text
TIMEAUTHCHECK Time which is spend for authority check
TALERTMON Time which is spend to read the pre-calculated alerts
TIMEREST Time which could not be mapped to a special area (should be around 0)
ODBOT Total time in ODBO interface
ODBOTINIT Time to initialise the ODBO interface
ODBOPREAXES Time to prepare the axis
ODBOTRQDATA Time to work when data is requested
ODBOTPREDTST Time for testing
ODBOTFLATENING Time to bring data in flat structure
ODBONCALLS Number of calls of the ODBO interface
ODBONBUFENTRIES Number of entries in the ODBO buffer
DMTDBBASIC Time which is spend to read from a Basis Cube
DMTDBREMOTE Time which is spend to read from a Remote Cube
88
SAP AG 2005, TEWA50 / 88
All Fields in Table RSDDSTAT(III)
DMTDBODS Time which is spend to read from an ODS Object
DMTNCUMPROC Time to reconstruct non-cumulative values
DMTREMOTESID Time to identify SIDs if you read from a Remote Cube
DMTCUBEACC Time to process the query in the data-manager
STARTTIME UTS time stamp in long form (JJJJMMDDhhmmss,mmmuuun)
WEBTEMPPREP Time which is spend to prepare the Web template
WEBTRENDER Time which is spend to render all items
TIMEOLAPTRANSF Not used
TIMEFRONTPROC Time which is spend on the frontend (in Excel)
TIMEGRAPHICSGEN Not used
NUMGRAPHICS Not used
TIMEGRAPHICSGEN
TIMEFRONTPROC
89
SAP AG 2005, TEWA50 / 89
1 Basics About Aggregates
2 How to Detect Tuning Potential
3 How to Tune Query Performance
5 Basics ofAggregate Maintenance
Unit 2.2: Check for Missing Aggregates
4 Basics about OLAP Cache
6 How to TuneAggregate Maintenance
2.1 BW Statistics
2.2 Check for Missing Aggregates
2.3 Influences on AggregateUsage
90
SAP AG 2005, TEWA50 / 90
Indicators of Missing Aggregates
2. Database time high:
a) Over 30% of total runtime
b) Higher than3 seconds
a) Ratio: rec. Selected / rec.transferred > 10
1. Many more records selectedthan necessary:
b) Records selected> 10000
InfocubeNumber of recordsread on thedatabase (QDBSEL)
Records transferred afterbeingsummarized on the database
(QDBTRANS)
Start 2s 4s 6s 8s 10s 12s 14s 16s 18s 20s
Total query runtime (QRUNTIMECATEGORY)
End
Database time (QDBTIME)
Total runtime of query
� Query performance may be bad due to missing aggregates:
�If much data has to be selected on the database
�Much more data than necessary is selected on the database
�The database time is high
�The database time is responsible for a high percentage of the total runtime
� The number of records read on the database is only the number of valid records (which fulfill the selection conditions). Only a high database time for a low number of records
read is a sign for a bad data access path.
� Aggregates will also help to reduce the I/O load of the database, due to less data which must be read from disk
91
SAP AG 2005, TEWA50 / 91
Detecting expensive queries due to missing aggregates
Different points of entry:
� Overview at cube level
� At query level
Detecting Expensive Queries
� You can check for expensive queries where performance can be improved by detectingmissing aggregates at either of two levels:
� Cube level:
�Information about query performance is summarized for all queries of the InfoCubes.
�Use this level if you have not created aggregates for all of your Cubes / specific Cubes or to check if the
existing aggregates are suitable for this Cube.
�You can find out whether queries of an InfoCube can be tuned with aggregates.
� Query level:
�Use this level to find specific queries that can be tuned with aggregates.
�If you know which query has bad performance, you can find out if the performance for this query can be
tuned with aggregates.
�After analyzing query performance at Cube level, you can use this level to take a closer look at single
queries.
92
SAP AG 2005, TEWA50 / 92
Overview at Cube Level
InfoCube
Runtime: 12 s
Database time: 5 s
Navigation step: 1
Runtime: 30 s
Database time: 20 s
Navigation step: 1
Runtime: 3 s
Database time: 1 s
Navigation step: 2
Runtime: 80 s
Database time: 37 s
Number of navigations: 6
Runtime: 5 s
Database time: 2 s
Navigation step: 1
Statistical information about queryruntime summarized at InfoCubelevel (all navigations)
Runtime: 10 s
Database time: 4 s
Navigation step: 2
Runtime: 20 s
Database time: 5 s
Navigation step: 1
� For the period you are interested in, the runtime of all navigations of all queries belongingto an InfoCube are summed up.
93
SAP AG 2005, TEWA50 / 93
Overview at Query Level
Runtime: 30 s
Database time: 20 s
Navigation step: 1
Runtime: 30 s
Database time: 20 s
Navigation step: 1
Runtime: 30 s
Database time: 20 s
Navigation step: 1
Runtime: 10 s
Database time: 5 s
Navigation step: 2
Runtime: 100 s
Database time: 75 s
Number of navigations: 4
Statistical informationabout query runtimesummarized at querylevel (all navigations)
� For the period you are interested in, the runtime of all navigations of one specific queryare summed up.
94
SAP AG 2005, TEWA50 / 94
When to Analyze at Which Level
At InfoCube Level At Query Level
After creation of (new) InfoCube
For already existing InfoCubes
Tuning overview Fine-tuning
General tuning Tuning of queries known to have performance problems
No aggregates exist yet (for a specific InfoCube)
Aggregates already exist
Aggregates found so far improve several queries
Aggregates found so far mostly tune specific queries
� Depending on the situation of your system, different level to start analysis should be usedto check whether aggregates can inprove the performance of your queries:
� To get a general overview, analyze query performance at InfoCube level.
� You can also analyze the performance of a specific query directly.
95
SAP AG 2005, TEWA50 / 95
Detecting Missing Aggregates at InfoCube Level
Queries of BW STATISTICS
Using table RSDDSTAT as InfoSource
Table RSDDSTAT
Function module RSDDCVER_RFC_BW_STATISTICS
BW Statistics
Collecting informationfrom table RSDDSTAT
BW Workload Analysis – ST03N
� To analyze query performance at InfoCube level, you can use the following tools:
�Queries of BW statistics InfoCube (BW statistics use table RSDDSTAT and other tables as
InfoSources)
�Function module RSDDCVER_RFC_BW_STATISTICS (this function module collects data from table
RSDDSTAT).
�ST03N for BW (starting with basis support package 20)
� Table RSDDSTAT contains the runtime for each query navigation. You cannot useRSDDSTAT to summarize runtime at InfoCube Level
96
SAP AG 2005, TEWA50 / 96
Set Up Your Focus (1)
Sort by mean overall time to find InfoCubeswith queries having the highest runtimes
� Depending on what you want to analyze, you can sort and analyze the informationdifferently:
�Search for specific InfoCubes that you know to have bad performance.
�Sort / search for InfoCubes with high number of navigations. For example, you can improve the
performance of queries that are used the most.
�Sort / search forInfoCubes with high average runtime. For example, you can improve the performance
of the queries of the InfoCube with the highest average runtime.
�Sort / search for InfoCubes with high overall time. For example, you can improve the performance of
the most expensive queries of the InfoCube (that is, the queries that consume the most CPU and
memory and block the most dialog work processes).
97
SAP AG 2005, TEWA50 / 97
Analysis of BW Statistics at InfoCube Level (1)
Database time ~40% of total runtime(6771 of 17 025 seconds)
Ratio recordsselected / recordstransferred: 24
Aggregates will probably
improve query performance
� In this example, 40% of the total query runtime of this InfoCube is spent on the database, and 24 times more data is selected on the database than is needed.
� Aggregates will probably improve query performance for this InfoCube.
98
SAP AG 2005, TEWA50 / 98
Analysis of BW Statistics at InfoCube Level (2)
Database time ~3,5% of total runtime (1011 of 28 540 seconds)
Ratio recordsselected / recordstransferred: 32
Aggregates will notimprove query
performance
� In this example, only 3.5% of the total query runtime is spent on the database. Muchmore data is selected on the database than necessary (32 times more). However, thetotal number of rows selected and transferred is small.
� Aggregates will not improve query performance for this InfoCube because the total number of rows selected and the database time are small.
99
SAP AG 2005, TEWA50 / 99
Analysis of BW Statistics at InfoCube Level (3)
Database time ~90% of total runtime(1420 of 1572 seconds)
Aggregates will notimprove query
performance
Ratio recordsselected / recordstransferred: 1.1
� In this example, 90% of the total query runtime is spent on the database. However, nearlyall the data selected on the database is needed (only 1.1 times more data is transferredthan needed).
� Aggregates will not improve query performance for this InfoCube because nearly all datawhich has been selected was actually needed.
� A low value of records selected/records transferred can also be caused by a wrong
ReadMode setting.
100
SAP AG 2005, TEWA50 / 100
Database time ~42% of total runtime (58 of 138 seconds)
Ratio recordsselected / recordstransferred: 7313
Analysis of Usage by Infocube
Aggregates will improve
query performance
Table RSDDCVER_RFC_BW_STATISTICS
� The same information that can be collected from the BEX queries can also be collectedfrom function module RSDDCVER_RFC_BW_STATISTICS.
� In this example, 42% of the total query runtime of this InfoCube is spent on the database. Much more data is selected on the database than necessary (7313 times more).
� Aggregates will improve query performance for this InfoCube.
101
SAP AG 2005, TEWA50 / 101
ST03N - WorkLoad By InfoCube
Highest contributor in Total Run Time
per InfoCube
High % DB Time
High ratio:
Selected / transferred
records
� This view gives us the InfoCubes which had the highest longest run time.
� All queries run time are grouped and totaled by their respective InfoCube.
� The same information that can be collected from the BEX queries can also be collected
from transaction St03N.
� In this example no aggregate would improve performance, as we have no records selected (its only a test system)
102
SAP AG 2005, TEWA50 / 102
EarlyWatch Alert:Aggregates suggested on Cube Level
Aggregates suggested on Cube Level
Cubename Time on DB (sec.) Total time (sec.) Time on DB /
Total time (%)
Rows selected on
DB
Rows transfered
from DB
selected /
transfered Rows
on DB
ZB_ORDERS 350,4 508,9 68,9 1802282 146120 12,3
ZINVINV 276,5 311,5 88,8 2262658 62228 36,4
� All InfoCubes where selected / transferred Rows on DB is higher than 5 and Time on DB /
Total time is higher than 30% are summarized in a table. We recommended to considercreate aggregates for those InfoCubes.
103
SAP AG 2005, TEWA50 / 103
Table RSDDSTAT
Function module RSDDCVER_RFC_BW_STATISTICS
Collecting informationfrom table RSDDSTAT
Detecting Missing Aggregates at Query Level
Queries of BW STATISTICS
Using table RSDDSTAT as InfoSource
� To analyze query performance at query level, the following tools can be used:
1. Table RSDDSTAT. The performance of single query executions can be checked. However, it is not
possible to summarize the information on query level.
2. Queries of BW statistics. BW STATISTICS use, among others, table RSDDSTAT as an InfoSource.
3. Function module RSDDCVER_RFC_BW_STATISTICS. This function module collects data from table
RSDDSTAT.
104
SAP AG 2005, TEWA50 / 104
Utilizing OLAP per Query
Sort by overall time to find queries that have the highesttotal runtime
Set Up Your Focus (2)
� Depending on what you want to analyze, you can sort and analyze the informationdifferently:
1. Search for specific queries that you know to have a bad performance
2. Sort / search for queries with high number of navigations
3. Sort / search for queries with high average runtime
4. Sort / search for queries with high overall time
105
SAP AG 2005, TEWA50 / 105
Analysis of BW Statistics at Query Level (1)
Utilizing OLAP per QueryDatabase time ~60% of total runtime (2.798 of 4.685 seconds)
Ratio recordsselected / recordstransferred: 54
Aggregates will improve
query performance
� In this example, 60% of the total query runtime is spent on the database. Much more datais selected on the database than necessary (54 times more).
� Aggregates will improve query performance for this query.
106
SAP AG 2005, TEWA50 / 106
Analysis of BW Statistics at Query Level (2)
Database time ~89% of total runtime (1064 of 1162 seconds)
Ratio recordsselected / recordstransferred: 2.1
Aggregates will not
improve query performance
� In this example, 89% of the total query runtime is spent on the database. However, nearlyall the data selected on the database was needed (only 2.1 times more data was transferred than needed).
� Aggregates will not improve query performance of this query because nearly all the dataselected is needed.
107
SAP AG 2005, TEWA50 / 107
Analysis of Usage by Query
Database time ~71% of total runtime (1181 of 1674 seconds)
Ratio recordsselected / recordstransferred: 130
Aggregates will improve
query performance
Table RSDDCVER_RFC_BW_STATISTICS
� The same information that can be collected from the BEx queries can also be collectedfrom function module RSDDCVER_RFC_BW_STATISTICS.
� In this example, 71% of the total query runtime is spent on the database. Much more datais selected on the database than necessary (130 times more).
� Aggregates will improve query performance for this query.
108
SAP AG 2005, TEWA50 / 108
Analysis of Table RSDDSTAT (1)
Database time at least ~67.5% of total runtime (135 of less than 200 seconds)
Ratio recordsselected / recordstransferred: 1288
Aggregates willimprove query
performance
� Instead of using the BEx queries or function moduleRSDDCVER_RFC_BW_STATISTICS, table RSDDSTAT can be used. However, onlysingle executions can be analyzed, and you cannot summarize the information.
� In this example, at least 67.5% of the runtime of this query exection is spent on thedatabase. Much more data is selected on the database than actually necessary (1288 times more). Only the total runtime is displayed. To display the details, choose Display
(F7).
� An aggregate will improve the performance of this query execution.
109
SAP AG 2005, TEWA50 / 109
Total time
140 seconds
QTIMEOLAPINIT + QTIMEOLAP + QTIMEDB + QTIMECLIENT + TIMECHAVLREAD + TIMEAUTHCHECK
Database time ~96% of total runtime (135 of 140 seconds)
Read mode
Navigational step
Analysis of Table RSDDSTAT (2)
� Table RSDDSTAT provides very detailed information. To check the details, chooseDisplay (F7).
� To get the total time, add the values of QTIMEOLAPINIT, QTIMEOLAP, QTIMEDB, QTIMECLIENT, TIMECHAVLREAD, and TIMEAUTHCHECK.
� You can also check if the query uses the appropriate read mode (check fieldQNACHLESEN) and whether this query execution is a first execution or a navigation
(check field QNAVSTEP).
� Field STATUID enables you to check which selections are used and which aggregate canmost improve the situation.
110
SAP AG 2005, TEWA50 / 110
ST03N - Drill Down to Query
Double Click
Highest contributor in Total Run Time per
InfoCube
High % DB Time
Drill down – check if there is any single query that could be the major contributor. Where is the major contribution coming from?
Example shows an active query, high DB time, with low OLAP & front-end time.
No of runs
Highest DB time
Low Front-end
time
� This view gives us the InfoCubes which had the highest longest run time.
� All queries run time are grouped and totaled by their respective InfoCube.
� To drill down to the individual query level, you can get that from table RSDDSTAT, see next slide. It may be the case where a particular user will have poorer response time if they enter different selection criteria. Each user running the identical query may produce
different amount of records read and transferred from the database to the frontend. Table RSDDSTAT reveal more information.
111
SAP AG 2005, TEWA50 / 111
EarlyWatch Alert:Aggregates suggested on Query Level
Aggregates suggested on Query Level
Queryname Time on DB
(sec.)
Total time (sec.) Time on DB / Total
time (%)
Rows selected on
DB
Rows transfered
from DB
selected / transfered
Rows on DB
PARTSALES1 263,0 277,3 94,9 2251296 59258 38,0
TRNCD_DMH1 136,3 183,8 74,2 876838 5960 147,1
AKPRODTEST 52,4 64,8 80,8 188122 1380 136,3
TRNCD_DMH2 48,8 55,3 88,3 340070 1262 269,5
ACTSUM2 40,3 124,1 32,5 217083 10192 21,3
� All queries where selected / transferred Rows on DB is higher than 5 and Time on DB /
Total time is higher than 30% are summarized in a table. We recommended to considercreate aggregates for those queries.
112
SAP AG 2005, TEWA50 / 112
1 Basics About Aggregates
2 How to Detect Tuning Potential
3 How to Tune Query Performance
5 Basics ofAggregate Maintenance
Unit 2.3: Influences on Aggregate Usage
4 Basics about OLAP Cache
6 How to TuneAggregate Maintenance
2.1 BW Statistics
2.2 Check for Missing Aggregates
2.3 Influences on AggregateUsage
113
SAP AG 2005, TEWA50 / 113
Major influences on aggregate usage
Read Mode
Database
Info Cube Aggregates
Query design
OLAPOLAP
� Two major aspects influence aggregate usage.
� Query design:
�To use aggregates optimally, queries should be created to start at an aggregated level. The user then
has the option of filtering certain interesting characteristic values and of drilling down according to other
characteristics. If efficient aggregates exist in such a scenario, then for each navigation step, a dataset
of roughly equal size must be selected from the database. When drilling down with more and more
restrictive filters, it is of little significance to the performance that the data sources (aggregates or
InfoCube) become larger and larger. In this scenario, the reply times thus remain roughly the same.
�The design of queries is also influenced by the InfoObjects (exceptional aggregation, virtual key figures)
used in the InfoCube and by some other features that can be used in a query (calculating with attributes
in calculated key figures, calculating before aggregation, time-related currency translation, presentation
hierarchy and filter)
� Read mode:
�The read mode specifies the level of detail data that is read from the database. This has an important
influence on query performance.
114
SAP AG 2005, TEWA50 / 114
Read Modes
Query Drill Down
Read all dataRead data
during navigation
OLAP ProcessorOLAP Processor
Read data during navigation and
expand thehierarchy
Read Mode
Database Database Database
� The read mode determines how often the OLAP processor reads data from the database during navigation.
� For a query, the OLAP processor can read the data from the fact table in one of three ways:
�Read all the data
�Read the data during navigation
�Read during navigation and when expanding the hierarchy
� Only presentation hierarchies have an influence on the read mode, no hierarchies which
are used as filter condition in the query.
115
SAP AG 2005, TEWA50 / 115
No database access
Customer *
Possible drilldowns/ navigations:
Country
Read All Data
Customer *Country * Product *
Customer *
Customer *
Drilldown (on customer, filter
country Germany)
MemoryCustomer *Country *
MemoryCustomer * Country *
First execution Drilldown
Customer *
Country F, Germany
Customer *Country *
Country H,
level 2
Country H,
level 3
All
Europe
America
All
Europe
America
USA
� If the query uses the read mode Read all data, then all the data that may be required is read on the database.
� In the first screen of this example, only Information grouped by customer is needed. Drilldowns and navigations are possible for country. In this query, product information is not necessary. With this read mode, the query collects the information grouped by customer and country from the database. Further drilldowns can be performed in extended memory. Since a large amount of information is kept in extended memory, a
large amount of extended memory is consumed.
� In this case, if you do not want to drill down, you do not need the information in extended
memory, but you must still wait a long time for all this information to be collected from the database.
116
SAP AG 2005, TEWA50 / 116
Read Data During Navigation (1)
Customer *
Possible drilldowns/ navigations:
Country
MemoryCustomer *
First execution
Customer *Country * Product *
Customer *
Customer *Country *
Country H,
level 2
Country H,
level 3
All
Europe
America
AllEurope
America
USA
Customer *
Country F, Germany
Customer *
Drilldown (on customer, filter
country Germany)
MemoryCustomer * Country F Germany
Drilldown
Customer *Country * Product *
Customer *
Customer *Country *
Country H,
level 2
Country H,
level 3
All
Europe
America
AllEurope
America
USA
Customer *
Country F, Germany
� If the query uses the read mode Read data during navigation, only the data that is required for the first screen is read on the database.
�If hierarchies are used, there is an exception – see next slide.
� In the first screen of this example, only information grouped by customer is needed. Drilldowns and navigations are possible for country. In this query, information about
product are not necessary. With this read mode, the query can collect the information grouped by customer from the database. Any further drilldowns access the database
again.
� In this case, if you do not want to drill down, you do not have to wait until the extra
information is collected from the database.
117
SAP AG 2005, TEWA50 / 117
Country H, Level 2
Possible Drill-down:
Expanding the hierarchy on Country
Read Data During Navigation (2)
Country H, Level 2
Expanding one node
MemoryCountry *
MemoryCountry *
First execution Hierarchy expansion
No database access
Customer *Country * Product *
Customer *
Customer *Country *
Country H,
level 2
Country H,
level 3
All
Europe
America
AllEurope
America
USA
Customer *
Country F, Germany
� If the query uses the read mode Read data during navigation and a presentation hierarchy is used, information is read on the database grouped by the characteristic the hierarchy belongs to. Data is always read on the lowest level of the hierarchy.
� If you use this read mode and a query that uses hierarchies for presentation, aggregates that summarize data on a specific hierarchy level cannot be used. For example, if the first screen of the query provides information like sales grouped by continent, the information is read from the database grouped by country. The information is then be summed up in
the BW system by the OLAP. When the hierarchy is expanded, the information is already available in extended memory. So more information than necessary for the first screen is read on the database.
� In this case, if you do not expand the hierarchies, you have to wait longer than necessary
while all this information is collected from the database.
118
SAP AG 2005, TEWA50 / 118
Expanding the Hierarchy
MemoryCountry H, Level 3
Hierarchy expansionCountry H, Level 3
Expanding one node
Customer *Country * Product *
Customer *
Customer *Country *
Country H,
level 2
Country H,
level 3
AllEurope
America
AllEurope
America
USA
Customer *
Country F, Germany
Country H, Level 2
Possible Drill-down:
Expanding the hierarchy on Country
MemoryCountry H, Level 2
First execution
Customer *Country * Product *
Customer *
Customer *Country *
Country H,
level 2
Country H,
level 3
AllEurope
America
AllEurope
America
USA
Customer *
Country F, Germany
� If the query uses the read mode Read data during navigation & expanding the hierarchy, in each case only the information required for the first screen is read from the database. If no presentation hierarchies are used in the query, there is no difference between this read mode and the read mode Read data during navigation.
� If no aggregates exist at hierarchy level, the query behaves as if it uses read mode Read
data during navigation.
� In this case, you do not have to wait while information you may not need is collected from the database. Only the information you need for the current navigation step is read from the database.
119
SAP AG 2005, TEWA50 / 119
Trade-Offs
Number of DB accesses
Amount of data retrievedper DB access attempt
Read all data
Read during navigation& when expanding hierarchy
Read during navigation
� The main differences between the three read modes are as follows:
�Read all data
A query accesses the database only once, but a large number of rows are read (even if you do not want
the use this information). Result: a high database runtime and consumption of a large amount of
extended memory.
�Read data during navigation
A query accesses the database during navigation, but if presentation hierarchies (which can be
expanded) are used, the information is read grouped by the characteristic this presentation hierarchy
belongs to. Result: shorter database runtimes and less extended memory consumption.
�Read data during navigation & expanding the hierarchy
A query accesses the database to collect only the data actually needed, so long as appropriate
aggregates are available. Result: the shortest database runtime and the least extended memory
consumption.
120
SAP AG 2005, TEWA50 / 120
For reading data during navigation and expanding
the hierarchy
Recommendation
READ MODEAWARD BW
� In special cases, another read mode may be more efficient.
� Run your query with different read modes to verify the right read mode. Switching the read mode can slow down the runtime of a query, if you don’t have fitting aggregates for this read mode.
� Read mode Read data during navigation & expanding the hierarchy supports aggregates best.
� If a query does not have a presentation hierarchy, read modes Read data during
navigation & Expanding the hierarchy and Read data during navigation are equivalent.
� Even with a hierarchy, if no aggregate exists at a particular level, then read mode Read
data during navigation & expanding the hierarchy is not better than Read data during
navigation and may even be harmful.
� For small InfoCubes it is sometimes better to use read mode Read all data.
121
SAP AG 2005, TEWA50 / 121
How to Set Read Modes
Default read mode can be defined (BW Customizing Implementation Guide). All new queries of this InfoCube will have this mode.
New Queries
Existing Queries
Read mode can be set to ‘predefined default’ in the Query Monitor (transaction RSRT) for:
A single query: BW2.xX: Query > Read ModeBW3.xX: Query > Properties > Read Mode
All existing queries at once: BW2.xX: Environment > All Queries Read Mode BW3.xX: Environment > Repair Read Mode
BW 2.xX/ BW 3.xX Predefined default: Read during navigation & when expanding the hierarchy
� There is a default read mode for all newly created queries of an InfoCube.
� BW 2.0B or higher: This default read mode can be changed for all InfoCubes: from the main menu, choose Tools > Business Engineer > BW customizing (or use transaction SPRO). Select SAP Reference IMG and choose BW Customizing Implementation Guide
> Business Information Warehouse > Reporting relevant settings to reach screen Set read mode for InfoCube (or use transaction RDMD to jump directly to this screen). Enter
the name of the InfoCube. Now you can change the default read mode.
� If you are below BW 2.0B support package 14, see SAP Note 387477.
� BW 1.2B: Use transaction READMODE to change the default read mode.
� To change the read mode of existing queries, start transaction RSRT. Enter a query
name and select Read mode / Properties > Read Mode. A dialog box appears and you can select the new read mode for this query. If you want to change the read mode of all
queries, choose Environment > All Queries Read Mode. Take care - this changes the read mode of all existing queries.
122
SAP AG 2005, TEWA50 / 122
Table RSDCUBE
Default Read Mode for new Queries at this Cube
� Table RSDCUBE enables you to check the default read mode of all your InfoCubes. This is the default Read Mode, which new queries at this Cube will get.
� To get only the InfoCubes that are currently active and usable in the system, restrict the values of OBJVERS to "A" and OBJSTAT to "ACT".
� To see the correct names of the InfoCubes, make sure that flag Check conversion exit is set: choose Settings > User parameters and in tab Data browser make sure that the flag
is set.
� In column READMODE the read mode is specified:
H : Read mode Read data during navigation and expand the hierarchy
X: Read mode Read data during navigation
Blank: Read mode Read all data
123
SAP AG 2005, TEWA50 / 123
Table RSRREPDIR
� Table RSRREPDIR enables you to check the read mode of all your queries.
� To get only the queries that are currently active and usable in the system, restrict the
values of OBJVERS to "A" and OBJSTAT to "ACT".
� To see the correct names of the InfoCubes and the queries, make sure that flag Check
conversion exit is set: choose Settings > User parameters and in tab Data browser make sure that the flag is set.
� In column GENUID the query name is displayed.
� In column READMODE the read mode is specified:
H: Read mode Read data during navigation and expand the hierarchy
X: Read mode Read data during navigation
Blank: Read mode Read all data
124
SAP AG 2005, TEWA50 / 124
Query Design: General Recommendation
Create queries to start at an aggregated level
First drilldown may have to read a larger structure
Second drilldown may have to read a large structure but with increasing filter restrictions
Amount of data that has to be read
InfoCube
Aggregate 2
Aggregate 1
InfoCube
InfoCube
Using Aggregates No Aggregates available
InfoCube
� When you create a query, keep the following in mind:
�Building free characteristics into the query design is a good way to reduce the size of the result set of
each drilldown step
�Small result sets per query step can greatly improve performance
�Small result sets are also much easier to analyze
�If great detail is desired for a large amount of data, check if batch printing can be used instead of online
reporting
�If you use Web frontend, please check if you can use predefined Web queries from the reporting agent.
� The more selective your drill down so much better is the usage of indexes on database
level
125
SAP AG 2005, TEWA50 / 125
Other influences on aggregate usage
Database
Info Cube Aggregates
OLAPOLAP
Many other aspects influence aggregate usage:
� Virtual Characteristics and Key Figures
� Exception Aggregation
� Time-Related Currency Translation
� Calculation Before Aggregation
� Presentation Hierarchy and Filter
� Processing MultiCubes parallel or nonparallel
126
SAP AG 2005, TEWA50 / 126
Virtual Characteristics and Key Figures
Customer exit Virtual characteristics and key figures
allows customer defined calculations
Data is read at the level of detail that the exit requires
Info Cube Unusable Aggregate
Query contains virtual key figure, level of detail is 0CALDAY
0CALDAY *
0CALMONTH *
0CALYEAR *
0MATERIAL *
0CALMONTH *
0CALYEAR *
0MATERIAL *
� As of BW 1.2B, you can use enhancement RSR00002 to define and evaluate user-specific virtual characteristics and key figures. These characteristics or key figures must be defined within the InfoCube but they are not filled in the InfoCube. These characteristics or key figures are calculated in the ABAP coding in this enhancement.
This exit operates on records presenting the current drilldown status of the query.
� If the query uses the exit Virtual characteristics and key figures (enhancement RSR00002), the data is read at the required level of detail in the exit.
127
SAP AG 2005, TEWA50 / 127
Exception Aggregation
Key figures can have three different normal aggregations: SUM, MAX, and MIN
One characteristic may require a different aggregation (such as stocks and non-cumulative values). This characteristic is the reference for exception aggregation
Exception aggregation offers many types of aggregation:
� SUM, MAX, MIN, AVG
� Different averages and counters
� FIRST, LAST, Variance, …
� If exception aggregation is used, when you create a key figure, you must define a reference characteristic. This characteristic is the reference for the exceptional aggregation on this key figure.
� If the reference characteristic is a SAP delivered time characteristic, use the feature of non-cumulative values with change or inflow and outflow.
128
SAP AG 2005, TEWA50 / 128
Exception Aggregation
Example: MaxValue is a key figure with normal aggregation SUM and exception aggregation MAX on Calday
Calday Stock MaxValue
01/9/8 Enjoy AG 10
01/9/8 Win AG 20
01/9/9 Enjoy AG 15
01/9/9 Win AG 10
Fact table:
Query: Stock MaxValue
Total stock value
?
Order of aggregation of the OLAP processor:1) All normal aggregation2) Exception aggregation3) Aggregation of currency / unit
� The order of the aggregation of the OLAP processor (note 310791):
1. All normal aggregation (normally, the database either accesses aggregates or aggregates the data)
2. Exception aggregation (always done in the OLAP, never on the database)
3. Aggregation of currency / unit (always done in the OLAP, never on the database)
� In the example above, a query requires the maximum of a total stock value. In the example, information is available about two stocks during two days.
129
SAP AG 2005, TEWA50 / 129
Exception Aggregation: Correct Calculation
Exception aggregation MAX on CaldayThe order is important
Calday Stock MaxValue
01/9/8 Enjoy AG 10
01/9/8 Win AG 20
01/9/9 Enjoy AG 15
01/9/9 Win AG 10
Fact table:
Query: Stock MaxValue
Total stock value
30
01/9/8 30 = (10 + 20)
01/9/9 25 = (15 + 10)
SUM grouped by calday:
01/9/8 30 = (10 + 20)
01/9/9 25 = (15 + 10)
MAX of 01/9/8 and 01/9/9
First all normal aggregation, performed on the database
Then Exception Aggregation,
Aggregate has to contain 0CALDAY to be used
performed in the OLAP
Order of aggregation of the OLAP processor:1) All normal aggregation2) Exception aggregation3) Aggregation of currency / unit
� Normal aggregation is done by the database. Exception aggregation is done by the OLAP processor.
� All normal aggregation is performed on the database. The information is always read grouped by the reference characteristic (in this example 0CALDAY). For this reason, if the InfoCube contains a key figure with exception aggregation, the reference characteristic is automatically included in all aggregates. If queries on the InfoCube do not all contain key figures with exception aggregation, you can also create aggregates
that are summarized using the exception reference characteristic. To do so, use expert mode:
�In BW 2.xX/3.xX, select an aggregate in aggregate maintenance and choose Extras > Switch Expert
mode on/off; otherwise, enter "EXPT" in the OK code field.
� If exception aggregation is performed before normal aggregation, you get a different result. Exception aggregation (MAX on reference characteristic 0CALDAY) gives the result 20 for 0CALDAY 01/9/8 and 15 for 0CALDAY 01/9/9. Then normal aggregation
(SUM) gives the result 35.
� A high number of distinct values for the reference characteristic has a negative impact on query execution time.
130
SAP AG 2005, TEWA50 / 130
Exception Aggregation and Aggregates
When you create an aggregate for an InfoCube with exception aggregation on key figures,
reference characteristics are added automatically
� If exception aggregation is used, reference characteristics are added automatically to every aggregate in the aggregate definition.
� If a SAP delivered time characteristic is the reference characteristic, all time characteristics that can be derived from it are added automatically.
� Note 125681: Aggregates and exception aggregation
131
SAP AG 2005, TEWA50 / 131
Time-Related Currency Translation
Example: Time related currency translation is active on 0CALDAY Date Euro in Dollar
01/9/8 0.9
01/9/9 0.8
Date Revenue (in $)
Total for all days ?
Fact table:
Query:
Currency translation table
Order of aggregation of the OLAP processor:1) All normal aggregation2) Exception aggregation3) Aggregation of currency / unit
Date Salesperson Revenue (in Euro)
01/9/8 Adam 10
01/9/8 Bill 20
01/9/9 Adam 30
01/9/9 Bill 20
� In the example above, currency translation is performed for the InfoCube object 0CALDAY. Information is read at the level of the reference characteristic 0CALDAY.
132
SAP AG 2005, TEWA50 / 132
Time-Related Currency Translation
Currency translation on 0CALDAYThe order is important Date Euro in Dollar
01/9/8 0.9
01/9/9 0.8
Date Revenue (in $)
Total for all days 67
Fact table:
Query:
Currency translation table
Aggregate has to contain 0CALDAY to be used
Date Revenue (in $)
01/9/8 30
01/9/9 50
First all normal aggregation, performed on the database
Order of aggregation of the OLAP processor:1) All normal aggregation2) Exception aggregation3) Aggregation of currency / unit
Date Salesperson Revenue (in Euro)
01/9/8 Adam 10
01/9/8 Bill 20
01/9/9 Adam 30
01/9/9 Bill 20
An aggregate must keep information at daily level
Then currency translation, performed in the OLAP
Total Revenue = (30 * 0.9) + (50 * 0.8)
� Because currency translation is used, the data must be read from the database at daily level to get correct results.
133
SAP AG 2005, TEWA50 / 133
Calculation Before Aggregation
Setting in the BEx must be specified in the Query Designer for reusable calculated key figures
Month Material Price Amount
July Hammer 10 3
July Nail 20 4
July Pliers 10 1
August Hammer 10 4
August Nail 20 5
August Pliers 10 6
Sales
30
80
10
40
100
60
Month Sales
July 120
August 200
Calculated key figure: Sales = Price * Amount
InfoCube Fact table; all rows transferred to BW instance
Query result Sales per month
Month Sales
July 320
August 600Query result sales per month with calculation after aggregation
Month Price Amount
July 40 8
August 40 15
Sales
320
600
Calculation before aggregation
Calculation after Aggregation
Already aggregated rows transferred to BW instance
� Calculation before aggregation is a feature, that can be defined in the Query Designer at the properties of a calculated key figure, which can then be used in query. Note 152638 contains further information when Calculation before aggregation is/can be set. Use it when the formula can only be calculated before the data in this formula is aggregated.
� Example: You store the number of units you sold of a specific product and the price for each unit. To calculate the value of the products sold, you cannot add up all units sold and multiply them with the sum of all prices. Before you aggregate, you must multiply the
price with the number of units sold.
� Problem: If you use Aggregates and “Calculation after aggregation” you can receive
“wrong” values, which means other values than you expect. Reason: The sum of products is not equal to the product of sums.
� Note 460255
134
SAP AG 2005, TEWA50 / 134
If a characteristic is drilled down with a presentation hierarchy
And there is a filter on this characteristic from another hierarchy
Or there is an interval filter
Or another complex selection
Then a query must read the data at the lowest hierarchy level
If a query contains a calculated key figure with a formula variable to be replaced by an attribute
Then the data is read according the characteristic for this attribute(note 379832)
Presentation Hierarchy and Filter
� If a characteristic is drilled down with a presentation hierarchy, and if at the same time there is a filter on this characteristic from another hierarchy, or an interval filter, or another complex selection, then the OLAP engine drills down the nodes of the presentation hierarchy into leaves.
�In this case, aggregates cannot be used with the presentation hierarchy, even if the read mode of the
query is set to Read on demand when expanding the hierarchy.
� If a query contains a calculated key figure with a formula variable that is to be replaced by an attribute value, then the data is read by the database grouped according to the
characteristic belonging to the attribute (note 379832).
�In this case, the calculation takes place in the OLAP engine before aggregation.
135
SAP AG 2005, TEWA50 / 135
1 Basics About Aggregates
2 How to Detect Tuning Potential
3 How to Tune Query Performance
5 Basics ofAggregate Maintenance
Unit 3: How to Tune Query Performance
4 Basics about OLAP Cache
6 How to TuneAggregate Maintenance
3.1 How to Tune Query Performance on Query level
3.2 How to Tune Query Performance on Cube level
136
SAP AG 2005, TEWA50 / 136
Contents
� Tuning at Query Level
� Tuning at Cube Level
Objectives
At the end of this unit you will be able to:
� Tune aggregates at Query level
� Tune aggregates at Cube level
How to Tune Query Performance
137
SAP AG 2005, TEWA50 / 137
1 Basics About Aggregates
2 How to Detect Tuning Potential
3 How to Tune Query Performance
5 Basics ofAggregate Maintenance
Unit 3.1: How to Tune Query Performance on Query Level
4 Basics about OLAP Cache
6 How to TuneAggregate Maintenance
3.1 How to Tune Query Performance on Query level
3.2 How to Tune Query Performance on Cube level
138
SAP AG 2005, TEWA50 / 138
Aggregates Necessary
IF analysis at query level shows that aggregates are missing for
a specific query
THEN you can create aggregates based on:
� Knowledge of which objects are used in the query
� Suggestions generated automatically by the BW system
� Suggestions provided by the query monitor
� Suggestions derived from table RSDDSTAT orRSDDSTATAGGRDEF
� If you want to create aggregates to improve the performance of a specific query, you cancreate aggregates based on any of the following.
�Suggestions of the BW system, based either on data of BW statistics or on the design of the query
�Knowledge about your business, the design of the query, and the distribution of data in the InfoCube
�Information about specific queries from the query monitor (transaction RSRT) or the trace tool
(transaction RSRTRACE)
�Information about specific queries from the database tables RSDDSTAT and RSDDSTATAGGRDEF
139
SAP AG 2005, TEWA50 / 139
Automatic Suggestions: Query Definition
Suggestion based on the query definitions
Restrict to the queryyou are interested in
� To get suggestion based on the query definition, proceed as follows.
� In the aggregate maintenance screen, from the menu bar choose Propose > Propose
from query. Mark the query you are interested in.
� The system suggests the aggregates MIN 1 and MAX 1:
�MIN 1 is used to processes the start list (the first execution of the query).
�MAX 1 is used for every navigation step.
� If a query contains a lot of free characteristics, MAX aggregates can be as large as the InfoCube.
� If a query does not have any free characteristics, aggregates MIN and MAX are the same.
� The system identifies similar aggregates, and counts how often each aggregate is used.
The number of calls indicates the importance of an aggregate.
� This function is not possible for MultiCubes, you should use Propose from BW statistics
instead
140
SAP AG 2005, TEWA50 / 140
Suggestion based on the last navigation. Information is collected form the database tables RSDDSTATAGGRDEF and RSDDSTAT
Automatic Suggestion: Last Navigation
� You can get suggestions for the last navigation of a query. The information about the best possible aggregate is collected in the database tables RSDDSTAT and RSDDSTATAGGRDEF. Take care that no other user is executing a query or navigatingin a query as you work.
� Before you create the suggested aggregate, keep in mind the restrictions covered in thefollowing slides.
� This function is not possible for Multicubes
141
SAP AG 2005, TEWA50 / 141
Query Analysis-Tools
Trace Tools – RSRTRACE / RSRCATTTRACE
can be used to get a trace with all navigation steps a user performed, can be replayed for analysis purposes
Query Monitor – RSRT
can be used to get aggregates suggestions for the firstexecution of a query and all further navigation stepsthat a user performed
� A dialog box appears. This is the same as in the query monitor (transaction RSRT). However, here the dialog box appears for each navigation recorded in this trace.
142
SAP AG 2005, TEWA50 / 142
Query Monitor – RSRT / Overview
Log forgenerating
query
Additional helptexts for OLAP
Processor(learning path)
Propertiesof thequery / slide 4
Generatesthe query
again
Options duringexecution /
slide 5
Optionsfor querydisplay / slide 3
Detailedtechnical
informationabout the query /
slide 1
Jump to transaction
RSRCACHE
Executesthe query
Detailedperformanceinformation
slide 2
Query selection
143
SAP AG 2005, TEWA50 / 143
RSRT (1) – technical information
� Performance relevant information
�Read mode
�Calc before Aggregation
�Virtual Char, / Key figures
�Attributes in Calc. Key figures
�Currency translation
�Query can use Aggregates
�…
144
SAP AG 2005, TEWA50 / 144
RSRT (2) – performance information
145
SAP AG 2005, TEWA50 / 145
RSRT (3a) – options for query display
� List
�Direct display of data, restricted navigation possible, executed without frontend- dependent coding
� BEX Analyzer
�Executed using a simulation of the BEX frontend (sin´milar layout isused for displaying formats)
�Possibility to use the Context-menu with right mouse click like in the BEX Analyzer
�No navigation block available as in the real BEX Analyzer
� HTML
�Executed using a standard weptemplate and WEB-Interface simulation
�Possibility to use the navigation block
�Best way when no frontend access is possible
146
SAP AG 2005, TEWA50 / 146
RSRT (3b) – options for query display
Possibility to add URL parameters orto use the URL directly (without server
name)
� Examples for parameters:
�Language = EN
�Javascript = X
�…
147
SAP AG 2005, TEWA50 / 147
RSRT (4) – query properties
Detailed description about Cache mode settings in Chapter ‚Olap Cache‘
� Options
�Read mode
� H
� X
� A
�Cache mode
� 0
� 1
� 2
� 3
� 4
�Optimization mode
�0
�1
�9
148
SAP AG 2005, TEWA50 / 148
RSRT (5a) – Options in Execute+Debug
149
SAP AG 2005, TEWA50 / 149
RSRT (5b) – Options in Execute+Debug
1 2
43
1. Aggregates
a) Displays optimal
aggregate
b) Using NO aggregate
c) Select a specific
aggregate to be used
2. Multiprovider
a) Force serialprocessing
b) Multprovider Explain(see note xxxxxx)
3. Others
a) Don‘t use Cache
4. Database
a) Display SQL Statement
b) Display Explain plan
150
SAP AG 2005, TEWA50 / 150
RSRT (5c) – Options in Execute+Debug
151
SAP AG 2005, TEWA50 / 151
Aggregate <-> InfoCube
� If an aggregate is already used you will only see the aggregate name (number) at Strucuture to be read. You have to go to table RSDCUBE to link the aggregate name to the correct InfoCube.
� To find the link between aggregate number and InfoCube name, you have to go to SE16
and enter table RSDCUBE
� Then you should enter:
�INFOCUBE: Aggregate number
�OBJVERS: A = Active version
�OBJSTAT: ACT = Active object
�CUBETYPE: A = Aggregate
� In the displayed screen you can see
�INFOCUBE: Aggregate name
�BASISCUBE InfoCube name
152
SAP AG 2005, TEWA50 / 152
Derived Characteristics
Best possibleaggregate as displayed
0MATERIAL *0CALMONTH *
0MATERIAL *0MATERIALGROUP *0CALMONTH *0CALYEAR *
Add all characteristicsthat can bederived
Best possibleaggregate withmaximum usage
� RSRT suggests aggregates without taking dependence into account. For example, if 0CALMONTH is added into an aggregate with summarization type *, the aggregate is no larger if 0CALYEAR is added too. However, more queries may be able to use this aggregate when 0CALYEAR is added. The same applies for 0MATERIAL and
0MATERIALGROUP.
� If you get suggestions in the aggregate monitor, these derived characteristics are added automatically for the time dimension. In the query monitor, the characteristics of the time
dimension that can be derived are not added automatically.
153
SAP AG 2005, TEWA50 / 153
Derived Characteristics vs. Flat Aggregates
Add all
characteristicsthat can be
derived
BW 2.x BW 3.x
Reduced Aggregate Maintenance Costs using Flat Aggregates
Pro
Contra Number of Line Item Dimensions are restricted. Therefore using derived characteristics is restricted, too.
Consider FlatAggregateConcept
Re
co
mm
en
da
tion
s
“Flat Aggregates”
l If an aggregate has less or equal then 14 components, each component is put into a separate dimension (“Flat Aggregates”) and the dimensions (except of the package and unit) are marked as “Line Item”.
154
SAP AG 2005, TEWA50 / 154
Query Monitor: BW Statistics
For detailedinformationchoose Details
� The query monitor (transaction RSRT) offers the statistics about the execution you just started.
� In the example above, aggregates do not significantly improve the situation. More data isselected than necessary (20 times more). However, the database time and the total runtime are small.
155
SAP AG 2005, TEWA50 / 155
Trace Tool: RSRTRACE
User already activated for logging
Activates user BECKGE
Deactivates user MIHAN
Displays all existing logs
RSRTRACE
� The trace tool (transaction RSRTRACE) can be used to trace query executions includingnavigations and drilldowns. Unlike normal traces, these traces record activity in such a way that exactly the same activity can be restarted. Make sure that the trace for a user isnot activitated for a long period in a production system, since this will lead to a decrease
in performance.
� To display all the logs in your system, choose All Logs.
� The analyzed query is optimized for this special user and his drill down path. If otherusers behave different, you have to doublecheck the recommended aggregate.
156
SAP AG 2005, TEWA50 / 156
To get to the next screen, double-click on the row
To restart the trace, choose DB debugging
Trace Tool: Starting Traces
� In the list of logs, double-click on the log of interest and choose DB Debugging.
157
SAP AG 2005, TEWA50 / 157
CATT Trace Tool: Analyzing Traces
To analyze the trace, choose
Execute + Debug
RSRTRACE
RSRCATTTRACE
You should select ‘Show output table’to see the results
screen between the different navigation
steps
The process mode should be “blank”
You can rerun the trace using the same user as during the execution of
the query to prevent authorization problems
You need the Logno. from RSRTRACE
� You still have to use the trace tool (RSRTRACE) to activate the user and to record thetrace for a query.
� In the list of logs in the trace tool (RSRTRACE), select the lognumber you are interestedin.
� To analyze the trace you should now use the CATT trace tool (RSRCATTRACE).
�Insert the Log Number from your recorded trace in the trace tool (RSRTRACE)
�Choose ‚blank‘ as process mode.
�Select ‚Show output table‘ to show the result screen between every navigation step.
�Choose ‚Execute + Debug‘ to analyze the trace
� In the appearing popup select ‚Displays aggregate found‘
�You will get a popup showing the best possible aggregate for every navigation step
� You can also use this CATT trace tool to check the consistency of your aggregates. Therefore you have to select a different process mode, (Master mode). To get more
information, please check SAP Note 202469.
158
SAP AG 2005, TEWA50 / 158
Database Tables
The STATUID connectsthe tables RSDDSTAT and RSDDSTATAGGRDEF
The best possible aggregate contains those Info-Objects
Table RSDDSTAT
Table RSDDSTATAGGRDEF
� If there are queries with a high database runtime in table RSDDSTAT, you can check in table RSDDSTATAGGRDEF for the best possible aggregate for this query execution. The statistical record in table RSDDSTAT is linked by STATUID to the best possibleaggregate, which is stored in table RSDDSTATAGGRDEF.
159
SAP AG 2005, TEWA50 / 159
Creation of Aggregates from Query Definition
Material *
Country *
CalYear/Month F, 01.2001
Best possibleaggregate
Material *
Country *
CalYear/Month F, 01.2001
� You can also create aggregates for specific queries by implementing aggregates thathave the same (or similar) definitions as the query.
� In the example above, it may be good to use aggregation type * instead of F forCalYear/Month to enable other queries to use the aggregate. However, this depends on:
�The size of the aggregate
�How much the aggregate will be increased
�The design of the other queries
160
SAP AG 2005, TEWA50 / 160
Comparison of All Possibilities
Automatic suggestion based on query design
Automatic suggestion based on BW statistics
Aggregates created manually on query design
Can be performed without knowledge about the query
Can be performed without knowledge about the query
Knowledge about query design required
Query has to be defined but does not have to be executed
Query must have been executed, statistics flag needs to be turned on
Aggregates can be created for existing and planned queries
Takes into account how often a query is used
Optimizes navigational steps
Navigation steps can be optimized but complex
Should be used directly after a query is created
Should be used after statistical data is collected
� After statistics about the runtime are collected in RSDDSTAT, you should verify thesuggestions based on BW statistics.
161
SAP AG 2005, TEWA50 / 161
Switching Aggregates On/Off
Turns gray if aggregate is turned off
Turn aggregates on/off
� To test whether a specific aggregate improves performance, compare the run times withand without the aggregate. To turn the aggregate off, use the button shown.
� Aggregates that are turned off are still affected by the change run and the rollup.
162
SAP AG 2005, TEWA50 / 162
1 Basics About Aggregates
2 How to Detect Tuning Potential
3 How to Tune Query Performance
5 Basics ofAggregate Maintenance
Unit 3.2: How to Tune Query Performance on CubeLevel
4 Basics about OLAP Cache
6 How to TuneAggregate Maintenance
3.1 How to Tune Query Performance on Query level
3.2 How to Tune Query Performance on Cube level
163
SAP AG 2005, TEWA50 / 163
Creating Aggregates
IF analysis at InfoCube level shows that aggregates are
missing for an InfoCube
THEN you can create aggregates based on:
� Suggestions generated automatically by the BW system
� Your business knowledge about which objects are generally used for reporting
� To improve the performance of queries belonging to an InfoCube, you have two ways to create aggregates:
�Based on the suggestions generated automatically by the BW System, either from BW statistical data
or from the design of the queries belonging to the InfoCube.
�Based on your business knowledge (about the design of the InfoCube, the design of the queries, and
the distribution of data in the InfoCube).
164
SAP AG 2005, TEWA50 / 164
Automatic Proposals
You have various ways to get suggestions from the BW system
� Choose transaction RSDDV or use the administrator workbench to maintain your aggregates. In the aggregate maintenance screen, choose ‘Propose’ in the menu to get the drop-down menu displayed in the slide.
� There are different ways to get suggestions:
�Propose (statistics, usually query)
�Propose from query
�Propose from last navigation
�Propose from BW statistics (table)
�Propose from BW statistics (InfoCube)
165
SAP AG 2005, TEWA50 / 165
BW Statistics (1)
Suggestion based on the database tables RSDDSTAT/
RSDDSTATAGGRDEF
Suggestion based on the InfoCube
BW Statistics
Choose the period to be used for the proposals
Proposals can be restricted to queries with
a minimum runtime
Suggestion based on the last entry of the database
tables RSDDSTAT/RSDDSTATAGGRDEF
� You can get automatic suggestions based on the BW statistics. The proposals can be restricted to a specific runtime or to a specific period or both.
166
SAP AG 2005, TEWA50 / 166
BW Statistics (2)
Aggregates suggested from BW statistics get
the name STAT <N>
Save the aggregate definition without
activation
Save and activate the aggregate
Aggregate definition is checked automatically
� The aggregates proposed on the basis of collected statistical data (BW statistics) are each given a name STAT <number>.
167
SAP AG 2005, TEWA50 / 167
Derived Characteristics
Best possible aggregate as displayed
0MATERIAL *0CALMONTH *0CALYEAR *
0MATERIAL *0MATERIALGROUP *0CALMONTH *0CALYEAR *
Add all characteristicsthat can be derived
Best possible aggregate with maximum usage
� The automatic suggestions take dependencies of the time dimension into account. For example, if an 0CALMONTH is added into an aggregate with summarization type *, the aggregate is no larger if 0CALYEAR is added too. However, more queries may be able to use this aggregate when 0CALYEAR is added. For this reason, 0CALYEAR is added
automatically to the suggested aggregates.
� The same applies for 0MATERIAL and 0MATERIALGROUP. However, the automatic suggestion tool does not know the dependencies for dimensions other than time. You
should add derived characteristics manually.
168
SAP AG 2005, TEWA50 / 168
Derived Characteristics vs. Flat Aggregates
Add all characteristics
that can be derived
BW 2.x BW 3.x
Reduced Aggregate Maintenance Costs using Flat Aggregates
Pro
Contra Number of Line Item Dimensions are restricted. Therefore using derived characteristics is restricted, too.
Consider Flat AggregateConcept
Re
co
mm
en
da
tion
s
“Flat Aggregates”
l If an aggregate has less or equal then 14 components, each component is put into a separate dimension (“Flat Aggregates”) and the dimensions (except of the package and unit) are marked as “Line Item”.
169
SAP AG 2005, TEWA50 / 169
Query Definition (1)
Suggestion based on the query definitions
Proposals can be restricted to specific queries; mark all (used queries) if you want to get general suggestions
� The tool proposes two aggregates for each query:
�Aggregate MIN <number>. The query needs this aggregate to process the start list.
�Aggregate MAX <number>. Data for each navigation step is read from this aggregate.
170
SAP AG 2005, TEWA50 / 170
Aggregates suggested from BW statistics get the names
MIN <N> and MAX <N>
Query Definition (2)
� The tool proposes two aggregates for each query:
�Aggregate MIN <number>. The query needs this aggregate to process the start list.
�Aggregate MAX <number>. Data for each navigation step is read from this aggregate.
� If a query contains a lot of free characteristics, the MAX aggregates may be as large or almost as large as the InfoCube. If a query does not have any free characteristics, then the aggregates MIN and MAX are the same.
� The system identifies similar aggregates, and counts how often each aggregate is called.
If all queries are used in the same way, the number of calls indicates the importance of an aggregate.
171
SAP AG 2005, TEWA50 / 171
Combination
This will decrease the number of suggested aggregates
Suggestion based on the BW statistics or query definitions
� If in the dialog box Propose you choose (statistics, usually query), you can restrict the suggestions for a specific period or a specific runtime. The suggestions are based on the BW statistics. If no statistics are available, the suggestions are derived from the query definitions.
� If the system creates too many suggestions, you can reduce them by choosing Optimize. The system then puts some aggregates together to reduce the total number. The resulting aggregates may be less effective, but a balance must be made between the
number of aggregates and performance.
172
SAP AG 2005, TEWA50 / 172
Generate Proposal
If no aggregates are created for an InfoCube, a dialog box appears to ask you, if the system should propose some
Generate proposal will choose the option ‘Propose (statistics, usually query)’
� If you enter the aggregate maintenance screen and there are no aggregates defined yet, a dialog box appears and asks if the system should propose some.
173
SAP AG 2005, TEWA50 / 173
Query Perspective
Cube design
Query design
Region *Country *Salesman *Product *Customer *Day *Month *Year *
Region *Country Salesman *Product Customer Day Month *Year *
Region CountrySalesman *ProductCustomer *DayMonth *Year *
Region CountrySalesman *Product Customer Day MonthYear *
Aggregate for all queries
Region * CountrySalesman *Product Customer * DayMonth *Year *
� You can also create aggregates by defining them yourself, using knowledge about the objects and distribution of data in your InfoCubes and the design of your queries. You can find out if you can create one or several aggregates to tune all or specific queries.
174
SAP AG 2005, TEWA50 / 174
Comparison of All Possibilities
Automatic suggestion based on query design
Automatic suggestion based on BW statistics
Aggregates created manually on query design
Can be performed without knowledge about queries
Can be performed without knowledge about queries
Knowledge about query design required
Queries have to be defined but do not have to be executed
Queries must have been executed, statistics flag need to be turned on
Aggregates can be created for existing and planned queries
Takes into account how often a query is used
Optimizes navigational steps
Navigation steps can be optimized but complex
Should be used directly after queries are created
Should be used after statistical data is collected
� Each way to get suggestions has advantages and disadvantages. Directly after the creation of an InfoCube and of queries for this InfoCube, you should either create aggregates derived from the automatic suggestions based on query design or create them manually, based on the query design.
� After statistics about the runtime are collected in RSDDSTAT, you should verify suggestions based on BW statistics.
175
SAP AG 2005, TEWA50 / 175
1 Basics About Aggregates
2 How to Detect Tuning Potential
3 How to Tune Query Performance
5 Basics ofAggregate Maintenance
Unit 4: OLAP Cache
4 Basics about OLAP Cache
6 How to TuneAggregate Maintenance
176
SAP AG 2005, TEWA50 / 176
OLAP Cache – Architecture Overview
DATABASE
Server
dependent
Server
independent
DB Buffer
ANY FRONTEND
Flat File
ANY SERVER
Cross-ServerFlat File
Main Memory
Export/Import Shared Memory Buffer
OLAP Cache
APPLICATION SERVER
OLAPProcessor
3rd PartyFrontends
WEBQueries
BEX Analyzer
Cluster Tables
BW 3.0B SP19:For each querythe cache mode and the cache
medium can bedefined !
�BW3.0B up to SP18:
�If the persistent mode is used, you have to decide wether the OLAP Cache uses database
cluster tables or flat files for storing query results outside of the main memory of an
application server.
�BW 3.0B SP19 / BW 3.1C SP13 / BW 3.5 SP02 (Note 683194):
�Since note 683194 the above restrictions aren‘t valid anymore. The following functionalities
were added to the OLAP Cache:
�Additional persistence mode - Transparent table with BLOB (Binary Large Object).
�Enhancement of the RSRT query properties: For each query the cache mode and the
persistence medium can explicitly chosen.
�Crystal Report does not use the OLAP Cache. This is because of the nature of reports
done with Crystal Reports which are normally highly parameterized reports. Because of this
parameterization the cache would not be used anyhow in most of the cases. That´s why
there is a special feature for Crystal Reports. This feature can be accessed in the Reporting
Agent. The last section in the Reporting Agent is called "Crystal Reports".
�The OLAP Cache can’t be used by queries called ‘RSDRI_QUERY’ (using the interface
RSDRI_INFOPROV_READ e.g. InfoSpokes).
177
SAP AG 2005, TEWA50 / 177
OLAP Cache or “(Cross-)Transactional Cache”
� Buffer containing query results
� Provides buffered results for users executing similar queries
� Query results are stored in the memory of the application serverand additionally on the database or in files
� Different cache modes allow a specific usage of the cache foreach query
� The cache can be switched off for specific queries
� Query results are stored in a compressed format
� Cache data is always consistent
� The OLAP Cache can be used by web templates and ODS queries
Available with BW 3.x Transaction RSRCACHE
� The OLAP Cache is available from release BW 3.x. As of BW 3.0A SP11=BW 3.0B SP04
you can reach the OLAP cache monitor (Transaction RSRCACHE) from the SAP Easy
Access screen in the Business Explorer � BEx Monitor � OLAP: Cache Monitor.
� The OLAP Cache buffers query results and provides them for different users executing
the same queries or subsets of them. Query results with their navigation statuses can be
stored in the main memory of the application server, database cluster tables, BLOB
tables and flat files.
� You can define on query level which of the five cache modes is used: Cache inactive,
Memory Cache without swapping, Memory Cache with swapping, Cluster/Flatfile Cache
per application server and Cluster/Flatfile Cache cross-application server.
� Due to compression the actual memory requirement for the OLAP Cache is usually lower
than the total size of (uncompressed) runtime objects of a query.
� The OLAP Cache for queries on a specific InfoCube is invalidated when new data is
uploaded into the respective InfoProvider or any other data relevant event occurs i.g.
master data change of navigational attribute. Hence, the data displayed is always up-to-
date and consistent with the original data in the InfoProvider. The OLAP Cache for a
specific query gets also invalidated when the query is re-activated, but please note that
the data is still physically stored in the OLAP Cache though it isn’t used anymore.
� We recommend using OLAP Cache whenever possible. It will substantially improve the
average response time of queries. However, for queries that are executed only once (or
very rarely) or for very small queries (e.g., DB time < 0.10 sec), the cache overhead could
lead to slightly worse performance.
178
SAP AG 2005, TEWA50 / 178
OLAP Cache – Goals and Advantages
� Reduces the load on the database server
� Reduces the displacement within the database buffer
� Reduces the load on the network
� Reduces the runtime on the database server
� Reduces the runtime of the OLAP processor
� Query results can be precalculated
� Reduces total query runtime
� Allows application server dependent tuning
� Reduces number of aggregates and their maintenance costs
� Reduces varying query execution runtimes
� By the usage of the OLAP Cache the load on the database server is reduced. Cached
queries especially those which are cached in the main memory and in flat files don’t
cause a database access or at least the database accesses are reduced. Because of this
reduction there are less displacements in the database buffer and therefore the cache
has an positive impact on the overall database performance and the network load.
� While query execution the runtime for reading the data is reduced by the cache especially
for queries reading a huge volume of data. Besides the data obtaining the cache can
reduce the OLAP processing time – it depends on the query (formulars, selection,
authority-check etc.) how much the OLAP processing runtime is reduced. In general the
total runtime of a query can be reduced for a query which can be cached.
� A precalculation of a query at night allows the first end user at the morning to have a good
query performance. Grouping the end users on certain application servers the cache
allows an application server dependent tuning. Additionally the usage of the OLAP Cache
can give you the chance to reduce the number of aggregates and therefore it’s possible
that your total runtime of the aggregate maintenance is reduced.
� Especially if queries are cached persistently in flat files, you can exspect more constant
query execution runtimes because of the more simplified data access.
179
SAP AG 2005, TEWA50 / 179
OLAP Cache – Customizing
Transaction RSCUSTV14 or via transaction RSRCACHE
OLAP Cache Size
The whole OLAP cachecan be switched off here
Local Cache size
Proposal valuefor queries:
Inactive, Flat File or Cluster Table
Cross-Applicationserverindependent file
Applicationserverdependent file
� By the flag ‘Cache inactive’ the whole OLAP Cache can be switched off which includes the invalidation of the whole OLAP Cache.
� There are two types of caches: The local cache and the transactional cache (OLAP Cache). The local cache belongs to a query session and therefore can’t be used by other sessions. The OLAP cache can store query data in the main memory of the application server and can have a swap file, use a swap cluster table or a BLOB table.
� Either the global cache, or if this is not possible or desired (e.g. cache inactive, query not cachable, InfoProvider/Query was deactivated or for Remote InfoCubes), the local cache is used by the system.
� Irrespective of this the global cache also shares some properties of the local cache: For instance, data is similarly retained in the roll area as long as it is required by the OLAP processor. On the other hand, the local cache size also has significance in the global cache: With both types of cache, cache objects that are no longer needed are deleted from the roll area if the local cache size is exceeded. However, as deleting objects is only possible with objects that are no longer required by the OLAP processor, the total size of cache objects in the roll area can exceed the local cache size.
� The Persistence Mode specifies, if query results that can’t be cached in the main memory anymore are deleted or can be written into a file or a cluster table. Additionally it defines how the results of queries with cache mode 3 and 4 are stored. But this customizing persistence medium maintained here is just a default value if the persistence medium was not defined explicitly on the query !
� Flat File NameLogical filename for the application server dependent cache data.
� ComprehensiveFileLogical filename for the cross-application server cache data.
180
SAP AG 2005, TEWA50 / 180
OLAP Cache – Main Memory
Delete
content
Buffer
Monitoring
Tool
Tune Summary
ST02
Directories
AL11
Logical
filenames
FILE
OLAP Cache
Parameter
Shared MemoryIndicators
� Location of the OLAP Cache:The OLAP Cache is located in the Export/Import buffer SHM. You can find parameter
setting recommendations in note 192658.
� Runtime Object:The defined cache size, current cache size and the number of cache entries is displayed
here. It’s recommended that the Export/Import Shared Memory is bigger than then the
OLAP Cache size, because otherwise the OLAP Cache is limited by the setting of the
Export/Import SHM but not of the OLAP Cache size. Because the Export/Import Shared
Memory can be used by other applications too, the idea is that the OLAP Cache size
defines how much free Export/Import Shared Memory can be used by the OLAP Cache.
Check if the Export/Import Shared Memory size (rdsb/esm/buffersize_kb) is bigger than
the OLAP Cache size. By default, the Shared Memory size is very small. We recommend
an initial size of 40 to 100MB.
� Shared Memory:Buffer Poll Time is the timestamp of the last check for free entries in the Export/Import
Shared Memory. Because this check is expensive there is a default delay of five minutes
until the next Shared Memory access will refresh this statistics.
Buffer Reserved is the percentage of occupied Export/Import Shared Memory, which can
be a limit for the OLAP Cache size too. In detail it is the maximum percentage of
occupied Export/Import Shared Memory and occupied entries.
Buffer Setting Cache is the percentage of OLAP Cache Objects stored in the Shared
Memory. Detailed: Size of OLAP Cache Objects / Size of all Shared Mem. Objects * 100.
181
SAP AG 2005, TEWA50 / 181
OLAP Cache – Export / Import Shared Memory
Calculation ofCalculation ofCalculation ofCalculation ofBuffer Reserved:Buffer Reserved:Buffer Reserved:Buffer Reserved:
a)a)a)a) Memory: 15281 / 16304Memory: 15281 / 16304Memory: 15281 / 16304Memory: 15281 / 16304~94% free~94% free~94% free~94% free~6% occupied~6% occupied~6% occupied~6% occupied
b)b)b)b) Number of entries: 1554 / 2000Number of entries: 1554 / 2000Number of entries: 1554 / 2000Number of entries: 1554 / 2000~78% free~78% free~78% free~78% free~22% occupied~22% occupied~22% occupied~22% occupied
Result = max. occupied = 22%Result = max. occupied = 22%Result = max. occupied = 22%Result = max. occupied = 22%
� Besides the limit of free memory and free entries in the Export/Import Shared Memory,
the maximal size of an OLAP Cache object is theoretically limited: An OLAP Cache object
must not allocate more than 20%-25% of the Export/Import Shared Memory size.
� The size of the Shared Memory shown on the slide (16.304.000) is the allocated memory.
To be precise not this value is taken by the system, but the available size: → ST02 →
Double click “Exp./Imp/SHM” - Here the real available memory is displayed.
� Parameter: rsdb/esm/mutex_n
Specifies the number of mutexes that are used for synchronisation of the ESM buffer. If
the value is set too low, the degree of parallelism can drop. If it is set too high, too much
memory space is used. If the values is zero (default), then a value is selected that allows
the greatest possible amount of parallelism (with an increased memory requirement).
Because of the parallel access it’s recommended never to change the default value 0.
182
SAP AG 2005, TEWA50 / 182
InfoProvider Properties - Default Cache Mode
Customizing or transaction RDMD
� By transaction RDMD you can define the default cache mode of an InfoProvider. Everynewly created query will get this cache mode.
� Cache Validity:This setting defines the cache retention period in seconds for specific InfoProviders. By default, queries on InfoProviders in which changes are made that are not controlled by the BW system (virtual InfoCubes, transactional ODS objects) are not contained in the OLAP Cache (validity: 0 sec.). The cache validity of queries in other InfoProviders is determined automatically using the time stamp from the last changes to the meta, master and transaction data.
� Cache Mode:Five cache modes are available as a default setting for newly created queries. Choosing cache mode (0) you can deactivate the usage of the OLAP Cache. Cache mode (1) stores the query results in the main memory of the application server. The LRU algorithmus determines which query is displaced if there isn’t enough space left. Cache mode (1) stores displaced entries in a flat file, cluster table or BLOB table. Cache mode (3) stores the data always persistently in the Cluster-Table RSR_CACHE_DB_IX, in a file on the application server (The filename contains the name of the server) or in the BLOB table (Oracle only). The advantage of a Cluster-Table compared with a regular query execution is that the Cluster-Table is buffered and therefore not the whole star schema has to be read. Cache mode (4) stores the data persistenly in the Cluster-Table RSR_CACHE_DBS_IX, in a file which can be accessed by all application servers or in a BLOB table (Oracle only).
�
Cache Mode (3) & (4): These cache modes do not store data separately for each instance, but for each application server. This means having only one large server with more than one instance installed, there is no difference between cache mode 3 and 4.
183
SAP AG 2005, TEWA50 / 183
Query Properties – Cache Mode
Transaction RSRT
Note 683194 OLAP: Enhanced cache function for 3.0B Support Package 19
� Cache and Persistence mode of a query:The cache and persistence mode of a query define how the OLAP Cache is used.
� This was enhanced by the mode ‚Transparent table with BLOB (binary large object)'. With
large result sets, this can improve the performance compared with the cluster table
because fewer database operations have to be executed. With smaller result sets, the
cluster table may be an advantage because BLOB field cause database in a sided way a
larger maintenance effort (details can be found in note 683194).
� Cache Validity:Please note that the period of validity can‘t be changed anymore after a query is created.
� Queries containing a Virtual Key Figure or a Virtual Characteristic:By default the OLAP Cache is inactive for queries which contain a virtual key figure or
characteristic. If you want to use the OLAP Cache for such a query you have to make
sure that the Customer-Exit calculates the data cache independent. Otherwise the reports
can contain inconsistant data. Under this prerequisites you can here switch on the OLAP
Cache for such a query.
� Most Recent Queries on Transactional InfoCubes:As of BW 3.0B Support Package 15 (or BW 3.1C Support Package 09), the OLAP cache
is also used (if it was activated) with Most Recent Queries (Note 650512).
184
SAP AG 2005, TEWA50 / 184
What is the best Cache Mode ?
Fatest is always the usage of main memory
� Main Memory Cache:Of course the fastest way is to use the main memory cache mode. If you use it with
swapping it is recommended to choose the persistent mode Flat File instead of Cluster
Table to avoid additional load on the database.
� Cluster / Flat File Cache:Caching a query in a Cluster Cache means that the query result is only stored in this
database table, but never in the main memory of an application server. The advantage in
contrary to a regular query execution is that the star join access on the database is
replaced by a simply access to a clustered table. Especially for large queries reading a
huge volume of data this cache mode is recommended.
� Logon Groups:By logon groups you can optimize the OLAP Cache usage to avoid cache swapping. End-
Users executing the same queries should work together on one application server.
185
SAP AG 2005, TEWA50 / 185
OLAP Cache – Buffer Objects
You can find a detailed explanation in the SAPNet -> alias ‚BW‘ -> Documentation -> 3.x Documentation Enhancements -> 3.0B - SP15 BExMonitor
Cache mode persistent: Set if data must be written to persistent media before replacement
Dirty-Flag
Data were written into the OLAP Cache and cache mode is persistentWrite-Flag
Data were read from the OLAP CacheRead-Flag
Data is stored in a persistent mediaSwapped
� From BW 3.0B SP18 / 3.1C SP11 the ‚Buffered Objects‘ information for queries cached
persistently is working.
� If the persistence mode file, database cluster or BLOB is used, it‘s possible that cache
data which isn‘t valid anymore is still stored. This can be the case if queries are deleted or
hierarchies are changed. You can see such entries in the ‚Buffer Objects‘ without having
a name. For queries using the cache mode ‚Main Memory Cache without swapping‘ this
data is just replaced after some time.
� The column size displays the compressed size of the stored data. Because of control
data the real size in the Export / Import Shared Memory Buffer is a bit higher.
186
SAP AG 2005, TEWA50 / 186
OLAP Cache – Object Hierarchy
Key of an OLAP Cache Object
1. Technical Query Name
2. Variables not changeable during navigation
3. Selection/Data The OLAP Processor sometimes treats changeable variables as not changeable ones e.g. all filter
variables.
� The OLAP Cache Key of an cache objects consists of the query-id (CUBENAME/QUERYNAME), variables not changeable during navigation and selections/data. This means the number of cache objects belonging to one query depends (in general) on the number of different filter restrictions (variables not changeable during navigation) and selections/data used.
� To improve the access to the OLAP Cache you should avoid the usage of variables not changeable at query navigation. Due to logical restrictions the OLAP Processor sometimes treats changeable variables as not changeable variables e.g. if it is used as a filter variable. Therefore the usage of filter variables can have a negativ impact for the OLAP Cache or to be more precise causes a higher amount of cache objects.
� The OLAP Processor always accesses only one cache object for a query execution/navigation step. This means the OLAP Cache can only reuse data if all required data can be derived from the stored cache objects, otherwise new cache objects are created.
� Because the cached data consists of the final query output, a subset of data of one cache object can only be used if there isn’t a logical restriction regarding the aggregation of data.
187
SAP AG 2005, TEWA50 / 187
OLAP Cache – Object Usage
Data is read from the OLAP Cache if the final query result can be retrieved by cache objects of one Hierarchies/Variables node:
Hierarchies/Variables must be identicalSelection/Data must contain all required data
‘No of Read Access’ = OLAP Processor Reads
� As a general rule you can say:
All query result cells must be calculatable by the cells stored in the cache objects of one
Hierarchy/Variable level.
� Special rules:
� From a big list of cells a small can be filtered if the relevant characteristics are drilled
down in the cache object.
� If characteristics are drilled down in a cache object, but not required by the query, they
can still be aggregated to get the required the information.
� During a query execution more than one Selection/Data entry can be created. With the
implementation of note 812562, this kind of precalculation (which is already done during
the first query execution) is reduced. Nevertheless depending of the number of drilled-
down characteristics there can be several Selection/Data objects created and accessed
during query execution.
� During navigation it’s possible that the OLAP Processor only accesses a
Hierarchy/Variables node, but not a Selection/Data object.
� The ‘No of read Accesses’ doesn’t correspond to the number of query execution, but to
the number of OLAP processor accesses. If you want to know if a query reads its data
from the cache, please of a look at statistics column QDBSEL of table RSDDSTAT.
Another possibility is to perform a SQL Trace during the run of the query.
188
SAP AG 2005, TEWA50 / 188
OLAP Cache – Reporting Authorizations
How do reporting authorizations influence the usage of the OLAP Cache ?
Handling of reporting authorizations by the OLAP Processor:
If a user hasn’t got enough reporting authorizations the query isn’t executed at all, but he gets a message like “You haven’t got enough authorities.”
For the OLAP Cache this means that the number of cache entries can increases with the number of different authorizations used.
Especially by the usage of so called “Authorization Variables” which are calculated by runtime, the number of OLAP Cache entries can increase
significant.
� “Authorization Variables” calculate the reporting authorizations of an enduser at runtime.
In general they are implemented as hidden filter variables. They are used because an
enduser often doesn’t know which reporting authorization he has and therefore he hasn’t
got any chance to execute a query (because he always is specifying selection criterias he
hasn’t got authorizations for).
189
SAP AG 2005, TEWA50 / 189
OLAP Cache – Display Attributes and Texts
Display attributes and text can’t be stored in the OLAP Cache
Cause:Display attributes and texts are added to the query result by the frontend. Therefore the OLAP processor can’t store these kind of objects in the OLAP Cache.
Buffering is not allowed for these time-dependent tables.
.. are single record buffered and therefore the accesses are
in general very fast.
Time-DependentMaster Data and Texts
Time-IndependentMaster Data and Texts
The performance of a “mass display” of time-dependent display attributes or texts can not be improved by the OLAP Cache.
190
SAP AG 2005, TEWA50 / 190
OLAP Cache – Deletion of old cache entries
Note 784979 „Deletion of old cache entries”
Old cache entries are not deleted automatically in the storage media of the OLAP cache, and, in particular, not in
the persistent media.
Manual deletion is too time consuming and difficult
Program: RSR_CACHE_RSRV_CHECK_ENTRIES
OLAP Cache entries on a persistent media which are not used anymore are displayed without a name in the olap cache
object overview
� Old cache entries are caused e.g. by query deletion or a change in a hierarchy.
191
SAP AG 2005, TEWA50 / 191
OLAP Cache – Important Notes
Note 822302 OLAP CACHE for remote providers
Note 812562 Cache problem large directory
Note 809324 OLAP_CACHE parameter to control the filling of OLAP cache
Note 809305 OLAP cache is not always filled
Note 807967 Cache aus Reporting Agent füllen
Note 798843 Query with calculated values list and OLAP cache
Note 798800 Query key date is ignored in the OLAP cache
Note 791065 OLAP cache with flat files -> cache is not used
Note 790818 Currency transl. (conversion to target curr.); incorr. date
Note 784979 Deletion of old cache entries
Note 781714 Displaying read counter and date in the Cache Monitor
Note 779015 Incorrect invalidation of OLAP cache by TODS objects
Note 768719 Preliminary calculated HTML document referenced incorrectly
Note 766431Cache displacement (shared memory) does not work correctly
� Old notes:
� Note 755501 No data in a very specific situation
� Note 753770 No data in a very specific situation
� Note 751402 Cache use: Can change queries w/ variables during navigation
� Note 733296 Characteristic as InfoProvider: Time stamp for OLAP cache
� Note 730949 Most recent queries return incorrect data for active cache
� Note 730236 Exchange rate entry does not invalidate OLAP cache
� Note 712630 No data in certain situations
� Note 700480 OLAP: Cache problem with variables with replacementpath
� Note 692937 OLAP: Cache problem for variables with replacementpath
� Note 681629 OLAP: Cache does not save empty resulting quantities
� Note 679604 OLAP: Error in cache invalidation
� Note 676572 OLAP: No data with hierarchy filtering on duplicatedleaf
� Note 667523 OLAP: Cache performance
� Note 661667 OLAP cache: Error in cache mode 3 (application server)
192
SAP AG 2005, TEWA50 / 192
1 Basics About Aggregates
2 How to Detect Tuning Potential
3 How to Tune Query Performance
5 Basics ofAggregate Maintenance
Unit 5: Basics of Aggregate Maintenance
4 Basics about OLAP Cache
6 How to TuneAggregate Maintenance
5.2 Rollup of Aggregates
5.3 Changerun
5.1 General Strategy
193
SAP AG 2005, TEWA50 / 193
Changes to the InfoCube or to Master data
� To achieve read consistency, any changes in the fact table of the InfoCube and any changes to hierarchies or navigational attributes must be reflected in the aggregates
Customer Industry
InfoCubeAggregate
Changes to navigational attribute Industrynot yet available for reporting
New transactional data in the InfoCube not yet available for reporting
� Read consistency is a must in the BW system. Whether or not a query has to access the InfoCube or can access an aggregate, the results must always be consistent. The aggregates store the same information, only in an aggregated way.
� After the upload of new transactional data, more transactional data is stored in the InfoCube than in the aggregate. To prevent inconsistencies, this data is not available immediately for reporting.
� Example: A query can access the aggregate for the initial screen to provide information summarized for the country hierarchy level 2 (continent):
� Sales in Europe: 30
� Sales in America: 50
� When you expand the hierarchy for continent Europe, the query accesses the InfoCube
to read the information at country hierarchy level 1 (leaf level). Newly uploaded transactional data shows, that total sales for Italy and Germany are higher than the sales for Europe:
� Sales in Germany: 20
� Sales in Italy: 20
� After the change of master data, inconsistencies occur between the data stored in the master data tables and the dimensions that contain navigational attributes affected by the changes. Those changes are only made available for reporting after they are reflected in
the aggregates.
194
SAP AG 2005, TEWA50 / 194
Rollup and Changerun (Master Data Activation)
Definition: Rollup
To apply the newly uploaded transactional data to the aggregate
Definition: Changerun (Master Data Activation)
To activate the changes of master data and hierarchies
� During the change run, all aggregates containing navigational attributes and/or hierarchies are realigned
� New data packets / requests that are loaded into the InfoCube cannot be used at first for reporting if there are aggregates that are already filled. The new packets to be added must first be written to the aggregates by a rollup.
� Aggregates that contain navigational attributes and/or hierarchies are affected by any change of master data.
� A Rollup affects only those Aggregates belonging to the InfoCube the data is loaded in.
� A Changerun affects all Aggregates over all InfoCubes which contain the changed
Attribute or Hierarchy.
195
SAP AG 2005, TEWA50 / 195
1 Basics About Aggregates
2 How to Detect Tuning Potential
3 How to Tune Query Performance
5 Basics ofAggregate Maintenance
Unit 5.1: General Strategy
4 Basics about OLAP Cache
6 How to TuneAggregate Maintenance
5.2 Rollup of Aggregates
5.3 Changerun
5.1 General Strategy
196
SAP AG 2005, TEWA50 / 196
Why Are Too Many Aggregates Harmful?
Reduced retrieval Cost
Cost of aggregate maintenance
The goal of aggregates is to balance
� Aggregates reduce query cost by reducing the amount of data used in the selection operation.
� However, a significant overhead in updating of aggregates is generated:
� When new data is loaded, aggregate rollup must take place.
� Changes to master data and hierarchies require that all dependent aggregates be recalculated by
calculating the differences (delta) or by rebuilding.
� Disk space is needed for the aggregates.
� Factors involved:
� Frequency of changes that will cause recalculation
� Availability of time to run the recalculation: no master data update or hierarchy update can take place
during a change run.
� Changed aggregate data is not available by query until recalculation is complete.
� Reporting on the old master data and hierarchies is possible during recalculation.
� You should delete unused aggregates.
197
SAP AG 2005, TEWA50 / 197
Time Frame
00 02 04 06 08 10 12 14 16 18 20 22 24
Period of time
14 hours online reporting;
current data in aggregates needed
Rep
ort
ing
Up
load 3 hours
data load
Ag
gre
gat
e
mai
nte
nan
ce
7 hours available for aggregate maintenance
� A specific time frame is available for aggregate maintenance. This may depend on whether aggregate maintenance is done during a working day or over a weekend.
198
SAP AG 2005, TEWA50 / 198
Time Frame: Problem
14 hours online reporting;
current data in aggregates needed
3 hours data load
11 hours needed for aggregate maintenance
Current data not available
Query performance harmed
00 02 04 06 08 10 12 14 16 18 20 22 24
Period of timeR
epo
rtin
gU
plo
ad
Ag
gre
gat
e
mai
nte
nan
ce
� Aggregate maintenance should be finished in the time frame available for it.
� If aggregate maintenance takes longer:
�Data is not available for reporting:
The new data is not available for reporting. Master data changes are only available after the change run
is finished. Uploaded transactional data is only available after the rollup.
�Performance:
Aggregate maintenance causes a high load on the system (CPU and memory consumption, I/O load).
This decreases performance for all users doing reporting.
199
SAP AG 2005, TEWA50 / 199
Monitoring and Performance Tuning Cycle
In a data warehouse environment, performance tuning is an ongoing exercise
With new queries and new cubes introduced, the system must be monitored and tuned
Aggregates created and utilized today may not be used at all for months, due to such changes in query patterns as:
- Different selections
- Different drilldowns
- Different navigations
Since volumes of data are loaded regularly, what is good now may not be so next month
� This section lists the steps and areas that are generally covered in monitoring and tuning a BW system, specifically in the area of aggregates.
200
SAP AG 2005, TEWA50 / 200
Analyzing Long Running Queries (1)
Detect and analyze
expensive query
� Detect unreported expensive queries with high database time. These can be caused by missing aggregates.
� By analyzing table RSDDSTAT and BW Statistics Cube, you can detect queries with:
� Long response time
� High database load
� High database reads
� When analyzing performance statistics gathered in table RSDDSTAT, watch out for:
� High ratio of QDBSEL over QDBTRANS
� High QTIMEDB
� Missing aggregates
� Other than analyzing table RSDDSTAT, you can use the delivered BW Statistics InfoCube (0BWTC2_C01). For BW 1.2B, the BW Statistics InfoCube is 0BWTC_C01.
201
SAP AG 2005, TEWA50 / 201
Analyzing Long Running Queries (2)
Analyze and
Create aggregate(s)
Detect and analyze
expensive query
� Create aggregates based on earlier analysis.
202
SAP AG 2005, TEWA50 / 202
Analyzing Long Running Queries (3)
Monitor query
performance
Analyze and
create aggregate(s)
Detect and analyze
expensive query
� Test and evaluate newly created aggregates.
� Compare times to see if query performance has improved.
� Monitor usage of aggregates.
� How effective is an aggregate in improving query runtime?
203
SAP AG 2005, TEWA50 / 203
Analyzing Long Running Queries (4)
Monitor aggregate
maintenance
Monitor query
performance
Analyze and
create aggregate(s)
Detect and analyze
expensive query
� Monitor rollup time.
� Find unnecessary aggregates.
204
SAP AG 2005, TEWA50 / 204
Analyzing Long Running Queries (5)
Delete
unnecessary
aggregates
Monitor aggregate
maintenance
Monitor query
performance
Analyze and
create aggregate(s)
Detect and analyze
expensive query
� Based on the analysis work carried out in your monitoring exercise, deactivate and remove unnecessary aggregates.
205
SAP AG 2005, TEWA50 / 205
Analyzing Long Running Queries (6)
•New data
•New Cubes
•New Queries
Delete
unnecessary
aggregates
Monitor aggregate
maintenance
Monitor query
performance
Analyze and
create aggregate(s)
Detect and analyze
expensive query
� In a production system:
�New cubes are created in the system.
�New queries are created.
�Existing aggregates may require modification to suit a change in query pattern.
�New data gets loaded. As new data gets loaded over time, the statistical distribution of data may be
skewed.
� Therefore, you need to monitor and detect expensive query or poor aggregates.
206
SAP AG 2005, TEWA50 / 206
When Not to Create Aggregates
No
Yes
High DB time?
Change in query or drilldown pattern
Create aggregates and monitor runtime
Yes
Query can be tuned by aggregates?Examples: high ratio on QDBSEL/QDBTRANS
No
Read ModeBasis Problem
Check
FrontendOLAPCheck
� At a given point in time, you may need to create an aggregate.
� For example, with changes in drilldown patterns or as new queries are defined, you may
need to create an aggregate.
� Following changes to master data and hierarchies, you may need to rebuild all dependent
aggregates.
� During a recalculation, no roll-up, no master data updates, and no hierarchy updates can take place. Also, changed aggregate data is not available via query until recalculation is
complete.
� If DB time is high but the ratio of QDBSEL/QDBTRANS is low, no aggregates will solve this problem. Check for correct Read Mode and general basis problems.
� If DB time in relation to Total runtime is not the largest part, you should analyze the part where most time is spent, for example OLAP time or Frontend time.
� Sometimes you want to create aggregates although DB time is not that high, e.g. to reduce I/O load on the database.
207
SAP AG 2005, TEWA50 / 207
1 Basics About Aggregates
2 How to Detect Tuning Potential
3 How to Tune Query Performance
5 Basics ofAggregate Maintenance
Unit 5.2: RollUp of Aggregates
4 Basics about OLAP Cache
6 How to TuneAggregate Maintenance
5.2 Rollup of Aggregates
5.3 Changerun
5.1 General Strategy
208
SAP AG 2005, TEWA50 / 208
Rollup (1)
Rollup applies the newly uploaded transaction data to the aggregate
Customer
InfoCubeNew transactional data in the InfoCube not available for reporting
Industry
� If there is new data in InfoCube, it has to be rolled up to aggregates. The new transactional data is not available for reporting.
209
SAP AG 2005, TEWA50 / 209
Rollup (2)
Rollup applies the newly uploaded transaction data to all aggregatesof an InfoCube
Customer
InfoCube
Industry
New transactional data now available for reporting
� After the roll-up, the newly uploaded transactional data is available for reporting. It is now available in the InfoCube and in the aggregates.
� During the rollup, reporting is still possible on the old data.
210
SAP AG 2005, TEWA50 / 210
CountryCountry CustomerCustomer SalesSales
USAGermanyUSAAustriaAustriaGermanyUSA
Winsoft Inc.InternetworksFunny Duds Inc.InternetworksThor IndustriesFunny Duds Inc.Winsoft Inc.
1015510102025
Fact Table: Sales DataFact Table: Fact Table: Sales DataSales Data Aggregate Tables: Sales DataAggregate Tables: Aggregate Tables: Sales DataSales Data
Country *Country Country **
CountryCountry SalesSales
151510
USAGermanyAustria
Rollup (3)
New uploaded data is not yet rolled up in the aggregate
New transactional data not available for reporting
� There would be different results for Austria whether the InfoCube is accessed or whether the aggregate is accessed. To resolve this inconsistency, the newly uploaded data is not available for reporting until the aggregate provides the same information.
211
SAP AG 2005, TEWA50 / 211
Country Customer Sales
USAGermanyUSAAustriaAustriaGermanyUSA
Winsoft Inc.InternetworksFunny Duds Inc.InternetworksThor IndustriesFunny Duds Inc.Winsoft Inc.
1015510102025
Fact Table: Sales DataFact Table: Sales Data Aggregate Tables: Sales DataAggregate Tables: Sales Data
Country *Country *
Country Sales
403520
USAGermanyAustria
New uploaded data is now rolled up in the aggregate
New transactional data now available for reporting
Consistency
Rollup (4)
Recalculated values in the aggregates fact table
� Now the data between aggregate and InfoCube is consistent.
212
SAP AG 2005, TEWA50 / 212
Aggregates
InfoCube
0REQUID = 0
0REQUID = 0
0REQUID = 1
Rollup and Consistency (1)
Read pointer
Steps of the rollup:
1) Initially, the aggregate and the InfoCube have consistent data
� At the starting point of the analysis, the InfoCube and the aggregates contain the same data packets. The data is visible up to the read pointer. Technically this is a filter on the characteristic 0REQUID. This is located in the technical packet dimension that is contained in every InfoCube.
� Steps of the Rollup:
�New request has a new assigned Request ID, RNSID is saved in the fact table
�Rollup new requests into aggregate
�Reset read pointer RNSID
�Compress aggregates
� Rollup to a RNSID is only possible where all smaller RNSIDs have a quality OK.
� New rows added to the Info Cube cannot be used until the aggregates are updated to ensure reporting consistency. After rows are added, three steps must be performed before the new data can be using in reporting:
�Technical verification (performed automatically by SAP)
�Quality approval (can be set to automatic)
�Aggregate rollup procedure (can be set to automatic)
213
SAP AG 2005, TEWA50 / 213
Aggregates
InfoCube
0REQUID = 0
0REQUID = 0
0REQUID = 1
Rollup and Consistency (2)
Read pointer
0REQUID = 2
Steps of the rollup:
2) Each request has an assigned request SID (RNSID) that is saved in the fact table
� A request has been uploaded to the F-fact table. This request is not yet rolled up into the aggregate. It is not available for reporting. The read pointer guarantees that this data is not available for reporting.
� If new data is loaded into the InfoCube, then it is written in the fact table without being made available for reporting, since the read pointer has not yet changed.
214
SAP AG 2005, TEWA50 / 214
AggregatesSteps of the rollup:
3) Rollup for new request (new RNSID)
InfoCube
0REQUID = 0
0REQUID = 0
0REQUID = 1
Rollup and Consistency (3)
Read pointer
0REQUID = 2
0REQUID = 2
Roll up
� If the data was loaded successfully into the fact table of the InfoCube, it can then be rolled up. Since the read pointer has not changed, the new data is still not yet visible. Queries thus still show the result of the start of the process.
215
SAP AG 2005, TEWA50 / 215
Aggregates
InfoCube
RNSID = 0
RNSID = 0
RNSID = 1
Rollup and Consistency (4)
Read pointer
RNSID = 2
RNSID = 2
Steps of the rollup:
4) Set the read pointer to the new position
� Once the data packets have been rolled up successfully into all aggregates of the InfoCube, the read pointer is set to the last rolled-up data packet. Queries started from this point display the new data.
216
SAP AG 2005, TEWA50 / 216
Aggregates
InfoCube
RNSID = 0
RNSID = 0
RNSID = 1
Rollup and Consistency (5)
Read pointer
RNSID = 2
RNSID = 2
Steps of the rollup:
5) Optional:Compress aggregates
Compress
Dependent on Flag “compress aggregates after roll up” Admin-Workbench > InfoCubes > right mouse click on InfoCube > Manage > tab rollup
� Since the characteristic 0REQUID also exists in every aggregate, records that logically belong together (records that have the same key in all non-technical dimensions) are saved physically in several records. So that the aggregate tables do not get too large, the data for the aggregates is compressed automatically after the successful rollup, meaning that it is summarized over characteristic 0REQUID. No query can be executed in this compression phase for databases that only support the dirty-read mode.
� During a rollup, further data can still be loaded into the InfoCube.
� It is not possible to delete a compressed request out of an aggregate, because the
request-id is 0 for all compressed requests (except for non-cumulative values).
� During a Changerun the compress on the aggregates is not performed automatically.
� InfoCubes can be marked such that the request is kept in its aggregates.
� Requests which are not compressed can be deleted out of the InfoCube and its aggregates, if it is marked.
� Aggregates of marked InfoCubes are compressed together with the InfoCube.
� Only InfoCubes, where the deletion of rolled up requests is necessary, should be marked.
� Using this option can decrease the query performance.
� Path to the screen to mark an InfoCube for keeping the request: Admin-Workbench > InfoCubes > right mouse click on InfoCube > Mange > tab rollup > flag: “compress
aggregates after roll up”
217
SAP AG 2005, TEWA50 / 217
Steps of the rollup:
6) After compression
Aggregates
InfoCube
RNSID = 0
RNSID = 0
RNSID = 1
Rollup and Consistency (7)
Read pointer
RNSID = 2
� At the end of the procedure, the InfoCube and the aggregates contain the same data packets again. The data is visible up to the read pointer. Technically, this is a filter on the characteristic 0REQUID. This is located in the technical packet dimension that is contained in every InfoCube.
218
SAP AG 2005, TEWA50 / 218
To use data recently loaded into the InfoCube
in reporting, do the following:
1. Technical verification(performed automatically by SAP)Example: allow checking of rows in input file against number of rows added
2. Quality approval(can be set to automatic)
3. Aggregate rollup procedure (can be set to automatic)
Aggregate Rollup: Overview
1. The system automatically runs a technical check on the loaded data packet. At the same time, the dataset requested is matched with the set that was transferred.
2. You can check the quality of the data that was loaded. To do this, in the administration screen (RSA1 > right-click on InfoCube > Manage), in the menu choose Environment >
Automatic request processing. In the next screen, remove the flag on “Set Quality Status to O.K.”. Before you release the new data for reporting, you can check it with specific queries, for example. These queries typically contain a variable for the number of the
data packet/request as a filter for the characteristic 0REQUID. You can also attach these queries to the exception reporting. If the quality of the data is OK, you can set the status of the request to OK: RSA1 > right-click on InfoCube > Manage > Request and select
the traffic light of the request)
3. The rollup of this packet is scheduled. This step can also be automatic.
219
SAP AG 2005, TEWA50 / 219
Aggregate Rollup: Create Batch Job
View InfoCube aggregates
View batch job logs for aggregate rollup
Parameters for system to calculate requests to rollup
Once selection is made, system-
generated batch job name goes
here
Way to trigger events based on success or
failure of roll-up
� Start the rollup manually. To do this, choose Rollup from the context menu Manage of the InfoCube in the Administrator Workbench. This process is appropriate if the data of several packets form a logical unit and only make sense if they are released together. An example would be if different plants deliver their data at different times, but the data should only be visible if all plants have loaded their data into the INFOCUBE. You can also run the rollup with the program RSDDK_AGGREGATES_ROLLUP. The rollup has to
be scheduled for each InfoCube separately.
� It is very important to roll up aggregates, or their data will be out of sync with their associated InfoCubes. The newly uploaded data is not available for reporting.
� During aggregate rollup, full service to users is uninterrupted.
� A rollup and a master data activation (change run) cannot run simultaneously for the same
InfoCube.
220
SAP AG 2005, TEWA50 / 220
Automatic Aggregate Rollup
InfoCubes > Manage >
Environment > Automatic request processing
Option to automatically fill aggregates upon completion of InfoPackage load
� This setting can be very helpful in reducing manual administration of aggregates. You can also set up the InfoCube so that every data packet is automatically rolled up into the aggregate if it is technically correct and the quality has been ensured. To do this, in the screen where you can schedule the manual rollup, choose Environment > Automatic request processing. In the next screen, select “Roll up data in the aggregate”. However, this mechanism does not work if you are loading several data packets in parallel. Use
event chains in this case.
� Aggregate rollup must occur at some point in the data load process.
� This automatic setting might not be desirable in some cases of parallel load and large
volumes. In these cases, the administrator can allocate all system resources to completion of other InfoPackage loads occurring in parallel.
221
SAP AG 2005, TEWA50 / 221
How to automate the Rollup (1)
Admin Workbench > InfoCubes > Manage
On tabRequests, choose Subsequent Processing
� Another way to trigger an event following a data load to the InfoCube.
� The check mark on button SubseqProcessing indicates that further events follow. If there is no check mark on this button, no further events follow.
222
SAP AG 2005, TEWA50 / 222
How to automate the Rollup (2)
Admin Workbench > InfoCubes > Manage
On tab Rollup, choose Selection
� There is no check mark on SubseqProcessing, so this is the end of the chain.
� Clicking on selection will give you the event connection. This means that the background job execution is linked to the condition that this event is raised.
223
SAP AG 2005, TEWA50 / 223
How to automate the Rollup (3)
The rollup background job is triggered only when the event called ROLLUP is raised
� The final step for this illustration. The aggregate rollup background job will only occur once it has been triggered by the successful data load into the cube.
224
SAP AG 2005, TEWA50 / 224
Rollup using Process Chains
� Since BW 3.x it‘s recommend to use Process Chains to define the whole uploadprocess. Process Chains allow a graphical definition and control of the load process. The manual definition of events and jobs reacting on these events can be avoidedusing chains.
� For the roll-up of aggregates there is the process type „Roll Up of Filled Aggregates“. If you want to fill newly created aggregates within a process chain you can use theprocess type „Initial Fill of New Aggregates“.
225
SAP AG 2005, TEWA50 / 225
Process Chains – Variant of RollUp Process
� The variant of the process type „Roll Up of Filled Aggregates“ allows to define bydate or by the number of requests which requests should be rolled up.
226
SAP AG 2005, TEWA50 / 226
1 Basics About Aggregates
2 How to Detect Tuning Potential
3 How to Tune Query Performance
5 Basics ofAggregate Maintenance
Unit 5.3: Changerun
4 Basics about OLAP Cache
6 How to TuneAggregate Maintenance
5.2 Rollup of Aggregates
5.3 Changerun
5.1 General Strategy
227
SAP AG 2005, TEWA50 / 227
Changerun (1)
Master data activation = Activating the changes of master data and hierarchies. During the change run, all aggregates containing navigational attributes and/or hierarchies are realigned.
Customer Industry
InfoCube
Changes to navigational attribute Industryare not available for reporting
� Changes in the master data means changes of navigation attributes or hierarchies, too. It is therefore recommended that you adjust the data in the aggregates after you load the master data. So that Reporting delivers consistent results, the master data and hierarchies are in two versions: in the active version where you can see the query, and in a modified version, which at some point will become the active version. The change run (also called hierarchy-attribute realignment run) adjusts the data in the aggregates and
turns the modified version of the navigation attributes and hierarchies into an active version. In almost every phase of the change run, you can carry out Reporting on old master data and hierarchies.
� If there are master data changes, those master data changes are not available for reporting until the Changerun is executed and finished.
� During Changerun no Rollup is possible for those InfoCubes which are affected by the Changerun.
228
SAP AG 2005, TEWA50 / 228
Changerun (2)
Customer Industry
InfoCube
Changes to navigational attribute Industryare now available for reporting
Master data activation = Activating the changes of master data and hierarchies. During the change run, all aggregates containing navigational attributes and/or hierarchies are realigned.
Realigned aggregate fact table
� All aggregates which summarize data by navigational attributes or on a specific hierarchy level have to be changed during the master data activation. These aggregates must be recalculated. In this example, all aggregates that contain navigational attribute Industryare changed during master data activation. Aggregates that do not contain Industry are
not changed during master data activation.
� During master data activation, it is not possible to perform a roll-up for the InfoCubes
which are affected by the Changerun.
� The Master data load and the hierarchy load is also locked for the touched objects.
229
SAP AG 2005, TEWA50 / 229
Changerun (3)
Country Customer Sales
USAGermanyUSAAustriaAustriaGermanyUSA
Winsoft Inc.InternetworksFunny Duds Inc.InternetworksThor IndustriesFunny Duds Inc.Winsoft Inc.
1015510102025
Fact Table: Sales DataFact Table: Fact Table: Sales DataSales Data Aggregate Tables: Sales DataAggregate Tables: Aggregate Tables: Sales DataSales Data
Customer__Industry *Customer__Industry Customer__Industry **
Customer__ Industry Sales
602510
TechnologyConsumer ProductsChemical
TechnologyConsumer ProductsTechnologyChemicalConsumer Products
IndustryCustomer
Winsoft Inc.Funny Duds Inc.InternetworksThor IndustriesInternetworks
Navigational Attribute for Characteristic Customer
Navigational Attribute for Navigational Attribute for
Characteristic CustomerCharacteristic Customer
Changed master data not available for reporting
Old:
New:
� The aggregate does not reflect the current status. When the aggregate has been created, Internetworks belonged to industry technology. Meanwhile it changed to consumer products. The aggregate still reflects the old status. This change is not yet available for reporting.
230
SAP AG 2005, TEWA50 / 230
Changerun (4)
Country Customer Sales
USAGermanyUSAAustriaAustriaGermanyUSA
Winsoft Inc.InternetworksFunny Duds Inc.InternetworksThor IndustriesFunny Duds Inc.Winsoft Inc.
1015510102025
Fact Table: Sales DataFact Table: Fact Table: Sales DataSales Data Aggregate Tables: Sales DataAggregate Tables: Aggregate Tables: Sales DataSales Data
Customer_Industry *Customer_Industry Customer_Industry **
Customer_ Industry Sales
355010
TechnologyConsumer ProductsChemical
TechnologyConsumer ProductsTechnologyChemicalConsumer Products
IndustryCustomer
Winsoft Inc.Funny Duds Inc.InternetworksThor IndustriesInternetworks
Navigational Attribute for Characteristic Customer
Navigational Attribute for Navigational Attribute for
Characteristic CustomerCharacteristic Customer
Old:
New:
Changed master data now available for reporting
� During master data activation, all affected aggregates are recalculated.
231
SAP AG 2005, TEWA50 / 231
/NRSATTR
Changerun (5)
� Menu Path for the master data activation is:
�Administrator Workbench (RSA1) -> Tools -> Apply Hierarchy/Attribute Changes
�To mark the Info-Objects you want to start the master data activation for, choose button „InfoObject“ or
„Hierarchy list“. By default, all InfoObjects and hierarchies that have been changed are marked
automatically. So if you leave everything as it is, all InfoObjects and hierarchies are activated.
�You can start the change run either manually in the Administrator Workbench (choose Tools >
Hierarchy/Attribute Changes) or with program RSDDS_AGGREGATES_MAINTAIN. You can give the
program a list of characteristics and hierarchies that are to be taken into account for the change run.
You can also schedule this program periodically or attach it to event chains. You can also save the list
of characteristics and hierarchies as variants and treat them as variants when scheduling. If you do not
enter anything, all those characteristics are taken into account whose master data you loaded or
changed manually, and all the hierarchies that were marked for the realignment run. If you changed a
hierarchy, you must activate this change. When you load hierarchies you can set whether you want the
hierarchy to be directly activated or marked. If there are no aggregates that contain these hierarchies,
the hierarchy is directly activated. If there are aggregates, a popup appears asking you if you want to
delete the aggregates in question and directly activate the hierarchies, or whether the changes should
just be marked. When you load and at the same time activate the hierarchy, it is marked for the change
run as long as aggregates exist.
232
SAP AG 2005, TEWA50 / 232
Change Run in a Process Chain
233
SAP AG 2005, TEWA50 / 233
Change Run in a Process Chain
� With a process chain you can define in the variant for which objects the change runshould be executed. If not objects is chosen the change run is executed for all necessary objects.
234
SAP AG 2005, TEWA50 / 234
Changerun and Consistency
Navigational Attribute for CharacteristicCustomer
TechnologyConsumer ProductsTechnologyChemicalConsumer Products
IndustryCustomer
Winsoft Inc.Funny Duds Inc.InternetworksThor IndustriesInternetworks
Object version
ActiveActiveActiveActiveModified
Changed master data, not available for reporting
Old:
New:
Situation before master data activation:
Navigational Attribute for CharacteristicCustomer
TechnologyConsumer ProductsChemicalConsumer Products
IndustryCustomer
Winsoft Inc.Funny Duds Inc.Thor IndustriesInternetworks
Object version
ActiveActiveActiveActive
Changed master data, now available for reporting
New:
Situation after master data activation:
Master data activation, including change run, of affected aggregates
� A Changerun has to be performed to activate new master data, even though the Attribute or Hierarchy is not used in any Aggregate.
� How the Changerun is performed:
�Generates all aggregates which are affected (in all InfoCubes) with a new Change ID (CNSID)
�Activates all hierarchies and attributes (update the data records)
�Sets the pointers to the right Change ID, that the reporting can use the new structures and the modified
aggregates
� New master data is available for reporting only if the Changerun has finished
successfully.
235
SAP AG 2005, TEWA50 / 235
Changerun technical details
� During Changerun all InfoCubes having aggregates affected by the Changerun are locked for Rollup.
� To enable reporting during Changerun a temporary aggregate /BIC/E2XXXXX is created. After creating the E2 aggregate, it is copied to E1 to have only a short time where the aggregate is not available.Afterwards table E2 is deleted from the temporary area of the database.
� During Changerun all InfoCubes containing aggregates affected by the Changerun are locked for Rolluip.
� To enable reporting during Changerun a temporary aggregate /BIC/E2XXXXX is created, where XXXXX is the same number as of aggregate E1. After creating the E2XXXXX aggregate, it is copied to E1XXXXX to have only a short time where the aggregate is not available.
Afterwards table E2XXXXX is deleted from the temporary area of the database.
� To monitor the changerun during execution, you can use ABAP-report RSDDS_CHANGERUN_MONITOR. To get detailed information please see SAP Note
388069.
236
SAP AG 2005, TEWA50 / 236
Delta Mechanism During Changerun
As of BW 2.0A, a delta mechanism for Changerun is available
Aggregates are not completely rebuilt if the percentage of changes is below a certain threshold
� The Delta mechanism causes that only rows of the aggregate which are affected have to be changed.
� You can customize the limit when the Changerun switches from delta to full recreation mode. This limit is defined as percentage of changed master data. The default threshold is 20, which means if more than 20% of the master data is changed, all affected Aggregates are completely rebuilt.
If no value is set, the aggregates are completely rebuilt every time.
� From BW 3.x the delta change run is activated automatically. For information about how
to switch on the delta mechanism in BW 1.2B, see SAP Note 0181944.
� The setting Block Size allows you to define a kind of package size for the aggregate
maintenance. By default the value is set to 10.000.000 records. You can find details in note 484536.
237
SAP AG 2005, TEWA50 / 237
Time-Dependent Aggregates
Change run for
aggregates with a time-dependent
component
(navigational attribute
or hierarchy)
newBW 3.X
� An aggregate is a time-dependent aggregate if it contains a time-dependent navigational attribute or a time-dependent hierarchy.
� A time-dependent aggregate is calculated for one key date. You can define a constant key date or you can use a variable e.g. 0DAT.
� The aggregate can only be used by queries using the same key date as the aggregate. If no key date is defined in the query definition the actual date is used by default.
238
SAP AG 2005, TEWA50 / 238
Adjustment of Time-Dependent Aggregates
Key Date adaption of a time-dependent aggregate
No variant can be defined.
Process adapts all time-
dependent aggregates
� Technically spoken the “Adjustment of Time-Dependent Aggregates” is a change run for
aggregates with a time dependent component (navigational attribute or hierarchy). You require this adjustment if you want to recalculate the aggregate for a new key date.
� Besides the “Adjustment of Time-Dependent Aggregates” a time-dependent aggregate is of course affected by the regular change run if there is a master data change. The regular change run adapts
master data changes, M-Version records are activated. The “Adjustment of Time-Dependent Aggregates” recalculates the key date of the aggregate. Please note that there isn’t a process which
activate records and recalculates the key date simultaneously.
� The process “Adjustment of Time-Dependent Aggregates” should be included in a process chain.
When defining a process chain (Transaction RSPC), the process “Adjustment of Time-Dependent Aggregates” can be found under „Other BW Processes“
� If the Keydate is the variable “Current Date” (0DAT) the variable is filled through System date of the application server. If a new data need to be processed daily the “Adjustment of Time-Dependent Aggregates” run needs to be scheduled after midnight. If you are a global player you need to
consider time differences between different countries. If necessary two aggregates with two different Keydates need to be created. A recalculation is only executed if the key date has changed.
� As for the regular change run the process type “Adjustment of Time-Dependent Aggregates” adapts the key date by refilling, a rollup or a delta method.
� The process “Adjustment of Time-Dependent Aggregates” has the job name
BI_PROCESS_TIMCHNGRUN.
� It’s not necessary to define a variant, the process only adapts time-dependent aggregates which have a changed key date.
239
SAP AG 2005, TEWA50 / 239
Aggregates - Blocksize
Read in several blocks to prevent resourceproblems when filling an aggregate
Introduce a blocksize that can be customized
����note 484536
During rollup and change run highlyressource consuming operations on thetemporary tablespace can be performed.
PROBLEM PSAPTEMP SIZE
Idea
Solution
Transaction SPRO >> General BW Settings > Parameters for Aggregates > Blocksize
� During rollup and change run high resource consuming operations are performed on the temporary tablespace . Depending on the data volume to be processed (and other things like the sizing of Database Management System) the size of the temporary tablespacecan grow dynamically hundreds of MB. This behavior can lead to disk bottlenecks.
� With the introduction of the data to be processed are read in several blocks. The
blocksize can be customized. The default is currently set to 100,000 data records. Current knowledge and best practise is to change the block size to 10,000,000 records.
With Patch 21 BW2.0B the blocksize standard will be 10,000,000 records
�Einfügen: Defaultwerte in BW3.0
� Path for setting the blocksize: Transaction SPRO > SAP Reference IMG > BW Customizing Implementation Guide > Business Information Warehouse > General BW Settings > Parameters for Aggregates
� Note 484536
240
SAP AG 2005, TEWA50 / 240
Parallel Change-Run
Default: Long runtime because of serial execution in one background job
Switch to parallel proccessing possible – Note 534630
InfoCube1
InfoCube2
InfoCube3
Aggr. Aggr.
Aggr.
Aggr.
Dialog Process
Dialog Process
Dialog Process
Ba
ckg
rou
nd
Pro
ce
ss
The parallel change-run is restartable as the serial change-run
When implementing this solution, bear in mind that the runtime of the
paralleled version of the hierarchy and attribute realignment run
depends on the adjustment times of aggregates on the different
InfoCubes. If the adjustment times of the aggregates are distributed
evenly across the different InfoCubes, you can significantly improve
performance with parallel processing. However, if one InfoCube and its
aggregates occupy most of the runtime of the conventional change run,
the speed gain during the parallel change run declines accordingly.
For detailed information please refer to note 534630.
241
SAP AG 2005, TEWA50 / 241
1 Basics About Aggregates
2 How to Detect Tuning Potential
3 How to Tune Query Performance
5 Basics ofAggregate Maintenance
Unit 6: How to Tune Aggregate Maintenance
4 Basics about OLAP Cache
6 How to TuneAggregate Maintenance
6.1 Aggregate Maintenance time
6.3 Useless Aggregates
6.4 Basis Aggregates
6.5 Aggregate Check tools
6.2 Aggregate Hierarchy
242
SAP AG 2005, TEWA50 / 242
Contents
� Rollup Hierarchy for Aggregates
� Detecting Useless Aggregates
� Analysis of Aggregate Maintenance Time
� Basis Aggregates
� Database-Dependent Tuning Methods
Objectives
At the end of this unit you will be able to:
� Describe the InfoCube rollup hierarchy
� Detect useless aggregates
� Analyze aggregate maintenance time
� Perform database-dependent tuning
How to Tune Aggregate Maintenance
243
SAP AG 2005, TEWA50 / 243
1 Basics About Aggregates
2 How to Detect Tuning Potential
3 How to Tune Query Performance
5 Basics ofAggregate Maintenance
Unit 6.1: Aggregate Maintenance Time
4 Basics about OLAP Cache
6 How to TuneAggregate Maintenance
6.1 Aggregate Maintenance Time
6.3 Useless Aggregates
6.4 Basis Aggregates
6.5 Aggregate Check tools
6.2 Aggregate Hierarchy
244
SAP AG 2005, TEWA50 / 244
Detecting High Aggregate Maintenance Time
Aggregate maintenance time is the total time needed for:
1. Master data activation (Change run)2. Rollup3. Filling of new aggregates
Aim: Detect problems in time
Reporting
Aggregate Maintenance
00 02 04 06 08 10 12 14 16 18 20 22
Period of time
24
Conflict – Actual data not available
� The total time needed for aggregate maintenance should fit in the time window available for aggregate maintenance.
� Check the time needed for aggregate maintenance regularly so that you can take corrective actions as soon as possible.
245
SAP AG 2005, TEWA50 / 245
Aggregate Maintenance – Monitoring by SM37
BI_PROCESS_CHANGE_RUNBI_STRU<XXX>Change-Run
BI_PROCESS_AGGRFILLBI_SAGR<XXX>(Re-)filling
BI_PROCESS_ROLLUPBI_AGGR<XXX>Roll-Up
Execution within a
process chain
Regular
executionJob Name
Too many different job names
No selection by InfoCubename possible
Easy to use by transactionSM37
No runtime overview per dayUseful for the analysis of onejob
DisadvantageAdvantage
� The job overview, transaction SM37, allows you to display all maintenance jobs within a chosen time period. By storing these lists, you can simply compare the total runtime witholder outputs to detect an increased runtime.
� The disadvantage is that you have to do this for each jobname. For further more detailedanalysis, you have to look into the job protocols to get the name of the affected InfoCubeor Aggregate.
� If you use another jobname because you‘ve defined your own job executing stepRSDDS_AGGREGATES_MAINTAIN, you can also search for this step in the job
overview.
246
SAP AG 2005, TEWA50 / 246
Aggregate Maintenance – Monitoring by ST03
Time Range isselectable
Display of total runtime
No historical analysisDisplay of InfoCube & Aggregate
DisadvantageAdvantage
� The prerequisite for this statistical data is that the BW Statistics flag for OLAP data isswitched on for the relevant InfoCubes.
� You can calculate the total runtime for a specific period for example weekly.
� The advantage to the regular job overview is that the InfoCube and Aggregate can be
displayed too. This allows a direct further analysis.
� To get a historic overview of the total runtime e.g. on a weekly basis, you have to executeST03 for each week.
247
SAP AG 2005, TEWA50 / 247
Aggregate Maintenance – Overview
Allows detailed analysis on InfoCube and Aggregate level over a specific time period. Data is based on the content of table RSDDSTATAGGR.
ST03
By manual analysis, this method is veryflexible and easy to use.Job Overview
Currently the only possibility to get a real historic display of the total runtimes.Technical Content
In the planning view of a process chainyou can right click on a process and choose ‚Display all jobs‘. You‘ll get a runtime historic of all jobs of this processtype.
Process Chain
Each roll-up, setup and change-run isrepresented by one record. Various filterselections are possible. BW Statisticsmust be switched on. Very inconvenient.
Table RSDDSTATAGGR
CommentMonitoring by
� If the BW Statistics aren‘t switched on, the only possibility to monitor the runtime of theaggregate maintenance is the job overview.
� Having statistical data, we recommend to use transaction ST03 or the Technical Contentfor the monitoring of the aggregate maintenance runtimes.
� The „Process Chain method“ is useful if you want to analyse one single maintenance job only.
248
SAP AG 2005, TEWA50 / 248
Aggregate Maintenance Time: Total
Check the runtime of aggregate maintenance regularly
If the time needed threatens to get higher than the time available foraggregate maintenance, check whether unnecessary aggregates are
increasing the time
12
9 3
6
12
9 3
6
!
� Try to perform as many maintenance activities in parallel as possible but monitor carefullyfor hardware bottlenecks.
� Aggregates can be rolled up in parallel for different InfoCubes. Filling of aggregates forone InfoCube can run in parallel with rollup of aggregates for another InfoCube.
� Master data activation and filling/rolling up aggregates cannot run in parallel for an InfoCube.
� Check whether the total runtime fits in the time window which is available.
� Check regularly if the runtime of aggregate maintenance is increasing and if the total
runtime threathens to get larger than the time window available. If it does, search forunnecessary aggregates.
249
SAP AG 2005, TEWA50 / 249
1 Basics About Aggregates
2 How to Detect Tuning Potential
3 How to Tune Query Performance
5 Basics ofAggregate Maintenance
Unit 6.2: Aggregate Hierarchy
4 Basics about OLAP Cache
6 How to TuneAggregate Maintenance
6.1 Aggregate Maintenance time
6.3 Useless Aggregates
6.4 Basis Aggregates
6.5 Aggregate Check tools
6.2 Aggregate Hierarchy
250
SAP AG 2005, TEWA50 / 250
Aggregates for an InfoCube
InfoCube
Aggr 2
Aggr 4
Aggr 5Aggr 3
0COUNTRY *0CUSTOMER *0REGION *
0CUSTOMER * 0COUNTRY *
Aggr 1 0MATERIAL *0SOLD_TO_COUNTRY *
0MATERIAL *
0COUNTRY0SALESMAN0PRODUCT0CUSTOMER0REGION0SOLD_TO_COUNTRY 0SOLD_TO 0SHIP_TO0MATERIAL
� This slide illustrates an existing set of aggregates for an InfoCube.
� A child aggregate can be built or rolled up from its parent aggregate.
�Example: aggregate 1 can be used to roll up aggregate 3, or to recreate aggregate 3 during changerun.
251
SAP AG 2005, TEWA50 / 251
Creating a New Aggregate
Newly activatedand filled aggregate
Aggr 6
0MATERIAL *0SOLD_TO_COUNTRY *0SOLD_TO *0SHIP_TO *
Fact table
Aggr 2
Aggr 4
Aggr 5Aggr 3
0COUNTRY *0CUSTOMER *0REGION *
0CUSTOMER * 0COUNTRY *
Aggr 1
0MATERIAL *0SOLD_TO_COUNTRY *
0MATERIAL *
0COUNTRY0SALESMAN0PRODUCT0CUSTOMER0REGION0SOLD_TO_COUNTRY 0SOLD_TO 0SHIP_TO0MATERIAL
� A new aggregate (number 6) is created.
� BW dynamically maps aggregate 4 as the child of aggregate 6. All future rollups for
aggregate 4 are now built from aggregate 6.
252
SAP AG 2005, TEWA50 / 252
Maintaining Aggregates
Displays the rollup hierarchy
� The slide shows an aggregate maintenance screen. Icon Rollup hierarchy has been chosen.
253
SAP AG 2005, TEWA50 / 253
First Aggregate
First aggregate
Rollup hierarchy for the aggregate
� A new aggregate has been created called Aggregate for the Leaf. This aggregate is filled directly from the InfoCube.
254
SAP AG 2005, TEWA50 / 254
Second Aggregate
Second aggregate
Roll-up hierarchy for the aggregate
� Another aggregate has been created called "Shoots – Leaf + 1". This aggregate can be filled and rolled up from aggregate Aggregate for the Leaf. The InfoCube does not have to be accessed when aggregate "Shoots – Leaf + 1" is rolled up or changed during the change run.
� After the aggregate "Shoots – Leaf + 1" has been created, the aggregate hierarchy screen shows the second aggregate as a child of the first aggregate.
255
SAP AG 2005, TEWA50 / 255
Third Aggregate
� Now a third aggregate called Basis Aggregate has been created. This screen shows all three active and filled aggregates with their respective Info-Objects. It also shows the date and time of manual creation for each aggregate. The date and time stamp shows that the aggregate Basis Aggregate was created last.
256
SAP AG 2005, TEWA50 / 256
Rollup Hierarchy
� This screen displays the hierarchy of all existing aggregates created for InfoCube 0SD_C03. Though the aggregate Basis Aggregate was created last, BW has dynamically mapped it as the parent to all child aggregates. From now on, the child Aggregate for the
Leaf will roll up data from Basis Aggregate instead of from the InfoCube.
� Although a third aggregate was created which is in the hierarchy located on top of both aggregates, the value for summarized records is still the same. So remember this value is always the value at the time when the aggregate was created or recreated.
Concerning the number of records, please check note 712237 (“Wrong number of records displayed in Aggregate Maintenance”)
257
SAP AG 2005, TEWA50 / 257
1 Basics About Aggregates
2 How to Detect Tuning Potential
3 How to Tune Query Performance
5 Basics ofAggregate Maintenance
Unit 6.3: Useless Aggregates
4 Basics about OLAP Cache
6 How to TuneAggregate Maintenance
6.1 Aggregate Maintenance time
6.3 Useless Aggregates
6.4 Basis Aggregates
6.5 Aggregate Check tools
6.2 Aggregate Hierarchy
258
SAP AG 2005, TEWA50 / 258
Useless Aggregates
Waste space in the database
Increase aggregate maintenance time by causing unnecessary rollup and change run time
Do not improve query performance
Useless aggregates
� Useless aggregates do not improve query performance. They have several negative effects:
�They increase the rollup and change run times.
�They waste disk space in the database.
259
SAP AG 2005, TEWA50 / 259
What to Do with Them
A useless aggregate is an aggregate that …
Is too large relative to the InfoCube or the aggregate that it is created from
Is used infrequently or not used at all
Is too similar to another existing aggregate
Detect and delete all useless aggregates
� You should check regularly for useless aggregates in your system and delete them.
�Useless aggregates increase the time needed for aggregate maintenance.
�Take care of aggregates which are only important at a special time period (e.g. month end reporting)
260
SAP AG 2005, TEWA50 / 260
InfoCube
Aggregate1
Region *Country *Salesman *Product *Customer Day *Month *Year *
7 000 000records
6 800 000records
Useless: Large Aggregate (1)
Region *Country *Salesman *Product *Customer *Day *Month *Year *
Only Customer is aggregated
Summarizationratio: 1.02
Query definition:RegionCountry F, GermanySalesman *Product Customer DayMonthYear
Database access to the aggregate Runtime on database: 20 seconds
� The aggregate has almost the same size as the InfoCube.
261
SAP AG 2005, TEWA50 / 261
Region *Country *Salesman *Product *Customer Day *Month *Year *
Useless: Large Aggregate (2)
Region *Country *Salesman *Product *Customer *Day *Month *Year *
Only Customer is aggregated
Query definition:RegionCountry F, GermanySalesman *Product Customer DayMonthYear
Database access to the aggregate Runtime on database: 20 seconds
Database access to the InfoCube Runtime on database: 20.5 seconds
InfoCube
Aggregate1
7 000 000records
6 800 000records
Summarizationratio: 1.02
� The aggregate has almost the same size as the InfoCube. Although it has good usage, there is no need for this aggregate. The query performance is about the same as retrieving from the InfoCube.
� This aggregate will not improve query performance, but it will increase the time needed to maintain the aggregates.
262
SAP AG 2005, TEWA50 / 262
Aggregate2
InfoCube
Aggregate1
7 000 000records
350 000records
Useless: Large Aggregate (3)
Region *Country *Salesman *Product *Customer *Day *Month *Year *
Query definition:RegionCountry F, GermanySalesman *Product Customer DayMonthYear
Database access to aggregate2 Runtime on database: 3 seconds
Summarizationratio: 17.5 Country *
Salesman *Product * Month *Year *
Country F, GermanySalesman *Product * Month *Year *
Only Country is aggregated
Summarizationratio: 1.14
400 000records
� Aggregate 2 is small compared to the InfoCube. However, this aggregate is nearly as large as Aggregate 1.
263
SAP AG 2005, TEWA50 / 263
Only Country is aggregated
Useless: Large Aggregate (4)
Aggregate2
InfoCube
Aggregate1
7 000 000records
350 000records
Region *Country *Salesman *Product *Customer *Day *Month *Year *
Query definition:RegionCountry F, GermanySalesman *Product Customer DayMonthYear
Database access to aggregate2 Runtime on database: 3 seconds
Summarizationratio: 17.5 Country *
Salesman *Product * Month *Year *
Country F, GermanySalesman *Product * Month *Year *
Summarizationratio: 1.14
400 000records
Database access to aggregate1 Runtime on database: 3.2 seconds
� Aggregate 2 has almost the same size as aggregate 1. For this reason, there is no need for aggregate 2. The query performance is about the same when using aggregate 2 compared to aggregate 1.
� This aggregate will not improve query performance, but it will increase the time needed to maintain the aggregates.
264
SAP AG 2005, TEWA50 / 264
The summarization ratio should be
larger than 10
Rule of Thumb 1
� If you are happy with the performance of your aggregate maintenance, lower values can be tolerated.
265
SAP AG 2005, TEWA50 / 265
Useless: Similar Aggregates (1)
Region *Country *Salesman *Product *Customer *Day *Month *Year *
Country *Salesman *Product *Month *Year *
Country *Salesman *Customer *Month *Year *
Query definition:RegionCountry *Salesman *Product *Customer DayMonth *Year *
Database access to aggregate1 Runtime on database: 7 seconds
Query definition:RegionCountry *Salesman *ProductCustomer * DayMonth *Year *
Database access to aggregate2 Runtime on database: 6.5 seconds
Aggregate2
Summarizationratio: 20
350 000records
Aggregate1
Summarizationratio: 17.5
400 000records
InfoCube
7 000 000records
� Aggregates 1 and 2 are quite similar. They contain nearly the same objects and have nearly the same size.
266
SAP AG 2005, TEWA50 / 266
Useless: Similar Aggregates (2)
Region *Country *Salesman *Product *Customer *Day *Month *Year *
Country *Salesman *Product *Customer *Month *Year *
Query definition:RegionCountry *Salesman *Product *Customer DayMonth *Year *
Database access to aggregate3 Runtime on database: 7.3 seconds
Query definition:RegionCountry *Salesman *ProductCustomer * DayMonth *Year *
Database access to aggregate3 Runtime on database: 7 seconds
Aggregate3
Summarizationratio: 15.5
450 000records
InfoCube
7 000 000records
� Aggregate 1 and 2 have been replaced by aggregate 3. Both queries now access aggregate 3 instead of the two aggregates they previously accessed. This leads to a small decrease of performance but it speeds up aggregate maintenance.
� Aggregates 1 and 2 can be replaced by aggregate 3 without a significant decrease in query performance.
267
SAP AG 2005, TEWA50 / 267
Number of calls: 580 Last call: 2001/01/30
Number of calls: 0 Last call: no calls
Number of calls: 56 Last call: 2000/01/30
Useless: Unused Aggregates
Region *Country *Salesman *Product *Customer *Day *Month *Year *
Region *Country *Day *Month *Year *
Aggregate2
Ratio: 11.7
600 000records
InfoCube
7 000 000records
Country *Salesman *Product * Customer * Month *Year *
Aggregate1
Ratio: 15.5
450 000records Region *
Country *Customer * Month *Year *
Aggregate3
Ratio: 28
250 000records
� Aggregate 2 has not been used at all. Aggregate 3 has not been used for a long time. In the aggregate maintenance screen,
�To find out how often an aggregate has been used, check column Usage.
�To find out when an aggregate was last used, check column Last Used.
�To find out when this aggregate was created or changed, check column Last Changed on.
� Consider deactivating an aggregate if it was last changed a long time ago and one of the
following applies:
�It has not been used at all.
�It has been used only very rarely.
�It was last used a long time ago.
� Remember that some aggregates may be used only at specific times (such as at the end of each week, month, or year). In this case, it is a good idea to document this in the aggregate name.
268
SAP AG 2005, TEWA50 / 268
Attributes Affected by a Change Run
Region *Country *Salesman *Product *Customer *Day *Month *Year *
Aggregate2
Summarizationratio: 20
350 000records
InfoCube
7 000 000records
Aggregate3
Summarizationratio: 28
250 000records
Affected by the
change run
Country H, Level 3Salesman *Product * Customer *Month *Year *
Country *Salesman *Product * Customer__Industry *Month *Year *
� If navigational attributes or hierarchies are changed, all aggregates that contain those objects are affected by the change run.
� A Changerun is locking all rollup actions, also for aggregates which are not affected by the Changerun.
� Consider how often a navigational attribute or hierarchy will change, before you use it in an aggregate.
� Consider how much a navigational attribute or hierarchy will change, before you use it in
an aggregate.
�Eventually the delta mode will help to reduce changerun time.
269
SAP AG 2005, TEWA50 / 269
A Situation to Avoid (1)
Aggregate is affected by the change run (if the relevant master data is changed)
Master data table /BI0/XCUSTOMER
Country Customer Revenue
Germany Winsoft 10
Germany Enjoy AG 20
Germany Thor Ind. 5
USA Winsoft 10
USA Enjoy AG 20
USA Thor Ind. 10
Customer Customer__Industry Revenue
Winsoft Software 20
Enjoy AG Hardware 40
Thor Ind. Hardware 15
Customer Customer__Industry
Winsoft Software
Enjoy AG Hardware
Thor Ind. Hardware
Country *Customer *
InfoCube
6 records
Aggregate1
3 records
Customer *Customer__Industry *
� Avoid navigational attributes and the characteristic they belong to in the same aggregate
�This is only possible in Expert-Mode.
� The size of an aggregate that contains a characteristic with summarization type * does not change at all if the navigational attribute is added. However, if it contains the navigational attribute, it will be affected by the change run.
270
SAP AG 2005, TEWA50 / 270
A Situation to Avoid (2)
Country Customer Revenue
Germany Winsoft 10
Germany Enjoy AG 20
Germany Thor Ind. 5
USA Winsoft 10
USA Enjoy AG 20
USA Thor Ind. 10
Aggregate is not affected by the change run and has exactly the same size as the aggregates with Customer__Industry
Customer Revenue
Winsoft 20
Enjoy AG 40
Thor Ind. 15
Customer Customer__Industry
Winsoft Software
Enjoy AG Hardware
Thor Ind. Hardware
Country *Customer *
InfoCube
6 records
Aggregate1
3 records
Customer *
Master data table /BI0/XCUSTOMER
� The aggregate has the same number of rows as the aggregate in the previous slide but does not contain any navigational attributes and is thus not affected by the change run.
271
SAP AG 2005, TEWA50 / 271
A Situation to Avoid (3)
Country Customer Revenue
Germany Winsoft 10
Germany Enjoy AG 20
Germany Thor Ind. 5
USA Winsoft 10
USA Enjoy AG 20
USA Thor Ind. 10
Aggregate is affected by the change run but is smaller than the aggregate with Customer *
Customer Revenue
Software 20
Hardware 55
Customer Customer__Industry
Winsoft Software
Enjoy AG Hardware
Thor Ind. Hardware
Country *Customer *
InfoCube
6 records
Aggregate1
2 records
Customer__Industry *
Master data table /BI0/XCUSTOMER
� If some queries are only interested in the navigational attributes, create an aggregate that no longer contains the characteristic. This aggregate will be smaller than an aggregate that summarizes the characteristic with summarization type *.
� If you use a combination of navigational attributes belonging to the same characteristic, it is possible, that the aggregate is larger than by using the characteristic itself, and even larger than the InfoCube.
272
SAP AG 2005, TEWA50 / 272
If navigational attributes or hierarchies
are used, the summarization ratio
compared to the InfoCube should be
larger than 10
Rule of Thumb 2
� If you are happy with the performance of aggregate maintenance, lower values can be tolerated.
273
SAP AG 2005, TEWA50 / 273
Valuation of Aggregates
� The System offers an automatic evaluation of the aggregates.
� A bad evaluation is given to aggregates that are:
�As large as the structure they are built from
�Not used at all
�Not used for a long time
� Column Records summarized (mean value) shows values that were valid when the aggregate was created. If further aggregates have been created, these values may no longer be valid. Please check note 712237 (“Wrong number of records displayed in
Aggregate Maintenance”)
� Column Usage is reset only if you redefine an aggregate. So you should use column Last
used to detect if queries are no longer used nowadays.
274
SAP AG 2005, TEWA50 / 274
EarlyWatch Alert - Aggregates suggested for deactivation
Cube name Aggr.-ID Aggr.-cube Number of entries Average reduce factor Number of calls
ZPCE01 100296 113 10 0
ZPHR01 MAX 12 100127 2275472 1 6
ZPHR01 MAX 0 100125 2275472 1 0
ZPHR01 MAX 10 100126 998343 1 1
ZPHR01 MAX 2 100128 2275472 1 84
ZPHR01 MAX 4 100129 2275472 1 0
ZPCE01 100290 21637 1 0
ZPCE01 XG1G2G3VT1T3T4 100118 1063 1 0
ZPCE01 MAX 4_12 100105 640405 1 0
� All aggregates where the average reduce factor is lower 11 or Number of calls is equal 0 are summarized in a table. We recommend to consider deletion of those aggregates.
275
SAP AG 2005, TEWA50 / 275
Tools for the Analysis
BW Workload Analysis – ST03N
Table RSDDSTATAGGR
Queries of BW STATISTICS
Using information from table RSDDSTATAGGR
� To analyze aggregate maintenance performance, you can use the following tools:
�Table RSDDSTATAGGR
�Queries of BW statistics. BW STATISTICS use information from table RSDDSTATAGGR and other
tables
�ST03N
276
SAP AG 2005, TEWA50 / 276
aggregatesaggregates
warehousemanagement
warehousemanagement
Table RSDDSTAT
Analysisby queries
(in workbooks)
Table RSDDSTATAGGR
Table RSDDSTATWHM
Info-Source
Info-Source
turn on/offlogging for each InfoCube
Updaterules
Updaterules
Overview
Info-Source
Info-Source
Info-Source
Info-Source
Updaterules
Updaterules
Updaterules
Updaterules
BW StatisticsMultiCube
Queries or ODBO
Queries or ODBO
� When maintaining aggregates, different statistical data about the performance of the maintenance is entered and is stored temporarily at the end of each navigation step. The data is stored in table RSDDSTATAGGR.
� The data is transferred from the InfoSources (RSDDSTATAGGR and others) into the Cube BW statistics.
�If you use BW 2.0B or higher, you can upload this data through the Administrator Workbench.
�If you use BW 1.2B or you migrated the BW Statistics Cube from 1.2B to 2.0, you cannot use the
Administrator Workbench to upload data. Instead, use report RSDDK_STA_WRITE_IN_CUBE.
� Function module RSDDSTAT_RFC_BW_STATISTICS gathers data from the database table RSDDSTAT and can be used to display this data.
277
SAP AG 2005, TEWA50 / 277
Table RSDDSTATAGGR
YYYYMMDDHHMMSS
Sort by TIMEREAD (database read time)
� Table RSDDSTATAGGR consists of many columns. To restrict the columns displayed, choose Settings > List format.
� In the selection screen of the data browser (transaction SE16) you can select for specific criteria by choosing Settings > Fields for selection.
�Start time format: YYYYMMDDHHMMSS (year, month, day, hour, minute, second)
�For parameter explanation, see SAP Note 202460.
�AGGRCUBE: Name of the aggregate
�INFOCUBE: InfoCube for which the aggregate is valid
�PARENTCUBE: Name of the source cube from which data is read
(may be the InfoCube or another aggregate)
�CHANGEMODE: Type of aggregate maintenance activity
(R = Rollup; N = Filling of aggregates; D = Delta change run)
�TIMEREAD: Read time
�TIMEINSERT: Time for saving
�TIMEINDEX: Time for index creation
�TIMEDBANALYZE: Time for analyzing DB statistics
�TIMEDBCONDENSE: Time for condensing
278
SAP AG 2005, TEWA50 / 278
Table RSDDSTATAGGR: Details
Total Time:
20 seconds
Type of activity
Technical name of the aggregates
� There are different types of aggregate maintenance:
�Change mode N
� An aggregate has been filled. This may have been done during initial filling or during the change run. If there are many changes to the master data, the aggregate is recalculated completely. In this case, the contents are deleted and the
aggregate is filled anew.
�Change mode R
� An aggregate has been rolled up. This may have been done during the rollup or during the change run.
�Change mode D
� An aggregate has been changed with the delta procedure during the change run.
279
SAP AG 2005, TEWA50 / 279
Tuning Aggregate Maintenance
Detect expensive aggregates that have high aggregate maintenance time
If an aggregate has low usage and/or is not important, then delete it
If the aggregate has high usage, then try to tune aggregate maintenance
� If you have detected, that an aggregate has a high aggregate maintenance time, check whether it can be deleted.
� Involve basis people to check for DB problems.
280
SAP AG 2005, TEWA50 / 280
ST03N – Aggregates Maintenance
� There are different types of aggregate maintenance:
�Change mode N
� An aggregate has been filled. This may have been done during initial filling or during the change run. If there are many changes to the master data, the aggregate is recalculated completely. In this case, the contents are deleted and the
aggregate is filled anew.
�Change mode R
� An aggregate has been rolled up. This may have been done during the rollup or during the change run.
�Change mode D
� An aggregate has been changed with the delta procedure during the change run.
281
SAP AG 2005, TEWA50 / 281
EarlyWatch Alert- Aggregates Maintenance I
Overview of Infocubes
InfoCube Nr. of Aggregates Total time (sec) % Rollup time % (Re)creation time % Delta Changerun
time
Records inserted
Total[22.05.2001
12:18:20-26.06.2001
05:01:42]
37 58615,8 14348315
ZPCE01_01 14 53549,9 0 100 0 13468627
ZPCE02 2 2598,6 0 100 0 786648
ZSD_DCO 15 2021,7 100 0 0 41042
ZPISTAT 6 445,6 0 100 0 51998
Overview of Aggregates
InfoCube Aggregate Total time (sec) % Rollup time % (Re)creation time % Delta Changerun
time
Records inserted
Total[22.05.2001
12:18:20-26.06.2001
05:01:42]
58615,8 14348315
ZPCE01_01 100412 13888,9 0 100 0 7636689
ZPCE01_01 100428 8276,6 0 100 0 1739090
ZPCE01_01 100492 5022,0 0 100 0 1319740
ZPCE01_01 100394 4899,5 0 100 0 1919124
282
SAP AG 2005, TEWA50 / 282
EarlyWatch Alert- Aggregates Maintenance II
Name Rollup (Re)create Delta Change Total
ZPCE01_01/100412
Executions 0 1 0 1
Time total (sec) 0.0 13888.8 0.0 13888.8
Avg. time total (sec) 0.0 13888.8 0.0 13888.8
Avg. time read (sec) 0.0 10001.0 0.0 10001.0
Avg. time insert (sec) 0.0 2128.1 0.0 2128.1
Avg. time index (sec) 0.0 1314.9 0.0 1314.9
Avg. time analyze (sec) 0.0 444.5 0.0 444.5
Avg. time condense (sec) 0.0 0.3 0.0 0.3
Avg. records read 0 29839404 0 29839404
Avg. records inserted 0 7636689 0 7636689
Maintenance of Aggregate 100412
Maintenance of Aggregate 100428
Name Rollup (Re)create Delta Change Total
ZPCE01_01/100428
Executions 0 1 0 1
Time total (sec) 0.0 8276.7 0.0 8276.7
Avg. time total (sec) 0.0 8276.7 0.0 8276.7
Avg. time read (sec) 0.0 7144.5 0.0 7144.5
Avg. time insert (sec) 0.0 509.4 0.0 509.4
Avg. time index (sec) 0.0 520.0 0.0 520.0
Avg. time analyze (sec) 0.0 102.6 0.0 102.6
Avg. time condense (sec) 0.0 0.2 0.0 0.2
Avg. records read 0 29839404 0 29839404
Avg. records inserted 0 1739090 0 1739090
� You get a very detailed view for the top 3 aggregates per total maintenance time
283
SAP AG 2005, TEWA50 / 283
1 Basics About Aggregates
2 How to Detect Tuning Potential
3 How to Tune Query Performance
5 Basics ofAggregate Maintenance
Unit 6.4: Basis Aggregates
4 Basics about OLAP Cache
6 How to TuneAggregate Maintenance
6.1 Aggregate Maintenance time
6.3 Useless Aggregates
6.4 Basis Aggregates
6.5 Aggregate Check tools
6.2 Aggregate Hierarchy
284
SAP AG 2005, TEWA50 / 284
Basis Aggregates
Basis aggregates
Contain no navigational attributes or hierarchies
Are built to avoid the aggregates that are changed during masterdata activation and must access the InfoCube
Are useful only if they are much smaller than the InfoCube and can be read by several aggregates during a change run
Should be build if line-item dimensions are used in the InfoCube
Goal: Decrease the time needed for Changerun and Rollup
� The goal of a basis aggregate is to decrease the time needed for the change run.
� Basis aggregates should not contain navigational attributes or hierarchies. However, they may contain navigational attributes or hierarchies that are changed very rarely or only slightly.
� Basis aggregates should be build if line-item dimensions are used, without containing the line-item characteristic(s) in the aggregate.
285
SAP AG 2005, TEWA50 / 285
Change Run: Access to InfoCube
Aggregate2
150 000records
InfoCube
7 000 000records
Aggregate1
100 000records
Aggregate3
150 000records
Region *Country *Salesman *Product *Customer *Day *Month *Year *
All Aggregates mustread the InfoCube during the change run
Customer__Industry *Day *Month *Year *
Country H, Level 3Day *Month *Year *
Product__Category *Day *Month *Year *
� During the change run, all information is read directly from the InfoCube. For each aggregate, the whole InfoCube is read. If there are many changes and the aggregates must be refilled anew, this decreases performance.
286
SAP AG 2005, TEWA50 / 286
Change Run: Access to the Basis Aggregate
Aggregate2
150 000records
InfoCube
7 000 000records
Aggregate1
100 000records
Aggregate3
150 000records
Region *Country *Salesman *Product *Customer *Day *Month *Year *
No Aggregate mustread the InfoCube during the change run
Customer__Industry *Day *Month *Year *
Country H, Level 3Day *Month *Year *
Product__Category *Day *Month *Year *
Basis Aggregate
400 000records
Customer *Country * Product *Day *Month *Year *
Not affectedby the
change run
� A basis aggregate can increase the performance of a change run. The basis aggregate should be a parent for as many aggregates as possible. This ensures that the InfoCube does not need to be accessed during the change run.
� Instead of 21 Million ( 3*7 Million) you have to read only 1,2 Million (3*400.000) records.
287
SAP AG 2005, TEWA50 / 287
1 Basics About Aggregates
2 How to Detect Tuning Potential
3 How to Tune Query Performance
5 Basics ofAggregate Maintenance
Unit 6.5: Aggregate Check Tools
4 Basics about OLAP Cache
6 How to TuneAggregate Maintenance
6.1 Aggregate Maintenance time
6.3 Useless Aggregates
6.4 Basis Aggregates
6.5 Aggregate Check tools
6.2 Aggregate Hierarchy
288
SAP AG 2005, TEWA50 / 288
Aggregate Check - Overview
584609Automatic check after roll up orchange run
Automatic Aggregate Check
646402Analysis of one aggregate with filter criterias
Report RSDDK_CHECK_AGGREGATE_SELOPT
584609Analysis of severalaggregates
Report RSDDK_CHECK_AGGREGATE
537422Manual analysis of one aggregate
Function Module RSDDK_CHECK_AGGREGATE
NoteTypeCheck Tool
� Another possibility is the usage of an aggregate trace. For details pleaserefer to note 202469.
289
SAP AG 2005, TEWA50 / 289
FunctionFunctionFunctionFunction modulemodulemodulemodule RSDDK_CHECK_AGGREGATERSDDK_CHECK_AGGREGATERSDDK_CHECK_AGGREGATERSDDK_CHECK_AGGREGATE
Note 537422 Function module for checking aggregates
Technical name of aggregate 1XXXXX
Build from InfoCubeor from parent
Creates log file entry(application log)
Check Mode: A, Q or C
290
SAP AG 2005, TEWA50 / 290
Aggregate Check – Application Log
Transaction SLG1
� The result of the aggregate check is displayed and stored in theapplication log. To get an overview of the executed aggregate checks youcan execute transaction SLG1 selecting the key values shown on theslide.
291
SAP AG 2005, TEWA50 / 291
Aggregate Check Modes
Complete Check (Mode A): Rebuild Aggregate out of InfoCube
or ParentAggregate as internal table and compare with Aggregate
on DB. Checktime approx. the time for the rebuild of the aggregate.
Check of keyfigure sums (Mode Q): Summarize Aggregate
and data source (InfoCube or ParentAggregate) over all characteristics and compare the sum of each keyfigure. Checktime
approx. 3-4 times faster than Mode A.
CheckAggregates (Mode C): Create new Aggregates
summarized over all characteristics (except package and unit
dimension) with the fixed value combination of the Aggregate to-be-checked. Very fast, but additional aggregates are created and have
to be maintained.
292
SAP AG 2005, TEWA50 / 292
Report RSDDK_CHECK_AGGREGATE
Note 584609 Automatic checks for aggregates (as of BW 3.0B SP09)
The report allows to check several aggregates in a background job
293
SAP AG 2005, TEWA50 / 293
Exceptions
Check not (yet) possible for Aggregates:
� Of an InventoryCube (Modi Q, C).
� Of an InventoryCube that doesn’t contain the inventory characteristic (Mode A).
� With fixed values (Mode Q).
� With fixed values on a navigational attribute (Mode C).
� With hierarchies without RESTNODE or NONUNIQUE (Modi Q, C).
The aggregate check cannot be executed using blocks
Report RSDDK_CHECK_AGGREGATE_SELOPT
� Note that, for technical reasons, the aggregates of non-cumulative cubes
� CANNOT be compared in blocks. For this reason, the internal tables may
� become very large and possibly exceed system restrictions ifthe
� aggregates have several records (> 2 million). This results in a
� termination with "SYSTEM_IMODE_TOO_LARGE". To check aggregates, you can
� only use the trace tool in note 202469 or implement strict
� characteristic restrictions (RSDDK_CHECK_AGGREGATE_SELOPT program).
294
SAP AG 2005, TEWA50 / 294
Report RSDDK_CHECK_AGGREGATE_SELOPT
Note 646402
Data Check Restriction
Blocksizesupported
295
SAP AG 2005, TEWA50 / 295
Automatic Aggregate Check
Switch On/OffAuto Aggregate
Check
Define check execution time
Check Mode
Selectaggregates to be
checked
Check Aggregate
To view the CheckAggregates: Menubar Extras � Expertmode �
Refresh Aggregate list. CheckAggregates can be activated/deactivated
and filled, but the definition not changed!
� Regarding the automatic aggregate check you can only specify onecheck mode for all CheckAggregates. If a CheckAggregate can’t be found the system uses check mode Q or check mode A automatically. If you want to use a mixture of check modes you can use the check report and
implement it within a process chain for example.
296
SAP AG 2005, TEWA50 / 296
Aggregate Check - Attention
The Aggregates are checked in the selected Checkmode. If Checkmode C was selected, but no Checkaggregate exists (e.g. Aggregate has now different definition, Checkaggregate was deactivated,…), Checkmode Q is selected if possible, else Checkmode A is performed (long runtime!).
Check messages are written to the Application Log entry of the process. If error is found the Application Log is red, but NO dump or further message is sent. Check the Application Log regulary.
The Check should not be run while the ChangeRun, RollUp or Delete is running, since the results can be misleading.
Redo AggregateCheck after changing aggregate definitions.
297
SAP AG 2005, TEWA50 / 297
AppendixAppendixAppendixAppendix
- F4 and Performance
298
SAP AG 2005, TEWA50 / 298
F4 (1): Where occurring / where to be defined?
F4 Help – where can it be influenced and where does it occur:
- Settings in the InfoObject Maintenance (transaction RSD1)
- Settings in the InfoCube-specific settings (transaction RSDCUBE)
- Differences Web Frontend ↔ BEx Analyzer Frontend
- Variables
- Navigation Area
- Query Designer
- XY in RSADMIN for Navigational Attributes
- Notes
- Exceptions
A) For a user the F4 help can occur in different situations in the BEx environment:
� A query is defined with input variables. When executing the query, a variable input screen
occurs, in which the characteristic values can be chosen via the F4 help.
� After executing a query, for the involved characteristics (and navigational attributes) you can
select filter values in the ´Navigation Area´. This so called dynamic filter value selection is done
via the F4 help.
� Already when defining a query in the Query Designer, a characteristic/navigational attribute can
be restricted. This so called fix filter value selection is done via the F4 help.
� As of BW Release 3.xX, to get the correct values for Navigational Attributes, they are retrieved
in a special way, i.e. the attribute’s master data table has to be matched with the so called X
table of the characteristic, to which the attribute is defined (-> further explanation – especially
concerning the performance – in one of the following slides).
B) The way, how the F4 help will work in the different situations, can be defined by settings to be
specified in:
� the InfoObject Maintenance (transaction RSD1) or
� the InfoProvider Maintenance (Transaction RSDCUBE -> Tab ‘Characteristics’ -> Main Menu:
‘Extras’ – ‘Structure-Specific InfoObject Properties’).
There are differences (which F4 settings are taken into account), depending whether the Web or the
BEx Analyzer is used! (see following slides)
299
SAP AG 2005, TEWA50 / 299
F4 (2): InfoObject Maintenance - RSD1 (1)
Query Def. Filter Value Selection:
-> Only Values in InfoProvider (Dimension)
-> Values in Master Data Table (Master Data)
Default
In the InfoObject Maintenance (transaction RSD1) at tab strip Business Explorer, the setting for Query Def. Filter Value Selection decides, which values will be offered in the F4 help, when restricting a characteristic in the Query Designer during query definition. The setting Values in Master Data Table is the default.
Values in Master Data Table considers all master data values which are visible at the
Master data/texts tab strip in transaction RSD1 of this characteristic.
Only values in InfoProvider considers all key figures of the InfoProvider and not only those of one query. These values can then be found in the relevant dimension table of the InfoProvider (in case the InfoProvider has a dimension table!).
300
SAP AG 2005, TEWA50 / 300
F4 (3): InfoObject Maintenance - RSD1 (2)
Query Execution Filter Val. Selectn:
-> Only Posted Values for Navigation (Query)
Default
-> Only Values in InfoProvider (Dimension)
-> Values in Master Data Table (Master Data)
In the InfoObject Maintenance (transaction RSD1) at tab strip Business Explorer, the setting for Query Execution Filter Val. Selectn decides which values will be offered in the F4 help, when selecting filter values for a characteristic in the Navigation Area after a Query was executed. The setting Only Posted Values for Navigation is the default, which means that those values are offered at the F4 of an executed query, which are relevant for this query’s definition (e.g. only posted characteristic values of
those key figures which are chosen within the Query Designer of this query).
Only values in InfoProvider considers all key figures of the InfoProvider and not only those of one query. These values can then be found in the relevant dimension table of
the InfoProvider (in case the InfoProvider has a dimension table!).
Values in Master Data Table considers all master data values which are visible at the Master data/texts tab strip in transaction RSD1 of this characteristic.
301
SAP AG 2005, TEWA50 / 301
F4 (4): InfoCube Maintenance - Structure-specific settings (1)
Only relevant for variable selection screens
With the Structure-Specific InfoObject Properties you can overrule the settings
of the InfoObject Maintenance (RSD1)
Attention: Up to BW Release 3.x this setting is only relevant for variable selection screens (in the Web and BEx), but NOT for filter value selectionafter Query execution and not for the characteristic restriction during Query
definition the Query Designer.
Changes are discussed for the next BW Release, but not for Rel. 3.x.
The settings can be defined as follows:
� Administrator Workbench (RSA1) – Choose ‘InfoProvider’ -> Change (or
alternatively transaction RSDCUBE)
� Choose the Characteristic tab strip and then in the main menu ‘Extras‘ –
‘Structure-Specific InfoObject Properties‘
302
SAP AG 2005, TEWA50 / 302
F4 (5): InfoCube Maintenance - Structure-specific settings (2)
Only relevant for variable selection screens
With the Structure-Specific InfoObject Properties you can overrule the settings
of the InfoObject Maintenance (RSD1)
Attention: Up to BW Release 3.x this setting is only relevant for variable selection screens (in the Web and BEx), but NOT for filter value selectionafter Query execution and not for the characteristic restriction during Query
definition the Query Designer.
Changes are planned for the next BW Release, but not for Rel. 3.x.
The settings can be defined as follows:
� Administrator Workbench (RSA1) – Choose ‘InfoProvider’ -> Change (or
alternatively transaction RSDCUBE)
� Choose the Characteristic tab strip and then in the main menu ‘Extras‘ –
‘Structure-Specific InfoObject Properties‘
303
SAP AG 2005, TEWA50 / 303
F4 (6): Variables and F4 (note 626887) (1)
Variable Screen with BEx Analyzer Query execution:
Variable Screen with Web Query execution:a) Browser b) RSRT+HTML display
For Variable Selection Screens in Web AND BEx execution:
The RSD1 setting ‘Query Execution Filter Val. Selectn’ should be taken into account
according to note 626887 (see next slide).
The ‘Structure-Specific InfoObject Properties’ of RSDCUBE (see slide before) can overrule this RSD1 setting.
304
SAP AG 2005, TEWA50 / 304
F4 (7): Variables and F4 (note 626887) (2)
Variables-F4 and InfoObject settings (RSD1) according to note 626887:
InfoProvider TypeQuery Definition
(Query Designer)Variable
Select Filter Value
(executed Query)
Basis InfoProvider 1. 2. 3.
MutiProvider with only
basis InfoProvider1. 2. 3.
Other InfoProvider Master Data Master Data 4.
As for the variable processing it is not clear, which InfoObject Maintenance (RSD1) setting – Query Def. Filter Value Selection or Query Execution Filter Val. Selectn –
is relevant, the following matrix is valid:
Legend:
1. The RSD1 setting for Query Def. Filter Value Selection is relevant.
2. If in RSD1 the Query Execution Filter Val. Selectn setting is Values in Master Data Table, then the master data table values will be taken, otherwise the dimension table values.
3. The RSD1 setting for Query Execution Filter Val. Selectn is relevant.
4. As Other InfoProvider cannot have a dimension table, only posted values will be shown if the RSD1 setting Only Posted Values for Navigation is defined (at Query Execution Filter Val. Selectn), otherwise the master data table values are taken.
For further information please check note 626887.
For Variable Selection Screens in Web AND BEx execution:
The RSD1 setting ‘Query Execution Filter Val. Selectn’ should be taken into account according to note 626887.
The ‘Structure-Specific InfoObject Properties’ of RSDCUBE can overrule this RSD1 setting.
305
SAP AG 2005, TEWA50 / 305
F4 in BEx (8): Dynamical Filters (‘Select Filter Value’ inNavigation Area after BEx query execution)
Example for which ONLY the InfoObject Maintenance (RSD1) setting Query Execution
Filter Val. Selectn is relevant.
The ‘Structure-Specific InfoObject Properties’ in RSDCUBE will NOT be taken into account!
306
SAP AG 2005, TEWA50 / 306
F4 in BEx (9): Fix Filters (‘Restrict’ Characteristic in Query Designer)
Example for which ONLY the InfoObject Maintenance (RSD1) setting Query Def. Filter Value Selection is relevant.
The ‘Structure-Specific InfoObject Properties‘ in RSDCUBE are
NOT relevant here!
307
SAP AG 2005, TEWA50 / 307
F4 in Web (10): Dynamical Filters (‘Select Filter Value’ inNavigation Area after Web query execution)
‘Select Filter Value’ in the Web
Default in Web:(see note 661251)
F4 mode ‘Q’
can be changedby adding theparameter&booked_values=M/D
to the URL
Note 661251 describes the following:
The F4 mode specifies in general whether all data from the master data tables (F4 mode: M), or only the data from the dimension of an InfoCube (F4 mode: D) or only the values that match the current navigational state (F4 mode: Q) are transferred.
Q is used as the default value in the Web, regardless of the settings. However, you
can override this behavior by attaching the booked_values=M (or D or Q) parameter to the URL (&booked_values=...) or in the Web template definitions.
The InfoObject Maintenance (RSD1) settings (Query Execution Filter Val. Selectn) are NOT relevant in the Web!
The ‘Structure-Specific InfoObject Properties‘ in RSDCUBE are NOT relevant in the Web!
308
SAP AG 2005, TEWA50 / 308
F4 in Web (11): Web Workaround for Dynamical Filters(‘Select Filter Value’ in Navigation Area)
A specific Web Template
can be created for a query, containing the Item ‘Generic Navigational Block’. For this Item you can specify in the properties the List of Characteristics (with F4 mode individually!) to be used in the navigational block
The restriction of note 661251 (Q is used as the default F4 mode value in the Web, regardless of the settings in RSD1/RSDCUBE) leads to the disadvantage that – even with booked_values=M (or D or Q) parameter – all the characteristics of the navigational block are handled with the same F4 mode.
As a workaround you can define a specific Web Template for a query, which can be
used within the URL by adding the parameter &template_id=<TemplateID>
This template should contain the Generic Navigational Block item. As DataProviderfor this item you have to choose your query.
In the properties of the Generic Navigational Block item you will find the setting ‘List of Characteristics’ , in which you can choose all the characteristics, which should beshown in the navigational block. For each of these characteristics you can maintain
individual F4 modes.
Attention: If using the ‘List of Characteristics’ setting, only those characteristics, which are chosen within this list, will be shown at all in the navigational block (even if the Query Designer contains more characteristics in the ‘Free Characteristics’ area!).
309
SAP AG 2005, TEWA50 / 309
F4 (12): Aggregate tuning for F4 possible? (1)RSRT (html) – Execute+Debug for F4
RSRT+HTML –> Execute + Debug
Performance of the F4 help might be tuned by aggregates. By using transaction RSRT (with HTML display), the ‘Execute+Debug’ function with the ‘Show aggregates used’flag can be taken, to find out the best possible aggregate – also for the F4 help!
310
SAP AG 2005, TEWA50 / 310
F4 (13): Aggregate tuning for F4 possible? (2)RSRT (html) – Execute+Debug for F4
Select Filter Value for ‘Country Key‘
Performance of the F4 help might be tuned by aggregates. By using transaction RSRT (with HTML display), the ‘Execute+Debug’ function with the ‘Show aggregates used’flag can be taken, to find out the best possible aggregate – also for the F4 help.
311
SAP AG 2005, TEWA50 / 311
F4 (14): Navigational Attributes (X & Y Tables)
Performance with the F4 help of navigation attributes (note 581079):
� Situation: As of BW Release 3.xX, new functionality was implemented: only those values should be shown for the navigational attribute’s F4, which are really only attribute values.This new feature can lead to long run-times at the F4 help, if the characteristic, to which the attribute belongs to, contains many master data values.
� Reason: When reading the data from the database, the attribute’s master data table is joined to the characteristic’s X-table.This can lead to an artificial increase of the attribute’s master data.Thus it’s vital that the database returns only unique records. Unfortunately the database optimizer does not chose the optimal execution plan for the SQL statement.
� Solution: The new 3.xX functionality can be switched off again as described in note 581079 (coding correction and):Table RSADMIN: OBJECT = RSDM_F4_NO_XYTAB_JOIN
VALUE = X
ATTENTION: This setting is valid system wide!
As soon as the execution plan will be improved in this situation, the RSADMIN entry will not be necessary anymore.
For more detailed information, please check note 581079 directly.
312
SAP AG 2005, TEWA50 / 312
�748623: Input help (F4) has a very long runtime – recommendations
�626887: Variables F4 and InfoObject settings
�661251: Filter value selection displays too few/too many values
important general information! (Default F4 mode Q in Web…)
�581079: Performance with the F4 help of navigation attributes
F4 (15): Notes
313
SAP AG 2005, TEWA50 / 313
When will the F4 settings have no influence on performance?
- Remote (Virtual) Cube
- ODS
- InfoObject is InfoProvider
- MultiProvider (containing ODS, …)
⇒ if no dimension tables are involved
The Master Data tables will be taken here – the F4 settings are not taken into account!
Attention: Disadvantage of Line Item dimensions => the F4 has to call the Master Data tables!
F4 (16): Exceptions
314
SAP AG 2005, TEWA50 / 314
� No part of this publication may be reproduced or transmitted in any form or for any purpose without the express permission of SAP AG. The information contained herein may be changed without prior notice.
� Some software products marketed by SAP AG and its distributors contain proprietary software components of other software vendors.
� Microsoft®, WINDOWS®, NT®, EXCEL®, Word®, PowerPoint® and SQL Server® are registered trademarks of Microsoft Corporation.
� IBM®, DB2®, DB2 Universal Database, OS/2®, Parallel Sysplex®, MVS/ESA, AIX®, S/390®, AS/400®, OS/390®, OS/400®, iSeries, pSeries, xSeries, zSeries, z/OS, AFP, Intelligent Miner, WebSphere®, Netfinity®, Tivoli®, Informix and Informix® Dynamic ServerTM are trademarks of IBM Corporation in USA and/or other countries.
� ORACLE® is a registered trademark of ORACLE Corporation.
� UNIX®, X/Open®, OSF/1®, and Motif® are registered trademarks of the Open Group.
� Citrix®, the Citrix logo, ICA®, Program Neighborhood®, MetaFrame®, WinFrame®, VideoFrame®, MultiWin® and other Citrix product names referenced herein are trademarks of Citrix Systems, Inc.
� HTML, DHTML, XML, XHTML are trademarks or registered trademarks of W3C®, World Wide Web Consortium, Massachusetts Institute of Technology.
� JAVA® is a registered trademark of Sun Microsystems, Inc.
� JAVASCRIPT® is a registered trademark of Sun Microsystems, Inc., used under license for technology invented and implemented by Netscape.
� MarketSet and Enterprise Buyer are jointly owned trademarks of SAP AG and Commerce One.
� SAP, R/3, mySAP, mySAP.com, xApps, xApp, SAP NetWeaver and other SAP products and services mentioned herein as well as their respective logos are trademarks or registered trademarks of SAP AG in Germany and in several other countries all over the world. All other product and service names mentioned are the trademarks of their respective companies. Data contained in this document serves information purposes only. National product specifications may vary.
Copyright 2003 SAP AG. All Rights Reserved
315
SAP AG 2005, TEWA50 / 315
� Weitergabe und Vervielfältigung dieser Publikation oder von Teilen daraus sind, zu welchem Zweck und in welcher Form auch immer, ohne die aus-drückliche schriftliche Genehmigung durch SAP AG nicht gestattet. In dieser Publikation enthaltene Informationen können ohne vorherige Ankün-digung geändert werden.
� Die von SAP AG oder deren Vertriebsfirmen angebotenen Softwareprodukte können Softwarekomponenten auch anderer Softwarehersteller enthalten.
� Microsoft®, WINDOWS®, NT®, EXCEL®, Word®, PowerPoint® und SQL Server® sind eingetragene Marken der Microsoft Corporation.
� IBM®, DB2®, DB2 Universal Database, OS/2®, Parallel Sysplex®, MVS/ESA, AIX®, S/390®, AS/400®, OS/390®, OS/400®, iSeries, pSeries, xSeries, zSeries, z/OS, AFP, Intelligent Miner, WebSphere®, Netfinity®, Tivoli®, Informix und Informix® Dynamic ServerTM sind Marken der IBM Corporation in den USA und/oder anderen Ländern.
� ORACLE® ist eine eingetragene Marke der ORACLE Corporation.
� UNIX®, X/Open®, OSF/1® und Motif® sind eingetragene Marken der Open Group.
� Citrix®, das Citrix-Logo, ICA®, Program Neighborhood®, MetaFrame®, WinFrame®, VideoFrame®, MultiWin® und andere hier erwähnte Namen von Citrix-Produkten sind Marken von Citrix Systems, Inc.
� HTML, DHTML, XML, XHTML sind Marken oder eingetragene Marken des W3C®, World Wide Web Consortium, Massachusetts Institute of Technology.
� JAVA® ist eine eingetragene Marke der Sun Microsystems, Inc.
� JAVASCRIPT® ist eine eingetragene Marke der Sun Microsystems, Inc., verwendet unter der Lizenz der von Netscape entwickelten und implementierten Technologie.
� MarketSet und Enterprise Buyer sind gemeinsame Marken von SAP AG und Commerce One.
� SAP, R/3, mySAP, mySAP.com, xApps, xApp, SAP NetWeaver und weitere im Text erwähnte SAP-Produkte und –Dienstleistungen sowie die entsprechenden Logos sind Marken oder eingetragene Marken der SAP AG in Deutschland und anderen Ländern weltweit. Alle anderen Namen von Produkten und Dienstleistungen sind Marken der jeweiligen Firmen. Die Angaben im Text sind unverbindlich und dienen lediglich zu Informationszwecken. Produkte können länderspezifische Unterschiede aufweisen.
Copyright 2003 SAP AG. Alle Rechte vorbehalten