Skip to Content

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.

     Error message3.jpg


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

          Error message1.jpg

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

Error message2.jpg

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

To report this post you need to login first.

8 Comments

You must be Logged on to comment or reply to a post.

  1. Victor Uta

    Hello,

    Thanks to this article I managed to work with CLOB objects on IDT, but the problem is still not solved on WEBI.

    The error on WEBI is : Database error: (CS) “unexpected behavior” : com.sap.connectivity.cs.core.CSError: Bad Value for Field”

    Does someone has a solution for it ?

    Thanks You in advance.

    (0) 
  2. Victor Uta

    Thank You,

    Unfortunately the substr(“Fieldname”,1,250) didn’t help on IDT level. It partially helps only on DB level, by using a view.  (The issue still exists if you use replaceStringExp function on IDT )

    The issue has been identified and logged under Problem Report ID ADAPT01715386.


    Cheers,

    (0) 
  3. Naveen Kumar

    For universe objects based on CLOB database columns, there is no need to use Dbms_lob.substr() function.

    Simply change ‘Data Type’ to Long Text.

    Regards

    Naveen Kumar 🙂

    (0) 
      1. Andrew Dale

        is there any solution to this problem with IDT?

        I am using multi-connection universe so SQL looks like this

        dbms_lob.substr(@catalog(‘BBLIVE’).”BB_BB60″.”BLOGS”.”DESCRIPTION”, 4000 ,1 )

        and get an error message when I validate that says

        Invalid Expression

        Encountered “(” at line 2, column 18.

        Was expecting one of:

        “from” …

        “as” …

        etc

        (0) 
  4. Bharath Reddy

    Hi we have single source universe and a object as blob but the substr is not working in idt.It throws an error when i click on show on values.Any other work arounds

    (0) 

Leave a Reply