1 / 15

Testing Microsoft SQL Server Analysis Services

Testing Microsoft SQL Server Analysis Services. Marius Dumitru Sivakumar Harinath Gonzalo Isaza Akshai Mirchandani. Outline. Introduction to Analysis Services Analysis Services Engine Architecture Current Testing Process & Challenges Approaches to improve testing .

ima
Download Presentation

Testing Microsoft SQL Server Analysis Services

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Testing Microsoft SQL Server Analysis Services Marius DumitruSivakumar HarinathGonzalo Isaza Akshai Mirchandani

  2. Outline • Introduction to Analysis Services • Analysis Services Engine Architecture • Current Testing Process & Challenges • Approaches to improve testing

  3. Introduction to Analysis Services • Multidimensional database • Multidimensional Expressions (MDX) queries • Server and client tools

  4. Query Execution Architecture Client Application MDX query Serialize results INFRASTRUCTURE Query Parser Data Mining Metadata Manager Populate axes Compute cell data Subcube operations Calculation Engine s FE Caches Formula Engine Partition Data Query s SE Caches SE Evaluation Engine Storage Engine Analysis Services

  5. Testing Analysis Services • Functionality Testing • Databases used • Exercise different components • Formula Engine (Calculations & rules) • Performance and Stress testing • Acceptance criteria & reporting

  6. Challenges • Functional testing • Most common type of queries • Recursive relationship definitions (P/C) complicate schema • Execution path evaluation may change easily • Size of query plan space • Many calculations may apply to a single cell • Size of cartesian product of dimensions • Combination of features

  7. Challenges • Performance testing • Wide variety of databases • Feature change has effect in other areas • Profiling issues

  8. Example query using IIF with member SalesIncludingAdPromotion as iif ([Sales Reason].[Sales Reason].currentmember is [Sales Reason].[Sales Reason].[Magazine Advertisement], [Measures].[Internet Sales Amount], [Measures].[Internet Extended Amount] + [Measures].[Internet Tax Amount]) select SalesIncludingAdPromotion on 0, [Customer].[Full Name].members on 1 from [Adventure Works] where [Customer].[Country-Region].&[United States]

  9. IIF(C, e1, e2) Naive Evaluation IIF(C, e1, e2) Eager Evaluation 15K σv is not null 1M 15K Apply Union 1M 5K Lookup 10K IIF CrossJoin σ C==true σ C==false 20K 30K 100 ½ M 100 1M 100 ½ M D1 D2 Apply D3 Apply C e1 e2 30K 20K e1 C e2 C

  10. Testing Enhancements • New tool (generate databases & queries) • Create db’s & Validate discovers with simple MDX queries • Different storage types • Combination of features

  11. Data from Mixed Sources Data from AS db Data from Rel db Same database with different storage modes. ROLAP HOLAP MOLAP Send same Query Compare Responses

  12. Testing Enhancements • New tool (generate databases & queries) • Create db’s & Validate discovers with simple MDX queries • Different storage types • Combination of features • Ability to influence execution path

  13. Block vs. cell by cell computation • SQL 2008: Execution path control

  14. Testing Enhancements • New tool (generate databases & queries) • Create db’s & Validate discovers with simple MDX queries • Different storage types • Combination of features • Ability to influence execution path • Databases with focused queries • Propose performance impact during upgrades

  15. Questions{gonzaloi,sivah,mariusd,akshaim} @microsoft.com

More Related