Access Optim archive file data using Cognos

Keep reporting while keeping online data growth under control

IBM® Optim™ Data Growth Solution can help keep data volumes under control so that critical business applications can continue to perform well. It can also help businesses maintain appropriate data for audit purposes at lower cost. Business intelligence users, however, may need access to information that has been archived. Optim Data Growth Solution, through Optim Open Data Manager, provides universal access to that archived data. In this article, follow the necessary steps to set up connectivity to data that has been archived by Optim Data Growth Solution so that Cognos® users can report on that archived data.

Sambasivam (Sam) Sampathnathan (samsampa@us.ibm.com), Technical Product Manager for Optim and Optim Oracle J.D. Edwards Solutions, IBM

Sambasivam (Sam) SampathnathanSam Sampathnathan has worked with Optim technologies for over five years. He worked as an Optim J.D. Edwards integration lead and has successfully implemented Optim and Optim JDE solution for several customers across the globe. Currently, he is responsible for the Optim JDE solutions offering, as well as Optim integration to other IBM products.



21 May 2009

Also available in Chinese Portuguese

Introduction

IBM Optim (along with IBM Data Studio) is the brand associated with IBM integrated data management solutions. Integrated Data Management provides the ability to manage enterprise application data and optimize data-driven applications, from requirements to retirement, across heterogeneous environments.

This article focuses on Optim Data Growth Solution, which provides proven database archiving capabilities, empowering organizations to segregate historical from current data and to store it securely and cost-effectively while maintaining universal access to the data. With Optim Data Growth Solution, you can:

  • Improve performance and availability by avoiding batch process overruns, reducing the amount of data applications have to process, and speed up backup and recovery times
  • Control costs by reducing storage and maintenance cost and speeding up application processing time
  • Mitigate risks by allowing the data you may need for compliance purposes be in a format that cannot be altered and providing independent access to archived data using reporting tools such as Cognos

In this article, learn the steps that are required to configure the Optim solution and Cognos 8 (which includes 8.1, 8.2, and 8.3) so that Cognos report writers can access archived application data (such as Oracle Enterprise Applications, JD Edwards, Siebel, or custom applications). By doing so, there is no need to restore the data into a live database, which provides greater flexibility and saves time and cost on resources to restore the data to a database.

Prerequisites

This article assumes that you have some working knowledge of Optim solutions and have access to Optim and Cognos to perform administrative tasks.

This article assumes you have the following software installed:

  • Optim, Version 6.x or 7.x, including Optim Open Data Manager (ODM), which is required to access compressed archive files for independent access to applications, reporting tools such as Cognos, or both.
  • Cognos 8 (8.1, 8.2, 8.3) on Windows® (installed and configured)

Overview of the solution

Figure 1 illustrates the solution architecture. This articles focuses on the steps needed to configure the connectivity between Cognos and Optim Open Data Manager (ODM) (circled in Figure 1). ODM is required for independent access to Optim's archive files from native applications or reporting tools such as Cognos, and it provides ODBC or JDBC driver connection to Optim archive files.

Figure 1. Architecture of the solution
Diagram of the architecture of the solution using circles, squares, cylinders, and lines

Overview of the configuration steps

The configuration steps are outlined in Figure 2. The key is to define ODBC data sources for Optim archive files or its collections that you want to access using Cognos for reporting needs. The ODBC data sources must be known to both ODM and Cognos. Cognos will connect to ODM using ODBC DSN, then ODM will access the desired archive file or collection.

Figure 2. Process flow of the solution
Diagram of the process flow of the solution using sqares and lines

