MS SQL Server OLAP - Aggration Wizard : Evaluating Aggregation Usage

Author

Aggration Wizard : Evaluating Aggregation Usage

Ohjoo Kwon

2006-01-24, 3:23 am

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

Dave Wickert [MSFT]

2006-01-26, 4:58 pm

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@online.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@olap.co.kr> wrote in message
news:OsbMl3LIGHA.1124@TK2MSFTNGP10.phx.gbl...
> 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
>
>

Ohjoo Kwon

2006-01-26, 4:58 pm

Thanks, Dave.

Your clean explanation is very helpful.

Ohjoo

"Dave Wickert [MSFT]" <dwickert@online.microsoft.com> wrote in message
news:ODt6%23DfIGHA.3752@TK2MSFTNGP11.phx.gbl...
> 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@online.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@olap.co.kr> wrote in message
> news:OsbMl3LIGHA.1124@TK2MSFTNGP10.phx.gbl...
page[color=darkred]
to[color=darkred]
>
>

Sponsored Links