The Importance of Data Warehouse and BI Testing

In the world of data, it’s imperative to test your data warehouse and BI systems. If you’re not testing early in the process, then you run the risk of incorrect results and poor performance, which can result in restarting a BI project all over again— wasting time, effort and money, as well as creating bad business decisions and losing business.

Pragmatic Works' COO, Tim Moolic, spoke with one of our consultants, Jessica Dzurek, about the importance of BI and data warehouse testing. BI data testing is the process of being able to trace the accuracy and validity of your BI system, from your databases to your output reports. The three key target testing areas are, functionality, results and performance. You want to make sure everything is functioning right, from the validity of code and logic to the analytics that are being produced in your warehouse systems and reports, so users can consume the data when needed.

BI is always about getting the right information to the right people at the right time. If your systems are not performing well, then you’re not meeting that goal. Let’s say a developer wrote a new SSIS package that functioned as expected and gave correct results, but took 12 hours to run in production! If your reports aren’t getting out until mid-day or later because you can’t consume the data in a timely fashion, then you’re missing business opportunities that could be going to your competitors.

What is the difference between database testing and BI testing? Database testing focuses on the data itself; all the data that is consumed by your application and BI systems. This includes monitoring your application databases to ensure the data in them meets your quality and accuracy standards, and comparing your source systems to your data warehouse to be sure that data is flowing into the data warehouse as expected. Testing the performance and accuracy of the ETL processes responsible for moving this data is also important.

BI testing encompasses all the different places where the data warehouse is consumed – the reports, data marts, OLAP cubes, and exports of data. Since information from the BI systems are the drivers for critical organization decisions, this is a vital place to ensure that you are working with validated, reliable data.

At Pragmatic Works, we are data professionals with a goal to stop bad data. If you do database testing, but you’re not doing BI testing on every application from the database through the dashboard, then you could be producing bad data that you’d miss with just database testing. And if you’re doing manual testing instead of using automated data testing, like LegiTest, the chances of you catching that bad data before problems occur can be slim.

For example, you’ve got your manual testing strategy laid out and you feel confident. Then the source system vendor sends your feed overnight and changes a column length size from 50 to 100. Your whole system crashes and you waste time digging into your monitoring system or ETL log to try to find what failed. Not how you want to start your day.

With the growth in quantity, of data and vendors, you may have 100s of files coming in. Keeping up with that growth manually is almost impossible. If you can automate tests ahead of time, you can catch problems before they happen, or at least be steered in the direction of the issue, saving your investigation time.

Final words from Jessica, don’t allow testing to become a secondary thought because of time constraints with a BI project – make testing a consideration throughout the process. If you’re rushing to get a project out, but that project is delivering bad results or poor performance, then you’re wasting time and energy to start all over again.

Pragmatic Works’ Legitest is an automated data testing software that can solve these problems. It allows you to easily create BI and data warehouse testing to catch issues before they occur. Not only does LegiTest stop bad data, but it saves you time, increases productivity and allows you to confidently use your data to make better business decisions.