OLE DB Data Connection and Querying Procedure - trillium_discovery - trillium_quality - 17.1

Perform this procedure for each query you want to run. Before you start this procedure you must first install the Trillium repository server and the Control Center on one system, and create a repository to which you want to create a connection.

Also, 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 each query and guidelines for using them, see Queries.

To configure a data connection and run a query for the first time:

1. On the client system, open an instance of Excel and open a workbook. (Depending on your version of Excel, the procedure may vary.)

2. In the Excel interface, click Data > Get Data > From Other Sources > From OLE DB.

3. Click Build. The Data Link Properties window opens.

4. On the Provider tab, select TSS 17 OLE DB Reporting Adapter and click Next. The Connection tab is selected.

5. In the Data Source field, type the name of the Trillium repository to which you want to connect.

6. In the Location field, enter the repository connection name; for example, primary. The default is the last location accessed.

7. To supply server log on information, do one of the following:

   Note: Use the same authentication method you use to log on to the Control Center.

   • If you are using Windows Authentication, select Use Windows NT Integrated security.

   • If you are using Trillium Authentication:

     1. Select Use a specific user name and password.

     2. Enter your Trillium user name.

     3. Clear the Blank password box and enter your Trillium password.

     4. (Optional) Check Allow saving password. This allows you to save the password in the .odc file so that you will not have to supply the password each time you run the query.

   Note: Security consideration: When you save your password, Excel stores the authentication credentials as part of the connection.

8. To confirm the connection, click Test Connection. When the test finishes, a test connection succeeded message opens. (If the connection failed, check the client log file to troubleshoot connection problems or verify the hex code in the error message. For more information, see Data Link Errors.)

9. Click OK to close the message. In the Data Link Properties window, click OK. The Data Connection Wizard/Select Database and Table window opens, displaying a list of available queries.

   If no queries display and you are using a version of Excel that does not use PowerQuery, see Troubleshooting and Additional Tasks.

   If you are using a version of Excel that uses PowerQuery, see Queries for a list of available queries and skip the steps below.

10. Ensure the Connect to a specific table box is checked and select the query for which you want to create this connection. Note the following:

    • There is a 1:1 relationship between query and connection; you configure one connection at a time. You cannot create one data connection for multiple queries.

    • You can overwrite an existing .odc file with a new data connection.

11. Click Next. The Data Connection Wizard/Save Data Connection File and Finish opens.

12. Accept or modify the default file and friendly names and description for the new data connection file.

13. Click Finish. The Import Data window displays. (If a message displays prompting you to save the password, click Yes.)

    Existing worksheet is selected by default. The cell where the data will be imported by default is highlighted.

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

15. If you checked the Allow saving password box in Step 7 or you want to add a parameter to the query syntax, click Properties. Otherwise go to Step 20. The Connection Properties window opens.

16. Click the Definition tab.

17. If you use Trillium Authentication and you are saving your password:

    1. Check Save password.

    2. In the Connection string section, add a Password=yourpassword; entry following the entry User ID=yourusername;. If the Password=“” entry already exists, replace “” with your password.

       

18. (Optional) On the Definitions tab, in the Command text field, add one or more parameters to the query syntax. See Adding Parameters to an OLE DB Query.

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

20. To run the query and import data, in the Import Data window click OK. 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 displays, 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 can open multiple query tables in the same worksheet: click an empty cell where you want to insert the data and run the procedure again.

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

• To run a query again, see Running a Query Using an Existing OLE DB Connection.

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