Monday, December 28, 2009

Defining and Assigning Web Group Memberships

Suppose you have a dashboard with three pages (tabs). Suppose further that not every user should see all three tabs. Most users will see a limited set of the tabs – some will see two, some will see only one. You want to define web groups to cover all the possible combinations. How many web groups would you have to create?

The combinations are:

321
32
31
21
3
2
1

So you would have to define 7 web groups.

Suppose there are five tabs. With five tabs there are 31 combinations.

Suppose there are ten tabs. Now there are 1023 combinations.

In general, for a collection of n things, there are 2^n-1 combinations.

Clearly, even with only 10 tabs, you cannot define web groups to cover all combinations.

The problem is made more difficult by the fact that OBIEE only tells you which web groups have permission to access a web object. It does not tell you which objects a particular web group has permission to access. This means that, for any given user, you cannot predict what his/her experience will be after logging into OBIEE.

Web security gets messy very fast.

One way to deal with this is to create a single web group for every object. That means that there will be at most only n web groups, not 2^n-1. (Both n and 2^n-1 are theoretical combinations for read permission. There could be another set of web groups for full control or other permissions). Then assign membership to the web groups using row-wise initialization at log on.

A sample table of users and web groups might contain these entries for users K and G. K is a member of Tab11, Tab13, and Tab15 web groups.  G is a member of Tab11, Tab12, Tab14, and Tab16.

image

When K logs on she sees these tabs:

image

When G logs on, he sees these:

image

With this approach, it is also easy to predict (or audit) the experience of each user simply by querying the table. It would also be easy to create an application (outside of OBIEE) to assign permissions to users.

Thursday, December 3, 2009

The Aggregate Function

In addition to familiar SQL aggregation functions such as Sum, Max, Min, Average, and Count, OBIEE also supports an aggregation function called “Aggregate”. This function is available for use in formulas written in Answers.

Business Model

To illustrate the use of the Aggregate function, consider the following simple business model that has one dimension and three facts. Two facts (“Qty Sold” and “Amt Sold”) have an aggregation rule of Sum in the metadata. “Avg Price” is defined as “Amt Sold”/“Qty Sold”.

image

The business model has two tables as it sources, “Time” and “POSTest”.

image

Query Using Sum

Consider a query that calculates the Qty Sold for each month and also calculates the total Qty Sold for all month, using the SUM function in a formula.image

This query generated the following SQL. Interestingly, 
the value in the SUM QS column was calculated
on the result set by OBIEE.


select T463.MONNAME as c1,
sum(T2144.QS) as c2,
T463.YEARMONTH as c4
from
TIME T463,
POSTEST T2144
where ( T463.DATE_ = T2144.DATE1 )
group by T463.MONNAME, T463.YEARMONTH
order by c1, c4


Query With Grand Total Using Default Rule


If instead of using the SUM function in a formula we had simply requested a grand total, we would have seen this.image



The logical query used the REPORT_SUM function:



SELECT "Time Dim"."Month Name" saw_0, Facts."Qty Sold" saw_1, REPORT_SUM(saw_1 BY ) FROM AggregateTest ORDER BY saw_0


Again, if you look at the physical query, you can see that the grand total is being calculated by OBIEE from the result set.



WITH 
SAWITH0 AS (select sum(T2144.QS) as c1,
T463.MONNAME as c2,
T463.YEARMONTH as c3
from
TIME T463,
POSTEST T2144
where ( T463.DATE_ = T2144.DATE1 )
group by T463.MONNAME, T463.YEARMONTH)
select distinct SAWITH0.c2 as c1,
SAWITH0.c1 as c2,
SAWITH0.c3 as c4,
SAWITH0.c1 as c6
from
SAWITH0
order by c1, c4


Query with Grand Total Using Server Complex Aggregate



The same query, with the aggregation rule on Qty Sold changed to “Server Complex Aggregate”, produces a logical query using the AGGREGATE function. AGGREGATE is used with BY followed by a null, which means aggregate over all rows.



SELECT "Time Dim"."Month Name" saw_0, Facts."Qty Sold" saw_1, AGGREGATE(saw_1 BY ) FROM AggregateTest ORDER BY saw_0



The physical query generated by OBIEE was:



WITH 
SAWITH0 AS (select D1.c1 as c1,
D1.c2 as c2,
D1.c3 as c3
from
(select sum(T2144.QS) as c1,
T463.MONNAME as c2,
T463.YEARMONTH as c3,
ROW_NUMBER() OVER (PARTITION BY T463.YEARMONTH ORDER BY T463.YEARMONTH ASC) as c4
from
TIME T463,
POSTEST T2144
where ( T463.DATE_ = T2144.DATE1 )
group by T463.MONNAME, T463.YEARMONTH

) D1
where ( D1.c4 = 1 ) ),
SAWITH1 AS (select sum(T2144.QS) as c1
from
POSTEST T2144)
select SAWITH0.c2 as c1,
SAWITH0.c1 as c2,
SAWITH0.c3 as c4,
SAWITH1.c1 as c5
from
SAWITH0,
SAWITH1


