Table Data Objects
A table data object contains the metadata for a relational database source in the Analyst tool. Use table data objects to analyze source data. When you add a table data object, the Analyst tool uses a database connection to connect to the source database to extract metadata.
You can add table data objects in the Analyst tool by importing the tables into projects or folders. Before you import a table data object, you select or create a database connection and select the database table that you want to add. You can add multiple tables from a connection as data objects. You can also search for a table or table schema when you import a table data object.
Use the New Table wizard to add a table data object to the project or folder. Use the Connections workspace to create a database connection to connect to the source table when you import it as a table data object.
Adding a Table
Use the New Table wizard to add a table data object to a project. Add the table data object that you want to analyze source data for. To add a table data object, select a connection, select the schema and tables, and add the table data object.
1. On the New header, click Table Data Object.
The New Table wizard appears.
2. Select a connection.
3. Click Next.
4. Optionally, unselect Show Default Schema Only to show all schemas associated with the selected connection.
5. Select the table you want to add.
6. Optionally, choose to search for a table by table name or schema name, or by table name and schema name.
- - To search for a table by table name, enter a table name in the Tables search box and click the Find icon to search by table name. Click the Clear icon to display all tables by name.
- - To search for a table by schema name, enter a table schema name in the Schema search box and click the Find icon to search by table schema name. Click the Clear icon to display all schemas by name.
- - To search for a table by table name and schema name, enter a table name in the Tables search box and a schema name in the Schema search box and click the Find icon to display all tables by name in schemas by name. Click the Clear icon to display all schemas by name.
7. Optionally, on the Properties tab, view the properties and column metadata for the table.
8. Optionally, click the Data Preview tab to view the columns and data for the table.
9. Click Next.
10. Select a project or folder in the Folders panel where you want to add the table.
The Tables panel displays that tables that exist in the project or folder.
11. Click Finish.
Rules and Guidelines for Tables
Consider the following rules and guidelines when you work with tables:
- •The Analyst tool displays the first 100 rows by default when you preview the data for a table. The Analyst tool might not display all the data columns in a wide table.
- •The Analyst tool can import wide tables with more than 30 columns to profile data. When you import a wide table, the Analyst tool does not display all the columns in the data preview. The Analyst tool displays the first 30 columns in the data preview. However, you can include all the columns in the wide tables and flat files for profiling.
- •You can import tables and columns with lowercase and mixed-case characters.
- •You can import tables that have special characters in the table or column name. When you import a table that has special characters in the table or column name, the Analyst tool converts the special character to an underscore character in the table or column name. You can use the following special characters in table or column names:
" $ . + - = ~ ` ! % ^ & * ( ) [ ] { } ' \ " ; : / ? , < > \ \ | \t \r \n
- •You can import tables and columns with Microsoft SQL92 or Microsoft SQL99 reserved words such as "concat" into the Analyst tool.
- •You can use an ODBC connection to import Microsoft SQL Server, MySQL, Teradata, and Sybase tables in the Analyst tool. The OBDC connection requires a user name and password.
- •When you use a Microsoft SQL Server connection to access tables in a Microsoft SQL Server database, the Analyst tool does not display the synonyms for the tables.
- •When you preview relational table data from an Oracle, IBM DB2, IBM DB2 for zOS, IBM DB2/iOS, Microsoft SQL Server, and ODBC database, the Analyst tool cannot display the preview if the table, view, schema, synonym, and column names contain mixed case or lower case characters. To preview data in tables that reside in case sensitive databases, set the Support Mixed Case Identifiers attribute to true in the connections for Oracle, IBM DB2, IBM DB2 for zOS, IBM DB2/iOS, Microsoft SQL Server, and ODBC databases in the Developer tool or Administrator tool.
- • You can view comments for the source database table after you import the table into the Analyst tool. To view source table comments, use an additional parameter in the JDBC connection URL used to access metadata from the database. In the Metadata Access String option in the database connection properties, use CatalogOptions=1 or CatalogOptions=3. For example, use the following JDBC connection URL for an Oracle database connection:
Oracle: jdbc:informatica:oracle:// <host_name>:<port>;SID=<database name>;CatalogOptions=1