OBIEE – Working with BLOB Image Content

Right … Since I know little bit ! about Oracle Application Express (APEX) (formerly known as HTMLDB from its birth) before I familiar with OBIEE and Siebel Analytics hence I thought of doing some experiment on BLOB(Binary Large Object)  image addition in OBIEE .Unfortunately OBIEE simply doesn’t support BLOB data type content . If you add the BLOB field in Repository and try to view data the column will not be displayed and even not thrown error .

However BI Catalog will generate a objection which is a erratic ORA error. Now try to google around what is the cause and landed to the Famous Blog of Venkat and yeah ! most of the information has been compiled nicely here and got some understanding as I have some idea about how the PL/SQL gateway works and communicate with OPMN process running Apache Web Server .

Still as this is more likely the integration part of OBIEE with APEX that how image extracted by passing the parameters in APEX via PL/SQL gateway .So I do think people need some more idea about APEX . Considering the  integration its worth to take some brief  idea from :

“Defining and Viewing BLOB Data in Oracle Application Express 3.1”

Look on Dimitri’s Blog space also .

OBE(Oracle By Example)  is rocking in providing such kind of very helpful and bootstrapping technical information . Rest of it left to Venkat’s URL . Once I have proper setup in place I will must do a experiment in this area and update 🙂

One more note, APEX is rocking as well with lots of features , enhancement and is a really handy tool for doing database centric web operations .See you next time in a new category area “APEX” 🙂

Javascript in OBIEE to Search a Report and Custom Data Format

How it would be if we can incorporate a Search kind of functionality so that when user put the search string in text box and press Go it will search from the list of reports from inside catalog ?

This is more or less similar kind of functionality that Catalog  Search functionality gives :

Javascript Search Catalog Report1

However there is limitation as this  Search only maps to the name of the Physical file inside the catalog directory and this name might not what end user interested for while searching . So idea is that maintain a logical catalog file name and actual physical catalog file name mapping in a backend table .Moreover end user might not have access to the answers for Searching or they might interested for this features from Dashboard page itself . Then you need to think something different to achieve your goal as one of our client demands the direct Report Search facility from Dashboard .

To achieve this I have imported the default EMP and DEPT table from Scott Schema .Create a table called “REPORT_SEARCH” and insert few rows as below .

Javascript Search Catalog Report2

Create the below simple reports .

Javascript Search Catalog Report3

And place the ‘Emp_Rep’ under ‘Emp’ Dashboard page ,’Dept_Rep’ under ‘Dept’  page , ‘Emp_Sal’ under ‘Sal’ page . And finally the Report Search template under ‘Report Search’ Page .

Javascript Search Catalog Report4

The objective is to input the report names i.e in the database in ‘Report_Name’ column and click on Find (Don’t press enter it will not work) will find the report it and you can click on it to open it .

Lets Edit the dashboard page ‘Report Search’ to see  the components .

Javascript Search Catalog Report5

So its simple text type having HTML Markup code as below .Note that I have added Javascript here to catch the user input and pass this value to URL using the Action Navigation and passing filters as parameters mechanism .(Take the reference of the detail from Bookshelf


<script language=”javascript” type=”text/javascript”>
function foo(f){
var input=f.str.value;
var url=’saw.dll?Dashboard&PortalPath=/shared/Test%20Reports/Report%20Search&Page=Target&Action=Navigate&P0=1&P1=cany&P2=”REPORT_SEARCH”.”REPORT_NAME”&P3=’+input;
<form name=’authentication’ method=’post’>
<font Color=Blue><i><b> Enter the Name of the Report to Search : </font>
<input type=’text’ id=’str’>
<input type=button value=’Find’ onclick=”foo(this.form);”>

Now My Javascript is ready to handle any input string .See the url portion .As per the Portal Path definition I have ‘Report Search’ report saved under “/shared/Test Reports” and the report has been placed under page called “Target” . However the Target page kept hidden .Lets see the target page then :

Javascript Search Catalog Report7

Its a combination of Tabular report and a narrative using the both option open i.e either click on “Go to Report” or Click on the Report name itself . So here is Report search Criteria .Note that the last Report Catalog column is hidden to drive the narratives using “@3” syntax .

Javascript Search Catalog Report8

Javascript Search Catalog Report9

Now see the “Table” view “Custom Text Format ” .This will necessarily take us to the physical location of the report where it actually saved as the catalog column fetched the data from database as “Catalog_Path” and I have converted it to Custom format for ease of navigation .

Javascript Search Catalog Report10

And finally the “Narrative” as below :

Javascript Search Catalog Report11

How hide that “Target” Page and Go to Report Search Page and put the report name say “Emp” and Click on Find (Don’t press Enter ,it will not work as per Jscript code)

Javascript Search Catalog Report6 You should not see below error at the bottom left of your Browser Status bar .If this display it will indicate there must have some problem in your Java script code.

Otherwise enter ‘Emp’ as initial of “Employee” Report Name .Your report list will be filtered out and display as below . Click either on “Go to Report” or “Emp Salary” or “Employee” to redirect to the intended page .Note that since the Action Navigate use  cany as P1 parameter that means it will search for “Contains Any” word match and hence result two reports . Two return only employee reframe your search using “Employee” . Similary do the same for “Department” .

In my case I Search “Salary” and it fires the below query :

select distinct D1.c1 as c1,
D1.c2 as c2,
D1.c2 as c3
(select distinct T248.REPORT_NAME as c1,
where ( T248.REPORT_NAME like concat(concat(‘%’, ‘Salary’), ‘%’) )
) D1
order by c1, c2

Then click on ‘Emp Salary’ or ‘Go to Report’  let me take on the  Employee Salary Page .Thus we achieve the report search functionalities across several steps .However the maintainability is the concern if the report moves from one catalog folder to other then we need to track them down and modify the backend table with the updated link otherwise the report throw error 🙂

Javascript Search Catalog Report13

The anatomy and heritage of OBIEE

Suddenly interested about knowing the History and all the past versions of OBIEE including all its predecessors and major acquisitions :

“These products have a long heritage with Oracle BIEE being based on Siebel Business Analytics which was originally created by nQuire in 1997 . The following major releases of the software have occurred” :

Siebel Analytics 7.0 – 2002
Siebel Analytics 7.5 – 2003
Siebel Analytics 7.7 – 2004
Siebel Analytics 7.8.2 and 7.8.3 – 2005
Siebel Analytics / Oracle Business Intelligence 7.8.4 and 7.8.5 – 2006
OBIEE 10gR3, – Jan 2007 (MAUI)
OBIEE 10gR3, – Apr 2007
OBIEE 10gR3, – Aug 2007
OBIEE 10gR3, – Oct 2007 (Enhanced Localization)
OBIEE 10gR3, – Dec 2007 (Essbase Integration)
OBIEE 10gR3, – May 2007
OBIEE 10gR3, – Aug 2008 (Disco Migration ,Workspace Integration, SmartSpaceIntegration )
OBIEE 10gR3, – Apr 2009

As of today :

The latest shipping versions are:
Future expected version : OBIEE 11g (Negril) – A Major and Mega blockbuster release .
Thanks Darryn for the above inputs .

Oracle 11g 64-Bit on Windows 7 Ultimate 64-Bit

Oracle 11g will still functional and run properly on Windows 7 Ultimate edition version and its pretty cool . Though I have install it in 32 bit machine but this is okay with 64 bit as well . See below from “An Expert’s Guide to Oracle Technology”