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.
-
Click .
The slide-out panel displays a list of data server connections.
-
In the Data server connections panel, click a data server name.
Tip: Ensure that the connection represents a relational database.
The data server properties panel is displayed. The panel contains 3 tabs:
General, Connections, and
Permissions.
-
Click the Connections tab, and then click the data server connection
name.
The connection properties panel is displayed. The panel contains 4 tabs:
General, Settings, Schemas, and
Permissions.
- Click the Schemas tab.
The list of database schemas is displayed. The check mark in the Status
column indicates that the schema was loaded before. The Load information
column indicates how many tables are loaded. If the schema is not loaded, this information is not
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 Show system schemas 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.
-
From the schema context menu , click one of the following options:
- Load options
Use these options to specify which load options to
select, and which schema tables to load.
- 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.
- Clear 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.
When loading is finished, the Status column indicates that the schema is
loaded. The Load information 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.