Use Cognos 8.3 + Oracle 11g for business reporting

Part I: Configuring Cognos to use OpenLDAP Authentication

Environment: Cognos 8.3 on Windows 2003 server
Objective: use external OpenLDAP server on Linux for Cognos web authentication.


Steps:

1    OpenLDAP Installation
Download the latest stable release of OpenLDAP stable release.
./configure
make
make test
make install


2. configuring /usr/local/etc/openldap/slapd.conf

[root@ldap1 scripts]# cat ../slapd.conf| grep -v ^#  | grep -v ^$
include         /usr/local/etc/openldap/schema/core.schema
pidfile         /usr/local/var/run/slapd.pid
argsfile        /usr/local/var/run/slapd.args
include /usr/local/etc/openldap/schema/corba.schema
include /usr/local/etc/openldap/schema/cosine.schema
include /usr/local/etc/openldap/schema/inetorgperson.schema
include /usr/local/etc/openldap/schema/java.schema
include /usr/local/etc/openldap/schema/misc.schema
include /usr/local/etc/openldap/schema/nis.schema
include /usr/local/etc/openldap/schema/openldap.schema

# assume your company name is 'dev'. Firstly create database for your own company
database        bdb
suffix          "dc=dev,dc=com"
rootdn          "cn=root,dc=dev,dc=com"
rootpw          secret
directory       /usr/local/var/openldap-data-dev
index   objectClass     eq

# access control part
access to attr=userPassword
        by self write
        by anonymous auth
        by dn="cn=Manager,dc=dev,dc=com" write
        by * none
access to *
        by dn="cn=Manager,dc=dev,dc=com" write
        by users read
       
database        bdb
suffix          "dc=client1,dc=com"
rootdn          "cn=Manager,dc=client1,dc=com"
rootpw          {SSHA}gFuMY3m3Cb0P4px3TNuf4o7sG30jHcwgi3urEA==
directory       /usr/local/var/openldap-data-client1
index   objectClass     eq

# access control part
access to attr=userPassword
        by self write
        by anonymous auth
        by dn="cn=Manager,dc=client1,dc=com" write
        by * none
access to *
        by dn="cn=Manager,dc=client1,dc=com" write
        by users read



Note:
a. use slappasswd to generate rootpw, the default is to use SSHA encryption.
b. create directory /usr/local/var/openldap-data-dev and /usr/local/var/openldap-data-client1 fist before restarting slapd.
c. for configuring slapd.conf, please refer to http://www.openldap.org/doc/admin24/slapdconfig.html

3. source file to import

[root@ldap1 scripts]# more batchuser.ldif
# entry-id: 1
dn: dc=dev,dc=com
dc: dev
objectClass: top
objectClass: domain

# entry-id: 2
dn: ou=Special Users,dc=dev,dc=com
objectClass: top
objectClass: organizationalUnit
ou: Special Users
description: Special Administrative Accounts

# entry-id: 3
dn: ou=People,dc=dev,dc=com
objectClass: top
objectClass: organizationalunit
ou: People

# entry-id: 4
dn: ou=Groups,dc=dev,dc=com
objectClass: top
objectClass: organizationalunit
ou: Groups

dn: cn=admin,ou=Groups,dc=dev,dc=com
cn: admin
objectClass: top
objectClass: groupofuniquenames
ou: Groups
uniqueMember: uid=jephe,ou=People,dc=dev,dc=com
uniqueMember: uid=zhitan,ou=People,dc=dev,dc=com

# entry-id: 5
dn: uid=zhitan,ou=People,dc=dev,dc=com
uid: zhitan
objectClass: inetorgperson
givenName: Zhitan
sn: Wu 
cn: Zhitan Wu
userPassword: {SSHA}h7HBuirlNhYJl1TwVEtKqJlJVCb53cqm

# entry-id: 6
dn: uid=jephe,ou=People,dc=dev,dc=com
uid: jephe
objectClass: inetorgperson
givenName: Wu
sn: Jephe
cn: Jephe Wu
userPassword: {SSHA}EWo+m4UrXE0yjgefxa4yJ54hz845B3xz

4. commands used

4.1 ldap search everything
ldapsearch -x -b 'dc=dev,dc=com' '(objectclass=*)'

4.2  ldap add user

ldapadd -x -D 'cn=Manager,dc=dev,dc=com' -W -f jephe.ldif
Enter LDAP Password:
adding new entry "uid=jephe,ou=People,dc=dev,dc=com"

