Aggregation in OBIEE

OBIEE as a reporting tool provides tremendous capabilities to do analytical reporting. Many new functions and decision features helps any user to do different reporting using OBIEE. For any analytical reporting anyone would accept that aggregation is the most basic and important tool. And so OBIEE provides amazing capabilities for doing different types of aggregation.

In this article we would see places where OBIEE provides features and capability is apply aggregation rules.

1.       The first and the most basic is the Aggregation tab in logical column of any logical table in BMM layer of RPD. In the figure below the area highlighted in red is used to set the default aggregation rule for any logical table column.

2.       The aggregation tab is used to specify default aggregation rules that would get applied when any user tries to query this column with other dimensions. OBIEE also provides a feature to specify different aggregation rules to be applied for different dimension table. To use this feature, one needs to check the “Based on dimensions” option as highlighted in green in above figure and specify different aggregation formula for different dimension.

If nothing is specified for any dimension then the aggregation rule as specified in the “Other” section gets applied in the physical query.

3.       The second feature that OBIEE provides related to aggregation is the concept of level based measure in any fact table. A level based measure in OBIEE is a measure in any fact table that is always pinned to a certain level of dimension table. Suppose there is a requirement where you have to show the monthly as well as yearly sales of products as two columns of a report. To do this one has to sum up sales column on monthly as well as yearly basis. To achieve this is SQL, you have to fire two queries and then join them together to get the final result.

SELECT time.cal_year,

        time.cal_period,

        SUM(sales.amount)

FROM sales,

       Time

WHERE sales.time_id=time.time_id

GROUP BY time.cal_year,

       time.cal_period;

And,

SELECT time.cal_year,

        SUM(sales.amount)

FROM sales,

       Time

WHERE sales.time_id=time.time_id

GROUP BY time.cal_year;

And then join the two result sets based on the cal_year column to get the monthly as well as yearly sales figure. However in OBIEE one can achieve this only when we tell OBIEE that one of the measure needs to be calculated at year level and one at monthly level separately and then it has to stitch the tow result sets together to get the final result. This can be done in OBIEE by creating a level based measure which is pinned at year level of time dimension hierarchy.

As before, we would consider a typical sales demo model. Here we would create a logical column amount_year in sales logical table and map it to sames.amount column and set the default aggregation rule as SUM as specified in step 2. Then we would double click on the sales.amount_year column in the BMM layer and go to Levels tab. This tab would be used to define the level of this measure.

As we have to pin the measure only related to Time_DDim, So we would specify the level for Time dimension as year level.

Now when a user queries columns as time.cal_year, time.cal_month, sales.amount and sales.amount_year column in OBIEE, the report produced would provide yearly as well as monthly sum of sales. Here one thing to mention is that if no level is specified for any dimension in the levels tab then it is considered as detail level for remaining dimensions. In the above example for location and product dimension the level pinned with amount_year column is detail level.

One of the most important and intriguing concept in OBIEE related to aggregation concept is the feature of aggregate navigation and fragmentation. In the next blog we would explore the concept of aggregate navigation.

Repository and Session variable in OBIEE

There has been a lot of discussion on the ETL aspect of business intelligence solution. Let us now discuss about the reporting aspect of business intelligence. Oracle BI as a part of fusion middleware solution provide its standard reporting platform Oracle business intelligence enterprise edition(OBIEE 11g) for doing reporting. OBIEE 11g as a reporting tool provides feature and functionalities that are used to provide out of box solution to different business needs. It leverages the weblogic technology and legacy Siebel Analytics solution platform.

In OBIEE 11g we have a concept of variables. Variables in OBIEE are used to store global values that are used in multiple areas. There are two types of variables in OBIEE.

1. Repository variable: These variables are initialized only once or are refreshed only after a certain interval. These variables are used for storing values like global currency for reporting. Default values for all the users for some variable.
These variables are of two types:
   a. Static variable: These variables are initialized during creation time only and the values can be modified only from       repository administration tool > Variable Manager.
   b. Dynamic variable: These variable can be changed after creation using event polling.

2. Session variable: These variables have a copy for each user instance. These variables are initialized after a user logs in to the system.

The variable are initialized using the initialization block. These variables are of two types.
   a. System variable: These variables are system sepcific variable like USER etc. These are system reserved variables and no new variables can be defined with the same name as system variable.
   b. Non System variable: These variables are defined by user for project spefic purpose and are initialized using initialization block at each login by any user.

