SAP BW aggregates minimize the resources needed to report on queries.Like database indexes, aggregates improve system performance without the need for end-user intervention or end-users having to know how to perform the procedure. Aggregates are created by the database administrator. Users benefit from improved system performance without having to make any changes themselves. When data is loaded from an InfoSource and into the InfoCube, the data is summarized physically on the specified level of the InfoCube dimensions.
When data is retrieved for a query, the OLAP processor determines dynamically if there is an aggregate available that meets the requirements of the query. Even if the parameters of the query are changed, for example, if a filter value is specified, the OLAP processor continues to summarize the data dynamically.Only one aggregate is used per query-step. An Info Cube can have more than one aggregate.
Despite the fact that the InfoCube stores data in a structured star schema, you could still be faced with a situation where a large number of queries do not have acceptable response times, because there are too many data records in the fact tables, and it takes too long to join dimension data, master data, and hierarchy data.
Using aggregates reduces the volume of data that is accessed per query. Within the aggregate, data can be summarized by time, by any of the dimension characteristics, or by the available hierarchies. Since this reduces the amount of data that is required, query performance is improved as a result. Aggregates that are created and filled with data in BW can be used immediately in the queries belonging to this InfoCube. The logic that is applied to the update rules that fill the InfoCube with data is also applied to aggregates.
Defining aggregate groups according to fields:
Ÿ ‘ * ’ include, create a summary for this field.
Ÿ ‘ ’ omit, do NOT create a summary for this field.
Ÿ ‘ F ’ include with a fixed value, create a summary for this field with the fixed value defined.
Ÿ ‘ H ’ hierarchy level, create a summary for this field at the defined hierarchy level.
Properties of Aggregates
Aggregates have 3 names:
Unique 25-figure name generated by the system
6-digit aggregate name for the database table (generated in number range from 10000)
User-defined name for description only (text)
6-digit aggregate name for the database table (generated in number range from 10000)
User-defined name for description only (text)
You are able to deactivate aggregates, but all the data is deleted if you do.
More than one key characteristic (‘*’ defined) is allowed in an aggregate.
The combination of structurally time-dependent hierarchies and time-dependent characteristics is not
permitted in aggregates.
More than one key characteristic (‘*’ defined) is allowed in an aggregate.
The combination of structurally time-dependent hierarchies and time-dependent characteristics is not
permitted in aggregates.
If the InfoCube uses a key figure for which a characteristic has been used to define exception aggregation, this characteristic must be included in the aggregate and defined as '*'.
Monitor the existing aggregates to determine if they are still being used effectively.Ÿ Use data from the RSDDAGGRDIR table.Ÿ Key fields for the analysis:
objvers = ‘A’ - aggregate is active
lastcall - timestamp showing when used most recently by a query calls - number of times the aggregate has been used by a query avgfactreduce
- average number of rows per aggregate record num_entries - number of records in the aggregate
objvers = ‘A’ - aggregate is active
lastcall - timestamp showing when used most recently by a query calls - number of times the aggregate has been used by a query avgfactreduce
- average number of rows per aggregate record num_entries - number of records in the aggregate
The ‘I’ information icon in the screen for maintaining aggregates in the transaction RSDDV also provides detailed information about how the aggregate is used.
Identify and delete aggregates that are:
Ÿ No longer or rarely used, or are so large that there is little or no benefit in using them.
The aggregate using the Customer characteristic is not affected if the customer group is changed. The aggregate using Customer/Customer Group (characteristic/navigation attribute) improves system performance when data is read using the navigation attribute.The price of this improvement in performance is an increase in the amount of maintenance required when the master data is changed.
Be especially careful when you create an aggregate containing a navigation attribute. This type of aggregate can be costly to maintain.Aggregates must be reconstructed after changes are made to master data attributes or hierarchies.Navigation attributes and hierarchies have two versions - an active version and a modified version.Since changes to this data invalidate the aggregates, you are not able to activate hierarchies or navigation attributes directly. The most you are able to do is to tell the system that you want it to activate certain hierarchies and navigation attributes.When a user makes changes to the data, the system continues to use the old data in reporting until the aggregates have been reconstructed.
If changes are made to hierarchies and attributes in characteristics, and these hierarchies and attributes are used in aggregates, you have to make changes to the structure of the aggregates to prevent inconsistencies and to adjust the data accordingly.When you change the structure, all the aggregates that are affected by the changes that have been made to the hierarchies and to the InfoObjects are adjusted in all of the InfoCubes. The change run may take a considerable amount of time to complete.
The amount of time the change run takes to complete depends on the number of navigation attributes and how often the master data upload takes place.During the change run, you are able to report only on the old hierarchies and attributes. Active versions of the changes are available when the change run has finished. This also applies to reporting using InfoCubes that are not affected by the change run, but do use the modified hierarchies and attributes.
The ABAP report RSDDS_CHANGERUN_MONITOR tells you which objects are affected by the change run. The report displays a list telling you which characterisitcs and hierarchies are activated and which aggregates are affected. If the change run is still active, the list also tells you whether or not an aggregate has been adjusted already.
Once the list tells you that the hierarchies have been activated, these modified versions of the hierarchies are available immediately, provided that they do not have any aggregates assigned to them.If aggregates are assigned to the hierarchies, you can deactivate these aggregates and use the hierarchies immediately. Otherwise, you are not able to see the changes that have been made to the hierarchies until the change run is complete.Before the change run starts, the hierarchies are selected for inclusion in the change run either manually or automatically after the upload.
Change Run
If an aggregate is affected by only minor changes, it is not neccesary to reconstruct the aggregate completely. In this case, the aggregate is simply adjusted to match the changes. The old data records are updated negatively and the new data records are updated positively.However, after a certain point, this type of modification becomes more complicated than a complete reconstruction of the aggregate. In the system settings there is a threshold value determining the point at which the system switches from updating only the changed records to a complete reconstruction of the aggregate. You are able to set this threshold value yourself, and decide at what point the number of changes warrants a complete reconstruction of the aggregate. Go to the implementation guide and choose the following path; Business Information Warehouse - General BW Settings - Maintain Aggregate Change Run, and set the threshold value that you want to use.
You can also schedule the change run using the direct execution of a report. You use the RSDDS_AGGREGATES_MAINTAIN program name to do this. You can schedule the program as a regular background job, or add it to an event chain.
When data is loaded into the InfoCube whole requests are loaded at a time. Each of these requests has its own request ID (RNSID) that is included in the fact table of the package dimension. This allows you to look at individual requests in detail. One advantage of using request IDs is that it allows you to delete whole requests from an InfoCube.
Roll-up
Steps in the Roll-Up:
1. For each new request a new request SID (RNSID) is stored in the fact table.
2. Roll-up the new requests in the aggregates.
3. Reset the read pointer RNSID.
4. Compress aggregates.
Roll-up finishes at an RNSID where all the smaller RNSIDs have the status OK. In the Administrator Workbench under Environment - Automatic Request Processing, you can specify that the data is rolled-up in the aggregates following a data upload.
Rows that are added to the InfoCube are available for you to use only after the aggregate has been updated. This guarantees the consistency of the reporting. After new rows have been added, there are three steps that you must carry out before you are able to report on the new data:
ŸTechnical check (performed automatically by SAP). The number of rows in the input file is compared with the number of rows that have been added, for example.
Quality approval (can be set to run automatically). Checks, for example, that the sum of a key figure is nnnnnnn,nn.
Roll-up for fact aggregate (can be set to run automatically).
We recommend that you roll-up fact aggregates regularly.In the analyser before the roll-up, you are able to use the 0S_RQMRC (current data) variable to restrict the request ID (0REQUID) for the characteristic to also allow you to display and check the data that has not yet been rolled-up in the report.Once compression is complete you are no longer able to delete requests from the agggregates. If requests containing errors have been rolled-up in the aggregates, you must delete and reconstruct the aggregates that are affected.
Optimizing Aggregates
Aggregates are optimized by importing data from the BW statistics InfoCube and from the queries. There are three areas in which you optimize the selection of aggregates.Defaults for new aggregates - for all the aggregates proposed by the system.Defaults for aggregates that you want to delete - for all aggregates that have been activated, but also selected for deletion.Available aggregates - for all aggregates that have been activated and filled with data, and that you do not need to modify.
Reduce query costs by reducing the amount of data.Reduce the amount of data that is requested. Aggregates without navigation attributes or hierarchies must be < 5-10 % of the fact table in size. Ÿ Aggregates with navigation attributes or hierarchies must be < ~1 % in size, depending how often the structure is changed.
Create aggregates only when they are specifically needed.
Delete any aggregates that are no longer used.
Create aggregates only when they are specifically needed.
Delete any aggregates that are no longer used.
Optimizing Aggregate Selections
SAP BW statistics includes tools that analyze queries and tell you when you might need to think about creating an aggregate.SAP assumes that improvements in system performance for aggregates that contain navigation characteristics are minimal compared to those of a simple aggregate.
You must be in the expert mode if you want to create an aggregate that contains a characteristic and one of the navigation characteristics belonging to it.Attributes with values that do not change can be added to the dimension without increasing the amount of maintenance required by the aggregates.You have to consider whether an increase in maintenance costs is worth a slight improvement in system performance. Conider the following factors:
The frequency in which changes are made that result in you having to reconstruct the aggregate
Ÿ
Amount of non-operative time available for the reconstruction (to avoid reducing system performance during normal working times).
Related Posts
I read this post your post so nice and very informative post thanks for sharing this post. i Search for SAP Evaluation many times but this is the perfect for gaining the knowledge about SAP Evaluation. Thank you so much.
ReplyDelete