The Data Compare test plan in BI Validator can be used to compare data from an ODBC DSN with the data in an BI Report. This article focuses on the steps needed for creating an ODBC DSN for the Excel file.
- Download and install the Microsoft Excel Driver for ODBC 64-bit from the below link:
https://www.microsoft.com/en-us/download/details.aspx?displaylang=en&id=13255
- Open the ODBC Administrator (64-bit) and create a new DSN for the Excel file. Please note that only .xlsx files are supported.
- From BI Validator, add a new DB Connection and select ODBC as the 'Database Type'. Enter the DSN name (eg. test) for username, password and schema name. The excel spreadsheet not be open while the ODBC connection is being created or used in a test plan.
- Create a Data Compare test plan in BI Validator and choose 'Report-to-Query' as the Comparison type:
- Navigate to the next step and add a report by clicking on the 'Add' button. Drag the report you want to test and the type in the query as shown below:
Please refer to the link below for the additional details on how to write SQL Queries on an Excel spreadsheet.