Saturday, 27 June 2015

Calculated Percentage Column always showing result as 0% Zero Percentage in OBIEE 11g

You may also face similar issue while upgrading OBIEE to a newer version or if you upgrade some of your dashboards from OBIEE 10.1.3.4 to 11.1.1.5. The issue is that, some of the calculations under certain columns in the reports were showing zeros instead of the calculated values.  The requirement was to calculate a match rate % for some data based on another field that showed 'MATCH' or 'NO MATCH'. 
 The real-time scenario was
(SUM(CASE Table1.Column1 WHEN 'MATCH' THEN 1 ELSE 0 END)/COUNT(Table1.Column1))*100

However, this was returning 0% as the result. With the help of some blogs and questions raised on OBIEE threads I came to know that this happens when you have an integer in a calculation. The solution which I used is very simple and was successful in achieving the requirement. The very easy fix to this issue is to multiply the calculation by 1.0.  The updated calculation which works is shown below and provides a meaningful result.
(don't use 1, as this will not work.  Use 1.0)

((1.0*SUM(CASE Table1.Column1 WHEN 'MATCH' THEN 1 ELSE 0 END))/(1.0*COUNT(Table1.Column1)))*100

Saturday, 25 April 2015

Informatica Repository Query to Find All the Source and Target connections in Your Respository

The below Informatica repository query can be used to get the Source Connections, Target connections of all the instances used in the informatica.

Apart from that we have details of the Folder, mapping name ,session name , and the connection name as shown in Informatica.

SELECT DISTINCT 

C.SUBJECT_AREA FOLDER_NAME, 
C.MAPPING_NAME MAPPING_NAME, 
C.SESSION_NAME SESSION_NAME, 
E.READER_WRITER_TYPE, 
E.INSTANCE_NAME, 
E.CNX_NAME, 

CASE 
WHEN E.WIDGET_TYPE = 2 
THEN
'TARGET CONNECTION' 
ELSE 
CASE 
WHEN E.WIDGET_TYPE IN
(1, 3, 56, 45, 55, 84) 
THEN 
'SOURCE CONNECTION' 
ELSE 
NULL 
END 
END CNX_TYPE 

FROM INFA_REPO.REP_VERSION_PROPS A, 
     INFA_REPO.REP_USERS B, 
     INFA_REPO.REP_LOAD_SESSIONS C, 
     INFA_REPO.REP_REPOSIT_INFO D, 
     INFA_REPO.REP_SESS_WIDGET_CNXS E 

WHERE ( A.USER_ID = B.USER_ID 
AND C.SESSION_ID <> D.REPOSITORY_ID 
AND C.SESSION_ID = A.OBJECT_ID 
AND C.SUBJECT_ID = A.SUBJECT_ID 
AND A.OBJECT_TYPE = 68 
AND C.SESSION_ID = E.SESSION_ID 
AND C.SESSION_VERSION_NUMBER = 
E.SESSION_VERSION_NUMBER)

Related Posts Plugin for WordPress, Blogger...

ShareThis