OBIA(Oracle BI Applicatiosns)

Oracle BI Applications

 

 

some important points /enhancements in obiee11g when compared with obiee10g

Oracle Business Intelligence Enterprise Edition (OBIEE) 11g is the most significant release of OBIEE since 2006.

This article contains the significant changes and new features of OBIEE 11g.

  1.  Oracle Business Intelligence is now more integrate with the Oracle Fusion Middleware software family.

2. Oracle WebLogic Server replaces Oracle Application Server and Oracle Containers for Java (OC4J).

3. Oracle Business Intelligence 11g Installer replaces the installer that was used in previous versions of Oracle Business Intelligence. It can perform software installation and configuration steps in the same process or separately in their own processes

4. This release add the following administration command-line utilities:
      a:  Oracle WebLogic Server scripting tool (WLST) for managing the Oracle WebLogic Server domain.
     b:   OPMN and the opmnctl commands for the Oracle Process Manager and Notification Server.
  
5.A database repository must be created before installation of the components with the Repository Creation Utility (RCU) tool
6.  Log centralization via the web log viewer

In addition, Oracle Business Intelligence is now deployed in a clustered configuration by default. Because of this, the default ODBC DSN for the Oracle BI Server points to the Cluster Controller by default, rather than to the Oracle BI Server.

Row Leve security in OBIEE11g

-->
 Row-level security is where groups of users have access to a set of reports, but they all see different sets of data within the reports due to filters being applied silently in the background.


-->

 As a report author you can of course apply filters to an analysis, but in this case I think what people generally associate row-level security with is filters that are applied automatically, according to a set of rules, without the user being aware of it or even filters being visible when you add a filter view to the analysis’ compound layout. 

 --->

In the two analysis screenshots below, for example, one user sees one set of stores when they view a particular report, whereas another user viewing the same report gets to see a completely different set of stores, but no filters have been explicitly added to the analysis – instead, some other process is applying row-level security to the analysis’ data in the background, automatically.




-->
So where can you set up row-level security in OBIEE, so that this background row-level filtering takes place?

 In general, you would set up row-level filtering like this using two repository features, together:

1. First, you would define what’s called a “session variable” to hold the region name that the user is allow to see, and then
2. You would define a filter, using the Identity Manager dialog, to filter rows against this region setting.

->

let’s look at the most common way to set up background row-level filtering and security.
Assuming that we’ve got two tables stored in a database somewhere, that list out users and the region they are assigned to, like this:

The steps to set up security to restrict users to a single region’s worth of data would look like this:

--->

Before we do anything, we’ll need to create a new, dedicated connection pool definition within the physical database settings that connects us to the database schema that holds these tables.

---->

Now, we’ll start by defining the session variable that will hold the region name that the user is allowed to see. Still using the Oracle BI Administrator tool and with the repository open online, select Manage > Variables… to open the Variable Manager dialog 

--->

Within the navigation tree on the left-hand side of the Variable Manager dialog, click on the Session > Variables node, then right-click in the area on the right and select New Session Variable… 

--->

 With the Session Variable dialog open, type in REGION as the Name, and then press the New… button next to the Initialization Block: area, which currently has not assigned as its setting   

--->

 The Session Variable Initialization Block dialog will then be displayed. Enter GET_REGION_SESS_VAR as the name, and then press the Edit Data Source button to bring up the Session Variable Initialization Block Data Source dialog. 

--->

Using this dialog, select Database as the Data Source Type, select the connection pool that you defined earlier, and then select the Default initialisation string radio button. Then, type in the SQL that returns the region for a given user, using the :USER substitution variable, like this:

select r.region from gcbc_hr.staff_logins l, gcbc_hr.staff_regions rwhere l.login_name = r.login_nameand l.login_name = ‘:USER’
Press OK to close the dialog, and the Session Variable Initialization Block dialog should look as in the screenshot below:

 



Using the Oracle BI Administrator tool and with the repository open online, 
select Manage > Identity… from the application menu.
 
The Identity Manager dialog will then be displayed. Click on the Application Roles tab on the right-hand side, and then then double-click on the role you wish to assign the row-level security settings to;
 in this case, BIConsumer, to apply the rule to all users of the system.
The Application Role dialog will then display. Press the Permissions… button to open the User/Application Role Permissions dialog.
With the User/Application Role Permissions dialog open, press the Add button to bring up the Browse dialog. Using the Browse dialog, select the table you wish to apply the filter to.

In this instance, there are two ways we can set up the filter. The Region column that corresponds to the filter is found in the Dim Stores table, which means that if we want to apply the filter when any column from this table is included in an analysis, we’d double-click on this particular table to select it for the filter condition. If, however, we wanted the filter to be applied even if no column from the Dim Stores table was selected, we’d double-click on the associated fact table Fact Sales instead, which would apply the filter regardless of what attribute columns were selected for the analysis criteria.

