Importing Metadata

You can import metadata into a new project or an existing project. Importing metadata is an operation that can be performed many times to extend the project. You can also export your model to a Common Warehouse Metamodel (CWM) Export Metadata.

Framework Manager can use the metadata and data from external data sources to build a project.

To import metadata, you must indicate which sources you want and where they are located. You can import from only one data source at a time. If you want to import from more than one data source, you must perform multiple imports.

If you want to add a project to a repository, you must set up the repository connection Creating Repository Connections.

You can import metadata from

• relational databases, such as Oracle, DB2, and Microsoft SQL Server

• SAP BW data sources
• Cognos 8 models
• Architect models and Impromptu catalogs
• DecisionStream or Cognos 8 Data Manager models
• third party metadata sources
• XML as a data source

For information about the data source types supported by Cognos, see the Cognos support Web site (http://support.cognos.com).

For information about working with data source connections, see .

When you import metadata, you can select how you want the import to handle duplicate object names. You have the option of not importing the object, or importing and creating a unique name. The advantage of importing everything except these duplicate objects is that you can add new database objects to the project without specifying them individually, and without going through synchronization Synchronize Projects. To import metadata that has the same table names, you must create two namespaces and import each table into a different namespace.

When you import SAP BW metadata, Framework Manager assigns a unique name to each object. Therefore, if you rename an object in the model and then reimport it, Framework Manager recognizes that it already exists. To reimport an object with a different unique name, you can create a new namespace and reimport the object into this namespace.

You can also import objects that have the same name .

Import Metadata from a Relational Database

When you import metadata from a relational database, you can import all the metadata or select particular object types such as tables, columns, views, synonyms, stored procedures, and functions. You can also import system objects from a relational source. System stored procedures are not supported. Framework Manager supports only user-defined stored procedures.

The following table shows the database objects that are mapped to Framework Manager objects.

Steps

1. Click the namespace, folder, or segment that you want to import into and, from the Actions menu, click Run Metadata Wizard.

2. Follow the instructions in the Metadata Wizard:

   • Select a data source connection and click Next.

     If the data source connection you want is not listed, you must first create it .

   • Select the check boxes for the objects you want to import.

   • Specify how the import should handle duplicate object names. Choose either to import and create a unique name, or not to import. If you choose to create a unique name, the imported object appears with a number. For example, you see QuerySubject and QuerySubject1 in your project.

   • If you want to import system objects, select the Show System Objects check box, and then select the system objects that you want to import.

   • Specify the criteria to use to create relationships.

   • If you want to convert all cardinalities to 1, clear the Fact detection enabled check box. Removing n cardinalities disables fact detection and the ability to automatically prevent double-counting. For more information, see Modeling 1-n Relationships as 1-1 Relationships.

3. Click Import.

   Import statistics including a list of objects that could not be imported and a count of objects that were imported are shown.

4. Click Finish.

After importing metadata, you must check the imported metadata for the following areas:

• relationships and cardinality

• determinants

• the Usage property for query items

• the Regular Aggregate property for query items

For more information about checking the metadata, see Verifying Imported Metadata.

Import Metadata from a Cognos 8 Model

You can import metadata from an existing Cognos 8 model.

Note: If you import from another Framework Manager project, expression syntax is not adjusted for each language. For example, you create a Framework Manager project using French as the design language and you use French-specific syntax in calculations and filters. You then create a new project using English as the design language and you import the French project into the new project. Expressions defined in the calculations and filters are not valid. You must manually modify the expression after importing the metadata.

Steps

1. Click the namespace, folder, or segment you want to import into and, from the Actions menu, click Run Metadata Wizard.

2. Click Cognos 8 Model and click Next.

3. Locate the Cognos 8 model (.cpf file) that you want, click Open, and click Next.

4. Follow the instructions in the Import wizard:

   • Select the check boxes for the objects that you want to import.

   • Specify how the import should handle duplicate object names.

     Choose either to import and create a unique name, or not to import. If you choose to create a unique name, the imported object appears with a number. For example, you see QuerySubject and QuerySubject1 in your project.

5. Click Next and click Finish.

Import Metadata from an Architect Model or an Impromptu Catalog

To import metadata from an Architect model or an Impromptu catalog, you must first convert it to XML files. Because of differences between Cognos Series 7 and Cognos 8, after you import the migrated metadata in Framework Manager, additional work is required to test and refine the metadata.

For information about the migration of specific Series 7 objects, see the Cognos 8 Migration and Upgrade User Guide on the Cognos support site (http://support.cognos.com).

Steps

1. Ensure that you exported the Architect model or Impromptu catalog.

2. Click the namespace, folder, or segment you want to import into and, from the Actions menu, click Run Metadata Wizard.

3. Click either Cognos Architect (.xml) or Cognos Impromptu (.xml) and click Next.

4. Locate the Architect or Impromptu XML file that contains the metadata to import.

   A message in the XML Preview window confirms that you chose a valid XML file.

5. Click Open.

6. Select the namespace containing your Series 7 security information.

7. Click Import.

   A list of created objects appears.

8. If you want to verify the imported metadata, click the Verify after import check box.

9. Click Finish.

Import Metadata from DecisionStream or Cognos 8 Data Manager

You can use Framework Manager to import metadata from an XML file created by DecisionStream or Cognos 8 Data Manager. You can import

• a physical layer residing in the Physical Metadata namespace

  This layer contains data source query subjects representing the imported tables. The physical layer contains query subjects and physical relationships between query subjects. These physical relationships are inferred from the relationships defined in the import file. Imported tables become Framework Manager query subjects, and surrogate keys become Framework Manager determinants.

• a dimensional layer residing in the Dimensions namespace

  This layer contains regular dimensions, measure dimensions, and scope relationships. The regular dimensions may be conformed or non-conformed. The measure dimension objects correspond to the imported stars. The scope relationships are inferred from the relationships defined in the import file.

• a logical layer residing in the Business View namespace

  This layer contains shortcuts to the regular and measure dimensions in the Dimension namespace. The shortcuts are organized as star schema groupings, which are namespaces with the same name as the stars from the import file.

The following diagram shows how objects from DecisionStream and Cognos 8 Data Manager are mapped to Framework Manager objects.

Note: A data source connection is not automatically created in Content Manager. You must manually create the connection in Content Manager as explained in the import procedure steps .

The following data source attributes are included in the model.

Steps

1. Click the namespace, folder, or segment that you want to import into and, from the Actions menu, click Run Metadata Wizard.

2. Click Cognos DecisionStream (.xml) or Cognos 8 Data Manager (.xml) and click Next.

3. Locate the XML file that contains the metadata to import.

   A message in the XML Preview window confirms that you chose a valid XML file.

4. Click Open and then click Import.

   A list of created objects appears.

5. If you want to verify the imported metadata, click the Verify after import check box.

6. Click Finish.

7. Create the data source connection in Content Manager. The name you define in Content Manager must be the same as the data source name shown in the Data Sources folder in Framework Manager.

   For information about how to create a data source connection, see .

Import Metadata From a Third Party Metadata Source

You can use Framework Manager to import metadata from a third party source. Metadata is imported using a metadata bridge.

You can import both third party metadata and relational metadata into the same model. We recommend that you start with a new Framework Manager model and import the third party metadata before the relational metadata. This avoids conflicts if you import objects that have the same name.

During a third party import, data sources are created based on information provided through the import wizard. If at least one physical object in the third party source references a database schema or catalog or both, one Framework Manager data source is created with its catalog or schema properties set to the names of the catalog or schema defined in the metadata. A generic data source is created for those physical objects that do not reference a catalog or schema.

If you want to access metadata from a third party data source, you must perform a physical model import.

Not all third party data sources contain metadata that is appropriate for business intelligence reporting and not all third party concepts map to Framework Manager. The metadata import is tailored to Framework Manager and only compatible metadata will be imported.

All metadata bridges deliver a physical layer that provides the basis for further modeling. The richness of the resulting Framework Manager model is directly related to the richness of the metadata source.

Before you can import metadata, there must be a connection to the data source .

Steps to Import from a Third Party Metadata Source

1. Click the namespace, folder, or segment you want to import into, and from the Actions menu, click Run Metadata Wizard.

2. Select Third Party Metadata Sources and click Next.

3. Click the metadata type to import.

4. In the File Name box, browse to locate the file that contains the metadata to import.

   A message in the XML Preview window confirms that you chose a valid XML file.

5. Click Open and then click Next.

6. In the Third Party Specific Import Options dialog box, click the options that you want.

   The options that you see are based on the selected third party data source.

   Note: We recommend that you use the default options. These default options optimize the third party metadata import. If you change the options, you may see unexpected results. To revert back to the default options, click Use Defaults.

7. Click Next.

8. In the Framework Manager Specific Import Options dialog box, click the options that you want, and click Next.

9. Follow the instructions in the Metadata Wizard:

   • Select a data source connection and click Next.

     If the data source connection you want is not listed, you must first create it .

   • Select the check boxes for the objects you want to import.

   • Specify how the import should handle duplicate object names. Choose either to import and create a unique name, or not to import. If you choose to create a unique name, the imported object appears with a number. For example, you see QuerySubject and QuerySubject1 in your project.

   • Specify the criteria to use to create relationships and click Import.

     For more information, see Relationships.

   Import statistics including a list of objects that could not be imported and a count of objects that were imported are shown.

10. Click Finish.

Modeling After a Third Party Import

The source metadata has a logical structure that is compatible with Cognos 8. During the import, this structure is preserved for higher fidelity with the source model.

After the import, only the physical metadata is available.

Do the following:

	analyze and review the model, see Analyze a Model
	set the determinants
	set the Usage property Some bridges require facts to be set manually because this is not available from the metadata source.
	verify the relationships and cardinality
	resolve ambiguous relationships, such as multiple valid relationships, reflexive relationships, and recursive relationships

Troubleshooting Metadata from Third Party Sources

This section describes issues that you can encounter when working with metadata that you have imported into Framework Manager.

Cannot Test a Query Subject from a Third Party Source

You cannot test a query subject that you have imported from a third party data source.

For example, you test a query subject and get this error message:

QE-DEF-0177 An error occurred while performing operation 'sqlPrepareWithOptions' status='-201'.

UDA-SQL-0196 The table or view "GOSALES1.COGNOS.COM.GOSALES.CONVERSION_RATE" was not found in the dictionary.

To resolve this problem, ensure that none of the following conditions exist:

• The Framework Manager data source object created by the third party import, and referenced by the query subject, does not have an identically named corresponding data source in the Cognos 8 Business Intelligence content store.

• The corresponding content store data source exists but is not valid (the connection information is wrong).

• The optional schema or catalog properties of the data source object that the third party import created and the query subject references are incorrect (for the databases accepting case-sensitive identifiers, ensure that the case is also correct).

• The database object (table or view) represented by the query subject no longer exists in the database or it was renamed after the third party model was created, or it is otherwise not accessible within the current connection.

• The Query Type is invalid. This can happen if you import a query subject from SAP BW and change the value of the Query Type property for the data source. By default the value of Query Type is multidimensional. Do not change it.

Relationships Involving Table Views Are Not Imported from an Oracle Designer File

Primary key and foreign key relationships involving at least one table view are not imported from an Oracle Designer file into Framework Manager.

Create the primary key and foreign key relationships manually.

SQL View Definitions Are Not Imported from a Power Designer File

The views in Power Designer must be linked to the table that they reference. Otherwise the information is not saved in the Power Designer file and cannot be imported.

To resolve this problem, see the tooltip for the third party import bridge for Power Designer.

Some Expressions Imported from Third Party Metadata Sources Are Not Valid

Support for expression parsing was improved in the MIMB in Cognos 8. Functions that are equivalent between third party metadata sources and Cognos are parsed. Exceptions to this include

• functions with no mapping between Cognos and the third party metadata source

  For example, a Business Objects expression that is problematic is @select Country) LIKE ’C%’.

• expressions that use a specialized syntax

  For example, DB2 Cube Views use DB2CV references ($$1, $$2, and so on).

Import Metadata Using XML as a Data Source

You can import an XML file as a tabular data source in Framework Manager. You can import it locally or from a remote site through a valid URL. In Framework Manager the XML file is used to model metadata and create a package.

The XML file is validated and parsed at run time, when the query is processed by either Report Studio or Query Studio. If you add the VALIDATE=ON option to the connection string, Framework Manager partially validates the XML file in the <columnList> tag that describes the metadata. For information about supported data types, see XML Data Types.

You must use the xmldata.xsd schema to validate the XML file. The schema is located in the \c8\bin folder. It is not necessary to specify the location of the schema in the XML file itself.

To use XML as a data source, ensure that

• you do not use Native SQL to access data in an XML file

• you do not access Binary Large Objects (BLOB)

• you use only sqlColumns() and sqlTables() metadata calls

  Other calls return an unsupported function error.

• the XML file is well-formed and valid

Before you import XML as a data source, there must be a connection to the data source . If the XML data source is on another computer, you must use an account that has permissions to access the data source.

To use XML as a data source, you must understand XML, schemas, and other XML-related technology.

Before you can import metadata, there must be a connection to the data source .

Steps

1. Click the namespace, folder, or segment that you want to import into, and from the Actions menu, click Run Metadata Wizard.

2. Click the XML data source that you want to import, and click Next.

Import Objects with the Same Name

Namespaces are containers like folders. Objects in a Framework Manager project must be uniquely identifiable. If you have two objects that have the same name, they must reside in two separate namespaces.

For example, you have a database that contains financial data. One set of tables represents Forecast and Actual information. Both the Forecast and Actual information have tables named Accounts Payable and Accounts Receivable. To import these tables into Framework Manager and use the same table names in the project, you must create two namespaces. You can name one namespace Forecast, and the other namespace Actual.

Steps

1. Click the model or root namespace and from the Actions menu and click Create, Namespace.

2. Right-click the namespace, click Rename, and type a descriptive name.