root@app1 scripts]# more jephe.ldif
# entry-id: 7
dn: uid=jephe,ou=People,dc=dev,dc=com
uid: jephe
givenName: Wu
objectClass: inetorgperson
sn: Jephe
cn: Jephe Wu
userPassword: {SSHA}hfJXE/3c8zK42rD6FL7mZB6SxG1DA2o+


4.3  ldap delete user
ldapdelete -x -D 'cn=Manager,dc=dev,dc=com' -W uid=jephe,ou=people,dc=dev,dc=com
Enter LDAP Password:

4.4 ldap change password for user
a. ldappasswd -D "cn=manager,dc=dev,dc=com" -x -W -s abcd1235 "uid=jephe,ou=people,dc=dev,dc=com"

b. ldappasswd -D "cn=manager,dc=dev,dc=com" -x -W  -S "uid=jephe,ou=people,dc=dev,dc=com"
New password: (new password for user)
Re-enter new password: (new password for user again)
Enter LDAP Password: manager password
Result: Success (0)

4.5  ldap add group
ldapadd -x -D 'cn=Manager,dc=dev,dc=com' -W -f group1.ldif
Enter LDAP Password:
adding new entry "cn=testgroup,ou=Groups,dc=dev,dc=com"

[root@app1 scripts]# more group1.ldif
dn: cn=testgroup,ou=Groups,dc=dev,dc=com
cn: testgroup
objectClass: top
objectClass: groupofuniquenames
ou: Groups
uniqueMember: uid=jephe,ou=People,dc=dev,dc=com
uniqueMember: uid=zhitan,ou=People,dc=dev,dc=com


4.6 ldap delete group
ldapdelete -x -D 'cn=Manager,dc=dev,dc=com' -W cn=testgroup,ou=Groups,dc=dev,dc=com
Enter LDAP Password:

4.7 ldap modify/delete groupmember

ldapmodify -x -D 'cn=Manager,dc=dev,dc=com' -W -f b
Enter LDAP Password:
modifying entry "cn=admin,ou=Groups,dc=dev,dc=com"

[root@app1 scripts]# more b
dn: cn=admin,ou=Groups,dc=dev,dc=com
changetype: modify
delete: uniquemember
uniquemember: uid=jephe,ou=people,dc=dev,dc=com

4.8 ldap modify/add member. ldapmodify -x -D 'cn=Manager,dc=dev,dc=com' -W -f b
Enter LDAP Password:
modifying entry "cn=admin,ou=Groups,dc=dev,dc=com"

[root@app1 scripts]# more b
dn: cn=admin,ou=Groups,dc=dev,dc=com
changetype: modify
add: uniquemember
uniquemember: uid=jephe,ou=people,dc=dev,dc=com

4.9. how to add user through phpldapadmin
4.9.1 manual way:
a. custom
b. uid=user1
c. ou=people,dc=dev,dc=com
d. inetorgperson
after done, add new attribute- userpasswd , then it will automatically encrypt it using md5 algorithm.
type in password, use mkpasswd.sh to generate

4.9.2 use tools from phpldapadmin
After adding existing users, you can copy the existing user setup to another user, even in different client.

4.10 how to reset password for user
use mkpasswd on linux to generate random password (4 digitals and 4 characters, mkpasswd -l 8 -s 0 )

Part II: install Cognos and configure web server and database

5. configuring Apache on Windows

We use apache server 2.2.2 for Windows.
In httpd.conf configuration, put the following:


options Indexes followsymlinks
allowoverride none
order allow,deny



allowoverride none
options none
order allow,deny
allow from all


in alias module, put
scriptalias /cgi-bin "C:/program files/apache software fouondation/apache 2.2/cgi-bin/"
scriptalias /cognos8/cgi-bin "C:/program files/cognos/c8/cgi-bin"
alias /cognos8 "C:/program files/cognos/c8/webcontent"

You can install cognos web gateway on Linux server also.

6. Configuring browser to use http://ipaddress/cognos8/ (in IE security, make http://ipaddress in the trusted website)

7. setup database for cognos
7.1 MS SQL database
a. go to enterprise manager,go to database, right click, create new one called 'cm'
b. go to security, logins, 'new login' put 'cmuser' as name, password is also 'cmuser', database is 'cm',
database access part, put 'cm' as public and 'db_owner'.

7.2 Oracle database
7.2.1 You have to create a separated instance only for cognos use, it requires AL32UTF8 character set.
can use dbca to create a new instance id 'cognos' with character set AL32UTF8.
After that, create a tablespace 'contentstore' and user 'contentstore' and give user contentstore as dba privileges.
Create tablespace contentstore datafile '/u01/app/oracle/oradata/cognos/contentstore.dbf' size 20m autoextend on next 5m flashback off;
Create user contentstore identified by contentstore default tablespace contentstore temporary tablespace temp;
Grant dba to contentstore;

