Non Unique Indexes on Dimension Table

[former_member213594]

Hello ,

I have Material Dimension that has Product MainGroup, Brand , SPC code broken from Product Hierarchy.

As per Business Requirement , we don't want to use the Product Hierarchy , that should need to split into 3 pieces.

Since The Cube Dimensions already reached the 13 , we can not increase the dimensions to keep these 3 fields into separate dimensions.

I heard from Basis guy as <b>we can have index on three fields in same dimension table</b> & read some negative impact on aggregate definition.

Is it true , which one is true , am not sure.

Which one impact causes more worst & usefull..?

Could please some experts throw some light on this.

Cheers

Martin

[Former Member]

Martin -

Not sure what you mean by "read some negative impact on aggregate definition".

But as far as adding additional indices on other columns of a dimension table, that certainly is doable. I have never done this as part of an actual intentiional design, but it seems like a valid apporach if you are limited by dimensions. I'm assuming when you say you already have 13, that the 13 does not include the three standard dimensions for time, request, (drawing a blank, is it currency), so that you really have 16 dimensions, 13 of which are user defined.

We have added dimension tabl e indices in our shop when we have found large dimension tables that, either from poor initial design, or changes to the data and/or queries, have resulted in full tables scans against large dimension tables. In some cases, the query costs of the full scan of the dimension table was more than the cost for the access of the fact table itself.

These indices must be added by your DBA as they can not be added thru the Admin Wkbench. You should also probably keep a record of any of these indices you create because if for some reason you delete the tables and reactivate the cube, you'll probably lose them.

Pizzaman