Applies to:
SAP BusinessObjects Business Intelligence Platform, SAP BusinessObjects Semantic Layer and SAP BusinessObjects Web Intelligence.
Summary
This article provides an overview of types of large objects and then describes how to create universe objects on CLOB or BLOB database columns. It also provides details on issues encountered while creating universe objects on CLOB or BLOB 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.
Author: Mallikarjuna Chary Narayandas
Company: Gyansys Inc.
Created on: June 12, 2013
Author Bio
The author has worked on several SAP BusinessObjects projects and has experience on using all the SAPBusinessObjects Business Intelligence platform tools. Author has experience on working with SAPBusinessObjects mobile including designing and accessing BusinessObjects content (Webi Reports, Crystal Reports, Dashboards, Analysis and Information Spaces) through IPAD / IPhone / Android.
Introduction
This article provides an overview of types of large objects and then describes how to create universe objects on CLOB or BLOB database columns. It also provides details on issues encountered while creating universe objects on CLOB or BLOB 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
References
SAP BusinessObjects Business Intelligence Platform
SAP BusinessObjects Web Intelligence.
Semantic Layer | SCN
SAP BusinessObjects Web Intelligence ... | SCN
Disclaimer and Liability Notice
This document may discuss sample coding or other information that does not include SAP official interfaces and therefore is not supported by SAP. Changes made based on this information are not supported and can be overwritten during an upgrade.
SAP will not be held liable for any damages caused by using or misusing the information, code or methods suggested in this document and anyone using these methods does so at his/her own risk.
SAP offers no guarantees and assumes no responsibility or liability of any type with respect to the content of this technical article or code sample, including any liability resulting from incompatibility between the content within this document and the materials and services offered by SAP. You agree that you will not hold, or seek to hold, SAP responsible or liable with respect to the content of this document
- SAP Managed Tags:
9 Comments
