Vidas Matelis Analysis Services Blog

SQL Server 2005 Best Practices Analyzer (BPA) was available as CTP already for some time, but just a few days ago Microsoft finally released it. I tested how this tool works with Microsoft SQL Server Analysis Services 2005.

Download is quite small – just about 2MB. Installation was trivial – just a few questions.  After installation when you start BPA, you can configure what do you want to scan and then run this scan right away or schedule it to run at certain time(s). For my test I choose Analysis Services computer and then SSAS database. I started scanning process that was pretty quick – less than 1 min.  So I started to investigate what this tool was checking.

There were 2 types of scans done: service level scan and database level scan.

For SSAS at the service level it scans accounts used to configure SQLBrowser and MSSQLServerOLAPService services. For my test computer Analyzer complained that I used LocalSystemAccount to configure services.

At the SSAS database level scan checks for over 30 rules and reports if database design breaks these rules. I found a list of rules that are tested in the BPA help file:

• Organize Attributes into Levels in User Hierarchies
• Define Relationships Between Levels in User Hierarchies
• Define Unique Key Columns for Attributes in Natural Hierarchies
• Hide Attributes Used as Levels in User Hierarchies
• Group Attributes Bound to Single Relational Table into a Single Dimension
• Use Only One Non-Aggregatable Attribute per Dimension
• Use Only Aggregatable Attributes in Dimensions with a Parent-Child Hierarchy
• Hide the Key Attribute in a Dimension Containing a Parent-Child Hierarchy
• Increase the Organization of Attributes into Levels in User Hierarchies
• Remove Attributes Below Granularity for All Measure Groups
• Set the Unknown Member Dimension Property to None
• Disable Attributes with 1-1 Relationship with Key Attribute
• Bind the Key Attribute for a Dimension to a Column with a Numeric Data Type
• Design Aggregations for All Measure Groups
• Use Appropriately Sized Partitions in All Measure Groups
• Eliminate Unused Aggregation Designs
• Design Aggregations for Granularity Attribute of Intermediary Dimensions
• Avoid Using Too Many Aggregation Designs
• Minimize the Use of Similar Aggregation Designs
• Place Distinct Count Measures in Separate Measure Groups
• Minimize the Use of Very Large Intermediary Measure Groups
• Split Single-Dimension Cubes into Multiple-Dimension Cubes
• Use the SQL Native Client Provider
• Use MOLAP for Dimensions with Unary Operators, Custom Rollups, and Semi-Additive Measures
• Avoid Linked Dimensions with Unary Operators, Custom Rollups, Semi-Additive Measures, and Calculation Scripts
• Minimize the Use of Unsupported OLE DB Providers
• Materialize Referenced Dimension Relationships
• Minimize the Use of Parent-Child Hierarchies
• Combine Multiple Measure Groups with the Same Dimensionality and Granularity
• Organize Attributes into Dimensions
• Minimize the Number of Measures Groups in a Single Cube
• Use Default Server Property Settings for Most Properties
• Set the Maximum Number of Threads Based on the Number of Processors
• Only Use Proactive Caching with MOLAP

As I run my tests on Adventure Works databases, there were no warnings or errors reported at the database level by BPA. 

I will be using this tool to test my SSAS 2005 databases for potential warnings.

Some screenshots of Best Practices Analyser are below:

Screenshot 1: Register SQL Server Components

Screenshot 2: Choose database

Screenshot 3: Start Scan

Screenshot 4: Scan Completed

Screenshot 5: Scan Result 1

Screenshot 6: Scan Result 2

Screenshot 7: Scan Result 3