This article covers the following configuration steps (illustrated in Figure 2):

  1. Create Open Data Manager (ODM) Data Source Name (DSN) for Optim archive files or collections and ensure connectivity. The ODM data source configuration is mandatory for Cognos or for any other reporting tool to access Optim's compressed archive file data.
  2. Create ODBC Data Source using ODBC Administrator on Windows and ensure to select the appropriate ODM DSN during ODBC data source configuration. The ODBC data source creation is required for Cognos to access the archived data using ODM.
  3. Create the data source in Cognos and include the appropriate ODBC data source created in Step 2. For Cognos data source configuration, there are some minor configuration differences between Cognos 8.1/8.2 and 8.3; this article outlines the steps required for both these releases.
  4. With the Cognos data source created in Step 3, access the archived data and create reports using Cognos Framework Manager.

Ensure Open Data Manager connectivity to Optim archived file or collection

This step is a basic check to ensure that you have installed ODM and configured it correctly to access the archived file or collection you want to use in the Cognos report creation. Figure 3 shows a configuration from ODM for Optim archived file and collection.

  • CUST1 is a single archived file of customer data
  • COLL1 is an archived file collection
Figure 3. Sample ODM configuration
Screen capture of a sample ODM configuration

(Click here to see a larger image of Figure 3.)

After successfully configuring the ODM data source(s), use the ODM nav_util executable to test ODM DSN connectivity to the archive file or collection data. The nav_util usually gets installed as part of the ODM installation and can be found under the ODM install directory path. The default location is C:\Program Files\Attunity\Server\tmp. For more details, see the Optim product documentation.

Figure 4. Nav utility shows connectivity to archive file data
Screenshot of Command Line Console using nav utility that shows connectivity to archive file data

Create ODBC data source using ODBC Administrator in Windows

For Cognos access to Optim archive file or its collection data, you must define an ODBC data source in Windows. To do this, you must create an ODBC data source for each of the ODM data sources defined in the previous section. In this case, you have two data sources — one for CUST1, and one for COLL1. Use the following steps to create an ODBC DSN for CUST1:

  1. From the Windows control panel, click on the Administrative Tools > ODBC Data Source Administrator.
  2. Select the User DSN tab or the System DSN tab, then click on the Add… button:
    Figure 5. ODBC Administrator dialog
    Screenshot of the ODBC Administrator dialog
  3. Select Attunity Connect Driver from list, then click on Finish:
    Figure 6. Select Attunity Connect Driver
    Screenshot showing how to select Attunity Connect Driver
  4. Enter the name of the data source (in this example, Attunity_Cust-Test), then click on Next:
    Figure 7. Name the DSN
    Screenshot showing how to name the DSN
  5. Use the defaults from the binding list, or select NAV, then click on Next:
    Figure 8. Select the defaults, or select NAV
    Screenshot showing how to select the defaults or NAV
  6. Select the appropriate data source name by clicking the browse button (looks like an ellipses […]) under the "Default Data Source" selection. For this example, select the CUST1 DSN, which is configured in ODM to access a single archive file data, then click on Finish:
    Figure 9. Choose the data source
    Screenshot showing how to choose the data source

Configure the data source in Cognos

This article assumes that you have installed and configured Cognos correctly.

Cognos 8.1 or 8.2

The steps in this section outline the configuration required to set up a data source in Cognos 8, Version 8.1 or 8.2 to access the archive file using ODM DSN.

  1. Launch the Cognos Connection Web portal, and select Tools:
    Figure 10. Select Tools from Cognos Connection portal
    Screenshot showing how to select Tools from Cognos Connection portal
  2. Select Directory from the drop-down list:
    Figure 11. Select Directory
    Screenshot showing how to select Directory
  3. From the Directory Web page, select the Data Sources tab:
    Figure 12. Select the Data Sources tab
    Screenshot showing how to select the Data Sources tab
  4. Click on the New Data Source icon, as circled in the Figure 13:
    Figure 13. Select New Data Source icon
    Screenshot showing how to select New Data Source icon
  5. In the Name field, specify the data source name that the user will see in Cognos. (This example uses the name "ODM Cust Test".)
    Figure 14. Create a data source
    Screenshot showing how to create a data source
  6. Click on Next, and select ODBC from the Type drop-down list:
    Figure 15. Specify ODBC from the drop-down list
    Screenshot showing how to specify ODBC from the drop-down list
  7. Click on Next, and specify the same ODBC data source name as you created in the previous section (in this example, Attunity-Cust_Test):
    Figure 16. Specify the ODBC data source name
    Screenshot showing how to specify the ODBC data source name
  8. From the same Configuration page, scroll down, and click on Test the connection…:
    Figure 17. Test the connection
    Screenshot showing how to test the connection
  9. Click on the Test button:
    Figure 18. Test the connection (2)
    Screenshot showing how to test the connection
  10. As shown in the Figure 19, you should see a successful status message. Click on Close twice to go back to the Data Source Configuration page:
    Figure 19. Connection succeeded message
    Screenshot showing the connection succeeded message
  11. Click on Finish to save the configuration:
    Figure 20. Click Finish after testing the connection
    Screenshot showing how to click on Finich after testing the connection

