Academic and Career Talks

Talk about Academic Life and Career

Codes and Scripts

Articles with Code Snippets, programming, source codes, and useful scripts for development

Computers, Gadgets and Mobile Reviews

Gadget and Mobile Reviews

IBM DB2, Lotus Notes, Cognos

IBM, DB2, Lotus Notes, Cognos

Wordpress, Blogging and Useful Stuffs

Wordpress, Blogging and Useful Stuffs

Home » IBM DB2, Lotus Notes, Cognos

Integrating Lotus Notes Data with Cognos 8 Reports

Submitted by on Saturday, 16 August 20085 Comments
Integrating Lotus Notes Data with Cognos 8 Reports

First a background:One of the reasons why i was exposed to Cognos Reporting Development was when one of the projects i got involved with had some reporting requirements. We used to have the reports published via Intelliprint Reporting Software (owned by Synaptris) in our Domino Databases until the company where i work for decided to purchase Cognos 8 BI Platform. This decision only means one thing. We have to migrate the Intelliprint Reports to Cognos Platform. I got mixed emotions. I thought i would lose my job since i dont know much about Cognos. Fortunately, the company considered my Notes Skills and trained me with Cognos 8 development. Learning a new platform i.e. Cognos 8 is something new to me. Don’t get me wrong though, Intelliprint Reporting Software is an excellent reporting tool which is one-of-a-kind (we used this mostly in our client side reporting). But Cognos offers more than just a reporting solution. It is actually a business intelligence (BI) and performance management software which is diverse, perfect for a distributed computing (for n-tier environment). Cognos offers dozens of other products, mostly they are known for PowerPlay online analytical processing (OLAP) tool, its Impromptu report and query language but i am currently exposed to its Reportnet Environment. In general, Cognos Products allows users to extract corporate data, analyze it, and then assemble reports for analysis, business decision making, scorecarding, dashboards, and event management.

The Dilemma: How to link Lotus Notes Data with Cognos BI Suite?

Ok so here’s the big problem we have to deal with. How do we link Lotus Notes data with Cognos Reports? From my research and experience, there are actually several ways to connect Lotus Notes Data with Cognos.

(1) Option 1. Using NotesSQL via ODBC

– Be sure you have properly installed Lotus Notes Domino and Cognos Server  and NotesSQL on your PC. Depending on your Lotus Notes/Domino version you installed, select the most appropriate version NotesSQL from IBM Website.

1. Setup ODBC Connection
a. Set up your ODBC by going to ODBC on Control Panel > Administrative tools.
b. Create a System ODBC specification .
c. Choose”Lotus NotesSQL driver (*.nsf)” from the list of drivers.
d. Fill in the information depending on the location and name of your Notes application. Specify UserID and Password
2. Setup Cognos Connection
a. Once the ODBC connection is created on the server, launch the Cognos Administration application.
b. Go to Configuration > Data Source Connections and create a new Connection. Specify UserID and Password
c. Fill in the name and description. Click Next and select ODBC as the type of data source.
d. Click Next. On the “ODBC data source” field, type the name of the ODBC connection created on the previous section.

3. Import the metadata to the Framework Manager

a.Open the Framework Manager and create a new Project.
b.Select the recently created connection as data source.
c.Select the form (Tables) that you wish to import. Forms would serve as tables in our model.
d.Once the project is created, proceed to create your analysis structures: Dimensions, measures and hierarchies.

Resources Links ,

(2) Option 2. Using XML (Extensible MarkUp Language) via Lotus Notes Agent (realtime report generation)

This is pretty much straight forward. This is what i do at the moment for my Cognos Reports.

a. First, you prepare a valid XML which can be extracted from Lotus Notes via an Agent which would serve as reference data source for Cognos to use. Test your Lotus Notes Web Agent to work in the browser. Be sure to add dataset xmlns for cognos. i.e.

Print “Content-type: text/xml” ‘prevents Domino from sending default headers
Print |<?xml version=’1.0′ encoding=’utf-8’ ?>|
Print |<dataset xmlns=”http://developer.cognos.com/schemas/xmldata/1/” xmlns:xs=”http://www.w3.org/2001/XMLSchema-instance“>|

