Add external data - IBM Cognos

IBM Cognos Business Insight and IBM Cognos Report Studio allow users to integrate external data, such as spreadsheets, into their reports.

When users import external data using this feature, a package with a new data model merging the data source of the report and the external data is created. Only a link for the local data source is created; the users cannot store the data on the server.

To be successful when creating reports using external data, users need to follow the workflow

Workflow: How to work with external data

Workflow: How to work with external data

Prepare your external data file for import

Advanced business users, professional report authors, and analysts must know their external data (the enterprise data to which they are trying to connect to make their analysis) and the objective of their analysis.

Import your external data

This step depends on the data source. If the user wants to merge external data with a relational data source, the user can link the external data directly with the enterprise data source or to a list report.

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 report .

Maximums: 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 data source file per package. The IBM Cognos modeler can override these governors in IBM Cognos Framework Manager.

Create reports with your external data file

After IBM Cognos Business Insight Advanced creates the package, users can create their reports with the new data source in the same manner as with regular packages. Users can create reports with their data and perform many operations, such as creating crosstabs, lists, and charts, summarizing data, applying sorting, and grouping and adding calculations.

Determine whether to share the reports

After you create a report using external data, you usually save the report in you My Folders folder. If a you want to share the report, the people who are to see the report need to obtain the file that is used by the external data source for their computers and have it located in the same location so that IBM Cognos BI can find the source file. Another option is to place the source file on a shared drive that IBM Cognos BI can access and create the external data source based on that location. With this second method, it is easier to share reports, because you do not have to distribute your file to each person withwhom you want to share a report.

If you want to share a report, you must maintain the report to keep it current.

External Data feature example

Lynn Cope, an Advanced Business User of the Great Outdoors company, needs to create a catalog report with product sizes and quantity available in English and French units, grouped by Product line and Product type.

Lynn has received a spreadsheet with the translation of the units to English and French. She wants to use this information to build her report, because she knows it is not available in the data warehouse. She can easily create this report in IBM Cognos Business Insight Advanced using the External Data feature.

Create the External Data package

Because Lynn wants to merge external data with a dimensional data source, she needs to create a list report with the data that she wants to be available in the external data package.

To create the External Data package:

  1. In IBM Cognos Connection, launch Business Insight Advanced. The Select a package window opens.
  2. Navigate to Cognos Public Folders Samples Models.
  3. Click GO Data Warehouse (analysis) to create a new report based on this dimensional package.
  4. Click Create New to create a new report, click List, and then click OK.
  5. If required, click View Metadata Tree.
  6. In the Insertable Objects pane, drag the following data items to the list From Go Data Warehouse (analysis) Sales and Marketing(analysis) Sales Products Products:
    • Product line
    • Product type
    • Product
  7. From Go Data Warehouse (analysis) Sales and Marketing (analysis) Sales Products Products details:
    • Product key
    • Product number
    • Product size code
    • Product size
  8. From Go Data Warehouse (analysis) Sales and Marketing (analysis) Sales Sales fact:→Quantity
    • Save the report in the My Folders folder with the name Product information.
    • Click the Manage External Data icon.
  9. Simple list report with Product data and Quantity values
    Simple list report with Product data and Quantity values

    • Click Browse and choose the location of the external data. This location can be on the local machine or on a network share. The following extensions are supported:
    • Microsoft Excel (.xls) spreadsheet software files
    • Tab-delimited text (.txt) files
    • Comma-separated value (.csv) files
    • XML (*.xml) files

    Here, users can specify which data they want to include on their reports.

    The users need to specify a namespace to use. The namespace provides a unique name to associate with the data items that the users import. The namespace appears in the data tree of the Source tab in the Insertable Objects window and is used to organize the data items. By default, the namespace is the imported file name without the extension.

    If you change the default name for the namespace, you are prompted to select the external data file each time that you run the report. To avoid this step, select the “Allow the server to automatically load the file” check box.

    Selecting an external data source
    Selecting an external data source

  10. Click Next.
  11. In the Existing report section, click the ellipsis (...).
  12. Browse to the My Folders folder.
  13. Click the Product information report, and then click Open.
  14. In the External data list, click PRODUCT_SIZE_CODE.
  15. In the Existing report list, click Product size code.
  16. Click New Link .
  17. Mapping the external data against an IBM Cognos data source
    Mapping the external data against an IBM Cognos data source

  18. Click Next twice.
  19. Linking columns: Before you create the data mapping, make sure that the columns that will be linked match (for example, product size code).

    Relational data source: When using the External Data feature with a relational data source, users do not need to create a report to link the data. They can link the external data to the relational IBM Cognos package.

  20. In the Existing query subject items section, click Some values exist more than once.
  21. Setting mapping options
    Setting mapping options

  22. Click Finish.
  23. On the Manage External Data window, click the ellipsis (...),
  24. Set the location of the Package to My Folders, and name it: Go Data Warehouse (analysis) External Data with Dimensional
  25. Click Save.
  26. Manage External Data window
    Manage External Data window

  27. Click Publish.
  28. A message displays with information about the new package that will be created

    External data source information message
    External data source information message

  29. Click OK.
  30. After the package is created, the package that is used for the current report is changed, and the new package appears in the Insertable Objects pane

    • This new package consists of two subjects:
    • One subject accesses the external data

    The other subject accesses the dimensional data that is extracted from the Product information report.

    External Data package
    External Data package

  31. On the top toolbar, click the New icon.
  32. Click No to saving the existing report because it is saved already, and then click List.
  33. Drag the following data items from the Insertable Objects pane
    • From Go Data Warehouse (analysis) External Data product_size
  34. →Product information:
    →Product line
    →Product type
    →Product

    Including Product information from the query subject created with the dimensional data
    Including Product information from the query subject created with the dimensional data

  35. Drag the following data items from the Insertable Objects pane
    • From Go Data Warehouse (analysis) External Data product_size product_size
  36. →PRODUCT_SIZE_EN
    →PRODUCT_SIZE_FR

    Including Product size information from the query subject created with the external data
    Including Product size information from the query subject created with the external data

  37. From Go Data Warehouse (analysis) External Data product_size Product information add Quantity
  38. Including Quantity measures from the query subject created with the dimensional data
    Including Quantity measures from the query subject created with the dimensional data

  39. In the crosstab, click Product line, Product type, Product, PRODUCT_SIZE_EN, and PRODUCT_SIZE_FR.
  40. On the top toolbar, click the Group/Ungroup icon
  41. Grouping list columns
    Grouping list columns

  42. In the crosstab, click Product line.
  43. On the top toolbar, click the Sort icon.
  44. Click Edit layout sorting
  45. Edit Layout Sorting option
    Edit Layout Sorting option

  46. Drag each member from the Data items area to the Groups section,
  47. Setting Grouping & Sorting configuration
    Setting Grouping & Sorting configuration

  48. Click OK.
  49. After performing these steps, the report shows the information from both external data and enterprise data sources, ordered by Product line, Product type, and Product.

    Report showing external and enterprise data ordered by Product line, Product type, and Product
    Report showing external and enterprise data ordered by Product line, Product type, and Product


All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd DMCA.com Protection Status

IBM Cognos Topics