These steps complete the Cognos data source creation to access the Optim archive file data.

Cognos 8.3

The steps in this section outline the configuration required to set up a data source in Cognos 8, Version 8.3 to access the archive file using ODM DSN.

  1. From the Cognos Connection Web portal, click on Launch, and select Cognos Administration:
    Figure 21. Launch Cognos Administration
    Screenshot showing how to launch Cognos Administration
  2. In the Cognos Administration page select the Configuration tab:
    Figure 22. Select Configuration tab
    Screenshot showing how to select Configuration tab

    (Click here to see the entire image of Figure 22.)

  3. Click on the New Data Source icon, as circled in the Figure 23:
    Figure 23. Select New Data Source icon
    Screenshot showing how to select New Data Source icon
  4. In the Name field, specify the data source name that the user will see in Cognos. This example uses the name "ODMTEST".
    Figure 24. Name the data source
    Screenshot showing how to name the data source
  5. Click on Next, and select Composite (ODBC) from the Type drop-down list:
    Figure 25. Choose ODBC as the type
    Screenshot showing how to choose ODBC as the type
  6. Click on Next, and specify the same ODBC data source name and connection string as you created in the previous section. In this example, assume that you have created the ODBC data source name as "COGTEST".
    Figure 26. Specify ODBC data source name
    Screenshot showing how to specify ODBC data source name
  7. From the same Configuration page, scroll down and click Test the connection…:
    Figure 27. Test the connection
    Screenshot showing how to test the connection
  8. Click on the Test button:
    Figure 28. Test the connection (2)
    Screenshot showing how to test the connection
  9. Verify that the connection succeeded, then click on Close twice to go back to the Data Source configuration page.
  10. Click on Finish to save the configuration.

This completes data source configuration for Cognos reports to independently access the Optim archive file or collection data.


Make the archive data accessible to report writers using Cognos Framework Manager

