Catalog Option Editor

Use catalog options to customize your warehouse catalog data options.

Access the Catalog Option Editor

  1. Open the Workstation window with the Navigation pane in smart mode.
  2. Connect to your environment(s).
  3. In the Navigation pane, click Schemas.
  4. Double-click a project.
  5. Right-click a source in Data Catalog and choose Edit Catalog Options.

Read Settings

Catalog Read Mode: Choose how to read data for data sources that support both SQL and ODBC calls to retrieve data. In most cases, using data source SQL is recommended as compared to using ODBC calls:

  • Data source SQL: This option allows you to customize the SQL to retrieve data for every platform (except Microsoft Access). If you select this option, the default SQL used to retrieve data appears. You can clear the Use default value checkboxes to make manual modifications to this default SQL. You must ensure that the syntax you use is valid for your data source.

  • ODBC Calls: This option is available if you connect to a database type that supports ODBC calls to retrieve the database catalog tables. If you use a Microsoft Access database, ODBC calls are automatically used.

    If you select this option, standard ODBC calls are used to retrieve the database catalog tables and no further customizations can be made. If you select this option and the results are not retrieving the database catalog tables correctly, you can select the Data source SQL option to customize how the database catalog tables are retrieved.

Retrieve namespaces available in the data source: This setting allows you to customize the SQL to retrieve namespaces if they exist. The default SQL for retrieving namespaces displays. Customize the SQL, as needed. To remove your customizations and revert to the default SQL, click Restore Default.

How to retrieve namespaces is defined by the SQL statement:

  • Empty: An empty SQL statement uses ODBC calls to retrieve namespaces. You must ensure ODBC call is supported for the data source.
  • Non-Empty: A SQL statement retrieves namespaces. You must ensure that the syntax used is valid for your data source.

Warehouse catalog table and column list retrieval SQL statement: This setting allows you to customize the SQL to retrieve the tables and columns from your data warehouse. The default SQL display. Customize the SQL, as needed. To remove your customizations and revert to the default SQL, click Restore Default.

Read the table Primary and Foreign Keys: Toggle on this option to display which columns are defined as primary keys or foreign keys in the data source. Primary keys and foreign keys can help facilitate joining tables to create Query Builder reports.

Displaying primary key or foreign key information can also help users designing a project determine which columns of data may be suitable to serve as the identification columns of attributes.

Count the number of rows for all tables when reading the database catalog: Toggle on this option to control whether the Warehouse Catalog should get the number of rows each table has when loading from the data warehouse. This option is helpful when you want to identify fact tables and aggregation tables. If performance is more important than obtaining the row count, do not select this option as it will have a negative effect on performance. By default this option is selected when you open the Warehouse Catalog for the first time.

Ignore current table name space when reading from the database catalog and update using new table name space: Toggle on this option to switch between warehouses found in different database name spaces.

Display a warning if the column data types are modified when updating the table structure: Toggle on this option to be warned when the data type for a column stored in the project is different from the one read from the data warehouse. The check for the data type change is only performed when updating a table's structure. By default this option is selected.

Automatically update information for all Partition Mapping tables when reading the database catalog: Toggle on this option to read the latest information for the partition mapping tables (PMTs) currently present in the project. This setting should be toggled off when the number of values in the project is so large that reading their structure is causing performance problems when opening the Warehouse Catalog. By default this option is selected.

Column Merging Options: When you add a new table to your data warehouse, it may redefine the data type for a column included in the project. For example, your project includes a table named Table1 that has column C1 of data type char(1). Then a new table named Table2 is added to the project, but it has column C1 set to data type char(4). This example is used to illustrate the options described below. When you update the table structure, the column data types are modified to maintain a consistent schema in one of three ways, depending on the option you select.

The options below do not handle the merge if the data type has changed to an incompatible data type. For example, a column is changed from data type char to data type integer. If the data type has changed to an incompatible data type, a warning is displayed and you are asked if you want to use the new data type.

  • Use most recent data type: This option updates the column data type to use the most recent column definition. In the example above, the column data type for C1 would be changed to char(4) since Table2 was added after Table1.

  • Use maximum denominator data type: This option updates the column data type to use the data type with the largest precision or scale. In the example above, the column data type for C1 would be changed to char(4), as defined in Table2. This is because char(4) has a higher precision than char(1) defined in Table1. If the data type has been changed to a different compatible data type, the data type with the largest precision or scale is used, as illustrated in the image below.

  • Do not merge: This option renames the column in the newly added table, which allows the columns to have different data types. From the example above, column C1 uses the char(1) data type for Table1. Column C1 in Table2 is defined as a separate copy of C1 and uses the char(4) data type. This option can cause unwanted schema changes and should be used only when necessary.

View Settings

Automatically define prefixes for all tables that are added to this project: This setting enables/disables the following options:

  • Set a prefix based on the warehouse table name space or owner (import prefix): When this option is selected, the Warehouse Catalog reads the name space for each table being added, creates a prefix having the same text as the name space, and associates it with the table being added.

  • Set a default prefix: Select this option to add a prefix to tables when they are added to a project. This option is only active when the database supports prefixes. You can select the default prefix from the Default prefix box drop-down list or create a new table prefix by clicking Modify prefix list.

Display the number of rows per table Toggle on this option to display the values calculated for the number of rows for the tables. By default, this option is selected and the number of rows are shown.

Automatic Mapping: When you add new tables to the Warehouse Catalog, you can determine whether existing schema objects in the project are mapped to these new tables automatically, using the following options:

  • Map schema objects to new tables automatically: Existing objects in the schema automatically map to tables you add to the project.

  • Do not map schema objects to the new tables: Objects in the schema are not automatically mapped to tables you add to the project.

These automatic mapping methods are only applied to existing schema objects when tables are added to the Warehouse Catalog. For example, the attribute Year with an attribute form mapped to YEAR_ID is included in a project. Then a new table which includes a YEAR_ID column is added to the Warehouse Catalog. With the Map schema objects to new tables automatically option selected, the Year attribute is automatically mapped when the new table is added.

If the table was added to the Warehouse Catalog first and then the attribute was created, the Warehouse Catalog automatic mapping settings do not determine whether the attribute and table are automatically mapped. Automatically mapping tables to schema objects when adding attributes or facts to a project is controlled by the Attribute Editor and Fact Editor, respectively.