,

Managing External Data in Cognos Workspace Advanced

/by

IBM Cognos Workspace Advanced and IBM Cognos Report Studio allow you to integrate external data directly into your reports. This allows you to create a report using an external source that contains data which is not part of your IBM Cognos Business Intelligence enterprise data. You can import your own data file and start reporting on it right away. After importing, your external data file is protected by the same IBM Cognos security as your enterprise data, thus allowing you to report on your data in a secure and private environment.

External Data can be utilized when in one of following formats:

– Excel (any version for which Cognos supports report output)

– Text File (tab-delimited)

– CSV (text file, comma-separated)

– XML (the XML must have specific formatting)

To be successful when creating reports using external data, users need to follow the four steps shown in the workflow below:

Workflow

External Data in Packages

When you import external data into a package, a new package is created. The new package includes: the original package, new external data, and any links or relationships that you defined between the two. By default, the newly created package is saved in the “My Folders” area of IBM Cognos Connection and the suffix “External Data” is appended to the original package name.

Note: You can change where your package is published.

Running a Report That Contains External Data

Reports that contain external data run the same way as reports that contain only enterprise data. If you have access to the report, you will also have access to the external data included within the report. You can determine whether data in a report uses external data by tracing its lineage. For more information, see View Lineage Information for a Data Item.

Making your Reports Public

After you create a report that uses your external data, you may want to make it public to share it with coworkers. They can run your report using your external data file that you made available on a shared drive that the Cognos server can access, or use their own version of the file. If they use their own version, the file must contain the same columns as your original external data file that you used to import the data and create the report. In addition, you must clear the Allow server to automatically load file check box in the Select Data page of the Manage External Data wizard.

Note: To make reports public, you must save them in the Public Folders area of the IBM Cognos portal.

Restrictions to Adding External Data to Reporting

  • The maximum file size that a user can import is 2.5 MB, with a maximum of 20,000 rows. A user can import a maximum of one external datasource file per package.

Note: The IBM Cognos modeler can override these governors in IBM Cognos Framework Manager.

  • External files must be maintained in order to keep current data for reporting and mapping. Data formatting must match Data formats in your enterprise data that you are mapping to.

  • If the user wants to merge external data with a dimensional data source, the user must create a list report and link the external data source to the content of the list.

  • Before you can import your own external data file, your IBM Cognos administrator must grant you permission for the Allow External Data capability found within the Report Studio capability, and you must have permission to use IBM Cognos Report Studio or IBM Cognos Workspace Advanced.

  • XML has specific formatting that can be reviewed in the IBM Cognos Workspace Advanced Manual.

Using External Data in a Report:

  1. Open Cognos Workspace Advanced, using the GO Data Warehouse (Query) package

  2. Select the List template

  3. Expand the Promotion (Query) and drag Product Line, Product Type and Product to the Report from the products query subject

  4. Click on Manage External Data button in the Insertable Objects pane

Manage External Data

5). Select Data

  • In the External Data file window, click the Browse…button

  • Navigate to: C:Program FilesIBMcognosc10webcontentsamplesdatasourcesother

  • Select Promotions.xls

  • Select Data Items for the report from the external report

Data Items

 

6). Select Data Mapping

  • Map the Data field from the External Data to the appropriate Data Item in the Existing Query Subject

 Data Mapping

7). Select Data Attributes

  • Assign Data types and decimal places (if required) to the fields in the external data source items

 Data types

8). Select Mapping Options

  • Determine if values in data source are unique and whether to exclude rows with missing values

 Mapping options

 

9). Select Finish and Publish the Package

Note: The Package name now has External Data after the Original Package chosen

 Publish

10). Add Product Number and Promotion Name from the External Datasource (promotions.xls) to the Report as seen below:

External Data Source

 

External Data within the Organization

With the introduction of external data come some obvious concerns that a BI organization needs to account for.

They are in effect bypassing their enterprise data warehouse practices to complete their reporting needs.

A few concerns are noted below:

  • Cleanliness and validity of the data is probably the paramount concern. That is: do we trust that an end user is capable of validating data themselves.

  • Security around these external data sources is another concern that always needs to be considered. What data are people using and who are they sharing it with?

  • How do you keep track and maintain the most recent external data source file?