{"id":70236,"date":"2017-03-08T18:00:05","date_gmt":"2017-03-08T18:00:05","guid":{"rendered":"https:\/\/www.simple-talk.com\/?p=70236"},"modified":"2021-06-03T16:48:24","modified_gmt":"2021-06-03T16:48:24","slug":"using-dbfit-framework-data-warehouse-regression-testing","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/t-sql-programming-sql-server\/using-dbfit-framework-data-warehouse-regression-testing\/","title":{"rendered":"Using the DbFit Framework for Data Warehouse Regression Testing"},"content":{"rendered":"

How do you, or should you, automate database testing? If you have a technique for doing so, how can you extend this for testing a data warehouse? How easy is it to use? There are some established tools that are designed for testing databases, and the FitNesse framework<\/a> is one of the best for user-acceptance tests. In this article I aim to demonstrate enough to get you started with using DbFit for regression-testing a SQL Server Data Warehouse.<\/p>\n

DbFit for the Job?<\/h2>\n

DbFit is a member from FitNesse framework. FitNesse is an implementation of the Framework for Integrated Test (FIT)<\/a> testing framework. DbFit is specially designed and developed for database testing.<\/p>\n

In DbFit, the test cases are written in a simple text format. The DbFit test cases are designed to work with simple SQL queries and stored procedures.<\/p>\n

There were six major reasons why we implemented DbFit within our DevOps environment. It was easy to use with our existing CI tools, the test cases were easy to write, we could extend it for our specialized requirements, DbFit worked smoothly with Excel, It was easy for the database developers to understand, and there was a good web-based GUI that everyone can use.<\/p>\n

    \n
  1. \n
      \n
    1. \n

      Support for XCopy Deployment<\/strong>
      \nDbFit test-cases can be executed from a web interface or from a command line. Because of the command line support, it is very easy to integrate with the continuous integration tools for XCopy deployment.<\/p>\n<\/li>\n<\/ol>\n<\/li>\n<\/ol>\n

       <\/p>\n

        \n
      1. \n
          \n
        1. \n

          Wiki Test cases<\/strong>
          \nAll the test cases are written in simple wiki format. It is easy to understand and aid for better communication between technical and non-technical stakeholders.<\/p>\n<\/li>\n<\/ol>\n<\/li>\n<\/ol>\n

           <\/p>\n

            \n
          1. \n
              \n
            1. \n

              Highly extensible framework<\/strong>
              \nThe DbFit framework can be extended using java or .Net languages.<\/p>\n<\/li>\n<\/ol>\n<\/li>\n<\/ol>\n

               <\/p>\n

                \n
              1. \n
                  \n
                1. \n

                  Export and Import test cases using Excel <\/strong>
                  \nThe DbFit has good interoperability with Excel. The test cases can be written in Excel and imported into DbFit. Also DbFit does support to export the test cases as Excel sheets.<\/p>\n<\/li>\n<\/ol>\n<\/li>\n<\/ol>\n

                   <\/p>\n

                    \n
                  1. \n
                      \n
                    1. \n

                      Developer\u2019s friend<\/strong>
                      \nIt enable the database developers to access the database objects in tabular format. This helps the database developers to adopt the framework easily.<\/p>\n<\/li>\n<\/ol>\n<\/li>\n<\/ol>\n

                       <\/p>\n

                        \n
                      1. \n

                        Web interface to manage test cases <\/strong>
                        \nDbFit has a fantastic GUI (frontend) to manage the test cases. Test cases can be created and edited in the front end. The test cases can be executed as a single test case or a suite (collection of test cases).This GUI helps a tester or anyone to create test cases without the knowledge of scripting.<\/p>\n<\/li>\n<\/ol>\n

                        DbFit for regression test:<\/h2>\n

                        The DbFit can be used to regression-test both the database metadata and the data itself. Testing the metadata will disclose the unexpected changes in database objects such as tables, views or stored procedures, whereas testing the data will disclose the unexpected changes to the data coming from the ETL packages.<\/p>\n

                        In this article we will concentrate about testing the data in the Data warehouse, and cover these areas in detail.<\/p>\n

                        <\/h2>\n

                        Installation & Configuration:<\/h2>\n

                        In this section I will explain how to install DbFit, and set up the necessary configuration on your local machine.<\/p>\n

                        Pre-requisite:<\/h3>\n

                        We need Java Runtime Environment (JRE) 7 or higher to set-up DbFit. The JRE can be downloaded from Oracle website here<\/a>.<\/p>\n

                        Installation:<\/h3>\n

                        You can download DbFit from the web site here<\/a>. Once the zip file has been downloaded then you can unzip the contents to a folder.<\/p>\n

                        <\/p>\n

                        Contents of the zip file can be seen as per the image below<\/p>\n

                        <\/p>\n

                        Starting DbFit Service:<\/h3>\n

                        The DbFit framework can be started by running the \u201cstartFitnesse.bat\u201d file. It may take few minutes to start for the first time.<\/p>\n

                        If, as a result, you can see this, it confirms that the DbFit service has been started successfully.<\/p>\n

                        \"C:\\WorkArea\\Nat\\SQL\\Blog\\SimpleTalk\\DbFit\\Images\\DbFit_Startup.png\"<\/p>\n

                        \"C:\\WorkArea\\Nat\\SQL\\Blog\\SimpleTalk\\DbFit\\Images\\DbFit_Startup.png\"<\/p>\n

                        Once the DbFit package is up and running, the service can be accessed using the URL http:\/\/localhost:8085\/<\/code> in a browser as shown below.<\/p>\n

                        \"C:\\WorkArea\\Nat\\SQL\\Blog\\SimpleTalk\\DbFit\\Images\\DbFit_Homepage.png\"<\/p>\n

                        Stopping the service:<\/h3>\n

                        The DbFit service can be stopped by pressing Ctrl+C on the MSDOS command prompt. The prompt \u2018Terminate Batch Job (Y\/N)\u2019, shown below, means that the service is about to be stopped.<\/p>\n

                        \"C:\\WorkArea\\Nat\\SQL\\Blog\\SimpleTalk\\DbFit\\Images\\Stopping.png\"<\/p>\n

                        \"C:\\WorkArea\\Nat\\SQL\\Blog\\SimpleTalk\\DbFit\\Images\\Stopping.png\"<\/p>\n

                        Connecting to SQL Server:<\/h3>\n

                        Because of licensing restrictions, the database drivers are not available with DbFit by default. Instead, we need to download them manually and then copy the files across to the DbFit folder.<\/p>\n

                        Unfortunately, DbFit does not connect to SQL Server by default. We need to install the JDBC driver and configure it to use in the DbFit framework. These steps will help you to install the driver and connect to SQL server.<\/p>\n

                        Installing JDBC driver:<\/h3>\n

                        The JDBC driver for SQL Server can be downloaded from Microsoft website here<\/a>.<\/p>\n

                        It is best to download the zip file \u201csqljdbc_6.0.8112.100_enu.tar.gz\u201d. Once downloaded, unzip the contents to a folder.<\/p>\n

                        You will be able to locate the JDBC driver file \u201csqljdbc42.jar\u201d in the folder:<\/p>\n

                        \u201c..\\sqljdbc_6.0.8112.100_enu.tar\\sqljdbc_6.0.8112.100_enu\\sqljdbc_6.0\\enu\\jre8\u201d<\/p>\n

                        \"C:\\WorkArea\\Nat\\SQL\\Blog\\SimpleTalk\\DbFit\\Images\\JDBC_Driver.png\"<\/p>\n

                        Now copy the JDBC driver file into the DbFit lib folder (..\\DbFit-complete-3.2.0\\lib), as shown in the image below.<\/p>\n

                        \"C:\\WorkArea\\Nat\\SQL\\Blog\\SimpleTalk\\DbFit\\Images\\JDBC_Driver_Deployment.png\"<\/p>\n

                        This has enabled us to connect the SQL server from DbFit, but if you would prefer to connect using the more secure Windows authentication, then you will need to follow one more additional configuration step, which I\u2019ll now explain.<\/p>\n

                        Setting up Windows authentication:<\/h3>\n

                        The assembly (sqljdbc_auth.dll) can be found on the folder \u201c..\\sqljdbc_6.0.8112.100_enu.tar\\sqljdbc_6.0.8112.100_enu\\sqljdbc_6.0\\enu\\auth\\x64\u201d.<\/p>\n

                        \"C:\\WorkArea\\Nat\\SQL\\Blog\\SimpleTalk\\DbFit\\Images\\SQLAuth.png\"<\/p>\n

                        \u2026so copy this file to the JRE folder C:\\Program Files (x86)\\Java\\jre1.8.0_121\\bin as shown in the next screenshot.<\/p>\n

                        \"C:\\WorkArea\\Nat\\SQL\\Blog\\SimpleTalk\\DbFit\\Images\\SQlAuth_Deployment.png\"<\/p>\n

                        Setting up a sample test case:<\/h2>\n

                        Now that the DbFit package has been set up successfully, we will now create a sample test case.<\/p>\n

                        Open http:\/\/localhost:8085\/SampleSQLTest<\/code> in your browser. You should see an editor where you can create and run your test case (page).<\/p>\n

                        \"C:\\WorkArea\\Nat\\SQL\\Blog\\SimpleTalk\\DbFit\\Images\\SampletestCase.png\"<\/p>\n

                        This will create a blank test case as shown in the image below.<\/p>\n

                        \"C:\\WorkArea\\Nat\\SQL\\Blog\\SimpleTalk\\DbFit\\Images\\SampletestCaseEdit.png\"<\/p>\n

                        Test cases are created in a wiki markup which is a simple text format. The representation is very similar to the relational model. Hence you can create DbFit test cases without the help of programming language.<\/p>\n

                        Test cases can be grouped logically in a test suite. DbFit expect you to follow Camel Casing when you give the name to a test case or a suit.<\/p>\n

                        In addition, the test cases can be created in Excel and imported from Excel to DbFit interface using the button \u201cSpreadsheet to FitNesse\u201d.<\/p>\n

                        Test cases are actually stored on the file system in a simple text format. You can create and edit them using a text editor such as Notepad or Notepad++.<\/p>\n

                        The additional drivers for DbFit need to be loaded into FitNesse to support the execution of tests. This can be achieved by loading the relevant libraries in the test case using this command<\/p>\n

                        !path lib\/*.jar<\/pre>\n
                        The line below indicates that DbFit will try to connect to MSSQL Server database. This helps DbFit to use the correct driver to execute the test case.<\/p>\n
                        !|DbFit.SqlServerTest|<\/pre>\n
                        More information can be found here<\/a> for other RDBMS.<\/p>\n
                        This line indicates the connection settings for the SQLServer<\/p>\n
                        !|Connect|localhost|DbFit_user|password|DbFit|<\/pre>\n
                        In DbFit, a SQL query can be executed using the Query command. The SQL query has to be defined after the command \u201c!|Query\u201d<\/strong> . We need to define the expected result set structure in the next line. Here, the result set will have only one column \u201cX\u201d. This is represented below.<\/p>\n
                        |x|<\/pre>\n
                        The subsequent rows define the expected results. It is enough to specify just the columns that you\u2019re interested within the result set. In this example, we are expecting only one value (\u201ctest\u201d) that is shown below.<\/p>\n
                        \u201c|test|\u201d<\/p>\n
                        Now all we now need to do is to copy the complete test case shown below, and paste it in the text box to create a test case. I will be using the JDBC driver to connect to SQL server and use Windows authentication to connect.<\/p>\n
                        !path lib\/*.jar\r\n  !|DbFit.SqlServerTest|\r\n  !|Connect|jdbc:sqlserver:\/\/localhost\\sql2012;integratedSecurity=true|\r\n  !|Query| select 'test' as X|\r\n  |x|\r\n  |test|<\/pre>\n
                        \"C:\\WorkArea\\Nat\\SQL\\Blog\\SimpleTalk\\DbFit\\Images\\Sampletestcasewithcode.png\"<\/p>\n
                        Once done, click the \u2018save<\/em>\u2019 button to create a test case. Now the test page will look like this.<\/p>\n
                        \"C:\\WorkArea\\Nat\\SQL\\Blog\\SimpleTalk\\DbFit\\Images\\TestCaseAfterCompletion.png\"<\/p>\n
                        Now the test case can be executed by clicking the \u2018Test<\/em>\u2019 button the header. The test case will, hopefully, be successful as shown in the image below.<\/p>\n
                        \"C:\\WorkArea\\Nat\\SQL\\Blog\\SimpleTalk\\DbFit\\Images\\TestResultExecution1.png\"<\/p>\n
                        Failure Case:<\/h3>\n
                        Now let\u2019s update the test case with an invalid SQL expression (\u201cSELECT GetDate1()\u201d)<\/strong> So that the test case will fail. Here we can see a failure case.<\/p>\n
                        \"C:\\WorkArea\\Nat\\SQL\\Blog\\SimpleTalk\\DbFit\\Images\\InvalidTestcase.png\"<\/p>\n
                        This represents the completion of the test case, and the test has failed.<\/p>\n
                        \"C:\\WorkArea\\Nat\\SQL\\Blog\\SimpleTalk\\DbFit\\Images\\TestResultCaseFailure.png\"<\/p>\n
                        Command line execution:<\/h3>\n
                        The DbFit test cases can be run from the command line. This is great for any DevOps environment because this will help us to integrate the DbFit with the Continuous Integration (CI) tools.<\/p>\n
                        This command line will help you to run the test page \u201cSampleSQLTest\u201d in DbFit<\/p>\n
                        Java -jar \".\\lib\\fitnesse-20150424-standalone.jar\" -c \"SampleSQLTest?test&format=text\"<\/pre>\n
                        Here we can see the successful execution of the test case from the Command line.<\/p>\n
                        \"C:\\WorkArea\\Nat\\SQL\\Blog\\SimpleTalk\\DbFit\\Images\\CommandLineExecution.png\"<\/p>\n
                         <\/p>\n
                        Naming Convention for Test Case:<\/h3>\n
                        There are two simple rules to follow while creating a test page name in DbFit<\/p>\n