Universe Objects based on XML, CLOB, BLOB Content

Recently, one of my users had an issue with XML, BLOB & CLOB data. The requirement was to create a report for which partial data was in XML format and some in normal relational format, and based on a single universe.

The database, on which the Universe was to be based, was a relational database. And the XML, BLOB & CLOB data to be used was contained as a table data in columns, along with normal Business Object supported data types.

We were able to create Universe Objects based on normal data types. But Issue was with creation of objects from XML, CLOB & BLOB data type columns. We were unable to drag and drop the table columns in Universe pane for object creation. Though, we were successful in creation the objects manually, we faced ‘Invalid Data Type’ and ‘Unsupported Data type’ errors at the Web Intelligence report level.

We could not find any normal straight way to create Universe objects based on such data types, which I do not think exist in first place, so we had to use a workaround…

What we did?

We were using Oracle as a database. It seems Oracle provides a package called DBMS_LOB which has functions to manipulate the various LOB objects.

–             –     Using the DBMS_LOB CAST function, the XML data content was converted into String/Varchar data type supported by Business Objects.

–             –     Used the Extract command from Oracle to convert all the XML tags into relational columns

–             –     A Derived table was then created based on these objects in Business Objects…and there we were able to create all the XML columns Universe 🙂

–             –     And, create reports based on these objects.