Oracle BI EE 11g – Reporting on CLOBs – Lookups

August 20th, 2010 by

One important new feature of BI EE 11g is the ability to report on CLOBs and other large objects. The main difference between normal Varchar/Numeric columns and LOB datatype columns is, LOB columns do not support GROUP BY, DISTINCT & ORDER BY operations on them (from a reporting perspective). Any query that BI EE generates will always have a GROUP BY or a DISTINCT in it. So, in 10g reporting on LOB columns have always been an issue(will result in Ora-XXXX errors). One had to resort to different types of workarounds for reporting on LOB columns.

In BI EE 11g, its possible to isolate different columns from the GROUP BY/DISTINCT operations by using the SPARSE/DENSE lookup operators. I have already blogged about how Sparse & Dense lookups work in general here. Lets see how the same can be used for CLOB reporting. To demonstrate this i shall be using the v$sql view. This view basically contains a CLOB called SQL_FULLTEXT. This column contains the SQL queries stored in the form of CLOB, that are fired to the Oracle database by multiple users. Our aim is to report on this view and display the SQL Queries from the CLOB column.

We start with granting select on the v_$sql view to SH schema and then importing it in BI EE.

Then edit the column properties of the SQL_FULLTEXT column and increase the size to 100000 (or higher) so that BI EE can display the values from CLOB.

Start with modeling a dimension and a fact in the BMM layer as shown below

Then create another logical table (same as the dimension) containing SQL_ID as the primary key. The idea is to use this primary key and show the SQL_FULLTEXT using a DENSE lookup. Mark the new logical table as a lookup table.

In the dimension logical table create a new column and use the following formula to derive the SQL_FULLTEXT as a lookup column. This will always push a separate SQL without DISTINCT/GROUP BYs on the SQL_FULLTEXT column.

Lets now include this new column in the presentation layer and use it for reporting.

As you see, we should now be able to see the CLOB data without any restrictions. If we look at the query generated, you can basically see 2 separate SQL queries, one with the group-by and the other with just the lookup.

In BI EE 11g, usage tracking can now log logical sql’s greater than 4000 characters into a separate CLOB column.

