Hi,
ODS PERFORMANCE TUNING
Performance tuning on an ODS is not as easy as on InfoCubes because you don't have aggregates. If you already have defined indexes check if the indexes are used in your queries. You can either check in in the RSRT when you do Execute & Debug and set a breakpoint at Show SQL-Statement and Show Execution Plan or trace your ODS with ST01. You should also check the database statistics if you haven't done so.
If these tips are not enough because your ODS is quite huge, create an InfoCube with the identical InfoObjects. With automatic delta upload activated in the ODS you can always keep an exact copy of the ODS data in your cube and build your reports on the cube with aggregates and all other performance tools you need.
There is a great presentation on performance on the service marketplace (service.sap.com/bw, register with your OSS-user, then click on performance. In the upper part there is a PDF called performance workshop that really goes into details.
...for the ODS reports you have less tools available in order to doing a tuning performance activity than the infocube...
OSS Note 444287 'Checking the index storage quality'...I think it contains a useful check to perform in order to assure a good quality of your index !
This time its with the performance of ODS.
Since no aggregates can be built on my ODS, what are all the methods in which i can optimise the runtime of a query which is using an ODS?
basically there is not very much to do. But just some tips.
1. Do not use nav.-attributes in reporting if possible
2. Create additional index in the maintenance of your ods on the fields that are mostly used in reporting.
Just build an index - it all seems so simple, but if you do not have a DBA background, identifying what column(s) make sense to include in an index or indices takes some effort.
Can't speak to other DBs, but in Oracle, some research and thought needs to go into deciding what to index, if a concatenated (aka compund) index would be good, and if so, what order the columns should be in. Adding an index also adds overhead to the load process and consumes disk space, so they are not something you just casually create without some thought. the more indices you build, the more overhead and storage consumed.
First thing you really need to do is get a handle on what the queries that are running look like. A query that must read all the rows to get a total will not benefit fomr an index. A query that must read more than a small percentage of the rows (somewhere in the 5% range) probably will not use the index either.
So you must determine if a characteristic is sufficiently selective, that if a user provides an variable input value or chooses a filter in a workbook, that value is selective enough that an index on it will be used.
Perhaps most queries filter on the same three characteristics, then you might want to consider an index built on all three columens, probably choosing the most selective characteristic as the first column.
Then you have a balancing act to see if you can create an index used by differnt queries, or do you need a second index.
Ideally, you should consult with a DBA.
Partitioning can provide a tremdous improvement, but again, it depends on data and the queries.
Could any of the following ODS settings individually or in combination have an impact on performance?
BEx Reporting
Set quality status to 'OK' automatically
Activate ODS object data automatically
If you built (as you said) some query on your ODS, you need (as you already know) BEx reporting checkbox flagged.
Clearly this have some impact when you activate every request since the systems have to work with SIDs too (in order to have the reporting capabilities and to let OLAP processor work).
Anyway, in order to identify the relevance of this impact, this depends from many other factors (how much powerful is the system you have...); but if every single request (as you wrote) contains only a few records, don't worry !
Set... and Activate... flags: no performance impact because are a simple automation process...anyway, you have to activate your ODS!!!
-Shreya
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.