Sadly, those tools have been lacking in the world of data centric development. There are some tools to assist with testing some aspects of data centric development, but a huge need has gone unfilled for a single tool to perform comprehensive testing across all areas of data centric development.
Pragmatic Works has answered the call for a comprehensive testing tool with its newest product: LegiTest.
LegiTest allows you to not only unit test a vast array of objects in your data centric world, but use those tests inside a continuous integration platform. Even further, LegiTest will let you setup tests to validate the data within your data targets, be they databases, files, reports, and more. Let’s look at the various things LegiTest can validate.
Naturally, LegiTest will allow you to test programmatic objects such as stored procedures and functions. With LegiTest however you can also test database structures such as views and tables. Any DDL (Data Definition Language) item can be tested.
And not just for SQL Server either, database testing can extend to any database which is OLE DB or ODBC compliant, such Oracle and DB2.
SSIS Packages remain one of the worst tested objects in the world of data centric development. A developer runs the package, it executes successfully, and finally it is blessed and sent to production.
LegiTest changes that. Developers can structure tests such that all code branches could be covered and tested. A developer could even insert records that will trigger failures, and LegiTest could validate that the package did indeed fail gracefully.
LegiTest supports Analysis Services in a variety of ways. First, it can trigger processing of a dimension, cube, or the entire SSAS database and validate that processing was successful. Then through MDX calls any query can be executed, and the results validated. The results of an MDX query can even be compared against a dataset from the database used as the source for the cube.
Testing Reporting Services reports has long been a manual, time intensive process. LegiTest greatly simplifies and automates the process. Reports can be executed and the result saved in XML. Through LegiTest’s XML Path Query tool the resultant report can be dissected for validation purposes. Even further, queries can be performed against the Report Server execution log to ensure the report executed in a timely manner.
Data is the lifeblood of any organization, and LegiTest doesn’t overlook it. Data can be tested and compared in many different forms. Data can be tested against scalar values, for example validating aggregate values are computed correctly.
Further, the testing framework can do data set comparisons, validating the rows of data in a target matches the source it came from. Sources and targets need not be of the same type, for example one could compare the data in an Oracle database to that in a SQL Server database, or the data from a file against a target database.
LegiTest compiles the tests you create into a DLL compatible with either NUnit or MSTest. Thus not only can it be run from within Visual Studio, it can also be run from the command line. Since it uses common, widely supported formats for the tests, it can be easily used in Continuous Integration systems such as TFS (Team Foundation System), TeamCity, or directly from MSBuild.
The command line capability of LegiTest not only allows integration into the aforementioned CI systems, but it could also be called from within an SSIS package via the Execute Process control to validate the results of an SSIS execution. Thus validation could be easily incorporated into your ETL processes.
LegiTest is a revolutionary tool in the world of data centric development. It allows testing across all aspects of your development lifecycle, providing validation at each stage of development. Built on an industry standard testing framework means it is compatible with your existing testing and CI platforms. Finally, its ability to perform data validation will legitimize your IT processes to the business stakeholders in your company.