Contents
1. Forewords
A. Why Handling Large Data Objects In OBIEE:
B. The Challenge:
C. Caveats:
2. Solution Methodology
A. Preamble:
B. Solution Nuts & Bolts
C. Retrieving and rendering large and long running text contents from CLOB:
* Render CLOB From Dimension: -
* Render CLOB from Fact:-
D. Retrieving and rendering image and audio contents from CLOB:
* What is Base-64?
* How to encode in Base-64 and convert a binary file into a string?
* Render Images:-
* Render Audio:-
E. Final Cosmetic Touch Up:
3. Conclusion
This technical white paper revolves around how to retrieve
and render large data objects from database and display them in regular OBIEE
reports and dashboards.
Often user comes with requests of viewing large data items
say for example Resumes, Questionnaires, Credit/Debit Memos, Invoices, images etc.
through OBIEE reports and dashboards. Now the question is whether OBIEE is the
best tool around to view and retrieve large data objects? The matter is debatable. However for online BI
reporting, customers mostly have a single preferred technology. And if that
preferred technology is OBIEE – we often hear questions that can we not view
large data objects in OBIEE reports and dashboards? The answer is YES, we can
do it.
B. The Challenge:
In a straight forward manner, OBIEE does not support CLOB,
BLOB datatypes. Please note that even though CLOB is a valid Oracle datatype,
it is not a valid Oracle Business Intelligence datatype. This isn't really a
bug because support for CLOBs was never there historically. An enhancement
Request (ER) was logged with Oracle Support Bug 5541850: CR 12-G4CK66 /FR 12-YNHGZL
PLEASE ENABLE NEW ORACLE DATATYPES, CLOB, NCLOB, BLOB to address this in future
releases. As per Doc ID 580436.1 - Oracle also suggests as a workaround one
might try to use BI Publisher, which comes integrated with OBIEE, and does
support CLOBs and BLOBs.
However there are few way-arounds available at various
online resources in bits and pieces to show large data items in regular Oracle
BI dashboards and reports. The purpose of the document is to collate the
information at one place and improve & improvise it based upon our day to
day learnings.
C. Caveats:
a This document does not cover the topic of
accessing highly formatted binary files from external file system based data
storage. So here we will restrict our conversation on retrieving large data
objects from database only.
b We have done our study and implementation on
Oracle database only. However the knowledge could be leveraged to extrapolate
the solution for other database vendors.
A.
Preamble:
Till OBIEE10g, as per my knowledge goes, there was only one
way to display long running texts from CLOBs. It was to split the text into
multiple 4000 character columns and then concatenate them during displaying it
on the reports and dashboards. The method was crude and primitive. New OBIEE11g
– allows us to retrieve data with lesser effort with the help of sparse/dense
lookup operators. We could leverage that new welcome feature to display CLOBs.
Now for BLOBs, the truth is – I do not know a way to retrieve BLOB objects in a
meaningful manner in OBIEE11g and it is highly unlikely that it exists (till
version: 11.1.1.7.1). So the only option we have at our hands is to use CLOB in
an improvised way to somewhat cover this limitation.
Often times – We confuse between CLOB & BLOB datatypes in Oracle.
So here goes a one-liner.
CLOB
|
Character Large Objects. CLOB could be some large and
long running text file contents e.g. txt, csv, xml etc.
|
BLOB
|
Binary Large Objects. This is to store binary file
contents like image, audio and highly formatted texts e.g. jpeg, gif, mp3,
pdf etc.
|
N.B. Binary file is a file whose content must be interpreted by a
program that understands in advance exactly how it is formatted.
|
The solution described in this document has two major
sections.
I.
To retrieve and render large and long running
text file contents from CLOB
II.
To retrieve and render binary image and audio
files again from CLOB
B. Solution Nuts & Bolts
In this section, the low level nitty-gritty of the solution
is described in details. From the perspective of OBIEE – CLOB columns has
always been a pain especially for the reason that OBIEE always generates a
‘distinct’, ‘order by’ clauses in all its generated queries. Now while
accessing CLOB column, if the SQL query has ‘distinct’, ’order by’ etc. –
oracle throws an error (ORA-00932:
inconsistent datatypes: expected - got CLOB error). Now in OBIEE11g, it can
segregate some columns from ‘distinct’ or ‘order by’ operations by using the
sparse or dense lookup operators. This feature has been exploited here in the
favor of reporting large texts from CLOB.
To demonstrate this – we shall be considering a case of a toy
Applicant Tracking System. We will have an Applicant dimension and Applicant
Evaluation fact. We have to show applicant’s resume and image from Applicant
dimension. Also we have to show evaluation questionnaire and audio recording of
the interview from the Evaluation fact. This is just a hypothetical case to
demonstrate the capability of showing long running text (Resume &
Questionnaire), image file (applicant’s photo) and audio file (recording of the
interview) in OBIEE11g reports and dashboards.
To create the prototype, two tables - xxlsr_avi_demo_applicant_d and xxlsr_avi_demo_applicant_eval_f
are created. Find below a
simplistic data model for your reference. Resume, EMAIL are two CLOB fields
from applicant dimension and Questionnaire and Interview Audio Rec are two CLOB
fields from evaluation fact.
Simplistic Data Model For Reference
|
|
|
Once the tables are created in the database, they are imported
into RPD physical layer. Following best practice two aliases have been created
for joining. Once aliases are created, proper joins are done between the
objects.
Now we will take a look at how the CLOB columns and see how
they have been imported into RPD.
So we saw that CLOB columns have been imported as
LONGVARCHAR. The length comes as 32768. Point to ponder is that – it is the
maximum length of varchar2 datatype in Pl-sql. However you can adjust the
length as per your need. As CLOB column can store till 4GB – technically we
could go upto 1024*1024*1024*4. However there was never a need for me to go to
that extent. I have gone upto 1500000
bytes and it has worked perfectly fine for me.
First we will concentrate on viewing the Resume of the
applicant. Just to refresh your memory, we have the Resume texts stored as CLOB
text format in Applicant dimension. First we will try to view the Resume text
from the Dimension table itself and experience the issue first hand. And then
we will use sparse or dense lookup operators and will try to understand the
difference. So we brought these two
tables to BMM layer and do the logical join. And then will expose them into
presentation. Make the RPD online and test from OBIEE report.
OBIEE11G – Create Analysis (Answers in popular Siebel
Analytics Legacy Lingo)
Error in Results tab:-
Excerpts from Query Log:-
WITH
SAWITH0 AS (select T642672.FULL_NAME as c1,
T642672.RESUME as c2
from
AVI_DEMO_APPLICANT_D T642672 /* Dim_APPLICANT_DEMO */ )
select D1.c1 as c1, D1.c2 as c2, D1.c3 as c3 from ( select distinct 0 as c1,
D1.c1 as c2,
D1.c2 as c3
from
SAWITH0 D1
order by c2 ) D1 where rownum <= 10000000
---------------------------------------------------------------------------
ORA-00932: inconsistent datatypes: expected - got CLOB
00932. 00000 - "inconsistent
datatypes: expected %s got %s"
As expected it fails. Just to reiterate, while accessing
CLOB column, if the SQL query has ‘distinct’, ’order by’ etc. – oracle throws
an error (ORA-00932: inconsistent datatypes: expected - got CLOB error).
So in regular way, we are facing issues to display CLOB
columns.
§ Render CLOB
From Dimension: - To
avert this problem, we will be using LOOKUP tables and DENSE/SPARSE LOOKUP
operators. So a Logical Table is created with
Dim_Applicant_Demo in its
Logical Table Source. A Primary key is defined. Lookup table check box is
checked. And all the other columns are deleted except primary key column and
CLOB column. And a derived column
resume Text is created.
In the Derived column expression builder – we will use
Dense/sparse lookup operator.
Lookup(DENSE
"LASER-PLAYPIT"."LKP-Dim Applicant Demo- Resume
Text"."Resume ,
"LASER-PLAYPIT"."LKP-Dim Applicant Demo- Resume
Text"."Applicant Id" )
So now this Resume Text is brought into Presentation Layer.
And then in the Answers we select the new Resume
Text. And as per expectation it returns data.
So now we will quickly check query log to understand that
what went right this time.
WITH
OBICOMMON0 AS (select T642672.FULL_NAME as c1,
T642672.APPLICANT_ID as c2,
T642672.RESUME as c3
from
AVI_DEMO_APPLICANT_D T642672 /* Dim_APPLICANT_DEMO */ ),
SAWITH0 AS (select distinct 0 as c1,
D1.c1 as c2,
D1.c2 as c3
from
OBICOMMON0 D1),
SAWITH1 AS (select D1.c1 as c1,
D1.c2 as c2,
D1.c3 as c3
from
(select D1.c1 as c1,
D1.c2 as c2,
D2.c3 as c3,
ROW_NUMBER() OVER (PARTITION BY D1.c2 ORDER BY D1.c2 ASC) as c4
from
SAWITH0 D1 inner join OBICOMMON0 D2 On D1.c3 = D2.c2
) D1
where ( D1.c4 = 1 ) )
select D1.c1 as c1, D1.c2 as c2, D1.c3 as c3 from ( select D1.c1 as c1,
D1.c2 as c2,
D1.c3 as c3
from
SAWITH1 D1
order by c1, c2 ) D1 where rownum <= 10000000
If you check closely – you will find that a small sub-query
has been created without Distinct. Also a small dummy result
set is created and then joined to get the result-set. Little convoluted? Yes
surely. But it works. Moreover this is OBIEE internal and if we now delete the
original “Resume” column from presentation by keeping only “Resume Text”, end
users will be clueless what is happening in side and they will see the result
every time .
NOTE – If you go back to the screen of the report, you will
see that new line characters are not coming in Resume. To ensure new line
characters are properly displayed, following is done in Critera tab and it
solves the issue.
§ Render CLOB
from Fact:- Now
we will check try to do the same on Fact and try to display Questionnaire from
evaluation fact. Initially let try to stick to the same game plan i.e. creating
a Look up table in BMM layer as the fact table on the logical table source and
then to apply Dense/Sparse Lookup operator.
So it did not go as planned. Lookup have to have a primary
key column. And fact does not have a single column primary key – ideally! So
now what could be done on this? The backup plan is to create an alias of fact
in physical and use it as dimension, just to fool OBIEE.
And then let’s bring this new dummy Evaluation fact as a
dimension table in BMM. Now creating the Lookup will be no big deal.
And then we have exposed this Questionnaire Text as a column
in the presentation table Applicant Evaluations. And then select it in Answers and
display it.
Now we will quickly look at the query.
WITH
OBICOMMON0 AS (select T642673.EVALUATION_ID as c1,
T642673.QUESTIONNAIRE as c2
from
AVI_DEMO_APPLICANT_EVAL_F T642673 /* Dim_APPLICANT_EVALUATION_DEMO_DUMMY */ ),
SAWITH0 AS (select distinct 0 as c1,
D1.c1 as c2,
D1.c1 as c3
from
OBICOMMON0 D1),
SAWITH1 AS (select D1.c1 as c1,
D1.c2 as c2,
D1.c3 as c3
from
(select D1.c1 as c1,
D1.c2 as c2,
D2.c2 as c3,
ROW_NUMBER() OVER (PARTITION BY D1.c2 ORDER BY D1.c2 ASC) as c4
from
SAWITH0 D1 inner join OBICOMMON0 D2 On D1.c3 = D2.c1
) D1
where ( D1.c4 = 1 ) )
select D1.c1 as c1, D1.c2 as c2, D1.c3 as c3 from ( select D1.c1 as c1,
D1.c2 as c2,
D1.c3 as c3
from
SAWITH1 D1
order by c1, c2 ) D1 where rownum <= 10000000
So here section (A) finishes as we successfully displayed
texts from CLOB datatype columns from both fact and dimension.
Now we have to retrieve image and audio contents from
database and render them into OBIEE reports and dashboards. I have mentioned
earlier that for binary file contents, BLOB is the best datatype to store when
speaking strictly from the database standpoint. Having said that – I already
confessed that I don’t know a way to retrieve & render BLOB into OBIEE. So
can we do something on this or we have to store these files in external storage
devices accessible from OBIEE server? When I was stumbling over this question –
I came across something called Base-64.
§
What is Base-64? Base64 is a group of similar binary-to-text encoding
schemes that represent binary data in an ASCII string format by translating it
into a radix-64 representation. The term Base64 originates from a specific MIME
content transfer encoding.Base64 encoding schemes are commonly used when there
is a need to encode binary data that need to be stored and transferred over
media that are designed to deal with textual data. This is to ensure that the
data remain intact without modification during transport. Base64 is commonly
used in a number of applications, including email via MIME, and storing complex
data in XML. Based upon this knowledge, the plan is to encode the binary
file into a string by Base-64 and then store the entire string as characters in
CLOB column. Retrieve it in OBIEE. Then decode it in browser while rendering it
as report and dashboards. Sounds good, isn’t it?
§ How to
encode in Base-64 and convert a binary file into a string? Free
Base-64 encoders are available free online. Let’s get hold of one. I got my one
from C-Net. We have to use the exe that suits your OS.
Now, I created a Base64Program directory
directly under C drive, and copied the exe over there. And then created input_dir
folder and output_dir folder under Base64Program folder.
Then I copied all the images and mp3 audio recording files
under input_dir.
Then I ran the exe
from command prompt. First let’s quickly see the help to know the syntax.
§ Render
Images:- Now try to encode two jpeg images with Base64
encoder that we download as we now know the syntax.
Did we succeed? Let’s check real quick.
So far so good. Let’s open one of them to be double sure.
Looks likes it is an unintelligible string of characters. Hopefully
browsers will be able to read.
Now update this value into PICTURE column of xxlsr_avi_demo_applicant_D table in Database.
Note – Do not try to update a column with a string more than
4000 characters long using Sql. Use a Pl-sql block. I am not going into that
topic as I want to strictly stick to the topic of retrieving and rendering the
CLOB values and don’t wish to digress into how to store it. There are ample
online resources that could guide us to do it.
Now as this column is present in dimension table – we will
follow the path by which we displayed RESUME column from dimension. We
described that step by step in one of the earlier section of this document. So
same DESNSE/SPARSE Lookup operators are used in BMM and a derived column Picture
Text is created and exposed through Presentation Layer.
Once done, we will check in the answers to see how it looks
like. It looks like that the entire base64 string is being displayed in the
report.
So now what can be done to see the image from this gibberish
string? W3 schools came into rescue. And syntax will be –
<img
src="data:image/png; ‘Base64 Encoded String’"/>
. Let’s give it a try with Narrative View.
Bingo. It seems like it is working. So till image it is working.
§ Render Audio:- Now comes the real hard one. What about the
audio? Well it sounds hard. Let’s try the same Base64 encryption again and
store the string in database.
And again
while retrieving in browser; let’s try to use Audio tag. Syntax will be -
<audio id="audio_player" src="data:audio/mp3;base64,‘Base64
Encoded String’"/>
Base64 Encryption of Image
|
|
Insertion of Base64 string into Database
|
|
Raw string Display in Answers Report
|
|
Display with Audio Controls in the Answers Report
|
|
Quickly look at the compatibility of Audio tag of HTML.
Point to note – IE8 does not support Audio.
Now we have made sure that independently each of the CLOB
fields are displaying data in the intended format. So finally – time has come
for a cosmetic touch up. We will display the CLOB fields as Pop-up in a
separate report as it is advisable to view CLOB fields one at a time as it
could have 4GB worth of data per cell.
So here is the final report lay outs. Below is the Applicant
Details report with two navigation links on Resume and Image. On
clicking they are opened in a separate popup window.
And here is the Evaluation Details report with two
navigation links on Questionnaire
and Interview Audio Recording.
NB. For the green
arrow icon on navigations, standardly available images are used. And on column
formula primary key column for the respective table is written. In the child
report – primary key of that table is prompted.
This white paper made an attempt to suggest different
possibilities of displaying large data objects through OBIEE standard reports
and dashboards. We demonstrated a way to render long free running texts, image
and audio contents from database by using a prototype Applicant Evaluation
system. It is important to reiterate that Oracle suggests BI publisher to
display such contents and this is just a work around (Doc ID 580436.1). The idea
of this way-around was first suggested by Mark Rittman in his blog and his
mention can be found in Oracle Support site. I tried to improvise the concept
by introducing Base64 encryption technique. Also it needs another mention that highly
formatted binary files can also be stored in file system based external drives
accessible from OBIEE server. In that case as-well it could be rendered in
OBIEE reports and dashboards. If we go in that direction, the overhead of
encryption and decryption will go away. However it is believed that database
provides better security, efficient storage and better backup & recovery
mechanisms etc. The matter is highly debatable. We generally follow a popular
ground rule that says if the average file size is more than 10MB it is better
to keep them in external file system based storage location. And if the average file size is less than 10
MB then we could well consider the idea of storing it in Database. And for
files less than 1MB – storing them in database has a clear edge.
Special Thanks :-
A special thanks to my colleague /
friend Avishek Bhattacharya
for his effort in this PoC, Implementation in the current project.
