AGGREGATES AND MULTICUBES

AGGREGATES

Aggregates are the small Baby Cubes.

an aggregate is a materialized, aggregated view of the data in an InfoCube. in an aggregate, the dataset of an InfoCube is saved redundantly and persistently in a consolidated form into the database and mainly used to improve the reporting performance.

SAP BW OLAP processor retrieves data from an appropriate aggregate during a query run in the navigation step. if no appropriate  aggregate exists, the BW OLAP Processor retrieves data from the original InfoCube instead.

Aggregates are the information stored in a DWH in a summarized form.

LIFE CYCLE OF AGGREGATES:

aggregates are defined by the DBA against an existing InfoCube and are updated when loading data into InfoCubes from InfoSource using the same update rules of the InfoCube.

AGGREGATES HAVE 3 NAMES:

1.       A system defined 25 digit unique name.

2.       A 6 digit integer number.

3.       A user defined description.

When do you choose Aggregates?

It is recommended to use aggregates in following situations:

1.       If an InfoCube contains lot of data.

2.       If attributes are used in queries often.

3.       If the execution and the navigation of a query data leads to delays with a group of queries.

4.       If you want to speed up the execution time and the navigation of a specific query.

5.       If you want to speed up reporting with characteristics hierarchies by aggregating the data into a specific hierarchy level.

AGGREGATION LEVEL:

An aggregation level indicates the degree of details to which the data of the InfoCube is compressed.

There are three levels o Aggregation:

1.       ALL CHARACTERISTICS (*)

2.       HIERARCHY LEVEL (H)

3.       FIXED VALUE (F)

1.       ALL CHARACTERISTICS (*)

Data is grouped by all the values of characteristics (or) navigational attributes.

2.       HIERARCHY LEVEL (H)

                Data is grouped at Hierarchy level.

3.       FIXED VALUE (F)

                The data is filled according to a single value.

IMPORTANT NOTES ON AGGREGATES:

·         an aggregate holds transaction data

·         an InfoCube maintains more than one aggregate

·         aggregates are built against InfoCubes only but not with ODS.

·         Aggregates are used for KeyFigures with aggregation property as ( SUM[], MAX[], AVG[]) and not on display attributes.

·         Aggregates must be recreated after the changes in the master data or hierarchies.

NOTE: We use the function Attribute Change Run to update the aggregates with the modified master data attributes and Hierarchies.

Aggregates will be maintained in a table RSDDAGGRDIR

WHAT ARE THE KEY POINTS TO IMPROVE PERFORMANCE DURING LOADING & REPORTING:

During data loading, switch off aggregates to improve loading performance.

During reporting, switch off aggregates to improve reporting performance.

MULTICUBE:

Defining a MultiCube:

A MultiCube is a union of basic cubes. The MultiCube itself does not contain any data, rather the data reside in the Basic Cubes. To a user, MultiCube resemble a Basic Cube. When creating a query, the user can select characteristics and KeyFigures from different BasicCubes.

Why we use MultiCube?

Most users need to access only a subset of information(data) in an InfoCube.

Example: Among 4 regions North, South, East & West, East region users are not allowed to access other regions data. In that case, we could create a new InfoCube, which contains only East Region sales data, a subset of the original InfoCube. By this during query execution, the process will be smaller and thus performance  is increased or enhanced.

What SAP recommends, Aggregate or MultiCube?

SAP recommends MultiCube. Why?

Because queries can be created on InfoCubes but not on Aggregates.

As we know that the InfoCube contains huge data regarding SALES and DELIVERY data from 4 regions. If we are running a query on both sales and delivery of a particular East Region, it searches data in the whole InfoCube which is larger, may increase the query run time, consequently degrading the performance in response time which will be slow.

Also when we do cross subject analysis from purchase to inventory, to sales to delivery and billing, the InfoCube becomes soo large that could not manage it. Thus query performance  will be degraded.

To overcome this problem, BW offers a technique called MultiCube, so we must build a larger InfoCube that contains both sales and delivery data.

The MultiCube contains no data, but simply links the basic cubes together.

On MultiCube we can create queries as we did on cube.

FEW QUESTIONS:

·         What are aggregates?

·         What are MultiCubes?

·         Differences between Aggregates & MultiCubes

·         Can we create queries on aggregates or MultiCubes, how?

·         What are aggregation levels?

·         Aggregates have 3 names. What are they?

·         In what situations you use or recommend aggregates?

·         Why SAP recommends MultiCube?

·         In which table aggregates maintained ?

·         Are the aggregates used for KeyFigures or display attributes?

·         Did you created Aggregates & MultiCubes? Explain the scenario