Loading relational metadata

After a data server connection is created, you need to load the metadata from the database schemas or catalogs. Only schemas where metadata was loaded can be used to create data modules. The loaded metadata is saved to the content store.

When you load metadata, IBM® Cognos® Analytics examines the data servers for information, such as primary and foreign keys, approximate number of rows in each table, or distinct values in certain columns. Based on this information, data is prepared for use in data modules. For example, relationships between tables are inferred automatically, and intelligent default settings are assigned for Aggregation and Usage properties. This process is also referred to as smart data preparation.

About this task

Loading metadata doesn’t take long for some data server schemas, but it can take a while for schemas with thousands of tables. If the schema contains tables that don’t have any analytical value, exclude them so that no time is wasted retrieving their metadata.

When specifying the load options, you can include a sample of statistical data that is retrieved from the underlying data server. This data is used by the Cognos Analytics AI to do better automation, and make better visualization suggestions.

Tip: The term schema in the Cognos Analytics user interface also represents the term catalog. Both terms denote a logical classification of database objects.

Procedure

  1. Click Manage > Data server connections.
  2. In the Data server connections page, click a data server name.

    The data server connections are displayed. A data server can have multiple connections.

    Tip: Ensure that the connection represents a relational database.
  3. In the Data server connections page, do one of these options:
    • Click a data server name.

      The data server connections are displayed. A data server can have multiple connections.

    • On-Demand only Hover over a data server name, click the Action menu icon Action menu icon, and then select Assets. If the data server has more than one connection, you are prompted to choose the connection for which the assets will be loaded.
    Tip: Ensure that the connection represents a relational database.
  4. Hover over a connection name, click the Action menu icon Action menu icon, and then select Assets.

    The list of database schemas appears.

    The Status column indicates the load status of the schema tables:
    • If tables are loaded, the message Loaded is displayed.
    • If no tables are loaded, the message Not loaded is displayed.

    The Tables loaded column indicates how many tables are loaded. If the schema is not loaded, the Load link is available.

    By default, the list doesn’t include the system and administration schemas for several types of data servers. These types of schemas are not loaded by default. For example, the PUBLIC schema in ORACLE is not displayed. To view the system and administration schemas for a data server connection, select the Load system assets checkbox.

    Tip: The set of system and administration schemas that are not displayed for specific (not all) vendors is defined in the install_location\configuration\moser\import.xml file.
  5. Hover over a schema to see the context menu Context menu icon, and click one of the following options:
    • Load options

      Use these options to specify which load options to select, and which schema tables to load.

      Load options dialog box
      • On the Load options tab, select or clear the following checkboxes (these checkboxes are selected by default):
        Retrieve primary and foreign keys
        Select this checkbox to facilitate automatic detection of relationships between tables.
        Clearing this checkbox reduces the time and memory usage by the system when the data is loaded. However, fewer joins might be created.
        Retrieve sample data
        Select this checkbox to retrieve a statistical sample of data from each selected table.
        By default, 1000 rows of the data per table is retrieved. You can modify this value, and specify up to 10000 rows. Too many rows might have negative impact on the system performance; too few rows might not gather enough information.
        Clearing this checkbox reduces the time and memory usage by the system when the data is loaded, and might be the right choice in some situations.
        For more information, see Data sampling.
      • On the Tables tab, select or deselect the tables to load.

        Use the Exclude tables option to exclude tables that aren’t used in your dashboards or explorations, which reduces the time and memory usage when queries run. You can also exclude tables that cause errors, or those that you cannot access.

        Use the Include tables option to select a fixed set of tables for metadata loading. This option is helpful if you want to refresh the loaded metadata, but do not want to automatically include any newly defined tables.

    • Load metadata.

      This option loads all tables in the schema by using the default load options.

    • Delete metadata

      This option is available only if the schema metadata was loaded before. Use this option to remove the previously loaded metadata from the content store. However, this option should be used with caution because it can break reports, dashboards, or explorations that are based on data modules that use the schema, and delete security filters from the data modules.

Results

When loading is finished, the Status column indicates that the schema is loaded. The Tables loaded column indicates how many tables are loaded.

What to do next

If the schema was loaded for the first time, it can now be used to create data modules. If this is a subsequent reload of the schema metadata, the data in the associated data modules is refreshed.