7.2.2 Configuring Net manager
From start - All programs - Oracle oraClient11g_home1-configuration and Migration tools- net manager
Oracle Net Configuration-local -Servide Naming, add a new one called 'cognos' which has the following information:
Service name: cognos
Connection type: Database default
Protocol: TCP/IP
Hostname: 1.2.3.4 (replace with your oracle server IP)
Port number: 1521

7.2.3 create tablespace and users:
create tablespace cognos datafile '/u01/app/oracle/oradata/cognos.dbf' size 20m autoextend on next 10m flashback off;
create user cognos identified by password default tablespace cognos temporary tablespace temp;
grant connect,resource,create view to cognos;
revoke unlimited tablespace from cognos;
alter user cognos quota unlimited on cognos;

7.2.4 Important - Changing 'cursor sharing' mode in Oracle 11g database used for Cognos reporting
When using Cognos 8 framework manager to fetch tables from Oracle 11g database, by default, it will give you the following errors. It is okay for views.
'RQP-DEF-0177' an error occurred while performing operation sqlbulkfetch status =-9
UDA-SQL-0177 general exception has occurred during the operation fetch.

Solution: In Oracle, change the cursor sharing mode to 'Exact'
Steps:
By default, Oracle 11g use 'SIMILAR' mode for cursor sharing, you need to change it to 'EXACT' mode.

Alter system set cursor_sharing='EXACT' scope = both;

To verify what's the value for cursor sharing, use:

select name, value from gv$parameter where name like '%cursor%'

8    Cognos Installation
8.1    Install Cognos for Windows and Framework manager
Install everything including web content for use for apache later

8.2    Configuring 'Cognos configruaton'
8.2.1    Use Oracle as content store
You have to delete the default Microsoft sql server content store first before you can create Oracle content store
Download the oracle 10g latest JDBC driver class12.jar then put it under c:\Program files\cognos\c8\webapps\p2pd\WEB-INF\lib

Note: Oracle 11g JDBC driver doesn't work with Cognos 8
Now you can configure Data Access - Content Manager- contentstore part as follows:
Database server and port number: Serverip:1521
Userid and password: contentstore/contentstore/contentstore
Service name which is oracle instance sid : cognos
save and start cognos service

8.3    Configuring data source in Cognos
Use browser to access http://localhost/cognos8/, then go to 'cognos connection', go to 'tools' - 'directory' , to create a new data source to use Oracle database 11g.

8.4    Open framework manager to create a new project and data source.
For sql net connection string, use 'cognos' which is defined earlier in tnsname.ora
Remember: you must change Oracle 11g cursor sharing mode to 'EXACT' so that Framework manager can receive table information from database, otherwise, it will give errors. For views, it is okay

9. cognos configuration for LDAP
Authentication: dev
Namespace ID: DEV
Host and port: 10.0.0.18:389
base distinguished Name: dc=dev,dc=com
user lookup: uid=${userID},ou=people,dc=dev,dc=com
Bind user DN and password: cn=Manager,dc=dev,dc=com
Password is the Manager password
Size Limit: 0
Time out in Seconds: 0

note: you also need to disable anonymous access from default 'Cognos' namespace.

10. restart cognos configuration and test it ( http://server/cognos8/)

Part III Cognos user permission and how it works
11. system administrators in cognos namespace itself
You can put your admin group 'admin' in 'dev' openldap database (pointing to dev openldap database for authentication) in the cognos directory 'system administrators'.
After adding to system administrator, you can view all directories under 'public folder' in cognos for reports.

steps below:

1. use IE to login https://url/cognos/
2. go to 'launch' menu, cognos administration
3. go to 'security'-'cognos', the second page, the last second one which is 'system administrators'
4. go to properities, members, add 'admin' group in 'dev' namespace
5. you only need to add 'admin' group to system administrator, no need to add to other cognos directories.


12. how to create user and assign permission for reports
If a user needs to view multiple openldap database/ cognos namespace reports, you should add this user in dev openldap database
instead of individual client namespace.

Then you can assign this user to multiple namespace to view their reports.
steps: login as dev admin group user, go to 'home' and clien properity for each client folder, permission, add the user.

Part IV Cognos reporting
13. create data source connection, use a user which can read all client schemas data ( select any table) or use system user for creating data source connection which can read all schema data
14. modify datasource schema before creating and publishing a new package for another client.
Before publishing a package, you need to change 'schema' part in data source name properities then save so that that published package is only for that schema only. Don't enable data source view when publishing package, choose only 'english'.