Sander van der Hoeff
Du Sabot
Zevenaar, The Netherlands
Actions
With over 20 years of experience in automation Sander van der Hoeff knows his way around the ICT business. Since 2000 Sander is specialized in business intelligence and data warehousing using the full SQLServer Stack.
In 2012 Sander founded his own BI-company: Du Sabot.
Nowadays Sander spends his time on several BI- and DWH-projects as an independent professional. Sander combines deep technical knowledge with the capability to explain difficult BI- and DWH-concepts to non-technical people.
In the last years he shifted towards Data platform testing
Area of Expertise
Topics
Database testing with tSQLt
tSQLtis a very simple but powerfull testframework for database-testing and more.
The biggest benefit: you write your test in a well known language which is SQL.
A secondary benefit, this is a method of non destructive testing. All test are run in a transactions which is rollback after the test is run. So there is no need of correct data after the test and test can be run over and over again.
In this session I introduce you to the framework and how we use it for testing our complete data-environment during development, test and the daily monitoring of the system.
Regression testing in a dataplatform environment
When testing new developments we need to ensure that the required functionality is delivered, but it is equally important to prove that the original functionality is still intact.
Since in a data platform both Code and Data influence the outcome of the test they both need to be tested.
To do so we used a combination of tSQLt as testframework, Analysis services for regression together with test generation to cover almost the whole platform with our regression test.
I will guide you along the way how to set up, create and run the test time after time.
Testing your data platform: How to gain and keep control
When you are responsible for the quality of a data platform , your system contains most likely not only of databases, but also a ETL, OLAP and reporting components. Data platform also implies that a change in source data can hugely influence the output.
I will take you through the step we took to gain and keep control and explain why you should do it and how, with which set of tools.
First of all, you need to know what software, are on your system and what will be changed during a release. There are a lot of paid tools on the market, but a lot can be done for free.
The Second step to gain control is to master your data. Codes which changes without notice can have big impact. To check on a daily basis if your assumptions on the source data are still valid tSQLt, a test framework for sqlserver is very handy.
Being able to perform a regression test, whenever that is in your release cycle or on a daily basis is the step to remain in control. Here can come SSAS cube or excel come to the rescue.
Finally don’t forget crosschecks to make sure that no data from your source system is lost in the processing.
Testing Datawarehouse and BI Solutions
Testing is becoming more common for databases. However the focus of these test are mostly on Stored Procedures.
Datawarehouses and reporting solutions uses databases in a much different way. Testing is as important as for stored procedures but less simple. And not to forget Cubes!
Another specific of Datawarehouses is the need to monitor the dataquality on a daily basis on production system.
I will take you on the journey on the tools I use for testing a datawarehouse, including good old Excel, WinMerge and the relatively new kid on the block tSQLt.
The testframework tSQLt is very usefull for testing which some minor adaptions.
When there are cubes available in the Datawarehouse they can easily be used for regressing testing which is automated with tSQLt.
Last but not least I want to share some thougths how to test de reporting part of the datawarehouse
Database projects in VS2017-The Road to BI Devops
Bi developpers are using Visual Studio for SSAS, SSRS and SSIS development all the time,but tend to forget to use Visual studio for there database development.
A database project, even when you generate all your structures, opens the road to putting your database code under source control and move forward to a continuous integration/deployment for the database part of your datawarehouse. You can start from scratch or start with your currenct databases as a starting point to move foreward with the reverse engineering option.
With a template in Visual studio you can generate all your tables and views in a second from a metadata solution.
I'll show how to set up your database project, how to generate tables and views from metadata directly in the database project and where database project can help in deploying your entire solution quickly to all you environments, including security settings and statics base data.
PowerBI vs Tableau: the battle
Working with two of the major (Self service) BI tools it gives me the opportunity to compare them.
There are a lot of similarities but looking at the differences is interesting in order to make the correct choice for implementing the tool which suits your organisation the best.
SQLServer under pressure: Can I fix it?
Where to start when your SQLServer is under pressure? If your server is misconfigured or strange things are happening, there are a lot of free tools and scripts available online.These tools will help you decide whether you have a problem you can fix yourself or you really need a specialized DBA to solve it.
Those scripts and tools are written by renouwned SQLServer specialists. Those tools provide you with insights of what might be wrong on your SQLServer in an quick and easy manner. You don’t need extensive knowledge of SQLServer nor do you need expensive tools to do your primary analysis of what is going wrong
And in a lot of cases these tools will tell you that you yourself can fix the problem.
SSIS Package generation with BIML
Ever ended up with a source system where an old datatype was replaced with a new one so you had to rebuild all your SSIS packages?
Entered a project with SSIS packages which needed a complete rebuild to perform but you didn’t have the time for a rebuild of your whole project?
Client specifications changed (again) and you had to start all over again building all your packages?
We did it all and rebuild 100+ SSIS packages within a day using biml for generating SSIS packages.
And with a generator at hand, we were able to absorb a new version of a source system including analysis of the changes made in the source in only two days. And the best of all, BIML is part of BIDSHelper and free to use!
Don' t just believe what I say, In my session I will demonstrate where to start.
I will show you around in BIML. After the quick basics of BIML I will show you how you can parameterize the BIML script in order to multiply the template to multiple packages over and over again.
Please note that Sessionize is not responsible for the accuracy or validity of the data provided by speakers. If you suspect this profile to be fake or spam, please let us know.
Jump to top