Although you would think why I suddenly started with variables in OBIEE as a start of discussion on OBIEE. Well this has to do with the problem I faced while learning OBIEE. During my learning curve I had a lot of confusion regarding variables and its types in OBIEE. In a hope to solve my problem I endlessly searched for a place to clear my concept. However all my endeavours ended in failure.

In the next post we would discuss about the concept of aggregation in BMM layer of OBI.

OPB backend query to get names of mapping with a table as lookup source

select DISTINCT opb_subject.subj_name "Folder Name"
,opb_mapping.mapping_name "Mapping Name"
from
opb_widget_inst,
opb_widget_attr,
opb_mapping,
opb_subject
where opb_widget_inst.widget_type=11
and opb_widget_attr.attr_id=2
and opb_widget_inst.widget_id=opb_widget_attr.widget_id
and opb_mapping.mapping_id=opb_widget_inst.mapping_id
and opb_mapping.subject_id=opb_subject.subj_id
and UPPER(attr_value) like '%<<Source Name>>%';

Oracle application security using Oracle's Row level security

As more and more application are being developed to support enterprise needs, security is becoming more and more demanding subject for research and innovation. Oracle's DBMS_RLS in collaboration with Application Context can be used for implementing fine grained access security. In this article we will see the step by step process to implement fine grained access using DBMS_RLS package.

DBMS_RLS package is used to add a dynamic predicate based on user's environment attributes set in its application context.
An application context enables application developers to create, set and access application attributes by acting as a lookup storage from where user's environment attributes can be accessed. Oracle has predefined application context, USERENV which can also be used for this purpose.

Here we will see steps to create custom application context for meeting requirements that cannot be fulfilled using USERENV application context.

Consider an application which has an employee table and orders table in OE schema. Employee table stores all the employee imformation along with the ORG_ID which points to the organisation in which the employee works in. We need to implement a RLS on orders table which allows and employee to see only those orders which are being ordered from their organization. Orders table also has a column called BILL_FROM_ORG_ID which stores the organization from which the order has been placed.

To create an application context ORD_SEC we need to create a package which can be used to set the attributes of the context. This package is called the trusted package.

1) Create a trusted package.

CREATE OR REPLACE PACKAGE app_security
IS
    PROCEDURE set_context_attribute;
END app_security;

CREATE OR REPLACE PACKAGE BODY app_security
IS
    PROCEDURE set_context_attribute IS
        v_org_id NUMBER;
    BEGIN
        SELECT ORG_ID INTO v_org_id from employee where emp_name=SYS_CONTEXT('USERENV','SESSION_USER');
        DBMS_SESSION.SET_CONTEXT('ORD_SEC','ORG_ID',v_org_id);
    END set_context_attribute;

END app_security;

2) After creation of the trusted package we need to create application context ORD_SEC which should be connected to the package app_security.

CREATE CONTEXT ORD_SEC USING app_security;

3) Now we need to create a logon trigger that should invoke the trusted package procedure app_security.set_context_attribute to set the attribute in the context so that it can be used by the application for further processing and security implementation.

4) Now when the custom application context has been created which sets the custom context attributes when ever user logs in to the database, we need to create a security policy package that will return the predicate to be used with statements assigned in the policy.

CREATE OR REPLACE PACKAGE sec_policy IS
FUNCTION get_policy_predicate RETURN VARCHAR2;
END sec_policy;

CREATE OR REPLACE PACKAGE BODY IS

FUNCTION get_policy_predicate RETURN VARCHAR2
v_org_id number;
v_predicate VARCHAR2(200);
IS
v_predicate:='BILL_TO_ORG_ID='||SYS_CONTEXT('ORD_SEC','ORG_ID');
RETURN v_predicate;

END get_policy_predicate;


5) After the creation of policy function, we need to create a policy that is used to add predicate to any database object.For this purpose we use DBMS_RLS predefined package.

EXEC DBMS_RLS.ADD_POLICY('OE','ORDERS','ORD_POLICY','SYS','SEC_POLICY.GET_POLICY_PREDICATE','SELECT');

The statement adds a policy ORD_POLICY to ORDERS table in OE schema using package procedure SEC_POLICY.GET_POLICY_PREDICATE in SYS schema for select statements.

Now whenever a user queries table ORDERS as,

SELECT * FROM ORDERS;

Then Oracle server will add the predicate WHERE BILL_TO_ORG_ID=<<The organization ID of the user>>.

You can try creating more complex security using DBMS_RLS package.