IBM Cognos Proven Practices
Using Netezza as an IBM Cognos 10 Data Source
Product(s): IBM Cognos 10; Area of Interest: Infrastructure
This content is part # of # in the series: IBM Cognos Proven Practices
This content is part of the series:IBM Cognos Proven Practices
Stay tuned for additional content in this series.
The following document provides the information required in order to use Netezza as a data source for IBM Cognos 10 for both Compatible and Dynamic Query modes.
The techniques and product behaviors outlined in this document apply to:
- IBM Cognos Business Intelligence 10.1.0 with updater 10.1.4801.1027 applied
Refer to the conformance information at the following link for a complete and up-to-date listing of supported data sources and their usage:
Exclusions and Exceptions
The techniques and product behaviors outlined in this document may not be applicable to future releases.
Netezza provides a family of data warehouse appliances and software products that allow you to deploy enterprise-scale high-performance data analytics. IBM Cognos 10 supports access to Netezza as a data source in both the Compatible Query Mode and the Dynamic Query Mode.
Configuring Connectivity to Netezza
The Compatible Query Mode uses the Netezza Open Database Connectivity (ODBC) driver to connect, while the Dynamic Query Mode uses the type 4 Java Database Connectivity (JDBC) driver. As with IBM Cognos 8, IBM Cognos 10 requires that the data source client connectivity be installed on all IBM Cognos 10 Framework Manager and Report Server machines that are to perform data access. The following sections will provide the steps required to configure both of these types of connectivity.
Netezza Connectivity for the Compatible Query Mode
The IBM Cognos 10 Compatible Query Mode can use Netezza as a pure relational data source and as a relational data source through dimensionally modeled relational (DMR) packages. Refer to the IBM Cognos 10 Framework Manager User Guide for guidelines on relational and DMR modeling.
Configuring ODBC Connectivity to Netezza
To configure Netezza ODBC connectivity for the Compatible Query Mode in IBM Cognos 10 installed on Microsoft Windows, follow these instructions:
- Ensure the Netezza ODBC driver is installed on the same servers where IBM Cognos 10 is installed.
If the Netezza ODBC driver is not installed, follow the step by step instructions outlined in the Netezza ODBC JDBC Guide (Netezza_odbc_jdbc.guide.pdf) which is provided with the Netezza ODBC install package.
- Launch the ODBC Data Source Administrator window from the Start > Settings > Control Panel > Administrative Tools menu.
- Within the ODBC Data Source Administrator dialog box, switch to the System DSN tab.
- Click the Add button to launch the Create a new data source dialog box.
- From the available driver list, select the NetezzaSQL driver and click the Finish button.
- Within the Netezza ODBC Driver Setup dialog box, select the DSN Options tab and provide the appropriate information for the Data Source, Server, Port, Database, User and Password fields. For this example the following information was entered for these fields.
- Data Source - CorpNetezza
- Server - CorpNetezzaDBServer
- Port - 5480
- Database - GOSALES1
- Username - wagemand
- PWD - MyPassword
Figure 1 Netezza ODBC Driver Setup with values supplied for the required settings under the DSN Options tab
- To test the Netezza ODBC connectivity, click the Test Connection button.
- Once the connectivity tests successfully, click the OK button to complete the Netezza ODBC driver configuration.
Netezza Connectivity for the Dynamic Query Mode
The IBM Cognos 10 Dynamic Query Mode can use Netezza as a relational data source through dimensionally modeled relational packages. The Dynamic Query Mode uses the dimensional layer of the model to provide OLAP query behavior over relational data sources. These DMR models and their respective reports must be newly created within the IBM Cognos 10 environment. Refer to the IBM Cognos 10 Framework Manager User Guide for guidelines on dimensional modeling.
Configuring JDBC Connectivity to Netezza
To configure Netezza JDBC connectivity for the Dynamic Query Mode in IBM Cognos 10 installed on Microsoft Windows, follow these instructions:
- Within the Netezza client install directory, locate and copy the file nzjdbc.jar.
- Within the IBM Cognos 10 install directory, locate the .\v5dataserver\lib directory.
- Copy the nzjdbc.jar file into the .\v5dataserver\lib directory.
- In order for the Netezza driver to be picked up by IBM Cognos 10, stop and restart the IBM Cognos 10 service.
IBM Cognos 10 Framework Manager uses the locally installed Netezza client and ODBC connectivity on the Framework Manager machine to import metadata from the Netezza data source. Once the metadata is imported, you can test against the Netezza data source using either the Compatible Query Mode or the Dynamic Query Mode.
When publishing a package in IBM Cognos BI Framework Manager, you can decide which query mode will be used when running reports against that package. If you wish to switch the query mode for a package, you must republish the package with the desired setting.
Considerations for the Dynamic Query Mode
Packages must only contain supported Dynamic Query Mode data sources to enable Dynamic Query Mode. If unsupported data sources are included in the package, the Dynamic Query Mode option will not be available when publishing the package.
Create a Project, Data Source Connection, and Package for Netezza
This example uses a database called GOSALES1 to illustrate creating a relational data source connection. You can create a data source connection in either IBM Cognos Connection or through IBM Cognos 10 Framework Manager. The example below uses Framework Manager. However, the configuration steps are the same in both environments.
- Open IBM Cognos 10 Framework Manager, and then click Create a new project.
- In the Project name box, type
Netezza DQM Modeland click OK.
The Select Languages dialog box appears.
- Ensure that English is selected and click OK.
The Metadata Wizard appears.
- Select Data Sources and click Next.
- Click the New button to create a new data source connection.
- In the New Data Source wizard, click Next.
- In the Name box, type
GOSALES(Netezza)and click Next.
- Under Type, select Netezza (ODBC) from the dropdown list.
Notice the Configure JDBC connection checkbox. Check this box to ensure that information can be provided to connect through the JDBC driver which is required for Dynamic Query Mode.
Figure 2 New Data Source wizard showing Netezza (ODBC) selected and the Configure JDBC connection checkbox
- Click Next.
In the next steps, the information provided is based on how the Netezza clients on the Framework Manager machine and the IBM Cognos 10 BI servers were configured and how security is implemented for Netezza. Connection information and sign on information should be provided by the database administrator.
- In the ODBC Data source box, type in the ODBC connection name you configured for Netezza, and then under Signon, select the Password check box.
Figure 3 New Data Source wizard with ODBC data source name provided and the Password box selected
- In the User ID box, type in the user ID, in the Password and Confirm password boxes, type in the password, and then click Next.
On the next screen, the JDBC connection information will be provided.
Figure 4 New Data Source wizard showing JDBC driver parameters
- In the Server name box, Port number box, and Database name box, enter the values provided by the database administrator.
- Click Test the connection, and then click Test.
On the results page of the connection test, notice the results showing a status of Succeeded for the Dynamic Query Mode.
Figure 5 Test the connection screen showing successful Dynamic Query Mode test results
- Click Close, click Close again, and then click Finish.
- Click Close.
The new data source appears in the list and is configured to query using either query mode.
Figure 6 Framework Manager Metadata Wizard showing new GOSALES(Netezza) data source
- Select GOSALES(Netezza), click Next, expand the database and schema, and then expand Tables.
- Select the following tables, and then click Next.
- Click Import, and then click Finish.
- In the Project Viewer, expand GOSALES(Netezza).
The query subjects appear as child objects as shown below.
Figure 7 Project Viewer showing the imported query subjects
- As stated earlier in this section, you must create a dimensionally modeled relational (DMR) model in order to leverage the Dynamic Query Mode. Please refer to the IBM Cognos 10 Framework Manager User Guide for guidelines on dimensional modeling.
Once your model is complete, you can create and publish a package that uses either the Compatible Query Mode or the Dynamic Query Mode.
- In the Project Viewer, right-click Packages, point to Create, and then click Create Package.
- In the Name box, type
GOSALES (Netezza), click Next, and then click Finish.
A prompt appears asking if you wish to open the Publish Package wizard.
- Click Yes, deselect Enable model versioning, and then click Next twice.
On the Publish Wizard - Options screen, when only supported data sources are in the package, there is an option called Use Dynamic Query Mode.
Figure 8 Publish Wizard showing the Use Dynamic Query Mode option
If this item is selected, any reports created using this package will use the Dynamic Query Mode instead of Compatible Query Mode. Currently the default is set to use Compatible Query Mode.
- Select Use Dynamic Query Mode if you want to publish a Dynamic Query Mode package.
- Click Publish, and then click Finish.