Note also that you can place the filter on either a presentation table, or a business model table. If you choose the former, and you’ve got several subject areas based off of the same business model, only the table from that particular presentation layer subject area will be subject to the filter. If, however, you click on the Business Model tab instead and then select a business model table, every subject area that contains presentation tables derived from that business model table will have the filter applied.  



    After double-clicking on a table, you’re returned back to the User/Application Role Permissions dialog, to define the actual filter expression




Click in the Data Filter area and then press the Edit Expression button to bring up the Expression Builder – Data Filter dialog.
10.    Using this dialog, set the following filter, which references the session variable you defined earlier using the VALUEOF(NQ_SESSION.variablename) syntax:
“Sales”.”Dim Stores”.”Region Name” = VALUEOF(NQ_SESSION.REGION)
Press OK to close the dialog, then keep pressing OK with the other dialogs to eventually return back to the Identity Manager dialog. To close that final dialog, select Action > Close.
Now, when users log in that have entries in this table, you should see that queries that reference either the Dim Stores table (in this case), or if you’ve set it up like this, any query against the fact table, will have the required restriction applied to the rows of data returned. If you don’t see this restriction happening, check the nqserver.log file for an error message like:

1    [2012-03-08T00:16:24.000+00:00] [OracleBIServerComponent] <br />[ERROR:1] [] [] [ecid: 3f3d2d8955322f32:1843bf05:135ee2cec8c:-8000-00000000000008c3] [tid: 10f4]  <br />[nQSError: 17010] SQL statement preparation failed. [[[nQSError: 17001] <br />Oracle Error code: 904, message: ORA-00904: "R"."LOGIN_NAME": invalid identifier at OCI call OCIStmtExecute.[nQSError: 13011] <br />Query for Initialization Block 'GET_REGION_SESS_VAR' has failed.
which indicates that there’s an error in your SQL, and the session variable value couldn’t be set.
So what happens if you log in as the weblogic or biadmin user, for example, who doesn’t have an entry in this table, or indeed a regular user that’s not in the table? If you try it out, you’ll see that in these cases, administration users get all values returned, whereas regular users get an error message saying that the variable hasn’t been populated, which probably is the behaviour that you wanted.
The other obvious question is how you would go about assigning more than one group to a particular user. For example, if you wanted the sfranceso user to be able to see all of the San Francisco regions, not just North SF, you could put entries in the STAFF_REGIONS table like this:







But the SQL in the variable definition that we defined earlier is expecting a single value, not a set of values. The way that you in fact deal with this is to create what’s called a “row-wise initialization” variable that can contain multiple values – internally, it still holds a single value, but this consists of all the values you load in, separated by semi-colons.

To define a row-wize initialised variable, follow these steps:

    Using the Oracle BI Administration tool and the repository online, select Manage > Variables… as before. This time though, do not create the variable first; instead, click on the Session > Initialization Blocks entry in the Variable Manager navigation tree, then right-cick on the right-hand panel and select New Initialization Block…
    With row-wise variables, you define the SQL query, and the variable, at the same time. Using the Session Variable Initialization Block dialog, type in a name for the initialisation block, for example GET_REGIONS_ROWWISE_SESS_VAR, and then press the Edit Data Source… button to bring up the Session Variable Initialization Block Data Source dialog.

    As before, select Database as the Data Source Type, select the Default initialization string radio button and select the connection pool as before, but then when you come to type in the SQL you define the variable at the same time, like this:

    select ‘REGIONS’, r.region
    from gcbc_hr.staff_logins l, gcbc_hr.staff_regions r
    where l.login_name = r.login_name
    and l.login_name = ‘:USER’

    with the literal values ‘REGIONS’, in this case, defining the row-wise variables as part of the initialisation block definition.

    Press OK to close this dialog and return to the Session Variable Initialization Block dialog.
    In the previous example, you’d see the variable name listed in the Variable Target area. To assign this initialisation block to the row-wise variable we’ve just defined in the SQL statement, press the Edit Data Target… button, and then when the Session Variable Initialization Block Variable Target dialog is shown, check the Row-wise initialisation checkbox. Your final Session Variable Initialization Block dialog should now look like this: 




--->


   In the previous example, you’d see the variable name listed in the Variable Target area. To assign this initialisation block to the row-wise variable we’ve just defined in the SQL statement, press the Edit Data Target… button, and then when the Session Variable Initialization Block Variable Target dialog is shown, check the Row-wise initialisation checkbox. Your final Session Variable Initialization Block dialog should now look like this: 








-->

Now, to reference the row-wise session variable in a filter, for example in the mandatory filter we applied to the BIConsumer role, you’d reference it in the same way as with a regular variable:
“Sales”.”Dim Stores”.”Region Name” = VALUEOF(NQ_SESSION.”REGIONS”)
Then, you should see any users that have more than one region assigned, getting the correct values in their analyses:





So that’s the basics of how row-level security works in OBIEE