ODBC Data Connection and Querying Procedure - trillium_discovery - trillium_quality - 17.1

Perform this procedure for each repository with which you plan to work. You run one query at a time, and are prompted during the procedure to supply the username and/or password of the user who created the DSN.

Before you start, ensure that the appropriate data has been generated in the Control Center. For example, to run the show_all_br query, you must have created and analyzed at least one business rule for the associated repository.

For descriptions of queries, see Queries.

To configure a connection and run a query in Excel for the first time

1. Open an instance of Excel 2019. (If you have another version of Excel, the steps will vary.)

   Note: Excel must be on the same system as the DSN you created.
2. In the Excel interface, click Data > Get Data > From Other Sources > From ODBC. The From ODBC window displays.

   Note: Each DSN is configured for a specific repository. Ensure you select the DSN for the repository from which you want to extract data.

3. Select a DSN that you created in the previous procedure, and click OK. The ODBC driver window opens, prompting you to log on to the data source.

4. Enter the User name and Password for the user who created the DSN.

   The details in the Credential connection string properties field are optional. These are security related connection string properties that shouldn't be shared in a query.

5. Click Connect. After a few seconds, in the Data Connection Wizard, the Select Database and Table window opens displaying all available queries in alphabetical order.  

   If no queries display, see Chapter 5: Troubleshooting and Additional Tasks on page 40.

6. Ensure Schema is selected as the database and the Connect to a specific table box is checked.

   Note: When you work with the queries, the term schema and the name of the query owner (for example, OAUSER) are added to the syntax and must not be removed or changed.

7. Select the query you want to run. If the list of queries is long, on your keyboard press the first two letters of the query name. The list scrolls down to query names that start with those letters.

8. Click Next. The Save Data Connection File and Finish window opens.

9. Click Finish. The Import Data window opens. Existing worksheet is selected by default and the cell where the data will be imported is highlighted and shown in the text field.

10. (Optional) Click another cell in the worksheet to change the default value. Click New worksheet to load the data into another worksheet.

11. (Optional) If you want to modify the query’s syntax:

    1. Click Properties to open the Connection Properties window.

    2. Click the Definitions tab and make changes as necessary to the query syntax in the Command text field. (For more information, see ODBC Query Syntax.)

    3. Click OK to save the settings and close the window.

12. To run the query and import data, in the Import Data window click OK.

13. Enter your password when you are prompted to log on to the data source, and click OK.

14. The data referenced by the query opens in the worksheet. The displayed format replicates the rows and columns in a Control Center List View.

Note the following:

• If an Initialization of Data Source Failed message opens, verify that you supplied the correct password. If you did not, run the procedure again.

• If the source data changes, refresh the worksheet to see the changes reflected in Excel. To refresh a worksheet, on the Data tab, click Refresh All.

• You open multiple query tables in the same worksheet by clicking an empty cell where you want to insert the data and running the procedure again.

• Excel has a limit to how many rows and columns it supports.

 The figure below shows the results of a table query in Excel.