Hi everyone,

I'm reading "Applied Microsoft Analysis Services 2005 and Microsoft Business
Intelligence Platform" written by Teo Lachev.

There Teo writes about "Evaluating Aggregation Usage" as following in page
462,

"For multilevel hierarchies, the wizard will scan the hierarchy from the top
level and evaluate each level. If the attribute relationships exist for the
attribute (natural hierarchy), the attribute will be prompted to
Unrestricted level. Otherwise, the Aggreagtion Design Wizard will move to
the next level in the multi-level hierarchy."

I wonder if the latter part says correctly. I also read the power point
slides presented by Dave Wickert and Len Wyatt from Microsoft in PASS 2005.
And there they writes as following,

"Else break out of the level scan and move to the next hierarchy."

Which one is correct? "Move to the next level of the same hierarchy" or
"move to the next hierarchy"?

Thanks in advance,

Ohjoo Kwon

The SQL PASS presentation is correct.
If an attribute relationship is missing, the aggregation design wizard knows
that the "natural" nature of the hierarchy stops at this level and it will
shift to the next hierarchy. There is no point in considering levels beneath
that level once the attribute relationship is missing.

The idea is that aggregates only work only if an attribute relationship is
in-place. Suppose your design had a hierarchy like:

Country ---> City --(but no attribute relationship)---> Customer --->
Contact

This indicates that a Contact person has one and only one Customer they can
uniquely identify (this is what a one-to-many relationship means) (if you
know the Contact, then you know the Custoemr it is in). A City has one and
only one Country (if you know the City, that implies you know the Country it
is in). But a Customer does *not* have one and only one relationship with
City (i.e. an attribute relationship does not exist), then you are saying
that you cannot infer from the structure itself that knowing the Customer
does not automatically imply that you know that City they are in -- it might
be technically and structurally possible that the data might have same
Customer rolling up to one (or more) City.

Now let's look at this from the aggregation design wizards perspective.
Since there is a one-to-many attribute relationship between Country and
City, the aggregation design wizard knows that if an aggregation was created
for City then the runtime engine could use that aggregate for Country also
(the reuse of aggregates is critical to the effectiveness of an aggregate).
But at design time (and this is an important point), the structure cannot
guarantee that if you have an aggregate designed for Customer, that it could
also be used for City, or Country -- this is because the attribute
relationship does not exist. Thus the design wizard stops at City and goes
on the next hierarchy. It doesn't consider Customer or Contact as a
candidate attribute in the aggregation design (well.. that is technically
correct, but since Contact is probably the key attribute, it may already be
consider a candidate attribute since the key attribute is always a
candidate). Now if there were other hierarchies which included Customer or
Contact then the wizard might re-examine them again, but then it is looking
at the attribute relationships which exists for that hierarchy. For example,
you might have another hierarchy like:

Customer -> Contact

In this case, Customer and Contact would both be added as candidate
attributes since the attribute relationships *are* in place.

Hope that makes things clearer.

--
Dave Wickert [MSFT]
dwickert (AT) online (DOT) microsoft.com
Program Manager
BI Systems Team
SQL BI Product Unit (Analysis Services)
--
This posting is provided "AS IS" with no warranties, and confers no rights.


"Ohjoo Kwon" <ojkwon (AT) olap (DOT) co.kr> wrote

Thanks, Dave.

Your clean explanation is very helpful.

Ohjoo

"Dave Wickert [MSFT]" <dwickert (AT) online (DOT) microsoft.com> wrote