In this step, use the Cognos Framework Manager client to access the Optim archive files.

  1. After you've configured the data source in Cognos Administration, launch the Framework Manager from the Windows Program list.
  2. Click on Create a new project….
    Figure 29. Create a new project in Cognos Framework Manager
    Screenshot showing how to create a new project in Cognos Framework Manager
  3. Enter the project name (this example uses COGTEST), and click on OK:
    Figure 30. Name the project
    Screenshot showing how to name the project
  4. Select the design language for the project, and then click on OK:
    Figure 31. Select a language
    Screenshot showing how to select a language
  5. From the Metadata Wizard, select Data Sources, and click on Next:
    Figure 32. Select data source
    Screenshot showing how to select data source
  6. Select the appropriate Cognos data source from the list (in this example, it's ODMTEST), and click on Next:
    Figure 33. Select the data source
    Screenshot showing how to select data source
  7. The Select Objects Wizard lists the schema name, the database owner names, or both that are included in the archive file, along with the objects in the archive file such as tables, view, synonyms, and so on. Expand the tree to view the objects that are included in the archive file.
  8. Select the appropriate database objects that you want to have available for reporting, then click on Next:
    Figure 34. See objects in the archive file
    Screenshot showing how to see objects in the archive file
  9. Select Import to generate the relationships in Cognos:
    Figure 35. Import the relationships
    Screenshot showing how to import the relationships
  10. Click on Finish, which will take you back to your project in the Cognos Framework Manager:
    Figure 36. Back to the project page in Cognos Framework Manager
    Screenshot showing how you're back to the project page in Cognos Framework Manager
  11. Select File > Save to save the project details for Cognos users to create reports:
    Figure 37. Save the project
    Screenshot showing how to save the project

After you save the project, create a package for the project and publish that on the Cognos Connection Manager portal. Using the published package, the Cognos user will be able to create their custom report using Cognos Report Studio, Event Studio, Query Studio, and Analysis Studio.

To create the package, select Actions > Create > Package. Figure 38, below, shows the Package option disabled, as it was taken after creating the package:

Figure 38. Create a package
Screenshot showing how to create a project

Once the package gets published, it appears on the Cognos Connection Manager:

Figure 39. The new package is now available from Cognos Connection Manager
Screenshot showing how the new package is now available from Cognos Connection Manager

Users can then create reports. To do so, select the package and click on Launch. They can choose the appropriate tool from there to create their reports.

Figure 40. Select a reporting tool
Screenshot showing how to select a reporting tool

The report illustrated in Figure 41 is created using the Cognos Report Studio, which can be saved as an XML, HTML, PDF, and more:

Figure 41. Report on archived data using Report Studio
Screenshot showing report on archived data using Report Studio

(Click here to see a larger image of Figure 41.)

Using Cognos Query Studio, the user is able to design a custom report and save it in any of the following supported format: HTML, XML, PDF, Word, Excel, and more.

Figure 42. Design the report
Screenshot showing how to design the report

(Click here to see a larger image of Figure 42.)

Using Cognos Event Studio, the user is also able to create reports and different types of charts for upper management.

Figure 43. Use Event Studio to create charts
Screenshot showing how to use Event Studio to create charts

(Click here to see a larger image of Figure 43.)


Conclusion

The configuration steps described in this article show how an enterprise can efficiently utilize Optim Data Growth Solution for its enterprise data management needs, as well as how Optim Data Growth Solution provides access to archive data for ad-hoc, audit, or e-discovery requests. These steps help you to access archive file data from its compressed file format and provide independent reporting capabilities.

Resources

Learn

Get products and technologies

  • Build your next development project with IBM trial software, available for download directly from developerWorks.

Discuss

  • Optim LUW discussion forum: Share innovative ideas and experiences with others who have implemented any of the solutions in the Optim LUW portfolio.
  • Optim z/OS discussion forum: Share innovative ideas and experiences with others who have implemented any of the solutions in the Optim z/OS portfolio.
  • Participate in developerWorks blogs and get involved in the developerWorks community.

Comments

developerWorks: Sign in

Required fields are indicated with an asterisk (*).


Need an IBM ID?
Forgot your IBM ID?


Forgot your password?
Change your password

By clicking Submit, you agree to the developerWorks terms of use.

 


The first time you sign into developerWorks, a profile is created for you. Information in your profile (your name, country/region, and company name) is displayed to the public and will accompany any content you post, unless you opt to hide your company name. You may update your IBM account at any time.

All information submitted is secure.

Choose your display name



The first time you sign in to developerWorks, a profile is created for you, so you need to choose a display name. Your display name accompanies the content you post on developerWorks.

Please choose a display name between 3-31 characters. Your display name must be unique in the developerWorks community and should not be your email address for privacy reasons.

Required fields are indicated with an asterisk (*).

(Must be between 3 – 31 characters.)

By clicking Submit, you agree to the developerWorks terms of use.

 


All information submitted is secure.

Dig deeper into Big data and analytics on developerWorks


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=Business analytics, Information Management
ArticleID=390902
ArticleTitle=Access Optim archive file data using Cognos
publish-date=05212009