Many Data and Business Intelligence teams don’t think they can automate their tests because they don’t have time to explore and choose a tool or they just can’t afford the tools on the market that meet their testing needs. With high quality testing practices and procedures, automation is right around the corner with tools that can be readily found already on a laptop/desktop or via free downloads. Here are the 5 components to build a test automation framework for FREE!
1. Test Data Storage
Several sets of data are stored in the test definition table(s):
· A fixed set of test data is loaded and stored to test against.
· Test components are defined and stored to test against the data.
· Test results (dates, results, values, etc.) are captured and stored for reporting.
Free Tools: SQL Server Developer or Express, either with SQL Server Management Studio (SSMS), can be downloaded and installed for free in non-production environments to get started.
2. Test Input Form
Test components (IDs, dates, codes, descriptions, types, flags, queries, etc.) need an input form, to be used by analysts, developers and testers, for capture and measure.
Free Tools: Microsoft Access, Sharepoint and Excel easily connect to SQL databases for data entry/validation. Web developers can also create a simple HTML or Javascript page as well.
3. Test Runner
Tests are automated to execute from the test definition table against source, stage, EDW, etc. tables and capture the test results.
Free Tools: SQL ETL task modules (SQL Server Information Services SSIS), scripts and stored procedures or Windows Powershell scripts run tests individually or via batch processing.
4. Test Output Reporting
Visualizing test results provides proactive feedback for data/BI teams and management.
Free Tools: Power BI, Power Pivot in Excel, Microsoft Access or SQL Server Reporting Services (SSRS) visualize test result metrics (pass/fail, by type, date, summary, detail, etc.)
5. Rebaseline
At the end of testing, the environment needs to be cleared out and recreated, basically resetting everything back to pre-testing. Staging ETL, dropping EDW schemas, running EDW ETL, etc.
Free Tools: SQL ETL task modules (SQL Server Information Services SSIS), scripts and stored procedures or Windows Powershell scripts can run rebaselines.
Most companies have these types of resources readily available, even on an enterprise level. Test Automation frameworks can also be built on Oracle and SAP platforms, using the wide variety of BI tools most companies already have (Tableau, SAP BOBJ & Crystal, Microstrategy, etc.)
Fox Consulting has 30 years of experience in software quality assurance. If you aren’t sure why or where to get started with test automation, contact us to discuss the roadmap to proactive automated data and business intelligence testing.
