Outer Joined better understanding

Hi Guys,

Below is something which sounds very simple.But understanding it will give you a better understanding of queries

Emp.emp_id = dept.emp_id(+)
dept.abc_id =state.abc_id(+)

the main driving force here is emp here .notice that it has outer join with dept .So in simple terms it means it will return all the values for emp even if
there are no matching values in dept.Notice there are no matching values in dept corresponding to emp_id so all are null then you try to join state based on
abc_id id to dept .Now since abc_id is null there wont be any location picked up .
So here the data is control based on emp. to get data you need to insert data into dept table with corresponding emp_id

So point to remember are when you are joining is outer join allows you to join nulls as if one side is null it cannot find anything matching on other sides
substitutes null. second once you outer join on one table and then you join the non driving table to other table .The driving table controls your data.In
this case driving table is EMP

Issue faced during Cognos Install

Hi Guys,

I reinstalled Cognos and I faced a couple of issue so below is their resolution.Hope you find them handy and you will save time.


Oracle Install 

If you are planning to use oracle as content store.I will tell you this will be the most difficult part of cognos install .80% of issue faced in your install will be related to Oracle.Atleast that has been my experience .But it will take your knowledge about Oracle to Next level .So dont Give up Easily.

Important Note .

I dont know how to uninstall Oracle Server if install goes wrong .Like u might set up wrong Character set .BEWARE.

Please create a System restore point in your Machine and Set the Memory size of your System restore to 3 GB so that it wont get over writen in a month by a New system restore point which gets created automatically by Windows everytime a new update to any software like flash or java is made by windows .or  even a simple windows update.


1) RIGHT CLICK ON MY COMPUTER
2) GO TO PROPERTIES
3)SYSTEM PROTECTION
4) CREATE SYSTEM RESTORE POINT.

if you are fun loving person like me and want to edit your window registries to get rid of oracle dont do this .there is fun in learning this tooo :-P.


Some points to remember .

1)Oracle is by default installed on E drive in Apps folder
2)Oracle does not have a default unistall option so if you messed it up its difficult to remove from your system without proper formating . or registry clean up.So have a system restore point handy
3)You need to install server type database and not desktop type database
4)There is a step called database identifiers where you have to specify the dataset as UTF8 .Remember that.If you miss this,you will have to uninstall Oracle(Painfull .....)
5)Below are the steps for some FOOLS :-P

If you have not created a system restore like me and dont have patience to unistall oracle using commands as there is no direct unistaller .Delete oracle folder and clean up your registry using below method .I would not recommend it .But sometime you might have to resort to it when time is short.
------------------------------------------------------------------------
Start Regedit.exe and navigate to the following branch:

HKEY_LOCAL_MACHINE \ SYSTEM \ CurrentControlSet \ Services

6) You will need to free up temp space once you do this.Otherwise it wont allow you to install Oracle again.Better to create System restore


use disk clean up utility to free up space on your windows 7 .this space might have been used due to bad oracle install.go to start and click on disk clean up to free up space.go to environment variables and remove oracle path also when deleting oracle

7)The oracle ojdc14 file can be in diff folder also .Just search for it files get moved to diff folder depending on version of oracle .so no worries

Ojdbc14 is for Oracle 10g .If you are using Oracle 11g then Ojdbc5

8)Once Oracle is installed succesfully.You will need to create a user called cognos for content store

Below are commands


-------create user cognos identified by cognos

you need to provide all the permission to this user otherwise you will get errors

--------grant all privileges to cognos

9) Please understand what is a Listener and What is tnsnames file .Visit below link in case you get any errors due to Listener .You will be 100% able to solve it .with below link.Not by me

edstevensdba-Explanation on Listener and Tnsnames

10) Some errors that I faced

ORA-12514, TNS:listener does not currently know of service requested in connect descriptor

Preety self explainatory . Listener does not know of service .

Solution 
1)Go to command prompt.Type tnsping ABC (where ABC is your tnsalias.)

Example Below


ABC =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = ADMIN-PC)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = SHARED)
      (SERVICE_NAME =orcl)
    )
  )

Note -tnsping only tells you that the listener is listening on the host and port .It does not tell you anything about the service .It does not even tell you that the service exists .service in above example is orcl.so you need to do below command in command prompt.

lsnrctl status . 

when you do this check what is the service name and what is its status .Is it showing as ready??

unknown status means because you have made entry in listener.ora file its showing that service.it does not have idea if that database instance is running or not.once you make a request then listener will try to find that out. so use only those services which have their status as ready in your cognos connection string for database you will definately make a connection.

Below is how it looks 


Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1521ipc)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.2.7)(PORT=1521)))
Services Summary...
Service "ORCL" has 1 instance(s).
  Instance "ORCL", status UNKNOWN, has 1 handler(s) for this service...


So this was the issue that i had .The listener did not know if the instance was there or not and whether the instance was ready.

I followed below steps to resolve the issue
Loging to system as sysdba and do

-----select * from v$instance

.this will give you the instace name of oracle running.

Please put the same host name , service name shown in this command in both listener.ora and tnsnames.ora . In my files it was differnet and that cost me 2 days to search for the error.If ADMIN-PC IS THERE be sure to put ADMIN-PC as host name in listener and tnsnames.ora file.


11) Try Creating a ODBC connection to content store it will help you check if there is any issue from oracle side or from cognos side.If you able to connect using ODBC the issue of connectivity is from cognos side.

Below are my tnsnames and listner files .Notice the difference in service name .Whatever name you give in listener that service will be shown as unknow because of reason i have explained above.

