Provide metadata information for BI connections

The Tosca BI Connection Manager can fetch the required metadata information in one of the following ways:

Directly from the ODBC driver
From SQL statements that you provide

You can choose your preferred option in the Read metadata section of the Connection Manager.

Work with metadata information from the driver

If you select this option, the Connection Manager fetches all required table, column, and constraint information directly from the driver. It requires no additional user input.

Your driver has to be ODBC-compliant and have a valid connection to a database.

Validate driver information

To validate driver information, click Validate.

There are two possible outcomes:

If the validation is successful, the Connection Manager loads the first 50 rows into the Data preview section.
If the validation was not successful, the subsequent dialog indicates that you need to check your validation messages.

Click OK to load the validation messages into the Data preview section.

Errors classified as Fatal indicate that the Connection Manager could not establish a connection to the database. In this case, you cannot save the connection.

Work with SQL statements

SQL statements tell the Tosca BI Wizards how to read the table, column, and constraint information of your database system.

If you select this option, the Connection Manager automatically adds default SQL statements for all data source categories except Custom.

You can validate these statements as they are, or you can adapt them to your needs.

If you selected a Custom data source category, you have to enter your own, custom SQL statements.

Modify SQL statements or add new ones

You can modify SQL statements in the tabs on the right side of the Connection Manager. If you disabled Get uniqueness information and/or Get referential integrity information, the Uniqueness and/or the Foreign keys SQL tab are not available.

To modify default SQL statements, follow the steps below:

Switch to the SQL tab that you want to edit.
Modify the default statement directly in the SQL statement entry field.

SQL entry field in the Tables and column tab

Add new SQL statements

You can add new SQL statements in the following ways:

To overwrite a default SQL statement with a new statement, click Reset SQL and enter your SQL statement.
If you selected the data source category Custom, the SQL statement entry field is empty by default. You can enter your SQL statement immediately. Custom SQL statements require specific information (see chapter "Required information for custom SQL statements").

Validate SQL statements

To validate SQL statements, click Validate.

To validate more than one SQL statement at a time, modify or add all respective statements and click Save connection.

There are two possible outcomes:

If the validation is successful, the Connection Manager loads the first 50 rows into the Data preview section.

Validated SQL statements

If the validation is not successful, the subsequent dialog indicates that you need to check your validation messages.

Click OK to load the validation messages into the Data preview section.

Errors classified as Fatal prevent the Wizard from reading the table altogether. In this case, you cannot save the connection.
Errors classified as Warning indicate issues that do not prevent the Wizard from reading the table, but that might impact data processing. Adjust the SQL statement if needed. You can save the connection as is.