Aggregates on BPC Generated Cube
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.
Very useful point , Thanks for sharing Manohar....
I have a question: Why do you state that MDX cannot use aggregates? Granted creating aggregates which are suitable for MDX is more complex and you probably require more aggregates that in the RSDRI case but I have created an aggregate and can see that BPC reports executed via EPM as MDX queries (UJSTAT) hit the aggregate.
From a technical point of view I can see no real reason why an MDX query would not try to use aggregates. They main difficulty would be the MDX slicer which is typically restricted using the dimension hierarchies. Thus one would need hierarchy level aggregates in order to get them being used by EPM in the MDX case.
Creating aggregates as a subset of data without a significant aggregation factor (i.e. summarization of records) will typically not lead to large improvements if the database indexes are fine as such (no matter if you use RSDRI or MDX). But if you manage to create aggregates with significant aggregation factors I would expect to see an improvement even with MDX being used.
Can you please elaborate a little bit on this topic and share your experience?
Kind regards,
Hanno.
Hi Hanno,
Thank you very much for the feedback/response.
So you mean to say need to create aggregates on each month?Then we need to have lot of aggregates where in trade off for space consumption,more time for LO job.
I have observed that,even if we use custom measures also MDX query is being generated.
Any how will check if MDX query can also hit aggregates again.
Thanks,
Manohar
I'm slightly confused. The text says "However, RSDRI queries do not bring in aggregated data, just base level information" but also says "RSDRI query can read the data from aggregates and query performance can be improved considerably." and "But MDX query would not read the data from Aggregates and read the data with it’s own logic" could you explain what is meant?
The wording might be a little bit confusing. Aggregates and aggregated data are really two different things.
The (BW) "aggregate" is a technical object in BW which is basically a shadow cube that containes an aggregated subset of the base' cubes data without all the details. This is typically achieved by omitting some dimensions.
Aggregated data means hierarchy nodes in the EPM report and not just base level members.
As I stated an MDX query can access BW aggregates from a technical point of view. It is however not easy to construct BW aggregates which will actually get used by MDX as the generated MDX queries typically restricts all dimensions, i.e. BW aggregates which simply omit a dimension to do the aggregation will never get used because they to not contain the required level of detail (or at least BW cannot know/determine that they would work). Only hierarchy level aggregates in BW would work to some extent.
Kind regards,
Hanno.
Hi Hanno, thanks for the clarification
I had created aggregates with only 2 dimensions(Category and Time) at that time and it didn't help for query performance.But now I have created aggregates with all the dimension members with different hierarchy levels and * for some dimensions and fixed value for some dimensions.Results are promising.Will share more results with my observations.
Thanks to Hanno for critical review.