| Rules for Efficient Aggregates
By thumb rule we can say that aggregates improve Query
performance.
Rules for Efficient Aggregates: "Valuation" column evaluates each aggregate as either good or bad. The valuation starts at "+++++" for very useful, to "-----" for delete. This valuation is only meant as a rough guide. For a more detailed valuation, refer to the following rules: 1. An aggregate must be considerably smaller than its source, meaning the InfoCube or the aggregate from which it was built. Aggregates that are not often affected by a change run have to be 108 times smaller than their source. Other aggregates have to be even smaller. The number of records contained in a filled aggregate is found in the "Records" column in the aggregates maintenance. The "Summarized Records (Mean Value)" column tells you how many records on average have to be read from the source, to create a record in the aggregate. Since the aggregate should be ten times smaller than its source, this number should be greater than ten. 2. Delete aggregates that are no longer used, or that have not been used for a long time. The last time the aggregate was used is in the "Last Call" column, and the frequency of the calls is in the "Number of Calls" column. Do not delete the basic aggregates that you created to speed up the change run. Do not forget that particular aggregates might only not be used at particular times (holidays, for example). 3. Determine the level of detail you need for the data in the aggregate. Insert all the characteristics that can be derived from these characteristics. For example, if you define an aggregate on a month level, you must also include the quarter and the year in the aggregate. This enhancement does not increase the quantity of data for the aggregate. It is also only at this point, for example, that you can actually build a year aggregate from this aggregate, or that queries that need year values are able to use this aggregate. 4. Do not use a characteristic and one of its attributes at the same time in an aggregate. Since many characteristic values have the same attribute value, the aggregate with the attribute is considerably smaller than the aggregate with the characteristic. The aggregate with the characteristic and the attribute has the same level of detail and therefore the same size as the aggregate with the characteristic. It is however affected by the change run. The attribute information in the aggregate is contained in the aggregate only with the characteristic using the join with the master table. The aggregate with the characteristic and the attribute saves only the database – join. For this reason, you cannot create this kind of aggregate. If they are ever going to be useful, since otherwise the database optimizer creates bad execution plans, you can create an aggregate of this kind in the expert mode (in 2.0B: In the aggregate maintenance select an aggregate: Extras > Expert Mode, otherwise enter "EXPT" in the OK code field). The factor 10 used in the following, is only meant as a rule of thumb. The exact value depends on the user, the system, and the database. If, for example, the database optimizer has problems creating a useful plan for SQL statements with a lot of joins, aggregates with smaller summarization are also useful, if this means that joins are saved.
Get help for your SAP BW problems
SAP BW Books
SAP BW Tips
Best regards,
All the site contents are Copyright © www.erpgreat.com
and the content authors. All rights reserved.
|