Secondary Index - Improving DSO insert/read performance

[snitwipro]

Agenda: This document gives an overview of the role a secondary index performs while query based on DSO runs or a database insert operation is being executed. This concept is not only limited to BW level but also gives us the view at database level.

Author Bio: Sourav Banerjee

Sourav has more than 6 years of experience in SAP BI. He has worked in different enhancement and support projects. He is currently working with IBM India Pvt Ltd.

Secondary Index at a glance and How DSO load/read operation works at database level: DSO's are created at administrator workbench. It consists of new table, active table and change log table (standard DSO). When ever a new request comes to a DSO, we activate it in order to make it available for reporting. This means that the data can now be reportable through a Bex query. The active table of the DSO is nothing but a transparent table with the same structure as the  DSO. Sometimes it is not possible to achieve acceptable query performance if the query is based out of a DSO/ an infoset based on DSO. The possible reasons might be lack of uniqueness, data volume etc. For example, let us assume that a DSO is having customer number and year as in the key field. However the query is having an input variable on Fiscal year/period. Therefore upon execution, the select query runs on the oracle table with a value not available as primary key which results in poor performance. This is where secondary index comes into picture. These are set of fields (single or multiple, fields available as primary index can also be specified as secondary if required for a combination) defined at database level to gather similar records and to get additional aggregation level. This means when a query runs on variable fiscal year/period, a database sort happens on the oracle table prior to the read and hence response time would be improved.

Steps carried out by the database while reading data:

Whenever a Bex query runs on a DSO, the following set of operations are executed -

Since all the operations are carried out at database level, it is necessary to create the index at oracle also. Moreover once it is created, it has to be adjusted also to make it applicable for all the existing data. Needless to say that if your DSO does not contain any data, adjusting database would take very less time. Typically for a large DSO, adjusting index takes 20-30 minutes of time, again this is not an exact figure, this depends on the volume of data available.

Database object check:

Example of a typical SQL query for reading a DSO is given below -

Select * From <Active table name> where <Fieldname> = '0080005000' FETCH FIRST 200 ROWS ONLY OPTIMIZE FOR 200 ROWS WITH UR - OPTLEVEL( 5 ) - QUERY_DEGREE( 1 ) - LOCATION ( /1BCDWB/DB/<Active table name>, 1205 ) - SYSTEM( <logical id of your BW system>, SAP<logical id of your BW system ).

Above is the sample SQL query which runs at the time of data read from the DSO. Therefore it is always important to make sure that whatever changes happening at the transparent table level should be reflected at the corresponding database object. Below are the steps using which one can test the consistency of a DSO table -

Step1: Go to Tcode  SE14 and select the active table of the DSO. Press edit

Step2: Click on 'check'. Two options will be displayed - Database object and Runtime object.

Step3: Check the database object and the status of the indexes.

Steps to create secondary index:

Step1: There are two ways to create secondary index on a DSO -

Option1: Go to change screen of the DSO in question. Right click on the indexes and select create new indexes.

Please note that index naming convention is 010, 020,.... and you can select the property either as unique or non-unique. Now you can drag the character to be used in the index. See image below -

Now save and activate the DSO.

Option2: Open the active table of the DSO in SE11. Select indexes and press create to create a new secondary index.

Then in the next screen, specify field name and description. You can select multiple fields of your choice.

You can either select unique or non unique index.

Index on all database system: This option let you create index for all type of database like Oracle, informatica, ms sql etc.

For selected database systems: With this option you can select any specific database type of your choice. Index will be valid for only that database.

No database index: With this option, index will not be created at database level at all.

  For Unique index, database filling is always required. Now save and activate your index.

Step2: Please note that, index is now created only at the BW server. In order to utilize the effect of this index, it must be created and filled at database. Open the DSO active table in SE14. Select indexes. Select your index (20) and press ok.

DB index name will be <active table name>~20.

Step3: Select background and press activate and adjust database. Then go to Sm37 to view the completion status.

Now your secondary index is ready for use.

Note: Sometimes due to large volume of data, secondary index filling might run for a very long time. Please don't be impatient and cancel the job. If you cancel the job for index filling, all the secondary index including the primary indexes would be deactivated and read/write performance would be unexpectedly low.

*************************************************** Thank You ********************************************************************

[karthick_ravichandran]

Hi saurauv,

Helpful document . Thanks for Posting.

Thanks

karthick

[Former Member]

Hi,

Very useful document.

Thanks & Regards,

Chandra Sekhar.

[Former Member]

Very nicely explained.

[former_member218377]

Hi,

thanks for posting

thanks

nishant

[former_member214274]

Hello,

Thanks for complete info on secondary indexes.

Akanksha

[sai_v]

Hi,

Thank you for posting the good one.

Sai.

[former_member185177]

Excellent info...Thanks for sharing.

[former_member218583]

I have some Z tables where the data has about 15 crores records. I want to initiate a non unique secondary index on just one of the field for this. Can I please know the estimate time it will take?

[Former Member]

Very helpful n nice article on secondary index creation for DSO.

 

THANKS,

Rupendra

[former_member318151]

Hi,

Nice blog, very well explained.

Have got a query on how the secondary indexes get updated on subsequent data loads. Do we need to schedule index creation jobs after every load or is it system will take of refreshing indexes?

 

Thanks,

Ravi Poluru

[AdemGuler]

Useful article. Thank you for sharing.

[parin_vora]

Very Helpful Document.

Thanks for Sharing Knowledge and Experience.