Below is tnsnames.ora file


ORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = ADMIN-PC)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME =orcl.168.2.7)
    )
  )

-------------------------------------------------------------------------------


Below is listener.ora file



SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = orcl)
      (ORACLE_HOME = E:\app1\ADMIN\product\11.2.0\dbhome_1)
      (PROGRAM = extproc)
      (ENVS = "EXTPROC_DLLS=ONLY:E:\app1\ADMIN\product\11.2.0\dbhome_1\bin\oraclr11.dll")
    )
  )






12)Below command might be helpful


netstat -ao

It will give you which pid is using which port .

so first you need to go to task manager and get teh pid of a service and then you need to go to netstat -ao and the port number that service is using to check if oracle listener is actually using 1521 as a port.

ORA-12520 -TNS:listener could not find available handler for requested type of server

Below is my listener configuration.Notice that when i do lsnrctl status i get status for instance orcl.168.2.7 as unknown ,because its manually hardcoded into my listener file


SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = orcl.168.2.7)
      (ORACLE_HOME = E:\app1\ADMIN\product\11.2.0\dbhome_1)
      (PROGRAM = extproc)
      (ENVS = "EXTPROC_DLLS=ONLY:E:\app1\ADMIN\product

\11.2.0\dbhome_1\bin\oraclr11.dll")
    )
  )

------------------------------------------------------------------------------------------------------------

Java Run time Environment (JRE)

You need java 1.5 or java 1.6 for cognos install .If you are having a new laptop these things might not be there . so please install them first .

Please download JRE -- Java run time environment.You  do not need JDK.that is java development kit.

Right Click on My computer .Go to Properties .then click on Advanced . Set environment Variable path to.

C:\Program Files\Java\jre6\bin

------------------------------------------------------------------------------------------------------------

Installing Apache Web Server 

Do not get confused with apache and apache tomcat . both are different .We need Apache and not apache tomcat . 

please download apache zip file with .msi extension for windows .most apache that are availabel will be for unix . so cant run it on windows . windows will have a single install file available to install apache.

Go to config folder , in http.conf file .Copy paste the below

ScriptAlias /cognos10/cgi-bin "C:\Program Files\Cognos_Server\ibm\cognos\c10\cgi-bin"

<Directory "C:\Program Files\Cognos_Server\ibm\cognos\c10\cgi-bin">

    Options FollowSymLinks

    AllowOverride FileInfo

    Order Allow,Deny

    Allow from All

</Directory>

Alias /cognos10 "C:\Program Files\Cognos_Server\ibm\cognos\c10\webcontent"

<Directory "C:\Program Files\Cognos_Server\ibm\cognos\c10\webcontent">

    Options FollowSymLinks

    AllowOverride FileInfo

    Order Allow,Deny

    Allow from All

</Directory>

------------------------------------------------------------------------------------------------------------

Firewall setting .

Please have a look ,Just in case it was not automatically set up during Cognos server install.

Applying security in Cognos framework manager

Below are types of security in framework

● data security (p. 257)
You create a security filter and apply it to a specific query subject. The filter controls the data
that is shown to your users when they set up their reports.
● object security (p. 259)
You secure an object directly by allowing users access to the object, denying users access to the object, or keeping it hidden from all users. In case of Object level security user will still see the object , but cant access it
● package security (p. 261)
You apply security to a package and identify who has access to that package.

Setting up data level security for product line

1) I created 2 groups Mountain and Camping in cognos administration
2) Put both those groups under Author role
3)Put user A under Mountain and B under Camping group.
4) Click on product dimension and click on security filter
5) add filter like below
[Dimensional view].[Products].[Products].[Product line]  =[Dimensional view].[Products].[Products].[Product line]->[Products].[991]

----- product 991 is your camping equipment.I have dragged it from members
6)Publish the model
7)Create a simple list report by draging product line from product dimension
8)Login with User A and run the report
9)Login with User B and run the report




Object Level security

1) Specified by using Action menu.
2) The object still will be shown but user will not have access




User Permission in Cognos

1)If you have a folder and in that you have your reports saved.You need transverse permission to see the reports and go inside the folder.So transverse is must.
2)Execute is must if you want to execute the report
3)Read is must if you want to open the report in report studio4)
4)set policy is must if you want to give permissions .Without set policy you can see the security tab
5)Write is required if you want to delete a report

Roles in Cognos

The one idea that is to be kept in mind is role have predefined permissions which are not shown in capabilities.Like consumers have read permission to cognos report studio.


These roles already have capabilities defined. For list of initial roles and what are their capabilities refer cognos administration guide

Predefined Entries - page 298

If a user wants access to query studio ,Add the user as member of query user role
If a user wants access to Analysis studio ,Add the user as member of Analysis user role
If a user wants access to report studio ,Add the user as member of Author user role


Note -when a person is added as author they can only execute and tranvese report cant create them.Initially only report administrators have capability to create reports.

Scenario 2 

We want to show as below

Users from each branch are allowed to see the sales of their own branch and the aggregated total sales accross all branches - ``Branch(ALL)'' as Cognos calls it.
If we put a security filter on the Branch dimension or the Sales measure then we filter out all the data, so the aggregate value will only show one branch.
(At least that's what's gonna happen with a DMR model.)
What should we do? 

Got this question from below link 

http://unofficialcognostraining.blogspot.in/2008/02/data-level-security-filter-challenge.html

I am taking the example of product line and revenue .