Thursday, January 27, 2005

Attributes queried separately

You may notice that when AS2005 processes a dimension, it creates a separate query for each attribute. One reason for doing this is that it allow you to encorporate data from different data sources. It will, however, sometimes query for multiple attributes--particularly at the leaf level--and in a snowflaked dimension, that may result in SQL Join clauses.

What it is really doing is a creating single query for all columns "related" to one attribute. That would include the Key, the Name, and all Member Properties. In a generic (manually created) dimension, all attributes are defined as member properties of the key attribute. In the sample AdventureWork database Product dimension, you can see a join at the leaf level between Product and Subcategory. The Category member property, however is moved from the key attribute up to the SubCategory attribute, so you should see the join in the query for Subcategory.

The developers told me that one of the ways that IntelliCube (or whatever its name ends up being) will assign member properties is based on same-table of snowflake dimension (and, if I remember correctly, that portion of Intellicube already works in Beta 2). You can, of course, manually move the member properties as needed. I would think that if a member property causes a join in a properly snowflaked dimension, it would indicate that you should move the member property to a higher level--to the attribute that is the PK of the higher table.

-- Reed