Database Reference
In-Depth Information
•
For queries involving a dimension with a many-to-many relationship to a
measure group, aggregations must not be built using any attributes from
the many-to-many dimension, but instead must be built at the granularity of
the attributes with a regular relationship to the intermediate measure group.
So for example you have a Customer dimension joining to a Bank Account
Balance measure group with a many-to-many relationship via a Bank
Account dimension at the Account attribute granularity, you should only
build aggregations involving the Account attribute. This is because when
Analysis Services runs a query using Customer against the Bank Account
Balance measure group, the selection on Customer is resolved to a list of
distinct Bank Accounts first. As a result, in most cases it's not worth building
aggregations for queries on many-to-many dimensions since the granularity
of these queries is often close to that of the original fact table.
•
Queries involving measures which have semi-additive aggregation types are
always resolved at the granularity attribute of the Time dimension, so you
need to include that attribute in all aggregations.
•
Queries involving measures with measure expressions require aggregations
at the common granularity of the two measure groups involved.
•
You should not build aggregations including a parent/child attribute;
instead you should use the key attribute in aggregations.
•
No aggregation should include an attribute which has
AttributeHierarchyEnabled
set to
False
.
•
No aggregation should include an attribute that is below the granularity
attribute of the dimension for the measure group.
•
Any attributes which have a default member that is anything other than the
All Member, or which have
IsAggregatable
set to
False
, should also be
included in all aggregations.
•
Aggregations and indexes are not built for partitions with fewer than
4096 rows. This threshold is set by the
IndexBuildThreshold
property
in msmdsrv.ini; you can change it but it's not a good idea to do so.
•
Aggregations should not include redundant attributes, that is to say
attributes from the same 'chain' of attribute relationships. For example, if
you had a chain of attribute relationships going from Month to Quarter to
Year, you should not build an aggregation including Month and Quarter - it
should just include Month. This will increase the chance that the aggregation
can be used by more queries, as well as reducing the size of the aggregation.
Search WWH ::
Custom Search