Testing microsoft sql server analysis services
Sponsored Links
This presentation is the property of its rightful owner.
1 / 15

Testing Microsoft SQL Server Analysis Services PowerPoint PPT Presentation


  • 78 Views
  • Uploaded on
  • Presentation posted in: General

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.

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.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -

Presentation Transcript


Testing Microsoft SQL Server Analysis Services

Marius DumitruSivakumar HarinathGonzalo Isaza

Akshai Mirchandani


Outline

  • Introduction to Analysis Services

  • Analysis Services Engine Architecture

  • Current Testing Process & Challenges

  • Approaches to improve testing


Introduction to Analysis Services

  • Multidimensional database

  • Multidimensional Expressions (MDX) queries

  • Server and client tools


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


Testing Analysis Services

  • Functionality Testing

  • Databases used

  • Exercise different components

  • Formula Engine (Calculations & rules)

  • Performance and Stress testing

  • Acceptance criteria & reporting


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


Challenges

  • Performance testing

    • Wide variety of databases

    • Feature change has effect in other areas

    • Profiling issues


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]


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


Testing Enhancements

  • New tool (generate databases & queries)

  • Create db’s & Validate discovers with simple MDX queries

  • Different storage types

  • Combination of features


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


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


Block vs. cell by cell computation

  • SQL 2008: Execution path control


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


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


  • Login