Lotus Domino is a tool widely used
for application development. Many companies use Domino for workflow
applications, tracking systems, collaboration applications, among other
A problem arises when we want to integrate the information stored in these systems with the information contained in our enterprise reporting systems.
In this tutorial, we will show you how to use ODBC to access Domino databases from Cognos BI. With this, you will be able to create reports, drill down and analyze data directly from a Domino database.
- Install and configure the Cognos Server
- Install Lotus Notes Client on the Cognos
- Install Notes SQL on the Cognos Server
Create the ODBC connection
- After all the prerequisites above are met, create the
Go to ODBC on Control Panel > Administrative tools.
It is very important that you define how do you want to access the Notes database. The ODBC tool will treat each form and view as a relational table, where each field shown in the form/view is a table column. You must consider this when selecting which view(s) or form you want to analyze or if you need to create a new view to have all the necessary information.
- Create a System ODBC specification.
- Select "Lotus NotesSQL driver (*.nsf)" from the list
- Fill in the information depending on the location and
name of your Notes application.
Take note of the "Data source name". This will be used as reference when creating the Cognos connection.
Select the user id that will be used to connect to the database. You can also set this id in the Cognos connection configuration.
Make sure the ID has proper access in the notes database ACL.Click OK to save the ODBC connection.
Create the connection from Cognos
- Once the ODBC connection is created on the server,
launch the Cognos Administration application.
- Go to Configuration
> Data Source Connections
and create a new Connection by clicking
- Fill in the name and description. Click Next and
select ODBC as the type of data source.
- Click Next.
On the "ODBC data source" field, type the name
of the ODBC connection created on the previous section.
On Signon, fill in the selected Notes ID for accessing the database.
Test the connection to make sure all works correctly.
If the test was not successful, check your ODBC, make sure you can connect. If you can connect, then check that the selected user can access the application from Lotus Notes.
If the test is successful, click Finish. We are now ready to upload the metadata to the Framework Manager.
Import the metadata to the Framework Manager
- Open the Framework Manager and create a new Project.
- Select the recently created connection as data source.
- Select the view or form (Tables) that you wish to
Do not select all views. If you do, you will get redundant information, since most of them will hold the same data, but displayed with different columns. You must select the view(s) or form that is the most relevant/best structured for your analysis process.
- Once the project is created, proceed to create your
analysis structures: Dimensions, measures and hierarchies. Since Notes
is not a relational database, all the categories will most likely be
contained in the same table(form) or view, but you will be able to
the hierarchies without any trouble.
After completing all these steps, data will work as any relational database source, and will be accessible for any Cognos BI report.