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.
Lookup (DENSE "LOB Reporting"."Lookup - CLOB"."SQL Full Text" , "LOB Reporting"."Lookup - CLOB"."SQL ID" )
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.
select sum(T43900.ROWS_PROCESSED) as c1, T43900.SQL_ID as c2 from SYS.V_$SQL T43900 group by T43900.SQL_ID
select T43900.SQL_FULLTEXT as c1, T43900.SQL_ID as c2 from SYS.V_$SQL T43900In BI EE 11g, usage tracking can now log logical sql’s greater than 4000 characters into a separate CLOB column.
No comments:
Post a Comment