• Article

Design Warning Rules (Analysis Services - Multidimensional Data)

This section of the documentation contains cause and resolution information for database design warning rules related to Microsoft SQL Server Analysis Services. 

Design Warning Categories

Dimensions

Aggregations

Partitions

Attributes and Attribute Relationships

Measures and Measure Groups

User-Defined Hieararchies

ROLAP and MOLAP Storage

Data Providers

Error Handing

Dimensions

A database should have no more than 3 parent-child dimensions that have the UnaryOperatorColumns, CustomRollupColumns, or CustomRollupPropertiesColumns calculations defined on the attributes

Avoid cubes with a single dimension

Avoid having 10 or more dimensions with a single attribute. Consider unifying them.

Avoid large parent-child dimensions that have 500,000 or members for the key attribute

Avoid non-materialized reference dimensions

Change the UnknownMember property of dimensions from Hidden to None

Create hierarchies in non-parent child dimensions

Define a time dimension

For dimensions, do not use proactive caching with a polling query but no processing query

Set the AttributeHierarchyVisible property of the key attribute in a parent-child dimension to False

Arrow icon used with Back to Top linkBack to Top

Aggregations

Design aggregations for partitions with 500,000 or more rows

Do not build more than 500 aggregations for a partition

Limit the number of aggregation designs in a measure group to three

Remove aggregation designs that are not used by any partitions

Include the granularity attribute of the time dimension in aggregation designs of measure groups with semi-additive measures, according to query patterns

Include the granularity attribute of the time dimension in all the aggregations of measure groups with only semi-additive measures

Arrow icon used with Back to Top linkBack to Top

Partitions

Consolidate partitions for measure groups with 5 or more partitions that have less than 2 million rows or are smaller than 50 MB in size

For partitions, do not use proactive caching with a polling query but no processing query

Split large partitions, with more than 20 million rows or that are over 250 MB in size, into smaller partitions to improve performance

Arrow icon used with Back to Top linkBack to Top

Attributes and Attribute Relationships

Avoid defining many-to-many relationships when either the intermediate measure group or any of the intermediate dimensions have 1 million or more rows

Avoid defining non-aggregatable attributes in a parent-child dimension with the exception of the parent attribute

Avoid diamond-shape relationships not covered by hierarchies

Avoid redundant attribute relationships because these relationships may prevent data from being aggregated when the granularity attribute of a cube is a non-key attribute

Avoid specifying more than one non-aggregatable attribute per dimension

Define attribute relationships as 'Rigid' where appropriate

Define the default member for non-aggregatable attributes in either the DefaultMember property or the cubes' MDX script

Either disable the attribute hierarchy or set the AttributeHierarchyOptimizedState property to NotOptimized for non-key attributes that have 1,000,000 or more members and a cardinality within 95% of the key attribute

Set 'Account' and 'Time' attribute types so that they are compatible with the dimension type

Set attribute types so that they are compatible with the dimension type

Set at least one of the attribute types to match the 'Account' or 'Time' dimension type

Set at least one of the attribute types to match the dimension type

Set attribute types so that they are compatible

The names of attribute relationships should match the names of the related attributes

Use numeric key columns for attributes with 500,000 or more members

Arrow icon used with Back to Top linkBack to Top

Measures and Measure Groups

Avoid creating measure groups that have the same dimensionality and granularity

Avoid defining measure groups with zero dimensional overlap

Avoid having 15 or more measure groups in a cube

Break distinct count measures into separate measure groups

Define partitions for measure groups

Include the default measure of a perspective in the perspective

Arrow icon used with Back to Top linkBack to Top

User-Defined Hierarchies

Avoid creating hierarchies where attribute relationships do not exist between one or more levels

Avoid visible attribute hierarchies for attributes used as levels in user-defined hierarchies

Design hierarchies such that lower levels have more members than higher levels

Arrow icon used with Back to Top linkBack to Top

ROLAP and MOLAP Storage

Avoid specifying ROLAP storage mode or enabling proactive caching with the OnlineMode property set to Immediate for dimensions containing either unary operators or custom rollups

Avoid specifying ROLAP storage mode or enabling proactive caching with the OnlineMode property set to Immediate on remote partitions

Set the Slice property on partitions with ROLAP storage mode or with proactive caching settings that allow entering ROLAP mode

Use MOLAP storage mode for dimensions when those dimensions are related to measure groups that contain semi-additive measures

Arrow icon used with Back to Top linkBack to Top

Data Providers

Do not use unsupported OLE DB providers in a DataSource

For faster performance, use the Microsoft OLE DB Provider for SQL Server or the SQL Server Native Client provider to connect to a SQL Server data source instead of the .NET Data Provider for SQL Server (SqlClient)

Arrow icon used with Back to Top linkBack to Top

Error Handling

Do not ignore duplicate key errors. Change the KeyDuplicate property of the error configuration so that it is not set to IgnoreError

Arrow icon used with Back to Top linkBack to Top