Aggregates on BPC Generated Cube

[manohar_pappireddy]

Aggregates on BPC cubes

EPM query performance is major concern for all the clients in SAP BPC.

When I posted about aggregates on BPC generated cubes to improve the performance, lot of people opined that, they would improve the query performance.

http://scn.sap.com/thread/3621371

In my case there are around 10 categories and most of the time users use only 1 category. So I wanted to create aggregates on Rolling Forecast category.

Created 1 aggregate for each fiscal year on Rolling forecast category. There is no query performance improvement at all. Query run time is same with and without aggregates.

Reason:

When reading the data from SAP NetWeaver BI Info Cubes, SAP BPC uses two different SAP NetWeaver technologies.

RSDRI – is the function group in SAP NetWeaver BW that deals with access to Info Providers. In the SQE, an RSDRI query calls the internal SAP NetWeaver BW functions to read data from an InfoProvider and accesses the E and F fact tables. This is the preferred query mode as RSDRI queries are faster than MDX queries. However, RSDRI queries do not bring in aggregated data, just base level information.

MDX – SAP NetWeaver BW exposes OLAP BAPIs to access data from an InfoProvider through MDX. The MDX query method in SAP BPC generates the MDX statements required to retrieve data with the SAP BPC application logic applied. It is always used when there are dimension member formulas, measure formulas, or hierarchy parent values (2014.TOTAL, Total Organization) to be retrieved and are often slower than RSDRI queries.

RSDRI query can read the data from aggregates and query performance can be improved considerably.

If you see the below screenshot, RSDRI query is executed. Refreshed the same report with and without aggregates.

(Cleared the cache before each execution.)

To have RSDRI query we should not use any custom measures or parent members.

All dimension members should be base level.

But if you use

1.       1.Custom measure (Except Periodic, QTD, YTD)

2.       2.Member formula dimension member in your report

3.       3.Hierarchy Parent(Total.2014,Total Organization)

MDX query would retrieve the data.We can get the infromation(whether the  EPM report executing RSDRI query or MDX query)

in SLG1 also.

But MDX query would not read the data from Aggregates and read the data with it’s own logic. In this case aggregates wouldn’t help to improve the query performance.

Usually we have at least 2-3 dimensions which we query on parent hierarchy in any EPM report.

Only case where we use all base members is input forms.

Conclusion: If you use base members for all the dimensions in EPM reports in most of your reports, you may get benefit with the creation of aggregates, else, it is of no use.