Where To Put an Attribute (SSAS)

Suppose you have a cube and you need to add an attribute to the cube. Say that the cube structure is: 1 MG containing 1 measure, and 1 dim containing 1 attribute.
The physical table structure is: MG1 is from on table fact1, and dim1 is from table dim1.
You need to add attribute2 to this cube. Attribute2 is located on table dim2. Fact2 links Dim2 to Dim1.

Here we have 2 options:
1. Create Dim2 in AS, create MG2 and link MG2 to both Dim1 & Dim2
2. Join dim2 to dim1 on DSV (using Fact2), and put attribute2 on dim1

I found that, even though the processing is slightly longer, the query on 2 is quicker than 1. The query that I mean is: list down all attribute2 members for a particular attribute1 member. Something like:

select measures.measure1 on columns,
 dim1.attribute2.members on rows
 from cube
 where dim1.attribute1.&[member]

I remember also on one occasion we get performance benefit by moving an attribute to another dimension. Attrib1 was in dim1 and attrib2 was in dim2. The query: list down all possible combination of attrib1 and attrib2 was slow. Moving attrib1 to dim2 make the query quicker.

Please refer here for a discussion about this by Mosha. My previous article about Autoexist is here.

So, it’s better to put attributes which are frequently used together, in the same dimension.

I should add a note to that last sentence: as long as the grain of the dimension doesn’t change a great deal. What I mean is: if after moving an attribute into the dimension, the number members increases a lot (say from a million to a billion), it is probably better not moving the attribute.