Comments

  1. Santhosh Says:

    Hi Venkat,
    I really admire the way you think about implementing different use cases with the available options in OBIEE.
    Great work.

    Thanks.

  2. Joe Says:

    Excellent. I see that in the LOOKUP Function. the parameters used are the columns from the Lookup table. How does BI Server know which column has to be used from the dimension to lookup?

  3. WatchMe Says:

    Hi Venkat,

    Could you please go into more detail on this statement: “In BI EE 11g, usage tracking can now log logical sql’s greater than 4000 characters into a separate CLOB column.” It does not appear that Oracle has enabled this out of the box. How do we take advantage of this in 11G and can we tie the Usage Tracking record to the v$SQL to make UT even more valuable to us?

    Thanks!

  4. Karthikeyan Says:

    Hi Venkat,

    I followed all the steps as above and created logical table and column. But when I add that logical column to the report and click on the filter option it is displaying all data correctly, but at the same time when I click on the results tab it is failing with this error “OPR4ONWY:U9IM8TAC:OI2DL65P
    State: HY000. Code: 10058. [NQODBC] [SQL_STATE: HY000] [nQSError: 10058] A general error has occurred. [nQSError: 43113] Message returned from OBIS. [nQSError: 43119] Query Failed: [nQSError: 17001] Oracle Error code: 932, message: ORA-00932: inconsistent datatypes: expected – got CLOB at OCI call OCIStmtExecute. [nQSError: 17010] SQL statement preparation failed. (HY000)
    SQL Issued: SELECT 0 s_0, “P6 Project History”.”Project Issue – Logical”.”Note – Logical” s_1 FROM “P6 Project History” . Please help.

  5. Vbk Says:

    Not working either, same error message. Any ideas?

  6. Originall Says:

    Not working either.
    The problem is ROW_NUMBER() function. I make select of two columns from BLOB-table. One is a lookup(dense …) function and have such a script:

    WITH
    SACOMMON98591 AS (select T56340.LOG_ID as c1,
    T56340.LOG_MESSAGE as c2
    from
    LOG_OBJ T56340),
    SAWITH0 AS (select distinct 0 as c1,
    D1.c1 as c2
    from
    SACOMMON98591 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, D2.c2 ORDER BY D1.c2 ASC, D2.c2 ASC) as c4
    from
    SAWITH0 D1 inner join SACOMMON98591 D2 On D1.c2 = 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, c3 ) D1 where rownum <= 65001

    I don`t know , why does this function is in my script?

  7. Originall Says:

    Logical SQL is

    RqList distinct
    0 as c1 GB,
    F4 Lookup.LOG_ID as c2 GB,
    lookup( DENSE F4 Lookup.LOG_MESSAGE, F4 Lookup.LOG_ID) as c3 GB
    OrderBy: c1 asc, c2 asc NULLS LAST, c3 asc NULLS LAST

  8. Paul Says:

    Hi Originall,
    I have the same problem as yours, I create the lookup table and the query at the physical level is below ..

    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
    (select D1.c1 as c1,
    D1.c2 as c2,
    D1.c3 as c3,
    D1.c4 as c4,
    D1.c5 as c5,
    D1.c6 as c6
    from
    (select D1.c1 as c1,
    D2.c1 as c2,
    D1.c2 as c3,
    D1.c3 as c4,
    D1.c4 as c5,
    D1.c5 as c6,
    ROW_NUMBER() OVER (PARTITION BY D1.c2, D1.c3, D1.c4, D1.c5, D2.c1 ORDER BY D1.c2 ASC, D1.c3 ASC, D1.c4 ASC, D1.c5 ASC, D2.c1 ASC) as c7
    from
    (select distinct 0 as c1,
    D1.c1 as c2,
    D1.c2 as c3,
    D1.c3 as c4,
    D1.c4 as c5
    from
    (select ROWNUM as c1,
    T169228.ID_DGIOR as c2,
    T169228.ID_DPCONT as c3,
    T169228.COD_ISTANZA_WF as c4
    from
    QZV_DIM_CLME_NOTE_ESITO T169228
    ) D1
    ) D1 inner join (select T169228.NOTE_ESITO_SINTETICO as c1,
    T169228.ID_DGIOR as c2,
    T169228.ID_DPCONT as c3,
    T169228.COD_ISTANZA_WF as c4
    from
    QZV_DIM_CLME_NOTE_ESITO T169228
    ) D2 On D1.c5 = D2.c4 and D1.c3 = D2.c2 and D1.c4 = D2.c3
    ) D1
    where ( D1.c7 = 1 )
    ) D1
    ) D1

    you have solved?
    Thanks

    Paul

  9. Sunil Says:

    Its is not working as per suggested. It is throwing error saying CLOB datatype is incompatible.

  10. Krystian Says:

    Hi Venkat,
    My issue is quite different to the previous ones. I am able the report on the CLOB data type but there is one problem i cant figure out.
    In OBIEE when i pick the columns from tables in criteria tab, when i pull one from Fact table and one from Lookup Table the result tab gives me this error:
    State: HY000. Code: 10058. [NQODBC] [SQL_STATE: HY000] [nQSError: 10058] A general error has occurred. [nQSError: 43113] Message returned from OBIS. [nQSError: 43119] Query Failed: [nQSError: 14025] No fact table exists at the requested level of detail: [,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,[a_FACT_CLOB_TEST.COLUMN_KEY]]. (HY000)

    I have spent hours at this stage trying to figure out what is the problem and im hopeless. In your example you have a column “Number of Rows”, which i assume comes from the fact table.

    From my further analysis the way lookup tables are done in RPD is with no ligical joins with the fact table.
    So my questions is, how to report on clob lookup table and a fact table at the same time?

    Thanks,
    Krystian

Website Design & Build: tymedia.co.uk