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

Testing Microsoft SQL Server Analysis Services PowerPoint PPT Presentation


  • 64 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

Testing Microsoft SQL Server Analysis Services

Marius DumitruSivakumar HarinathGonzalo Isaza

Akshai Mirchandani


Outline

Outline

  • Introduction to Analysis Services

  • Analysis Services Engine Architecture

  • Current Testing Process & Challenges

  • Approaches to improve testing


Introduction to analysis services

Introduction to Analysis Services

  • Multidimensional database

  • Multidimensional Expressions (MDX) queries

  • Server and client tools


Query execution architecture

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

Testing Analysis Services

  • Functionality Testing

  • Databases used

  • Exercise different components

  • Formula Engine (Calculations & rules)

  • Performance and Stress testing

  • Acceptance criteria & reporting


Challenges

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


Challenges1

Challenges

  • Performance testing

    • Wide variety of databases

    • Feature change has effect in other areas

    • Profiling issues


Example query using iif

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]


Testing microsoft sql server analysis services

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

Testing Enhancements

  • New tool (generate databases & queries)

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

  • Different storage types

  • Combination of features


Testing microsoft sql server analysis services

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 enhancements1

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

Block vs. cell by cell computation

  • SQL 2008: Execution path control


Testing enhancements2

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

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


  • Login