- Ensure Open Data Manager connectivity to Optim archived file or collection
- Create ODBC data source using ODBC Administrator in Windows
- Configure the data source in Cognos
- Make the archive data accessible to report writers using Cognos Framework Manager
- Downloadable resources
- Related topic
Access Optim archive file data using Cognos
Keep reporting while keeping online data growth under control
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.
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
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
This article covers the following configuration steps (illustrated in Figure 2):
- 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.
- 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.
- 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.
- 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
(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
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:
- From the Windows control panel, click on the Administrative Tools > ODBC Data Source Administrator.
- Select the User DSN tab or the System DSN tab, then
click on the Add… button:
Figure 5. ODBC Administrator dialog
- Select Attunity Connect Driver from list, then click on
Figure 6. Select Attunity Connect Driver
- Enter the name of the data source (in this example,
Attunity_Cust-Test), then click on Next:
Figure 7. Name the DSN
- Use the defaults from the binding list, or select NAV, then
click on Next:
Figure 8. Select the defaults, or select NAV
- 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
Figure 9. 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.
- Launch the Cognos Connection Web portal, and select Tools:
Figure 10. Select Tools from Cognos Connection portal
- Select Directory from the drop-down list:
Figure 11. Select Directory
- From the Directory Web page, select the Data Sources tab:
Figure 12. Select the Data Sources tab
- Click on the New Data Source icon, as circled in the Figure 13:
Figure 13. Select New Data Source icon
- 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
- Click on Next, and select ODBC from the Type
Figure 15. Specify ODBC from the drop-down list
- Click on Next, and specify the same ODBC data source name as
you created in the previous section (in this example,
Figure 16. Specify the ODBC data source name
- From the same Configuration page, scroll down, and click on Test
Figure 17. Test the connection
- Click on the Test button:
Figure 18. Test the connection (2)
- As shown in the Figure 19, you should see a successful status
message. Click on Close twice to go back to the Data Source
Figure 19. Connection succeeded message
- Click on Finish to save the configuration:
Figure 20. Click Finish after testing the connection
These steps complete the Cognos data source creation to access the Optim archive file data.
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.
- From the Cognos Connection Web portal, click on Launch,
and select Cognos Administration:
Figure 21. Launch Cognos Administration
- In the Cognos Administration page select the Configuration
Figure 22. Select Configuration tab
(Click here to see the entire image of Figure 22.)
- Click on the New Data Source icon, as circled in the Figure 23:
Figure 23. Select New Data Source icon
- 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
- Click on Next, and select Composite (ODBC) from the
Type drop-down list:
Figure 25. Choose ODBC as the type
- 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
Figure 26. Specify ODBC data source name
- From the same Configuration page, scroll down and click Test the
Figure 27. Test the connection
- Click on the Test button:
Figure 28. Test the connection (2)
- Verify that the connection succeeded, then click on Close twice to go back to the Data Source configuration page.
- 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.
- After you've configured the data source in Cognos Administration, launch the Framework Manager from the Windows Program list.
- Click on Create a new project….
Figure 29. Create a new project in Cognos Framework Manager
- Enter the project name (this example uses COGTEST), and click on
Figure 30. Name the project
- Select the design language for the project, and then click on
Figure 31. Select a language
- From the Metadata Wizard, select Data Sources, and click on
Figure 32. Select data source
- 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
- 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.
- 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
- Select Import to generate the relationships in Cognos:
Figure 35. Import the relationships
- 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
- Select File > Save to save the project details
for Cognos users to create reports:
Figure 37. 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
Once the package gets published, it appears on the Cognos Connection Manager:
Figure 39. 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
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
(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
(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
(Click here to see a larger image of Figure 43.)
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.
- Optim Data Growth Solution: Learn more about Optim Data Growth Solution.