 Re: SSAS 2005: Time intelligence and aggregation designs -
12-24-2006
, 08:00 PM
Hi,
I post Matt's helpful information here for benefiting others.
================================================== ===
Q. Should I set [Time Filter] AggregationUsage value to 'Full' or 'None' or
'Unrestricted'?
A. We recommend that you set it to "Unrestricted." AggregationUsage is a
property that is used to influence the Aggregation Design Wizard when it is
designing aggregations (which happens at design time, not at processing
time or query time). It has the following possible values:
- Full: Instruct the Aggregation Design Wizard that every
aggregation it designs should include this attribute
- None: Instruct the Aggregation Design Wizard that no
aggregation it designs should include this attribute; i.e. this attribute
should be excluded from every aggregation
- Unrestricted: Let the Aggregation Design Wizard decide
whether or not to include the attribute
- Default: Translates to one of the other three options
depending upon the attribute and the role it plays in the dimension
There are other factors that play into the Aggregation Design Wizards
decision. Relevant to this discussion is the "IsAggregatable" property of
an attribute; this is relevant because most attributes have this property
set to "True," but the attributes created by the Time Intelligence Wizard
have this property set to "False." Every attribute has an attribute
hierarchy associated with it; this property (IsAggregatable) controls
whether or not that hierarchy has an "(All)" level; if it is "True" then
the attribute's attribute hierarchy has an (All) level; if "False" then the
attribute's hierarchy does not contain an (All) level. If an attribute
hierarchy does not contain an all level, then the default member of that
hierarchy must be some actual member of the attribute; in order for
aggregation to work properly, therefore, it must include the members of
that attribute. In short, the net effect of having "IsAggregateable" set to
false is that every aggregation designed by the Aggregation Design Wizard
will include that attribute.
So, the [Time Filter] attribute will be included in every aggregation, and
the value of AggregationUsage is therefore irrelevant. I recommend that you
therefore set it to "Unrestricted" and let the Aggregation Design Wizard do
its business. In general, we recommend that you leave the AggregationUsage
property at Default or Unrestricted unless you have specific reasons for
setting it to Full or None; those reasons would typically revolve around
your knowledge of how frequently the attribute will be used in queries;
very frequently used attributes would be set to "Full," while rarely used
attributes could be set to "None." Aggregation design is a complex topic
and I don't want to cover it in depth here. I would recommend that you let
the Aggregation Design Wizard make the decisions initially, and come back
and adjust these settings later if you determine that some useful
aggregations are missing.
Q. Can anyone confirm that if none of the Time attributes is used in query,
then default values from all Time attributes are used, but if any Time
attribute is used in query, then default values of attribute not in query
are ignored?
A. To the first part, the answer is, "yes," but to the second part, the
answer is, "not necessarily." Given any dimension, if you do not navigate
(i.e. specify a particular member or set of members) an attribute of that
dimension in your query, then all attributes of that dimension will be set
at their default value. In the case of most attributes (who have their
IsAggregatable property set to "True") this means the attributes will be
set to their "(All)" member; for the attributes that do not have an (All)
level in their hierarchy (their IsAggregateable property is set to
"False"), then they will be set to whatever their default member is.
However, if you navigate an attribute of a dimension, the situation becomes
more complex, and the outcome is dependent upon the attribute relationships
that exist between the attributes in the dimension. There is a very good
whitepaper on this by one of our developers; it can be found at:
http://www.sqlserveranalysisservices...elationships.h
tm. I recommend that you read this. Time dimensions tend to have their
attribute relationships well defined, and as a result of this, when you
navigate a particular attribute, other attributes of the dimension will
also be navigated. For example, suppose you have a time dimension
containing the following attributes: Day, Week, Month, Quarter, Year. And
suppose the following relationships exist between these attributes:
Day ' Week
Day ' Month
Month ' Quarter
Quarter ' Year
Meaning that if I know the Day, then I can determine what the Week is and
what the Month is; and if I know the Month, I can determine the Quarter;
and if I know the Quarter, I can determine the Year. If you have specified
these attribute relationships in your dimension, then the following query:
SELECT [Measures].Members ON Columns,
[Time].[Month].[Month].[March 2005]
FROM [MyCube]
Will navigate not only the Month attribute, but the Quarter and Year
attributes as well; i.e. the attributes will be set to the following
members:
Day = (All)
Month = March 2005
Quarter = Q1 2005
Year = 2005
Again, the white paper I pointed out explains this in far more detail, and
is recommended reading.
Q. Is it true that value "Full" (in the AggregationUsage property) will
prevent me from using aggregates when I have Time dimension anywhere in the
query, while value "None" will prevent me from using aggregate when [Time
Filter] dimension is used?
A. No. As I mentioned in answer to the first question, AggregationUsage
controls the Aggregation Design Wizards decision process, which is a design
time issue, not a query time issue. The value of AggregationUsage is not
considered by the formula engine when executing queries and deciding which
existing aggregations to use in responding to those queries. Instead, this
property is used by the Aggregation Design Wizard to determine which
aggregations to create in the first place.
Q. Will multiple "fake" attributes (i.e. attributes of the type created by
the Time Intelligence Wizard) impact your answers?
A. Not inherently. There is nothing inherent in their design that should be
problematic. However, it is possible that how you use them could be
problematic. The attributes created by the Time Intelligence Wizard consist
largely of calculated members. As with all calculated members, the order in
which the calculations are executed can be significant; that is, not all
calculations are commutative. As a result, queries where two or more of
these "fake" attributes are involved can result in one "fake" attributes
calculations overwriting another "fake" attributes calculations. I don't
know of any documented cases of this, and in my own simple experimentation,
the "fake" attributes created by the Time Intelligence Wizard seemed pretty
robust as far as this is concerned; the results still made sense. However,
it is possible to create "fake" attributes that are not so resilient. For
example, I created two calculated members in the Adventure Works database
in the Date dimension: "My Fake Member," which I added to the Fiscal
hierarchy, and "My Other Fake Member," which I added to the Calendar
hierarchy. My Fake Member" simply has the value of "0", while "My Other
Fake Member" simply has the value of "500.00." I then used the cube browser
to view these members, with "My Fake Member" on the rows, and "My Other
Fake Member" on the columns."; here's the result if I place the "My Fake
Member" calculation so that it is calculated before "My Other Fake Member"
in my cube's MDX script:
I.e. my cell values are set to the value of the last executed calculation,
"My Other Fake Member," so their value is "$500.00." If I change the order
of these calculated members in my cube MDX script, the results change; if I
place "My Fake Member" after "My Other Fake Member" I get:
Now my cell value is set to "$0.00" because "My Fake Member" is calculated
last. So, keep in mind that having multiple "fake" attributes - indeed,
having multiple calculations of any type - included in your query can
return unexpected results, because the calculation order is significant.
You have to keep that in mind when you are designing your calculations to
ensure that calculations are placed in the right order.
Q. Should we set "fake" attributes' AttributeHierarchyOptimizedState
property to "Not Optimized" as it will always have default value? Should we
set "real" attributes value to "Fully Optimized"?
A. For "fake" attributes setting it to Not Optimized is probably a good
idea. Since the fake attribute contains only one real member, and that real
member is related to every other member of every other attribute in the
dimension, there is no value to the indexes that are created when
AttributeHierarchyOptimizedState is set to "Fully Optimized." With "real"
attributes the rule of thumb is set to Fully Optimized for attributes that
you frequently query, and Not Optimized for attributes that are rarely
queried.
================================================== =====
Merry Christmas!
Charles Wang
Microsoft Online Community Support
 |
Linear Mode
