cancel
Showing results for 
Search instead for 
Did you mean: 

ODS index

Former Member
0 Kudos

Hi,

I want to know how to delete and create ODS index in process chain. I only see it in cube load, not ODS.

Thanks.

Accepted Solutions (1)

Accepted Solutions (1)

Former Member
0 Kudos

ODS Index creation and deletion will be taken care by the basis team. Please check with your basis team. Also it is not a best practice and definitely not required to delete and create the index in the ODS every time.

Former Member
0 Kudos

Do you know the reason? Why we need delete the index from cube and regenerate again, not ODS? For ODS, the additional index will also affect loading performance.

Thanks

Victor

Former Member
0 Kudos

hi,

Database has to maintain both indexes and actual database tables. Thus slows down the performance of DML (inserts, deletes,update) statements. For each statement it has to update both index and actual tables.

Before loading drop indexes and load data and re-create indexes is best practises to do.

otherwise if u do not delete the index before loading data to cube it will impact the performance 'ocz the database has to take care of both indexes and loading data to cube..

Additional indexes improves performance of reading data from the ODS (query or extraction to another InfoProvider), if the index was hit.

but will slow down the loading processes Updating the ods takes some more time because of the overhead for maintaining the additional indexes.

more info on indexes http://help.sap.com/saphelp_erp2004/helpdata/en/cf/21eb20446011d189700000e8322d00/frameset.htm

hope u clear

Assign Points if useful

shreya

Former Member
0 Kudos

Thanks for reply. That goes back my question, how to delete and recreate the additional index in process chain?

Victor

Former Member
0 Kudos

hi,

when u select ur data target in process chain.. u see the check boxes options there

1. delete index

2. generate index

3. load data

4. initial fill up to aggr's

5 rollup... options

u can select the check boxes..

Shreya

Former Member
0 Kudos

Victor,

it's a good Question. we don't have any process to that automatically in the process chain.

the reason is because of Infocube Model Vs ODS scenario. in ODS only 3 tables. for cube so many tables in the model.

you can't schedule automatically in the Process Chain for ODS.

All the best.

Regards,

Nagesh Ganisetti.

Former Member
0 Kudos

What you said is ok for infocube, I can not see all these options for ODS. Only following is available,

execute info package

active ODS..

Update ODS Object data (Futher Update)

Thanks

Former Member
0 Kudos

Thank you for confirming that. Usually what we do for ODS index during loading?

Former Member
0 Kudos

we won't delete the indexs for ODS during the loading. we have Index on one of the ODS, we are not deleting that. Refresh the DB statistics that will give good loading performence.

Not only in for ODS, we created index on one table in the SAP System while i'm working on ABAP. we never deleted that.

One more thing is loading performence is good for existing records, bad for new records i think (update will give good, insert is bad).

I think we can write a ABAP program to this. I'm not sure. we can keep ABAP Program in Process Chain.

All the best.

Regards,

Nagesh Ganisetti.

Former Member
0 Kudos

Thanks

Answers (1)

Answers (1)

Former Member
0 Kudos

This might not apply to all DBs that BW runs on -

InfoCubes have mulitple <b>bitmap</b> indices built on the fact table (except for line item and high cardinality dimensions which are btree indices).

The indices that BW builds on ODS are <b>btree</b>.

The database can update the <b>btree</b> indices reasonably quickly. <b>Bitmap</b> indices, however, due to their design, are NOT updated very efficiently and that is the reason it is recommended to drop them before a load and rebuild them afterwards.

As with any rule, there are exceptions, with very large InfoCubes, the time to rebuild the indices can become significant. And in these cases, you might opt not to drop and rebuild with every load, and instead, perhaps drop and rebuild over a weekend or monthly. Would depend on the volume of new records being added relative the the number of records in the fact tables.

Oracle 10i is supposed to have be able to update bitmap indices more efficiently, so that might change what we do in BW.