Universe Objects on CLOBs/BLOBs
database columns. and accessing them in web intelligence reports. The content in this article is intended for semantic layer users and designers those who would wish to bring the CLOB or BLOB data type columns from databases as universe objects and provide them for users and developers access via web Intelligence, Explorer etc.
Large Objects and Types Overview
Below are three types of large objects that some of the relational databases support for storing large texts, images, voice, and graphical types of data.
CLOB(s) - Character Large Object(s): Large objects stored as single byte characters with an associated code page and it is mostly used for storing text information where amount of information can go beyond limits of VARCHAR type.
BLOB(s) - Binary Large Object(s): Large objects stored as binary string with no associated code page and mostly used to store images, voice, graphical stuff, and other types of application data.
DBCLOB(s) - Double-Byte Character Large Objects: Large objects stored as string made up of double-byte characters with an associated code page.
Web Intelligence Support Observations:
Based on the tests performed on the universe while trying to create objects on below, I have arrived on the following observations.
DBCLOB(s) are not a supported data type in the universe, so this type of database columns cannot be carried over as universe objects to web intelligence for standard or ad-hoc reporting.
Coming to BLOB(s), these are partially supported i.e. BLOBs that store large text can be carried over to web intelligence for standard or ad-hoc reporting, whereas BLOBs that store images, voice, graphical stuff, and other types of content are not supported.
Note: As a work around, to deal with unsupported BLOB columns that store images, voice, graphical stuff etc., content stored in the BLOB column can be moved to a virtual directory or so and the links to them are to be stored in the database column, so that they can be represented as hyperlinks from Web Intelligence to access the content.
CLOB(s) are fully supported and can be carried over to web intelligence for standard or ad-hoc reporting
Create Universe Objects on CLOB(s) and Supported BLOB(s)
1. Right Click on Class/Folder and Choose New Dimension object in Information Design Tool(IDT).
2. Enter the Table.Column SQL for the CLOB or BLOB and Assign the data type as "Long text".
3. Validate the SQL syntax and Click on Show values button to see if the values are being populated.
4.Designer would encounter with the error messages as below
ORA-00932: inconsistent data types: expected - get CLOB
ORA-00932: inconsistent data types: expected - get BLOB
5. To avoid this error message large text needs to be explicitly extracted using Dbms_lob.substr function as below. So update the SQL in step 2 reflect as below
Dbms_lob.substr(Table.CLOB_Column , number of characters, start position)
6. Validate the syntax of the object and Click on Show Values Button to see if the object is populating LOV. If its populating LOV, then CLOB/BLOB object creation in the universe is successful and designer can start using it from web intelligence after the universe is published. But in some cases, designer will encounter the below error message
ORA-06502: PL/SQL: numeric or value error: character string buffer too small ORA-06512: at line 1
7. In order to resolve the above issue, designer has to play around with “number of characters” value within Dbms_lob.substr function by reducing the value by multiples of fifties or hundreds until the error goes off and LOV is populated.
8. Publish the universe to repository to start using the CLOB(s) and BLOB(s) created at universe layer in web Intelligence
Note: Above mentioned steps refer to creating objects in IDT on UNX universe and similar steps can be followed in universe design tool to create objects in UNV universe.
Note: Uncheck the “Limit long text objects” on Universe Parameters>Controls to avoid truncation of text in the above created objects in the web intelligence reports
0 comments:
Post a Comment