Index

[Former Member]

Hi ,

How can I create secondary index on Infocube?(Not on fact table)?

Thank you,

Sindu

Message was edited by: Sindu

[GFV · Accepted solution]

Hi Sindu,

I can't understand why you need to (I'm curious), but simply go in SE11 -> specify the Table Name (e.g. /BIC/D<IC_NAME>1, 2 ... if you are working on a DImension) and start Index creation with button "Index" (Ctrl+F5). Let me suggest to work with a DB Administrator for the first times you do this.

Hope it helps

GFV

[Former Member · Accepted solution]

Thank you GFV and Aneesh,,

I got a clear picture now.

[Former Member]

While you would not create secondary indexes on a the fact tables, it may very well make sense to create a secondary index on a dimension table or master data table.

Much of this depends on your DB and the queries your users perform, since an Oracle Star Transformation execution plan may look very different than an execution plan on SQL Server or DB2.

I have achieved some very nice performance gains doing this.

<u><b>Where to Start</b></u> -

I only look at the most frequently run queries hitting the DB (OLAP cache can preclude a lot of these) and most frequently referenced master data tables to start. See where I'm getting full table scans on large dim or master data tables and then look at how the user filters their queries.

<u><b>Considerations</b></u> -

You need to be very familiar with the queries that your users run, what chars / nav attrs selected and filtered on, how often they are run, DB resources consumed, etc.

You need to be familiar with the dimension tables, the size (# rows), the order that the characteristics are in the dimension table, the number of distinct values of each char, what chars are regularly used for filtering, etc.

Need to understand how to interpret the query execution plan.

Understand load and storage impacts of adding indexes.

Have implemented other performance tuning, e.g. compression, aggregates, OLAP cache. Partitioning and DB table buffering (not SAP buffering) may be considerations too.

<u><b>Other Things</b></u>

Typically a BW Developer does not have the rights to SE11 to build indexes (other than for an ODS).

Unless your DW system has a good amount of data similar to PW, testing in DW is worthless. What I have done, when I idnetify a table I want an index added, is have our DBAs build it at the Database level on PW. After confirming it is being used and helping, they go back and do it thru SE11 and transport. No point in transporting something to PW if you don't know if it is useful.

[Former Member]

As suggested by GFV you should not have to create any indexes on the InfoCube to improve performance.

Let the application handle that bit. Go to the Performance tab in manage InfoCube. Press the check indexes button and if it comes up in red, hit the create index button. This will refresh the indexes on all tables of the InfoCube.