How to re-arrange objects within dimension

[Former Member]

I'm stumped on how to delete an infoobject from a dimension. All I really want to do is re-arrange a large dimension to effect the way index is created. Can someone tell me the steps to change the dimension. Its obvious how to add them.

Thanks

Richard

[former_member188975 · Accepted solution]

Richard,

You can try this: Remove the object from the cube (move from left to right) and then add it again (right to left). It will be without the dimension assignment.

The functionality Roberto mentions will be available with BI in NetWeaver 2004s.

Hope this helps...

Message was edited by: Bhanu Gupta

[Former Member · Accepted solution]

That seems to work but why is the "Save" button greyed out in the graphical assignment display ?

[former_member188975 · Accepted solution]

Don't know, but if you make the assignment and hit the green check, move out and come back the details are saved...

[Former Member]

Richard -

The Dimension table index is created based on the order the characteristic appears in the cube. So if you have a characteristic that is fairly selective and is used to filter data in the queries, just make that characteristic the first one in the cube of the characteristics in that dimension, e.g.

Chr1 - Dim 2

Chr2 - Dim 1

Chr3 - Dim 3

Chr4 - Dim 1

Chr5 - Dim 2

Chr6 - Dim 2

Assuming none of these are set as line item, the result of the indices on the Dim tables would be:

- Dim 1 indexed on Chr2, Chr4

- Dim 2 indexed on Chr1, Chr5, Chr6

- Dim 3 indexed on Chr3

If you have multiple chars in the same dim that are both fairly selective and both used independently in many queries, you might end up creating a secondary index, e.g. add a secondary index on Dim table 2 on Chr6

An interesting observation I made the other day is that the opposite appears to happen for dim tables created for aggregates. It that case, the dim table indices get built based on the order the characteristic last appears in the cube - or from the bottom up. I need to pursue this with SAP to find out if that is by design or the developer just chose to do it that way, not understanding the DB index use implications.

Now, it probably isn't worth even doing this unless the Dim table ends up being at least a few thousand rows.

Oracle 9 was supposed to do a better job of keeping small tables frequently referenced with a short table scan in the buffer, but it still has problems. If you check your table accesses, you'll find many small master data tables that are frequently referenced still going to disk. Oracle 10 should resolve this problem and keep these tables in the DB buffer.

Another thing you can look at is using your DB buffering to pin the smaller dim tables in the buffer, or put them in the Keep (Oracle) buffer. SAP Table buffering is NOT not used for BW fact and dimension tables since the queries do aggregation.

hope this helps,

Pizzaman

[Former Member]

Pizzaman,

Once you create those secondary indexs on the dim table how do you transport it and how do you get the new index included in the index create job for the cube ?

[Former Member]

Our DBA actually creates them in the target system once the cube exists.

In our environment, our D and Q systems really aren't even worth creating the indices on since the data is so sparse, any testing on those systems really doesn't confirm that the index will be used in P. We still create them on those systems, but just for landscape consistency.

When I identify some dimension table or master data table indices I want to build, I give the list to one of our DBAs (since I lack the access) and they'll create the indices first at the Oracle level. I'll test and verify the indices are getting used by queries, and then the DBA will go back and create them thru SE11.

[Former Member]

once you create them thru se11 are they then picked up by the create index batch job listed under the performance tab within "manage" infocube.

[Former Member]

The secondary indices that we are talking about adding are on the dimension tables.

They are NOT affected by the drop/create index jobs on the manage tab, which drop/create indices on the fact tables.

I have not had the need to empty and refill a cube (and opting to delete dimension tables) that has a secondary dimemsion table that I added. It is possible the secondary dim table index could be lost in that situation, but I just don't know.

[Former Member]

Hi Rich,

till now this is not possible...but I remember that there is a new functionality that allows to adjust dimension WITH data inside...

Just a second to check if I'm able to find something (release and so on..)...

Bye,

Roberto

[Former Member]

Just so you know I deleted all data in test system. This is were I'm trying to rearrange. Any help always appreciated and points rewarded.

[Former Member]

Mmmmh, just for information...

Look here this fantastic new BI functionality (pag. 16-17):

https://www.sdn.sap.com/irj/servlet/prt/portal/prtroot/docs/library/uuid/5c46376d-0601-0010-83bf-c4f...

Bye,

Roberto

[former_member188975]

Hi Richard,

Does this cube have data? You cannot remove a char from the dimension if there is data...