Data Warehousing and Business Intelligence

24 April 2011

Where To Put an Attribute (SSAS)

Filed under: Analysis Services — Vincent Rainardi @ 6:28 am
Tags:

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.

Advertisements

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Blog at WordPress.com.

%d bloggers like this: