Chapter 3. 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...

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. Give your project a name, choose the location where the project model will be stored, and then click on the OK button.

   Note

   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

   It is not necessary to select Dynamic Query Mode at this stage as it may also be selected later when your package is published.

4. Choose your language for the project, and then click on the OK button:

   

5. Choose the metadata source for the project and then click on the Next > button:

   

6. By default, the Framework Manager will choose Data Sources, which...

Importing stored procedures

When you import your metadata from a relational data source, you can also import stored procedures. The Framework Manager only supports the import of user-defined stored procedures. When you have imported your stored procedure, you can treat it as if it were a database table and can create joins to other objects.

You can import your stored procedures as part of the initial import of metadata or at a later time. After importing your stored procedure into the Framework Manager, the procedure must first be run in order to get a description of the result set that the procedure returns.

There are certain rules that imported stored procedures must follow for them to work with the Framework Manager:

• The stored procedure must return a single result set. The Framework Manager only supports the first result set that is returned.

• If the procedure could conditionally return a different result set, then any result set returned should be in the same form, with the same number,...

Importing additional database objects

After completing the initial import of data source objects into your Framework Manager project, it is sometimes necessary to import additional objects into your project.

Importing from the same data source

To import additional tables from your existing data source:

1. Right-click on the namespace where you have already imported your data source:

   

2. Select Create | Query Subject.

   Tip

   You can also choose Run Metadata Wizard to import additional objects from your data source.

   These options are also available from Actions in the menu bar.

   

3. Give your Query Subject a name, and ensure that Data Source (Tables and Columns) is checked.

4. Follow the same steps as in the Importing from an existing data source section.

Importing from additional data sources

It is not necessary for all the objects to be imported from the same database or data source, you may also import objects from many different data sources into the same project.

Note

If you import data from different database sources...

Importing OLAP data sources

Examples of OLAP data sources are IBM Cognos PowerCubes, Microsoft Analysis Services Cubes, SAP BW, and Oracle Essbase. Although they can also act as data sources for Framework Manager, it is not necessary to model them; but it is necessary to create and publish packages for the IBM Cognos reporting tools.

To import your OLAP data source into your Framework Manager project, proceed in the same way as for importing relational data sources, and choose your OLAP data source.

At the end of the import, a summary screen will display telling you that the import process has been completed:

Check the Create a default package box, and click on the Finish button. You can now specify a name for your package and publish it to your Cognos Server in the usual way.

You do not need to do anything more with your OLAP data source; all the modeling of the data has been done in the data source itself, and you are merely providing a mechanism to make the data source available to your report...

Combining OLAP and relational data sources

Although there is no restriction on importing both relational and OLAP data sources into your Framework Manager project, you need to be aware of two things when adding OLAP data sources:

1. The metadata for OLAP sources is checked when the package is opened in one of the BI Studios. If there are a lot of cubes in the package, it will take a long time for the package to open or it may not open at all.

2. If an OLAP source is not available, the package will not open even if the relational sources are available.

Your Report Designers will need to be made aware that there are relational and OLAP data sources available within the published packages since combining data from the different sources may add complexity to the reporting.

After importing your data sources

If you did not import your data sources into the correct layer of your Framework Manager model, then you can drag-and-drop all of you data source objects into the correct layer.

Summary

In this chapter, we have looked into some of the different types of data that can be imported into your project. We have looked into the import of relational data sources since these will be the main data source for many of our projects, and we have also looked into the use of stored procedures as a data source within a relational database. We have briefly mentioned OLAP data sources since they do not require any additional modeling for your project.

To follow the rules established in Chapter 2, Proven Practice, we will have to import our data sources into the data layer of our Framework Manager model.

In the next chapter, we will look at modeling our data sources to create the necessary relationship between objects. We will also look at the rules for cardinality and determinants.