As mentioned in the blog entry here,
BI Server 11g now supports more functions which were added to support
the new UI features. Each of these functions can be used as part of the
Logical SQL or can even be used directly in the Answers UI. In this blog
entry we shall be looking at how some of these functions can be used.
1. SELECT_PHYSICAL: Though
this is not a function, this is a new feature supported by BI Server
that can bypass the BMM layer in the SQL queries. So, any SQL that uses
SELECT_PHYSICAL will be more closer to the ANSI SQL standard. There are 2
main advantages of this
a. Any external reporting tools that generate SQL
automatically can now leverage the functionality of BI Server. For
example, in the blog entry here i had shown the difficulty in actually using Logical SQL with Cognos. But introduction of SELECT_PHYSICAL should alleviate this issue thereby making BI Server open for better integration externally.
b. Using SELECT_PHYSICAL one can now make direct
joins across data sources without actually having the need to model them
separately in the Business Model and Mapping layer.
With the addition of more in-memory operations in
the BI Server, this should act as a point of integration for external
tools that have the need to leverage the metadata framework of BI EE.
To illustrate how this works, lets look at the repository shown below,
As
you see, the above repository contains all the relevant tables in the
SH schema. Now, to use the SELECT_PHYSICAL command, lets go to the Issue
SQL section of the BI EE and fire the SQL given below
SELECT_PHYSICAL 0 s_0, CHANNEL_CLASS s_1, SUM(AMOUNT_SOLD) s_2 FROM "ORCL - SH".."SH"."CHANNELS" A, "ORCL - SH".."SH"."SALES" B WHERE A.CHANNEL_ID = B.CHANNEL_ID GROUP BY CHANNEL_CLASS
Remember, though this is a SELECT_PHYSICAL SQL
statement, BI Server will still do the parsing as the objects will have
to reside inside the repository.
This
option can be enabled at the ODBC/JDBC level as well. That way the DSN
can be configured to just accept SELECT_PHYSICAL or SELECT commands.
I will cover more details of how this can be leveraged from external tools like QlikView, Cognos etc in a future blog entry.
2. AGGREGATE AT:
This function adds more multi-level analysis
capabilities within the BI Server. This function provides the same
functionality as the Level Based Measures. What this does is, it
provides an ability to extract the values of a parent level in a query
without having to SUM up the values in the lower level. To illustrate,
lets look at the simple report below
In the above report, lets now apply a filter to display the just 2 quarters.
Now,
what we need is an ability to display the value of yearly sales
alongside the quarterly sales. To do that, we need to enter the formula
as shown below
AGGREGATE("Sales"."AMOUNT_SOLD" AT "Times"."TimesDim - Calendar Year"."Calendar Year")
If
we look at the report now, we should see that we can compare the Yearly
sales along the Quarterly sales i.e. the AGGREGATE AT function will not
respect the filter on that dimension. If you want the filters to be
honored as well then the older SUM BY functions can still be used.
If
you look at the Physical SQL generated, you would actually notice 2
separate WITH clauses (minimum). One for the Sales figure. And the other
for the Yearly Sales figure. The Yearly Sales figure will not have the
filter applied.
WITH SAWITH0 AS (select D1.c1 as c1, D1.c2 as c2, D1.c3 as c3, D1.c4 as c4, D1.c5 as c5 from (select sum(T44515.AMOUNT_SOLD) as c1, T44523.CALENDAR_QUARTER_DESC as c2, T44523.CALENDAR_QUARTER_ID as c3, T44523.CALENDAR_YEAR as c4, T44523.CALENDAR_YEAR_ID as c5, ROW_NUMBER() OVER (PARTITION BY T44523.CALENDAR_QUARTER_ID ORDER BY T44523.CALENDAR_QUARTER_ID ASC) as c6 from TIMES T44523, SALES T44515 where ( T44515.TIME_ID = T44523.TIME_ID and (T44523.CALENDAR_QUARTER_NUMBER in (1, 2)) ) group by T44523.CALENDAR_QUARTER_DESC, T44523.CALENDAR_QUARTER_ID, T44523.CALENDAR_YEAR, T44523.CALENDAR_YEAR_ID ) D1 where ( D1.c6 = 1 ) ), SAWITH1 AS (select D1.c1 as c1, D1.c2 as c2, D1.c3 as c3 from (select sum(T44515.AMOUNT_SOLD) as c1, T44523.CALENDAR_YEAR as c2, T44523.CALENDAR_YEAR_ID as c3, ROW_NUMBER() OVER (PARTITION BY T44523.CALENDAR_YEAR_ID ORDER BY T44523.CALENDAR_YEAR_ID ASC) as c4 from TIMES T44523, SALES T44515 where ( T44515.TIME_ID = T44523.TIME_ID ) group by T44523.CALENDAR_YEAR, T44523.CALENDAR_YEAR_ID ) D1 where ( D1.c4 = 1 ) ), SAWITH2 AS (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 0 as c1, D1.c2 as c2, case when D2.c2 is not null then D2.c2 when D1.c4 is not null then D1.c4 end as c3, D1.c1 as c4, D2.c1 as c5, D1.c3 as c6, ROW_NUMBER() OVER (PARTITION BY D1.c2, D1.c3, case when D2.c2 is not null then D2.c2 when D1.c4 is not null then D1.c4 end ORDER BY D1.c2 ASC, D1.c3 ASC, case when D2.c2 is not null then D2.c2 when D1.c4 is not null then D1.c4 end ASC) as c7 from SAWITH0 D1 left outer join SAWITH1 D2 On D1.c5 = D2.c3 ) D1 where ( D1.c7 = 1 ) ) select D1.c1 as c1, D1.c2 as c2, D1.c3 as c3, D1.c4 as c4, D1.c5 as c5 from SAWITH2 D1 order by c1, c3 NULLS FIRST, c2
3. CALCULATEDMEMBER:
This function is used to derive calculated
members using members at any level of a hierarchy. In 10g, we can do
this from a pivot table. In 11g, this function has been introduced at
the BI Server level itself so that we can leverage these directly from
the repository.
To illustrate this, consider the hierarchy shown below
In
this hierarchy, we want to find out the difference between Steven
King’s Salary and his 2 direct reportees (Neena Kochhar and Lex De
Haan). To do this, we need to use a function given below
CALCULATEDMEMBER("HR"."Employee"."EmployeeDim",'Test', MEMBER(100) - MEMBER(101) -MEMBER(102))
This
would push the function directly on the parent-child hierarchy and give
us the correct values. This function cannot be used if there are other
attributes of the same dimension in the report. This is generally used
by external application tools. But within BI EE Answers, there is no
need for using this as we can achieve the same using Custom Groups and
Calculations.
4. EVALUATE_ANALYTIC:
This is another significant new feature that can
be useful to push down the oracle database analytic functions. I have
covered EVALUATE number of times before for function shipping database
functions. EVALUATE_ANALYTIC extends that further and provides us an
ability to function ship the analytic functions as well. The syntax of
this function remains the same. I will cover this in detail in another
blog post as there are quite a few use cases where EVALUATE_ANALYTIC can
prove to be very useful.
5. HIERARCHICAL FUNCTIONS:
BI Server now supports hierarchical functions
like ISCHILD, ISROOT, ISANCESTOR, ISDESCENDANT & ISLEAF that can be
used to traverse a Parent-Child hierarchy. These functions generally
return a boolean data type (true/false) and hence can be used in filters
to restrict data. They can also be used in column formula case
statements.
For example, in a hierarchy if we want to know
whether a specific member is a leaf node or not, we can use the below
function to display Yes/No as an attribute of the parent-child hierarchy
CASE WHEN ISLEAF(Employee.EmployeeDim) THEN 'Yes' ELSE 'No' END
Next
up is some interesting posts on the BI EE integration with Fusion
Middleware & Essbase. I will be doing these posts towards the end of
next week.
No comments:
Post a Comment