InfoProvider Properties

[Former Member]

Hi all.

Does anyone know the difference between

"Del. index before every data load & then re-create"

and

"Del. index before each data load & then re-create"

in the DB Performance Tab of InfoProvider properties?

Thanks all for your help.

Mark

[Former Member · Accepted solution]

<b>Rationale -</b>

When you load large quantities of data in InfoCubes, you should delete

the secondary indexes before the loading process and then recreate them afterwards if the following applies: The number of the records that are loaded is big in comparison to the number of records that already exist in the (uncompressed) F fact table. For non-transactional InfoCubes, you must delete the indexes to be able to carry out parallel loading.

<b>Detailed-</b>

If your uncompressed F-table is small, drop indices before loading.

In this case, it is faster to rebuild the index at the end of the load process instead of updating the index for each record loaded. Be sure that only one process drops data before several load jobs are scheduled. Rebuild the indices immediately after the load process. It is recommended to integrate these processes within the process chains.

Be careful dropping the indices on large F-fact tables as the rebuild might take a very long time. Regular InfoCube compression (and hence small F fact tables) reduces the index rebuild time.

Hope it Helps

Chetan

@CP..

[Former Member · Accepted solution]

Thank you for the info.

I still don't know the difference between the two options in the DB Performance Tab.

Also, anyone know if you have this selected, will the success status wait until the indexes are rebuilt? In other words, if you have the above selected, and in the process chain you don't have a rebuild index process will the load DTP process success status wait until the index is rebuilt before going to the next chain?

Thanks again,

Mark

[Former Member]

Hi Mark,

There is no difference between. Both are for deleting and recreating the secondary indexex. Chetan has explained the reason in details.

Regarding your second question, ofcourse Process Chain will wait for the completion od index creation before going to next step of data laod process.

You can do a quick test in your development system

Thanks

Rupesh

[Former Member]

Well directed by Rupesh.

Each Process type has an EVENT sucess or failure ,depending on the EVENT's outcome, it follows the chains data flow -BELOW.

Hope it Helps

Chetan

@CP..

[Former Member]

I'm not sure how Chetan answered my first question but here is the answer:

"Del. index before every data load & then re-create"

If this option is checked, index deletion and recreation occurs when you do an initial load into the InfoCube.

"Del. index before each data load & then re-create"

If this option is checked, index deletion and recreation occurs when you do a delta load into the InfoCube.

Chetan, per your comment "Rebuild the indices immediately after the load process. It is recommended to integrate these processes within the process chains." Is this per 2004s or 3.x? My assumption is that as of Netweaver 2004s, the InfoCube properties allows you to set the deletion and recreation of indexes per InfoCube and therefore no longer recommends integrating these processes within the process chains. It definitely helps reducing the clutter look of a process chain with multiple data targets.

Can anyone from SAP confirm this?

Regards,

Mark

[Former Member]

Mark,

Complementing Chetan et al, I've not seen any properties in 2004s that allow the deletion and recreation of indexes, if what I'm thinking of, i.e performance tab- create index- that has been there since 3.x.

As you well know just like the old way of loading an ODS- in 3.x was to ignore the activation flag and use a p chain instead- best practice. I'd stick to delete index > load data > activate index in a p chain.

Works and avoids confusion.

Cheers,

Sarah

[Former Member]

The properties I'm speaking of is located in the Menu Path in the change/display mode of the InfoCube:

Environment -> InfoProviders Properties -> Change/Display

Mark