Validate Your Data Before Your End Users Invalidate You

Database testing is a critical aspect of development. BI and Data Warehouse testing is another level of testing that is key to ensuring the quality of your data throughout your BI system. Data quality, or lack of it, is a focus of many these days. Bad data is an epidemic, and data testing is the key to solving the epidemic while allowing you to have confidence in your data.

What exactly is BI and Data Warehouse Testing? It’s a process to trace the accuracy and validity of data in your BI system. It expands upon general database testing, with concepts and processes borrowed from software testing, such as unit, regression and performance testing.

Most testing comes with challenges. BI and Data Warehouse Testing challenges include:

•  A general learning curve, depending upon the experience of your team.
• Time – Testing takes time to implement and can be a challenge.
• Resources – What resources do you have available? Smaller teams often wear many hats in development.
• Multiple moving parts of a BI System to consider (i.e. ETL, OLAP Cubes, Tabular models and reports).

But, overcoming any hurdles is well worth the time. Once you have a testing strategy in place, you will gain confidence in your data and decisions that affect your business. Creating a testing strategy helps you address challenges associated with BI and Data Warehouse testing. It also helps you establish roles and responsibilities, manage expectations up front, and create a communication process.

There are 3 options/approaches to BI and Data Warehouse testing:

Validate Data Via T-SQL User Stored Procedures

This approach can be a long-term solution, but only if requirements are simple. It is a repeatable process that requires no additional software investment. The ETL Team should already have these T-SQL statements written for validating SSIS packages. The cons of this approach, although, outweigh the pros. This is not a good solution for complex data and requires a foundation of metadata in your data model at the start. Source data must be staged in persisted SQL Server Tables (linked servers not recommended). Business logic must be replicated in T-SQL and there are not SSRS, SSAS or Power BI data visualizations available.

Validate Data Via SSIS Packages

As with the first approach, this option requires no additional software investment. This is an expandable solution coupled with the power of SSIS tasks, which results in a structured, more feature-rich solution. Logic is contained in step with other package solutions with other ETL objects. Cons include the need for senior-level development skills and SSIS package development time. Business logic still must be replicated in a variable or source component. And, as with Option 1, there are no SSRS, SSAS, or Power BI data visualizations available.

Validate Data Via LegiTest Automated Testing

LegiTest features automated testing through scheduling and notifications, making communication between testers more efficient. With Wizards and built-in templates, it’s easy to quickly develop test suites to related tests. Use design patterns to isolate variability and promote consistency. LegiTest integrates with Microsoft Visual Studio, which can help cut down on the learning curve. LegiTest also has reporting tool testing.

The cons of this option include, licensing, tool updates and the potential need for a wider range of skills needed to take advantage of features. Luckily, Pragmatic Works On-Demand Training platform, with its 30+ courses, can help you on that end.

Bottom line is, BI and Data Warehouse testing is an important piece in your testing strategy. Here are some critical success points:

1. Do something! Too many companies have no data validation in place.
2. Identify Your Unique Requirements – What fits best with your company?
3. Choose a Scalable Solution – How much can you manage? What do you NEED to do?
4. Guarantee Automated Notification – Have the contact info and know who to go back to when problems arise.
5. Retain History for Analysis

Pragmatic Works and LegiTest can help you get your BI and Data Warehouse testing strategy in place. LegiTest will help you automate your testing, so you can save time, but more importantly, gain confidence in your data. Not sure where to begin? Our consultants are experts in their field. Visit our website or contact us today to see how we can help you get your testing strategy on track.