Transcript TUGA IT 2016 - SQLSaturday

Automating the testing of BI Solutions wih NBi
Cédric L. Charlier
@Seddryck
In a nutshell
 Agile data warehouse
 Quality-oriented
 Automation fanboy
Cédric L. Charlier
@Seddryck
seddryck.wordpress.com
Data & BI architect
Open-sources
contributions
 Testing framework for BI
 www.nbi.io
 Other projects on github
 Deployment SSRS - RsPackage
 Starting SSIS from .Net - Cassis
 Toolset for special cases of modeling:
ERMine et Tibre
 And much more
Skills &
Interests




Database architecture
Data modeling
Business intelligence
Data warehousing
 ETL
 Olap/Tabular
 No SQL
 Big data
 Data analysis
 Machine learning
Recognitions
Experience
 15 years avec SQL Server
 Former consultant, working
internally for Elia
 International speaker
 Microsoft MVP Data
Platform award
Agenda





Why automating is a must?
Tests with queries (Sql/Mdx/Dax)
Tests with ETL packages (SSIS)
Testing models (SSAS and DB engine)
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
Ensure
consistency
Speed up
testing to
accelerate
releases
Improve
test
coverage
Tests automation
doesn’t apply to
Business Intelligence
Member of the Asscoiated Press .
Aenean commodo ligula eget dolor.
Aenean. Aenean commodo ligula eget
dolor. Aenhswse. Cejhciebce fcdcdcd.
ILLUSTRATED WEEKLY NEWSPAPER
Wednesday, November 24, 2012
Est. 1869
More than 33% of BI
projects fail to deliver
Lack of
agility.
Low quality of Simply Too
deliverables
late.
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
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,
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,
Price 6d
We haven’t
time to create
these tests
Elapsed time
Time to execute a test-suite
Exponential
slope
Manual
Why?
- New objects
- Old objects
Automate
1.0
1.1
1.2
1.3
Release
1.4
1.5
Elapsed time
Time to write a test-suite
Manual
Automated
With Framework
Initial gap
1.0
1.1
1.2
1.3
Release
1.4
1.5
No existing framework
for automating tests
dedicated to BI
… I’m not proficient
enough in C#
This framework
exists: NBi
No C#, just xml
syntax
Opensource
Agenda





Why automating is a must?
Tests with queries (Sql/Mdx/Dax)
Tests with ETL packages (SSIS)
Testing models (SSAS and DB engine)
Automate the creation of test-suites
Compare queries … result-sets
Assert
System-under-test
MDX
Cube and data
warehouse
Two different releases of
… or
any brilliant idea
a same data warehouse
DAX
Tabular
file
Tabular model
model versus
versus Csv
Xmlfile
SQL
Previous
release
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
 Example: all the cells have effectively 2 digits after the decimal
separator and this separator is a comma.
Assert row count
 Assert how many rows are returned in the result-set.
 Equal, more-than, less-than
Agenda





Why automating is a must?
Tests with queries (Sql/Mdx/Dax)
Tests with ETL packages (SSIS)
Testing models (SSAS and DB engine)
Automate the creation of test-suites
Demo SSIS
 Assert the execution status of an ETL
 Alternatively test performances
Inside this ETL
insert
Dimension
Calendar
Dimension
Geography
Unique
index
violation
insert
Dimension
Scientist
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
Limitations
 ETLs must be stored
 SSIS Catalog
 File share
 SQL Server
 Tests must be executed on the server running the ETL
 No remote testing
 Supports SQL Server 2008R2, 2012 and 2014
Agenda





Why automating is a must?
Tests with queries (Sql/Mdx/Dax)
Tests with ETL packages (SSIS)
Testing models (SSAS and DB engine)
Automate the creation of test-suites
Testing dimension’s members
“Italy” is a member of dimension “Country”
“Italy” is not a member of the set “Nordic countries”
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)
 Members are equivalent to members of this other instance
 …




Example: members’ pattern
Testing structure (SSAS)
 Assert that a dimension, hierarchy, measure, set, … is visible
for end-user in a given perspective.
 Assert the existence of a relation between a measure-group
and a dimension.
Testing structure (DB engine)
 Assert existence of a




schema,
Table
Column
Routine (stored procedure or function)
 Assert the type of a column
 Full type: decimal(10,3)
 Light type: decimal
Agenda





Why automating is a must?
Tests with queries (Sql/Mdx/Dax)
Tests with ETL packages (SSIS)
Testing models (SSAS and DB engine)
Automate the creation of test-suites
Some tests are
really repetitive
It should be
possible to
automate the
creation
A set of testcases
It exists! Take a
look at Genbi
A template for
the tests
First steps with Genbi
File 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 automated tests
 Save Time (Time-on-market)
 Better Quality (end-result)
 More confidence
 NBi





Supports large range of different tests
No need of compiler (neither C#)
Good feedback when some tests are failing
Open-Source
Automate the creation of your test-suites (genbi / genbiL)
Read further
http://www.nbi.io/




Download releases
Read documentation
Report bugs
Suggest new features