I attended a Pragmatic Works webinar entitled ‘Analysis Services Design Mistakes and How to Avoid Them’ presented by Devin Knight.  The following is an overview of the key ideas presented in the webinar.

Common Design Mistakes in Analysis Services (SSAS)

• Covering up data warehouse design mistakes
• Making things more difficult for end-users
• Hurting performance

Designing a Better Data Source

• Cubes are only as good as the data warehouse design.
• Data warehouse design should be done with cube performance and usability in mind.
• Try not to deviate from a star-schema; minimize snowflaking as it affects performance in SSAS.
• Parent – Child dimensions
• Variable # of levels depending on data.
• Aggregations are only created for key and top attributes.
• Flatten the parent – child dimensions when you know the number of levels. Consider creating a SQL view to accomplish this.
• Analysis Services Parent-Child Dimension Naturalizer found on codeplex.com.
• Snowflake schemas
• All levels should ideally be contained in one dimension in SSAS.
• Reference Dimensions – only creates aggregations when materialized. Materialized join is done during processing instead of query time.
• Many-to-Many Dimensions – Similar to reference dimensions: lack aggregations, performs a run-time join.

Designing for End-Users

• Don’t overwhelm them – organization is key.
• Include end-users in portions of the design process
• Naming attributes
• Folder design
• Hierarchy design
• Dealing with unrelated data
• One Cube vs. Many: Ideally separate data into multiple cubes if the data is completely unrelated (e.g. HR data vs. Inventory data). If it is necessary to include unrelated data in one cube, set the ‘IgnoreUnrelatedDimensions’ property (measure group property) to False. Setting the value to ‘False’ ignores the unrelated dimensions.
• Train the users how to use the ‘Show fields related to:’ drop-down to select a measure group. When a measure group is selected, only related attributes are displayed.
• Use perspectives (Enterprise-only feature): Similar to a view in SQL Server. Not a security mechanism, only for usability (Security is handled via roles in SSAS). Can ‘section out’ different types of data, perspectives filter out data to simplify and streamline the view for end-users . Perspectives can be created in the ‘Perspectives’ tab in BIDS.

Designing for Performance

• Create Partitions
• Partitions improve query and processing performance.
• Only process data that has changed by placing old data in a separate partition from new data.
• Can query from one specific partition – improves I/O.
• Most partitions are built around date/time.
• 20-30 million rows / partition (recommendation).
• SQL profiler may be used to understand query performance.
• Understand how users query the cube, build partitions accordingly.
• Design Aggregations
• Pre-calculated summarizations of data.
• Helps query time, hurts disk space and processing time. Aggregations are stored in a file that is created during processing time.
• AggregationUsage property (attributes)
• Full – attribute will be included in all aggregations (this option is rarely used).
• None – attribute will never be used in an aggregation (example is phone number).
• Unrestricted – wizard may/may not use the attribute.
• Default – attribute is considered for aggregations.
• Usage Based Optimization – creates table and logs queries. Table is read into SSAS. User specifies frequency of query logging (e.g. 1 in every 10). Properties need to be set in SSAS (CreateQueryLogTable, QueryLogConnectionString, QueryLogSampling, QueryLogTableName). Table can be used in aggregation design.
• Build method for ‘warming’ query cache
• After reprocessing cube, cache is ‘cold’.
• Pre-cache user queries to minimize response time for end users.