sample output can be seen here:

lotusnotesxmlagentviaweb

sample script source code can be found in this forum, feel free to register and participate.

Ensure that you have a valid XML file when you generate GenerateXML?OpenAgent in the browser.

validxmlnotesagent

b. Suppose you have a valid xml file generated by http://servername/yourdatabase.nsf/GenerateXML?OpenAgent , you use this URL Source as part of the connection string (for datasource) in Cognos. Specify this in the following format connection string:

;LOCAL;XML;http://<yourservername>/<yourdatabasename>/GenerateDMSXML?OpenAgent

c. Test the connection by clicking on the “test” button.

d. Now that you have successfully link the XML data to Cognos Reports, it’s time to make a Cognos framework model and publish it. You have to create a query first. For those new to Framework Manager, FM is a metadata modeling utility. A model is a business image of the information coming from one or more data sources. When adding security and multilingual capabilities to this business presentation, one model can be used for the reporting, ad hoc querying, and analysis needs of many groups of users around the globe.

d.1 Creating a Query. Right Click on the Model and choose Query Subject.

create-a-query-in-fm

d.2 Specify Query Subject Name. and Select Datasource. Click Ok.

create-a-query-in-fm-2

d.3 Select the specified Datasource name. For this example, the name of the datasource i specified was DMS_XML . Notice that i also have other database sources coming from other database engines i.e. Oracle 10g, Sybase. I would suggest you create a standard naming convention so that right away you know where the source comes from. Click Next.

create-a-query-in-cognos-framework-manager-3

d.4 Expand the table and Select all the fields. This would serve as the columns for your report. Click Next.

create-a-query-in-cognos-framework-manager-4

d.5 Verify the select statement. When you click on the Test button. you would see the extracted data from the Lotus Notes database.

create-a-query-in-cognos-framework-manager-5

d.6 This is sample output taken from the XML source – Lotus Notes Data.Click on Finish to go back to framework manager.

create-a-query-in-cognos-framework-manager-6

d.7 After successfully making a query. You need to publish your report. Right click on the Package. Publishing wizard window would appear. Click Next.

create-a-query-in-cognos-framework-manager-7

d.8 This would publish the model to the server which would be the basis when creating our report.

create-a-query-in-cognos-framework-manager-8

e. Then you can now create reports. Creating a report involves the following steps:

  • Open your Report Studio
  • Specifying your package
    – > Choose a package by going to cognos connection>report studio>Select a module from the list selected.
  • Choose report template
    – > Create a empty report first,click one of the predefined report templates ,click OK.
  • Adding query items
    – > In the insertable objects plane,select the query item that you want to add to report,drag it to the desired location.
  • Saving report
    – > Save the report from file menu.
  • Running the report
    – > Open the report that you want to run.
    – > From the Tools menu, click Validate Report.
    – > A message box appears indicating whether any errors were found in the report.
    – > If you want to enable Design Mode Only filters defined in the package, from the File menu,
    click Use Model Design Filters.
    – > Use these filters to limit the amount. Open the report that you want to run.
    – > From the Tools menu, click Validate Report.
    – > A message box appears indicating whether any errors were found in the report.
    – > If you want to enable Design Mode Only filters defined in the package, from the File menu,
    – > click Use Model Design Filters.
    – >If you want to set run options, from the Run menu, click Run Options.
    – > From the Run menu, click one of the options to produce the report in the format you want.
    You can actually produce various kinds of report to be displayed in HTML, PDF, CSV, XLS, or XML. So you can choose which best fit your needs.

Option 3:  Data Modeler for Notes as described at sunandson. Honestly, I haven’t tried this yet but it’s a good try.

Collaborative and Business Intelligence for Notes

DataModeler Information

There you have it. I hope you learned something from this tutorial.

Feel free to browse over the board

 

Thank you for visiting Techronnati.com. I hope you enjoy your stay. =)
Close Box