Display rows as columns:

SELECT  *
 FROM  (SELECT job,
               sum(decode(deptno,10,sal)) DEPT10,
               sum(decode(deptno,20,sal)) DEPT20,
               sum(decode(deptno,30,sal)) DEPT30,
               sum(decode(deptno,40,sal)) DEPT40
          FROM scott.emp
      GROUP BY job)
ORDER BY 1;

Fmap usage in OBIEE 10g

Create a folder named ' Missing_ ' under OracleBI\oc4j_bi\j2ee\home\applications\analytics\analytics folder

copy the images under this folder

ex:copy images.jpg to OracleBI\oc4j_bi\j2ee\home\applications\analytics\analytics\ Missing_ folder

restart oc4j services.

call the image ex: fmap:\images.jpg

note: it does not support files with JPEG extensions

Cummulative Sum or Running Totals in SQL Oracle

select ename,sum(sal),sum(sum(sal)) over (order by ename)  from emp group by ename;

OBIEE Certification Cost and Syllabus

SYLLABUS:

Exam Cost: Rs 5845
More Info
 http://education.oracle.com/pls/web_prod-plq-dad/db_pages.getpage?page_id=41&p_exam_id=1Z0_526

Data Densification /Data repetition in sql

Data Densification 



Example1:

SELECT deptno,
COUNT(*) DEPT_COUNT
FROM emp
WHERE deptno IN (20, 30)
GROUP BY deptno;

DEPTNO                 DEPT_COUNT             
---------------------- ---------------------- 
20                     5                      
30                     6    

The above query returns departments and their employee count, what if we want to 

repeat the count for all employees of corresponding dept's.

SELECT empno, deptno, 
COUNT(*) OVER (PARTITION BY 
deptno) DEPT_COUNT
FROM emp
WHERE deptno IN (20, 30);

     EMPNO     DEPTNO DEPT_COUNT
---------- ---------- ----------
      7369         20          5
      7566         20          5
      7788         20          5
      7902         20          5
      7876         20          5
      7499         30          6
      7900         30          6
      7844         30          6
      7698         30          6
      7654         30          6
      7521         30          6
 

Example2:

We had a requirement where in even if the data has gaps the report should be continuous , regardless of presence of data.     There are two tables below.                                              

       

Expected Output:

Query:

select Pen_NAme,Time_id,nvl(sum(quantity),0) from temp ym

LEFT OUTER JOIN

Gags co PARTITION BY (co.pen_name)

ON (co.procurement = ym.time_id)

group by Pen_NAme,Time_id;

nqcmd :cache rebuilding to Improve Performance in 10g

Sometimes we want users to access OBIEE reports quickly without delay, in this case we try to populate cache by executing logical queries of the reports.

Step 1: Purge cache : run the following cmd directly from 'Issue Sql Directly' in                                    settings-->Administration O
Call SAPurgeAllCache(); 

Step 2: Take logical queries in settings-->Administration-->Manage Sessions and collate all the required queries in a text file.E


Step 3: Navigate to nqcmd path  \OracleBI\server\Bin in cmd prompt and execute in the below format


NQCMD -d AnalyticsWeb -u Administrator -p Administrator -s C:\Cache.txt

OBIEE 11g Connection pool Problem

After Installation or after deploying a new RPD,if we face any issue in accessing the Database or previewig the data ,the following might be one of the reasons:

The TNS entry containing the DB Instance Deatails should be also present in the TNS entry of the OBEE.The Best method to do this is to jus copy past the whole TNS entry document from DB to the OBIEE.

Hope this helps.......

OBIEE 11g Authentication Problem after deploying new RPD , Refresh GUID

Open the BI Server configuration file NQSConfig.ini file from the following location :

\InstallHome\instances\instance1\config\OracleBIServerComponent\coreapplication_obis1

Set the below tag to NO

FMW_UPDATE_ROLE_AND_USER_REF_GUIDS = NO;

And restart your server.

We usually get this error when we deploy a new rpd, bcoz the id (which is unique for every rpd) is not registered with the BI server.