{"id":26,"date":"2007-07-04T21:49:35","date_gmt":"2007-07-05T02:49:35","guid":{"rendered":"http:\/\/www.ssas-info.com\/VidasMatelisBlog\/26_sql-server-2005-best-practices-analyzer-test-on-ssas-database"},"modified":"2008-03-23T16:25:20","modified_gmt":"2008-03-23T21:25:20","slug":"sql-server-2005-best-practices-analyzer-test-on-ssas-database","status":"publish","type":"post","link":"https:\/\/www.ssas-info.com\/VidasMatelisBlog\/26_sql-server-2005-best-practices-analyzer-test-on-ssas-database","title":{"rendered":"SQL Server 2005 Best Practices Analyzer &#8211; test on SSAS database"},"content":{"rendered":"<p>SQL Server 2005 Best Practices Analyzer (BPA) was available as CTP\u00a0already for some time, but just a few days ago\u00a0Microsoft finally <a target=\"_blank\" href=\"http:\/\/www.microsoft.com\/downloads\/details.aspx?FamilyID=da0531e4-e94c-4991-82fa-f0e3fbd05e63&amp;DisplayLang=en\">released it<\/a>. I tested\u00a0how\u00a0this tool\u00a0works\u00a0with Microsoft SQL Server Analysis Services 2005.<\/p>\n<p>Download is quite small &#8211; just\u00a0about 2MB. Installation\u00a0was\u00a0trivial &#8211; just a few questions.\u00a0 After installation when you start BPA, you can\u00a0configure what do you want to scan and then run this scan right away or schedule it to run\u00a0at\u00a0certain time(s). For my test\u00a0I choose Analysis Services computer and then SSAS database. I started scanning process that was pretty quick &#8211; less than 1 min.\u00a0 So I started to investigate what this tool was\u00a0checking.<\/p>\n<p>There were 2 types of\u00a0scans done: service level\u00a0scan and database level scan.<!--more--><\/p>\n<p>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.<\/p>\n<p>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:<\/p>\n<ul>\n<li>Organize Attributes into Levels in User Hierarchies<\/li>\n<li>Define Relationships Between Levels in User Hierarchies<\/li>\n<li>Define Unique Key Columns for Attributes in Natural Hierarchies<\/li>\n<li>Hide Attributes Used as Levels in User Hierarchies<\/li>\n<li>Group Attributes Bound to Single Relational Table into a Single Dimension<\/li>\n<li>Use Only One Non-Aggregatable Attribute per Dimension<\/li>\n<li>Use Only Aggregatable Attributes in Dimensions with a Parent-Child Hierarchy<\/li>\n<li>Hide the Key Attribute in a Dimension Containing a Parent-Child Hierarchy<\/li>\n<li>Increase the Organization of Attributes into Levels in User Hierarchies<\/li>\n<li>Remove Attributes Below Granularity for All Measure Groups<\/li>\n<li>Set the Unknown Member Dimension Property to None<\/li>\n<li>Disable Attributes with 1-1 Relationship with Key Attribute<\/li>\n<li>Bind the Key Attribute for a Dimension to a Column with a Numeric Data Type<\/li>\n<li>Design Aggregations for All Measure Groups<\/li>\n<li>Use Appropriately Sized Partitions in All Measure Groups<\/li>\n<li>Eliminate Unused Aggregation Designs<\/li>\n<li>Design Aggregations for Granularity Attribute of Intermediary Dimensions<\/li>\n<li>Avoid Using Too Many Aggregation Designs<\/li>\n<li>Minimize the Use of Similar Aggregation Designs<\/li>\n<li>Place Distinct Count Measures in Separate Measure Groups<\/li>\n<li>Minimize the Use of Very Large Intermediary Measure Groups<\/li>\n<li>Split Single-Dimension Cubes into Multiple-Dimension Cubes<\/li>\n<li>Use the SQL Native Client Provider<\/li>\n<li>Use MOLAP for Dimensions with Unary Operators, Custom Rollups, and Semi-Additive Measures<\/li>\n<li>Avoid Linked Dimensions with Unary Operators, Custom Rollups, Semi-Additive Measures, and Calculation Scripts<\/li>\n<li>Minimize the Use of Unsupported OLE DB Providers<\/li>\n<li>Materialize Referenced Dimension Relationships<\/li>\n<li>Minimize the Use of Parent-Child Hierarchies<\/li>\n<li>Combine Multiple Measure Groups with the Same Dimensionality and Granularity<\/li>\n<li>Organize Attributes into Dimensions<\/li>\n<li>Minimize the Number of Measures Groups in a Single Cube<\/li>\n<li>Use Default Server Property Settings for Most Properties<\/li>\n<li>Set the Maximum Number of Threads Based on the Number of Processors<\/li>\n<li>Only Use Proactive Caching with MOLAP<\/li>\n<\/ul>\n<p>As\u00a0I run my tests on Adventure Works databases, there were no warnings or errors reported at the database level by BPA.\u00a0<\/p>\n<p>I will be using this tool to test my\u00a0SSAS 2005 databases for potential warnings.<\/p>\n<p>Some\u00a0screenshots of Best Practices Analyser are below:<\/p>\n<p>Screenshot 1: Register SQL Server Components<\/p>\n<p><img src=\"BlogFiles\/Post26\/01-EnterServerName.JPG\" alt=\"01-EnterServerName\" title=\"01-EnterServerName\" \/><\/p>\n<p>Screenshot 2: Choose database<\/p>\n<p><img src=\"BlogFiles\/Post26\/02-ChooseDatabase.JPG\" alt=\"ChooseDatabase\" title=\"ChooseDatabase\" \/><\/p>\n<p>Screenshot 3: Start Scan<\/p>\n<p>\u00a0<img src=\"BlogFiles\/Post26\/03-StartScan.JPG\" alt=\"Start Scan\" title=\"Start Scan\" \/><\/p>\n<p>Screenshot 4: Scan Completed<\/p>\n<p>\u00a0<img src=\"BlogFiles\/Post26\/04-ScanCompleted.JPG\" alt=\"Scan Completed\" title=\"Scan Completed\" \/><\/p>\n<p>Screenshot 5: Scan Result 1<\/p>\n<p><img src=\"BlogFiles\/Post26\/05-ScanResult1.JPG\" alt=\"Scan Result 1\" title=\"Scan Result 1\" \/><\/p>\n<p>Screenshot 6: Scan Result 2<\/p>\n<p><img src=\"BlogFiles\/Post26\/05-ScanResult2.JPG\" alt=\"Scan Result 2\" title=\"Scan Result 2\" \/><\/p>\n<p>Screenshot 7: Scan Result 3<\/p>\n<p><img src=\"BlogFiles\/Post26\/05-ScanResult3.JPG\" alt=\"Scan Result 3\" title=\"Scan Result 3\" \/><\/p>\n","protected":false},"excerpt":{"rendered":"<p>SQL Server 2005 Best Practices Analyzer (BPA) was available as CTP\u00a0already for some time, but just a few days ago\u00a0Microsoft finally released it. I tested\u00a0how\u00a0this tool\u00a0works\u00a0with Microsoft SQL Server Analysis Services 2005. Download is quite small &#8211; just\u00a0about 2MB. Installation\u00a0was\u00a0trivial &#8211; just a few questions.\u00a0 After installation when you start BPA, you can\u00a0configure what do [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":[],"categories":[4],"tags":[],"aioseo_notices":[],"_links":{"self":[{"href":"https:\/\/www.ssas-info.com\/VidasMatelisBlog\/wp-json\/wp\/v2\/posts\/26"}],"collection":[{"href":"https:\/\/www.ssas-info.com\/VidasMatelisBlog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.ssas-info.com\/VidasMatelisBlog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.ssas-info.com\/VidasMatelisBlog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.ssas-info.com\/VidasMatelisBlog\/wp-json\/wp\/v2\/comments?post=26"}],"version-history":[{"count":0,"href":"https:\/\/www.ssas-info.com\/VidasMatelisBlog\/wp-json\/wp\/v2\/posts\/26\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.ssas-info.com\/VidasMatelisBlog\/wp-json\/wp\/v2\/media?parent=26"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.ssas-info.com\/VidasMatelisBlog\/wp-json\/wp\/v2\/categories?post=26"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.ssas-info.com\/VidasMatelisBlog\/wp-json\/wp\/v2\/tags?post=26"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}