Understanding attribute relationships and aggregations in SSAS2005

(Cross posted from MSDN forums, no response there yet)

I'm working on fixing some major query performance problems in an AS2005
cube. My suspicion was that they were caused by the original cube design not
specifying attribute relationships in the time dimension. I've read the
Project REAL stuff and the P&P BI Guidelines in an effort to fully grok the
implications of attribute relationships, natural hierarchies, and
aggregations, but there's still some stuff I'm unclear on.

Our calendar hierarchy is moderately deep - Year > Month > Day > Hour >
Minute (where minute is actually a 15 minute time window). The dimension
table columns are the key, minute (0,15,30,45), hour of day (0-23), day of
month (0-days in month), month (1-12), year. Originally there were
attributes defined for each of these columns, with each attribute using its
corresponding column as its KeyColumn, and with all attributes related
directly to the key. A user hierarchy provided navigation from the year down
to the minute. Performance, in a word, sucked (drilling down in the cube
browser to a low level in the hierarchy could take as long as 45 minutes!).

I changed the KeyColumn for each attribute below year to be a composite of
each higher level attribute (so the KeyColumns in Month were Year and Month,
for Day they were Year/Month/Day, etc). Then I set the attribute
relationships accordingly. I'm not sure if this is the most efficient way to
define the attributes, but it seemed to be the only way to do it with our
current dimension table schema.

Once I made this change and re-processed, drilling through the hierarchy
went from 45 minutes to less than a second. Not a bad improvement. :)
However, there's still some things that I'm unclear on.

* The performance seems to be about the same (that is, pretty fast) whether
the aggregations are set to 0% or 70%. That confuses me - I thought that
setting up the relationships is what allows the aggregations to be computed.
But why would no aggregations perform so well?

* I'm assuming that by setting up these relationships, I'm giving up some
flexibility in my queries. Is that true? For example, what if I want to
compare how January compares to February, across all years. Are those
aggregations not longer being performed, because of the attribute
relationships? If so, what's the best way to deal with that? Or am I
misunderstanding something?

* I'm looking at making similar changes to a different dimension, but that
dimension has two different user hierarchies that combine the same
attributes in different ways. Does defining a natural hierarchy through
attribute relationships limit your ability to combine those attributes in
different navigational hierarchies?

Thanks,
Kevin


.

Loading