In this query, the derived table D1 evaluates to this:



image



SAWITH0 evaluates to:



image



SAWITH1 evaluates to



image





The physical query taken as a whole evaluates to



image



There are a couple of things to notice here. One is that by using the Server Complex Aggregate rule, the aggregation formula in the logical query for the grand total changes from REPORT_SUM(saw_1 BY )  to AGGREGATE(saw_1 BY ). The physical query also changes in form. The grand total is not directly calculated in the physical SQL generated for Report_Sum, whereas it is calculated in a separate SQL block when the logical SQL uses Aggregate.  The Aggregate function is a message to the BI Server saying, essentially, “determine how to aggregate by using the information in the metadata”.



Using the Aggregate Function in Answers



The fact that the presentation server generates logical SQL using the Aggregate function is a clue that the Aggregate function can be used in a formula in Answers (even though Aggregate is not listed as an aggregate function in the expression builder!).



image 



When writing a formula with Aggregate, you can use “(..by)”.



image



Using this formula, here are the results:



image





The logical SQL generated is almost the same as the logical SQL generated in the previous example, with the column label “Qty Sold” used instead of “saw_1”.



SELECT "Time Dim"."Month Name" saw_0, Facts."Qty Sold" saw_1, Aggregate(Facts."Qty Sold" by) saw_2 FROM AggregateTest ORDER BY saw_0



The physical SQL generated is exactly the same.



Complex Aggregation Rule



Consider what happens when Avg Price (defined in the metadata as Amt Sold/Qty Sold)  is included in the query.



image



The BI Server generates the following physical SQL, with Avg Price calculated in the last Select block.



WITH 
SAWITH0 AS (select sum(T2144.QS) as c1,
sum(T2144.AMT) as c2,
T463.MONNAME as c3,
T463.YEARMONTH as c4
from
TIME T463,
POSTEST T2144
where ( T463.DATE_ = T2144.DATE1 )
group by T463.MONNAME, T463.YEARMONTH)
select distinct SAWITH0.c3 as c1,
SAWITH0.c1 as c2,
SAWITH0.c2 / nullif( SAWITH0.c1, 0) as c3,
SAWITH0.c4 as c4
from
SAWITH0
order by c4


Now we’ll include a Grand Total in the query.


image 


The logical SQL generated uses the Aggregate function for Avg Price.



SELECT "Time Dim"."Month Name" saw_0, Facts."Qty Sold" saw_1, Facts."Avg Price" saw_2, REPORT_SUM(saw_1 BY ), AGGREGATE(saw_2 BY ) FROM AggregateTest ORDER BY saw_0



This results in the following physical SQL being generated.



WITH

SAWITH0 AS (select D1.c1 as c1, 
     D1.c2 as c2, 
     D1.c3 as c3,


     D1.c4 as c4


from 
     (select sum(T2144.QS) as c1, 
               sum(T2144.AMT) as c2, 
               T463.MONNAME as c3, 
               T463.YEARMONTH as c4, 
               ROW_NUMBER() OVER (PARTITION BY T463.YEARMONTH ORDER BY T463.YEARMONTH ASC) as c5 
          from 
               TIME T463, 
               POSTEST T2144 
          where  ( T463.DATE_ = T2144.DATE1 ) 
          group by T463.MONNAME, T463.YEARMONTH 
     ) D1


where  ( D1.c5 = 1 ) ),


SAWITH1 AS (select sum(T2144.QS) as c1, 
     sum(T2144.AMT) as c2


from


     POSTEST T2144)


select SAWITH0.c3 as c1, 
     SAWITH0.c1 as c2, 
     SAWITH0.c2 / nullif( SAWITH0.c1, 0) as c3, 
     SAWITH0.c1 as c4, 
     SAWITH0.c4 as c6, 
     SAWITH1.c2 as c7, 
     SAWITH1.c1 as c8


from 
     SAWITH0, 
     SAWITH1



SAWITH0 evaluates to



image



SAWITH1 evaluates to



image





The result set for whole query evaluates to



image



Notice that the physical query does not calculate the grand total for Avg Price directly. The BI Server calculates the result (20.26) from these results as 12842/634 (or c7/c8).



We can use the Aggregate function directly in Answers by writing a formula like this:



image



image





Using Aggregate we could write a formula to compare the monthly Avg Price to the Avg Price for the whole time period.



image



image



Knowing how to use the AGGREGATE function can be a useful addition to your OBIEE tool set.