testing microsoft sql server analysis services
Download
Skip this Video
Download Presentation
Testing Microsoft SQL Server Analysis Services

Loading in 2 Seconds...

play fullscreen
1 / 15

Testing Microsoft SQL Server Analysis Services - PowerPoint PPT Presentation


  • 113 Views
  • Uploaded on

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.

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about ' Testing Microsoft SQL Server Analysis Services' - colin-leonard


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]

slide9

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
slide11

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
ad