Automating the testing of your BI solutions with NBi

Cédric L. Charlier

Agenda

• Why automating is a must?• Tests with queries (Sql/Mdx/Dax)• Tests with ETL packages (SSIS)• Testing multidimensional and tabular models (SSAS)• Automating the creation of test-suites

What’s automated

testing?

A piece of software written to reproduce

steps/actions defined in a manual

process

Benefits of automation

Allow testing to happen more

frequently

Speed up testing to accelerate releases

Improve test coverage

Ensure consistency

Tests automation doesn’t apply to

Business Intelligence

Wednesday, November 24, 2012Est. 1869 Price 6d

More than 33% of BI projects fail to deliver

Lack of agility. Aenean commodo ligula eget dolor. Aenean massa. Cum sociis natoque penatibus et magnis dis Aenean commodo ligula eget dolor. Aenean massa. Cum sociis natoqueCum sociis natoqueCum sociis natoque

Low quality of deliverables Aenean commodo ligula eget dolor. Aenean massa. Cum sociis natoque penatibus et magnis dis parturientAenean commodo ligula eget dolor. Aenean massa. Cum sociis natoqueCum sociis natoque penatibus et magnis dis parturient montes, nascetur ridiculus mus. Donec quam felis, ultricies nec,

Simply Too late.Cum sociis natoque penatibus et magnis dis parturient montes, nascetur ridiculus mus. Donec quam felis, ultricies nec, Cum sociis natoque penatibus et magnis dis parturient montes, nascetur ridiculus mus. Donec quam felis, ultricies nec,

Member of the Asscoiated Press . Aenean commodo ligula eget dolor.

Aenean. Aenean commodo ligula eget dolor. Aenhswse. Cejhciebce fcdcdcd.

ILLUSTRATED WEEKLY NEWSPAPER

We haven’t time to

create these tests

Time to execute a test-suite

1.0 1.1 1.2 1.3 1.4 1.5

Manual

Automate

Release

Ela

pse

d t

ime

Exponential slope

Why?- New

objects- Old objects

Time to write a test-suite

1.0 1.1 1.2 1.3 1.4 1.5

ManualAutomatedWith Framework

Release

Ela

pse

d t

ime

Initial gap

No existing framework for test

automation dedicated to BI

… I’m not proficient enough in C#

This framework exists: NBi

No C#, just xml syntax

Open-source

Agenda

• Why automating is a must?• Tests with queries (Sql/Mdx/Dax)• Tests with ETL packages (SSIS)• Testing multidimensional and tabular models (SSAS)• Automate the creation of test-suites

Compare queries … result-setsSystem-under-

testAssert

Previous release

Two different releases of a same data

warehouse

Tabular model versus Csv fileDAX

… or any brilliant idea

Cube and data warehouseMDX SQL

Demo: compare queries’ result

• Compare cube/operational database• 101

• Compare cube/flat files• Defaults and References• Tolerance• Intervals

Other testing goals for queries

• Performance of a query• Executed in less than x seconds• Cache management

• Syntax of a query• Extract queries from reports and assemblies

Query’s result and format

• Result’s format• Sample: all the cells have effectively 2 digits after the decimal

separator and this separator is a comma.

Check calculations in your query

• Assert a logical test, row by row, for you result-set• Sample: Calculate the total price from the unit price, quantity

and discount percentage.

• If at least one row doesn’t validate this logical test, the whole test shift to red

Agenda

• Why testing automation is a must?• Tests with queries (Sql/Mdx/Dax)• Tests with Etl packages (SSIS)• Testing multidimensional and tabular models (SSAS)• Automate the creation of test-suites

Demo SSIS

• Assert the execution status of an ETL• Alternatively test performances

Inside this ETL

DimensionCity

Dimension Scientist

Factless fact Birth/Death

insert insertUnique index

violation

Setup and Cleanup

• Setup: • Define the state of your testing environment before executing

a test

• Cleanup: • Remove consequences of your tests on the testing

environment

Tasks and behaviors

• Available tasks • Truncate/Load tables with csv files• Run sql batch, exe or batches• Run Etl packages• Move or delete files• Start/Stop Windows services

• Run-once: execute only once this set of tasks• Parallel: run a set of tasks in parallel

Demo Setup

• Setup to register a clean state before test’s execution• Assert database’s state after the execution of an ETL

Agenda

• Why automation is a must?• Testing queries (Sql/Mdx/Dax)• Testing Etl packages (SSIS)• Testing multidimensional and tabular models (SSAS)• Automate the creation of test-suites

Testing dimension’s members

• “Germany” is a member of dimension “Country”• “Europa” is not a member of dimension “Country”• Dimension “Country” has more than 190 members.• All members of dimension “Country” have at least 4

characters• Members of hierarchy “Month” are ordered

chronologically (and not alphabetically)• …

Testing structure

• Assert that a dimension, hierarchy, measure, … is visible for end-user in a given perspective.

• Assert the existence of a relation between a measure-group and a dimension.

Agenda

• Why automation is a must?• Tests with queries (Sql/Mdx/Dax)• Tests with Etl packages (SSIS)• Testing Multidimensional and tabular models (SSAS)• Automating the creation of test-suites

Some tests are really repetitive

It should be possible to

automate the creation

It exists! Take a look at

Genbi

A template for the tests

A set of test-cases

First steps with GenbiFile

loaded from Genbi

Variables to be substituted

(surrounded by $)

Demo: Genbi

• Load test-cases• From a flat file

• Select a template• Generate the test-suite• Grouping option

I’m scripting macros to automate the

actions performed by genbi …

With genbiL!

Sample for genbiL

Subject

Action Parameters

Demo for genbiL

• Command line

Conclusions

• Benefits of test automation• Save Time (Time-on-market)• Better Quality (end-result)• More confidence

• NBi• Support large range of different tests• No need of compiler (neither C#)• Good feedback when tests are failing• Open-Source• Automate the creation of your test-suites (genbi / genbiL)

Read further

• Website: http://nbi.codeplex.com

• Download, documentation, bugs/Wishes list, support

• Blog: http://seddryck.wordpress.com

• Twitter: @Seddryck

Partnership

TestingSquad

Rue des Déportés, 471200 Bruxelles

phone : +32 470/84.07.47email  : daniel.mueller@testingsquad.euWeb   : www.testingsquad.eu