Importing Data Sources

This chapter covers the different types of data sources that the Framework Manager can import, and how to import those data sources. This includes the importing of stored procedures, which are an important component of many relational databases. This chapter also includes details on importing OLAP data sources, which do not require modeling but must be imported and then published to make them available to the BI Reporting Studios.

The topics covered in this chapter include:

• Importing metadata from relational data sources
• Using stored procedures as a source of metadata for relational data sources
• Importing from multiple data sources
• Importing metadata from OLAP data sources

By the end of the chapter, you will know how to import data sources into your project.

These imported data sources will form the data layer of your Framework Manager project.

Most Framework projects will start with a relational data source irrespective of whether this is a database, XML, or Excel; they are all considered to be relational data sources. There are also a few exceptions to this, but these tend to be for more specialized data sources that are maintained outside the Cognos environment, such as SAP or Microsoft Analysis Services Cubes.

Importing relational data

To start the import of your relational database metadata into your Framework Manager project, carry out the following steps:

1. Start the Framework Manager, and choose Create a new project:
2. 
3. Give your project a name, choose the location where the project model will be stored, and then click on the OK button.

NOTE

2. IBM Cognos now recommends that all new Framework Manager models use the Dynamic Query Mode for all data sources.
3. At the bottom left of this dialog screen, there is a checkbox labeled Use Dynamic Query Mode. If you want to turn on the Dynamic Query Mode for your model, then check this box; otherwise leave the box unchecked.

TIP

2. It is not necessary to select Dynamic Query Mode at this stage as it may also be selected later when your package is published.
3. Choose your language for the project, and then click on the OK button:
4. 
5. Choose the metadata source for the project and then click on the Next > button:
6. 
7. By default, the Framework Manager will choose Data Sources, which will be highlighted for you. There are also several other sources available:
• Data Sources: This will allow the import of relational data sources. These data sources may be already defined in the Cognos environment, or may be created by the user during the import process.
• IBM Cognos Model: This allows you to import an existing Framework Manager model.
• IBM Cognos Architect and IBM Cognos Impromptu: To import metadata from an IBM Cognos Architect model or from an Impromptu catalog, you must first convert it to XML files using the IBM Cognos Migration Assistant.
• IBM Cognos DecisionStream and IBM Cognos Data Manager: You can use the IBM Cognos Framework Manager to import metadata from an XML file created by IBM Cognos DecisionStream or IBM Cognos Data Manager.
• IBM Metadata Sources: You can use the IBM Cognos Framework Manager to import metadata from IBM data sources such as IBM InfoSphere DataStage.
8. Accept the Data Sources option.
9. You will now be presented with already defined data sources, and a New option to create a new data source:
10. 

Importing from an existing data source

To start importing from an existing data source, perform the following steps:

1. To use an existing data source, select the data source name and click on the Next button.
2. You will now be presented with a tree view of the chosen data source from which the objects can be selected:
3. 
4. Expand the tree view by clicking on the + sign besides the object of your choice:
5. 
6. As you can see, you have the options to select from Tables, Views, Synonyms, Procedures, and Functions.
7. You may also expand the tree even further to see more details of your chosen objects:
8. 
9. Select the objects to be imported by placing a check mark into the check box besides the object.

TIP

2. With a new project only import those database tables that you need initially. If you have several thousand database tables, there is no point in importing them all, if you only use a few.
3. When you have chosen all the objects to import, click on the Next button to import the objects. You will be presented with the options to create relationships between your imported objects:
4. 

TIP

2. When allowing the Metadata Wizard to create relationships, do not check the Use matching query item names box since this can have unexpected results, if all your tables contain columns called CreatedDate or UpdatedDate.
3. When all your selected objects have been imported, you will be presented with a summary of the objects imported, including any joins created between the imported tables:
4. 
5. Click on the Finish button to complete the import process.

Importing from a new data source

To start importing from a new data source, perform the following steps:

1. After selecting New on the data sources selection screen, you will be presented with the New Data Source wizard screen:
2. 

TIP

2. You can also create your new data sources from the Cognos Connection Administrator screens, which uses the same New Data Source wizard.
3. Click on the Next button to continue:
4. 
5. Specify a meaningful name and if desired, a description and screen tip for the data source, then click on the Next button:
6. 
7. From the drop-down box, select the data source type:
8. 

TIP

2. If your data source type is not listed then try ODBC as the data source connection. Most databases will have an ODBC driver available as this is an industry standard.
3. After selecting your data source type, click on the Next button to continue.

TIP

2. If you leave the Configure JDBC connection box checked, then you will need to provide the JDBC information, which will allow you to use Dynamic Query Mode. If you do not want to provide this information or do not intend to use Dynamic Query Mode then uncheck the box.
3. Dynamic Query Mode is an enhanced Java based query engine, originally designed for OLAP processing and now also recommended for relational processing. Further details on Dynamic Query Mode can be found in the IBM Cognos documentation and on the IBM Cognos website.
4. 
5. Fill in all the information required for your data source connection, and click on the Next button to continue.

TIP

2. Always select Test the connection after creating a new connection.
3. 
4. When you have finished creating your data source connection, click on the Close button. You can now continue to import the metadata for your data source.
5. Follow the same steps described in the Importing from an existing data source and Importing from a new data source sections to import the data source objects.

People who finished this also enjoyed: