A Configuration Steps for OBIEE 11g

Setting Up Users, Groups and Application Roles for ORDM OLAP in OBIEE 11g

The supported version of Oracle Business Intelligence Suite Enterprise Edition for use with Oracle Retail Data Model: OBIEE 11g 11.1.1.6 or higher, as shown in Figure A-1.

Figure A-1 About Oracle BI Administration Tool Version Dialog

Description of "Figure A-1 About Oracle BI Administration Tool Version Dialog"

Note:

Some of the snapshots have been taken with Oracle Business Intelligence Suite Enterprise Edition 11g (11.1.1.5) AdminTool. However the Oracle Retail Data Model rpd and webcat are delivered and supported on OBIEE 11g (11.1.1.6) version and that is the supported version. For example, the parameter EVALUATE_SUPPORT_LEVEL is present in OBIEE 11.1.1.6 version.

Clean up the rpd in online mode to remove all superficial users/groups/applicationroles:

In ONLINE mode, remove all custom defined users/roles and so on, so that only the BI<…> Application Roles remain within the rpd. The Online mode rpd should look similar to that shown in Figure A-2: (without the custom user, groups, roles).

The User: Administrator is not mandatory (need not be present).

Figure A-2 Identity Manager Identities

Description of "Figure A-2 Identity Manager Identities"

Figure A-3 shows the WebLogic Server Administration console, with the security realm.

Figure A-3 WebLogic Server Administration Console - Security Realm - myrealm - Define Users and Groups

Description of "Figure A-3 WebLogic Server Administration Console - Security Realm - myrealm - Define Users and Groups"

Define five users, as shown in Figure A-4:

ordm/password

ordm1/password

ordm2/password

ordm3/password

rbiaii/password

Figure A-4 WebLogic Server Administration Console: Define Five Users

Description of "Figure A-4 WebLogic Server Administration Console: Define Five Users"

Define two groups: ordm_olap_users and ordm_obiee_users, as shown in Figure A-5.

Figure A-5 WebLogic Server Administration Console: Define Two Groups

Description of "Figure A-5 WebLogic Server Administration Console: Define Two Groups"

Table A-1 shows the defined users and their associated group.

Table A-1 User and Groups

User	Group
ordm	ordm_olap_users
ordm	ordm_obiee_users
ordm1	ordm_olap_users
ordm1	ordm_obiee_users
ordm2	ordm_olap_users
ordm3	ordm_obiee_users
rbiaii	ordm_olap_users
rbiaii	ordm_obiee_users

Figure A-6 shows the Oracle Enterprise Manager 11g Fusion Middleware Control for Business Intelligence - coreapplication - Configure and Manage Application Roles.

Figure A-6 coreapplication: Configure and Manage Application Roles

Description of "Figure A-6 coreapplication: Configure and Manage Application Roles"

Figure A-7, Figure A-8, and Figure A-9 shows screens for creating two new Application Roles: ordm_olap_role and ordm_obiee_role containing groups: ordm_olap_users and ordm_obiee_users respectively.

Figure A-7 coreapplication: Create Two New Application Roles

Description of "Figure A-7 coreapplication: Create Two New Application Roles"

Figure A-8 coreapplication: Edit Application Role ordm_olap_role

Description of "Figure A-8 coreapplication: Edit Application Role ordm_olap_role"

Figure A-9 coreapplication: Edit Application Role ordm_obiee_role

Description of "Figure A-9 coreapplication: Edit Application Role ordm_obiee_role"

Figure A-10 shows the security setting to configure and manage application policies to add specific privileges to application roles: ordm_olap_role and ordm_obiee_role.

Figure A-10 coreapplication: Configure and Manage Application Policies

Description of "Figure A-10 coreapplication: Configure and Manage Application Policies"

Figure A-11 shows the initial State of Application Policies.

Figure A-11 coreapplication: Initial State of Application Policies

Description of "Figure A-11 coreapplication: Initial State of Application Policies"

Figure A-12 shows the final State of Application Policies. The ordm_olap_role has been granted the privilege manageRepositories (oracle.bi.server.manageRepositories).

Figure A-12 coreapplication: Final State of Application Policies

Description of "Figure A-12 coreapplication: Final State of Application Policies"

Figure A-13 shows the same privilege granted to ordm_obiee_role.

Figure A-13 coreapplication: Same Privilege Granted to ordm_obiee_role

Description of "Figure A-13 coreapplication: Same Privilege Granted to ordm_obiee_role"

The OBIEE Server configuration file "NQSConfig.INI" is present in the following location:

<OBIEEHOME>/instances/<instanceN>/config/OracleBIServerComponent/coreapplication_obis1

Modify the settings for EVALUATE_SUPPORT_LEVEL from the default value 0 to 1.

# EVALUATE_SUPPORT_LEVEL:
# 1: evaluate is supported for users with manageRepositories permission
# 2: evaluate is supported for any user.
# other: evaluate is not supported if the value is anything else.

EVALUATE_SUPPORT_LEVEL = 1;

Bounce, restart, the OBIEE services from Enterprise Manager. Now all front end logins (users) who belong to application roles: ordm_olap_role or ordm_obiee_role can use the EVALUATE() function (and allied functions such as EVALUATE_AGGR(), EVALUATE_ANALYTIC(), EVALUATE_PREDICATE() etc.) to make database calls from the front end and leverage database features and functionality.

Re-start Oracle BI:

./opmnctl stopall
./opmnctl startall

Or, using the Enterprise Manager console.

Open up the rpd in online mode. Open Tools - Identity

Figure A-14 shows a screen that verifies that the newly defined application roles are present in the online mode of the rpd.

Figure A-14 Tools: Identity Management Verify Newly Defined Application Roles

Description of "Figure A-14 Tools: Identity Management Verify Newly Defined Application Roles "

Apply the OLAP security filters to the appropriate Application Roles. Once the application roles are present in the rpd, the data filters can be added in either offline mode or online mode of the rpd.

Adding the data filters in offline mode is much more efficient.

We need to do this (adding OLAP security filters) for ordm_olap_role application role.

As a result, only users belonging to ordm_obiee_role will not enforce OLAP security filters automatically in obiee reports.

Verify that the relevant users are present under the application role, as shown in Figure A-15.

NOTE: you should also find that user ordm is also present in the list.

Figure A-15 Application Roles for ormd_olap_role

Description of "Figure A-15 Application Roles for ormd_olap_role"

Add the data security filters for all the cubes as shown in Example A-1.

Example A-1 Security Filters for Cubes

# For the OLAP Users group (ordm_olap_role), create a security filter on the Logical Table(s)
# in the Business Model using the following text. Do not include the initial # character.
#
# For Cube Logical Table: Activity Request
#
# "ORDM_OLAP"."Activity Request Type"."Activity Request Type Key" = "ORDM_ OLAP"."Activity Request"."Activity Request Type" AND "ORDM_ OLAP"."Customer"."Customer Key" = "ORDM_OLAP"."Activity Request"."Customer" AND "ORDM_OLAP"."Employee"."Employee Key" = "ORDM_OLAP"."Activity Request"."Employee" AND "ORDM_OLAP"."Interaction Reason"."Interaction Reason Key" = "ORDM_OLAP"."Activity Request"."Interaction Reason" AND "ORDM_ OLAP"."Interaction Status"."Interaction Status Key" = "ORDM_OLAP"."Activity Request"."Interaction Status" AND "ORDM_OLAP"."Interaction Type"."Interaction Type Key" = "ORDM_OLAP"."Activity Request"."Interaction Type" AND "ORDM_ OLAP"."Organization"."Organization Key" = "ORDM_OLAP"."Activity Request"."Organization" AND "ORDM_OLAP"."Request Origin"."Request Origin Key" = "ORDM_OLAP"."Activity Request"."Request Origin" AND "ORDM_ OLAP"."Time"."Time Key" = "ORDM_OLAP"."Activity Request"."Time"
#
#
# For Cube Logical Table: Asset
#
# "ORDM_OLAP"."Assets Type"."Assets Type Key" = "ORDM_OLAP"."Asset"."Assets Type" AND "ORDM_OLAP"."Organization"."Organization Key" = "ORDM_ OLAP"."Asset"."Organization" AND "ORDM_OLAP"."Time"."Time Key" = "ORDM_ OLAP"."Asset"."Time"
#
#
# For Cube Logical Table: Carrier Compliance
#
# "ORDM_OLAP"."Carrier"."Carrier Key" = "ORDM_OLAP"."Carrier Compliance"."Carrier" AND "ORDM_OLAP"."Organization"."Organization Key" = "ORDM_OLAP"."Carrier Compliance"."Organization" AND "ORDM_ OLAP"."Time"."Time Key" = "ORDM_OLAP"."Carrier Compliance"."Time" AND "ORDM_OLAP"."Vendor Site"."Vendor Site Key" = "ORDM_OLAP"."Carrier Compliance"."Vendor Site"
#
#
# For Cube Logical Table: Customer Order
#
# "ORDM_OLAP"."Campaign Media"."Campaign Media Key" = "ORDM_ OLAP"."Customer Order"."Campaign Media" AND "ORDM_ OLAP"."Customer"."Customer Key" = "ORDM_OLAP"."Customer Order"."Customer" AND "ORDM_OLAP"."Organization"."Organization Key" = "ORDM_ OLAP"."Customer Order"."Organization" AND "ORDM_OLAP"."Product"."Product Key" = "ORDM_OLAP"."Customer Order"."Product" AND "ORDM_ OLAP"."Time"."Time Key" = "ORDM_OLAP"."Customer Order"."Time"
#
#
# For Cube Logical Table: Customer Rfmp
#
# "ORDM_OLAP"."Customer"."Customer Key" = "ORDM_OLAP"."Customer Rfmp"."Customer" AND "ORDM_OLAP"."Organization"."Organization Key" = "ORDM_OLAP"."Customer Rfmp"."Organization" AND "ORDM_ OLAP"."Rfmp"."Rfmp Key" = "ORDM_OLAP"."Customer Rfmp"."Rfmp" AND "ORDM_OLAP"."Time"."Time Key" = "ORDM_OLAP"."Customer Rfmp"."Time"
#
#
# For Cube Logical Table: Customer Rfmp Dc
#
# "ORDM_OLAP"."Customer"."Customer Key" = "ORDM_OLAP"."Customer Rfmp Dc"."Customer" AND "ORDM_OLAP"."Organization"."Organization Key" = "ORDM_ OLAP"."Customer Rfmp Dc"."Organization" AND "ORDM_OLAP"."Rfmp"."Rfmp Key" = "ORDM_OLAP"."Customer Rfmp Dc"."Rfmp" AND "ORDM_ OLAP"."Time"."Time Key" = "ORDM_OLAP"."Customer Rfmp Dc"."Time"
#
#
# For Cube Logical Table: Customer Sku Sale Return
#
# "ORDM_OLAP"."Customer"."Customer Key" = "ORDM_OLAP"."Customer Sku Sale Return"."Customer" AND "ORDM_OLAP"."Organization"."Organization Key" = "ORDM_OLAP"."Customer Sku Sale Return"."Organization" AND "ORDM_ OLAP"."Post Code"."Post Code Key" = "ORDM_OLAP"."Customer Sku Sale Return"."Post Code" AND "ORDM_OLAP"."Product"."Product Key" = "ORDM_ OLAP"."Customer Sku Sale Return"."Product" AND "ORDM_OLAP"."Time"."Time Key" = "ORDM_OLAP"."Customer Sku Sale Return"."Time"
#
#
# For Cube Logical Table: Employee Labor
#
# "ORDM_OLAP"."Business Unit Shift"."Business Unit Shift Key" = "ORDM_ OLAP"."Employee Labor"."Business Unit Shift" AND "ORDM_ OLAP"."Employee"."Employee Key" = "ORDM_OLAP"."Employee Labor"."Employee" AND "ORDM_OLAP"."Organization"."Organization Key" = "ORDM_ OLAP"."Employee Labor"."Organization" AND "ORDM_OLAP"."Time"."Time Key" = "ORDM_OLAP"."Employee Labor"."Time"
#
#
# For Cube Logical Table: Employee Wage Payment
#
# "ORDM_OLAP"."Employee"."Employee Key" = "ORDM_OLAP"."Employee Wage Payment"."Employee" AND "ORDM_OLAP"."Organization"."Organization Key" = "ORDM_OLAP"."Employee Wage Payment"."Organization" AND "ORDM_ OLAP"."Pay Type"."Pay Type Key" = "ORDM_OLAP"."Employee Wage Payment"."Pay Type" AND "ORDM_OLAP"."Time"."Time Key" = "ORDM_OLAP"."Employee Wage Payment"."Time"
#
#
# For Cube Logical Table: Inventory Adjustment
#
# "ORDM_OLAP"."Environment Type"."Environment Type Key" = "ORDM_ OLAP"."Inventory Adjustment"."Environment Type" AND "ORDM_OLAP"."Inventory Location"."Inventory Location Key" = "ORDM_OLAP"."Inventory Adjustment"."Inventory Location" AND "ORDM_ OLAP"."Organization"."Organization Key" = "ORDM_OLAP"."Inventory Adjustment"."Organization" AND "ORDM_OLAP"."Product"."Product Key" = "ORDM_OLAP"."Inventory Adjustment"."Product" AND "ORDM_ OLAP"."Reason"."Reason Key" = "ORDM_OLAP"."Inventory Adjustment"."Reason" AND "ORDM_OLAP"."Time"."Time Key" = "ORDM_OLAP"."Inventory Adjustment"."Time"
#
#
# For Cube Logical Table: Inventory Cube
#
# "ORDM_OLAP"."Organization"."Organization Key" = "ORDM_OLAP"."Inventory Cube"."Organization" AND "ORDM_OLAP"."Product"."Product Key" = "ORDM_ OLAP"."Inventory Cube"."Product" AND "ORDM_OLAP"."Time"."Time Key" = "ORDM_OLAP"."Inventory Cube"."Time"
#
#
# For Cube Logical Table: Inventory Forecast Cube
#
# "ORDM_OLAP"."Organization"."Organization Key" = "ORDM_OLAP"."Inventory Forecast Cube"."Organization" AND "ORDM_OLAP"."Product"."Product Key" = "ORDM_OLAP"."Inventory Forecast Cube"."Product" AND "ORDM_ OLAP"."Time"."Time Key" = "ORDM_OLAP"."Inventory Forecast Cube"."Time"
#
#
# For Cube Logical Table: Inventory Forecast Statistic
#
# "ORDM_OLAP"."Organization"."Organization Key" = "ORDM_OLAP"."Inventory Forecast Statistic"."Organization" AND "ORDM_OLAP"."Product"."Product Key" = "ORDM_OLAP"."Inventory Forecast Statistic"."Product" AND "ORDM_ OLAP"."Time"."Time Key" = "ORDM_OLAP"."Inventory Forecast Statistic"."Time"
#
#
# For Cube Logical Table: Inventory Receipt
#
# "ORDM_OLAP"."Organization"."Organization Key" = "ORDM_OLAP"."Inventory Receipt"."Organization" AND "ORDM_OLAP"."Product"."Product Key" = "ORDM_ OLAP"."Inventory Receipt"."Product" AND "ORDM_OLAP"."Time"."Time Key" = "ORDM_OLAP"."Inventory Receipt"."Time"
#
#
# For Cube Logical Table: Inventory Unavailable
#
# "ORDM_OLAP"."Organization"."Organization Key" = "ORDM_OLAP"."Inventory Unavailable"."Organization" AND "ORDM_OLAP"."Product"."Product Key" = "ORDM_OLAP"."Inventory Unavailable"."Product" AND "ORDM_ OLAP"."Reason"."Reason Key" = "ORDM_OLAP"."Inventory Unavailable"."Reason" AND "ORDM_OLAP"."Time"."Time Key" = "ORDM_OLAP"."Inventory Unavailable"."Time"
#
#
# For Cube Logical Table: Liability
#
# "ORDM_OLAP"."Liability Type"."Liability Type Key" = "ORDM_ OLAP"."Liability"."Liability Type" AND "ORDM_OLAP"."Time"."Time Key" = "ORDM_OLAP"."Liability"."Time"
#
#
# For Cube Logical Table: Store Hours
#
# "ORDM_OLAP"."Organization"."Organization Key" = "ORDM_OLAP"."Store Hours"."Organization" AND "ORDM_OLAP"."Time"."Time Key" = "ORDM_ OLAP"."Store Hours"."Time"
#
#
# For Cube Logical Table: Store Traffic
#
# "ORDM_OLAP"."Organization"."Organization Key" = "ORDM_OLAP"."Store Traffic"."Organization" AND "ORDM_OLAP"."Time"."Time Key" = "ORDM_ OLAP"."Store Traffic"."Time"
#
#
# For Cube Logical Table: Purchase Order Line Item State
#
# "ORDM_OLAP"."Organization"."Organization Key" = "ORDM_OLAP"."Purchase Order Line Item State"."Organization" AND "ORDM_OLAP"."Product"."Product Key" = "ORDM_OLAP"."Purchase Order Line Item State"."Product" AND "ORDM_ OLAP"."Time"."Time Key" = "ORDM_OLAP"."Purchase Order Line Item State"."Time" AND "ORDM_OLAP"."Vendor"."Vendor Key" = "ORDM_OLAP"."Purchase Order Line Item State"."Vendor" AND "ORDM_OLAP"."Vendor Site"."Vendor Site Key" = "ORDM_OLAP"."Purchase Order Line Item State"."Vendor Site"
#
#
# For Cube Logical Table: Purchase Order State
#
# "ORDM_OLAP"."Order Type"."Order Type Key" = "ORDM_OLAP"."Purchase Order State"."Order Type" AND "ORDM_OLAP"."Organization"."Organization Key" = "ORDM_OLAP"."Purchase Order State"."Organization" AND "ORDM_ OLAP"."Time"."Time Key" = "ORDM_OLAP"."Purchase Order State"."Time" AND "ORDM_OLAP"."Unit Of Measure"."Unit Of Measure Key" = "ORDM_ OLAP"."Purchase Order State"."Unit Of Measure" AND "ORDM_ OLAP"."Vendor"."Vendor Key" = "ORDM_OLAP"."Purchase Order State"."Vendor" AND "ORDM_OLAP"."Vendor Site"."Vendor Site Key" = "ORDM_OLAP"."Purchase Order State"."Vendor Site"
#
#
# For Cube Logical Table: Retail Transaction Employee Workstation
#
# "ORDM_OLAP"."Employee"."Employee Key" = "ORDM_OLAP"."Retail Transaction Employee Workstation"."Employee" AND "ORDM_ OLAP"."Organization"."Organization Key" = "ORDM_OLAP"."Retail Transaction Employee Workstation"."Organization" AND "ORDM_OLAP"."Time"."Time Key" = "ORDM_OLAP"."Retail Transaction Employee Workstation"."Time" AND "ORDM_ OLAP"."Touchpoint"."Touchpoint Key" = "ORDM_OLAP"."Retail Transaction Employee Workstation"."Touchpoint"
#
#
# For Cube Logical Table: Sales Plan Item Organization Hierarchy
#
# "ORDM_OLAP"."Organization"."Organization Key" = "ORDM_OLAP"."Sales Plan Item Organization Hierarchy"."Organization" AND "ORDM_ OLAP"."Product"."Product Key" = "ORDM_OLAP"."Sales Plan Item Organization Hierarchy"."Product" AND "ORDM_OLAP"."Time"."Time Key" = "ORDM_ OLAP"."Sales Plan Item Organization Hierarchy"."Time"
#
#
# For Cube Logical Table: Sales Cube
#
# "ORDM_OLAP"."Organization"."Organization Key" = "ORDM_OLAP"."Sales Cube"."Organization" AND "ORDM_OLAP"."Product"."Product Key" = "ORDM_ OLAP"."Sales Cube"."Product" AND "ORDM_OLAP"."Time"."Time Key" = "ORDM_ OLAP"."Sales Cube"."Time"
#
#
# For Cube Logical Table: Sales Cube - Cube based Qr enabled
#
# "ORDM_OLAP"."Organization Qr Dimension"."Organization Qr Dimension Key" = "ORDM_OLAP"."Sales Cube - Cube based Qr enabled"."Organization Qr Dimension" AND "ORDM_OLAP"."Product Qr Dimension"."Product Qr Dimension Key" = "ORDM_OLAP"."Sales Cube - Cube based Qr enabled"."Product Qr Dimension" AND "ORDM_OLAP"."Time Qr Dimension"."Time Qr Dimension Key" = "ORDM_ OLAP"."Sales Cube - Cube based Qr enabled"."Time Qr Dimension"
#
#
# For Cube Logical Table: Sales Cube Forecast
#
# "ORDM_OLAP"."Organization"."Organization Key" = "ORDM_OLAP"."Sales Cube Forecast"."Organization" AND "ORDM_OLAP"."Product"."Product Key" = "ORDM_ OLAP"."Sales Cube Forecast"."Product" AND "ORDM_OLAP"."Time"."Time Key" = "ORDM_OLAP"."Sales Cube Forecast"."Time"
#
#
# For Cube Logical Table: Sales Cube Forecast Statistic
#
# "ORDM_OLAP"."Organization"."Organization Key" = "ORDM_OLAP"."Sales Cube Forecast Statistic"."Organization" AND "ORDM_OLAP"."Product"."Product Key" = "ORDM_OLAP"."Sales Cube Forecast Statistic"."Product" AND "ORDM_ OLAP"."Time"."Time Key" = "ORDM_OLAP"."Sales Cube Forecast Statistic"."Time"
#
#
# For Cube Logical Table: Space Utilization
#
# "ORDM_OLAP"."Organization"."Organization Key" = "ORDM_OLAP"."Space Utilization"."Organization" AND "ORDM_OLAP"."Product"."Product Key" = "ORDM_ OLAP"."Space Utilization"."Product" AND "ORDM_OLAP"."Time"."Time Key" = "ORDM_OLAP"."Space Utilization"."Time"
#
#
# For Cube Logical Table: Vendor Compliance
#
# "ORDM_OLAP"."Organization"."Organization Key" = "ORDM_OLAP"."Vendor Compliance"."Organization" AND "ORDM_OLAP"."Time"."Time Key" = "ORDM_ OLAP"."Vendor Compliance"."Time" AND "ORDM_OLAP"."Vendor"."Vendor Key" = "ORDM_OLAP"."Vendor Compliance"."Vendor" AND "ORDM_OLAP"."Vendor Item"."Vendor Item Key" = "ORDM_OLAP"."Vendor Compliance"."Vendor Item"
#

Figure A-16 shows the end result for ordm_olap_users role.

Figure A-16 ordm_olap_users Data Filters

Description of "Figure A-16 ordm_olap_users Data Filters"

You should see ordm, ordm1, ordm3 and rbiaii Members, as shown in Figure A-17.

Figure A-17 Application Role: ordm_obiee_role

Description of "Figure A-17 Application Role: ordm_obiee_role"

Figure A-18 User and Application Role Permissions for ordm_obiee_role with Data Filters Tab

Description of "Figure A-18 User and Application Role Permissions for ordm_obiee_role with Data Filters Tab"

Re-start Oracle BI with the commands:

./opmnctl stopall 
 ./opmnctl startall

Login as ordm, ordm1, ordm2, ordm3, rbiaii and weblogic and check results for same report. Create a test rpt1 report, as shown in Example A-2.

Sales Cube is dimensioned by Organization, Product and Time.

Create a report which uses two dimensions:

Product (Total Product)

and

Time (Business Year - BY 2008, BY 2009, BY 2010 and BY 2011)

Include a few measures from the Sales Cube.

Note: the Organization dimension not included.

In the case of ordm, ordm1, ordm2 and rbiaii, the obiee user belongs to application role: ordm_olap_role, the missing dimension is inserted into the query internally (automatically) leading to a report which contains a single row for each "Total Product, <Bsns Yr>" combination, with four in all.

Example A-2 Test Report for Sales Cube with Two Dimensions

[2011-08-06T17:38:50.000+00:00] [OracleBIServerComponent] [TRACE:3] [USER-18] [] [ecid: ca92a5c9efc3ac16:592577f6:131950fbbf0:-8000-00000000000057b2] [tid: 466a6940] [requestid: 62600003] [sessionid: 62600000] [username: ordm2] -------------------- Sending query to database named RBIAII OLAP (id: <<261698>>), connection pool named ORDM_LOCAL: [[
WITH
SAWITH0 AS (select distinct T173737.SU as c1,
T173737.SU_LY_PCT_CHG as c2,
T173737.HOW_IS_SU_G_YOY as c3,
T171557.TPROD_LONG_DESCRIPTION as c4,
T171981.BSNS_YR_LONG_DESCRIPTION as c5,
T171557.HIER_ORDER as c6,
T171981.HIER_ORDER as c7
from
TIME_HTBSNS_VIEW T171981,
PROD_HPROD_VIEW T171557,
ORG_HORG_VIEW T171393,
SLS_VIEW_FIN T173737
where ( T171393.DIM_KEY = T173737.ORGANIZATION and T171557.DIM_KEY = T173737.PRODUCT and T171393.LEVEL_NAME = 'TORG' and T171557.LEVEL_ NAME = 'TPROD' and T171981.DIM_KEY = T173737.TIME and T171981.LEVEL_ NAME = 'BSNS_YR' and (T171981.BSNS_YR_LONG_DESCRIPTION = 'BY 2008' or T171981.BSNS_YR_LONG_DESCRIPTION = 'BY 2009' or T171981.BSNS_YR_LONG_ DESCRIPTION = 'BY 2010' or T171981.BSNS_YR_LONG_DESCRIPTION = 'BY 2011') ) )
select distinct 0 as c1,
D1.c4 as c2,
D1.c3 as c3,
D1.c2 as c4,
D1.c1 as c5,
D1.c5 as c6,
D1.c6 as c7,
D1.c7 as c8
from
SAWITH0 D1
order by c7, c8, c5, c4, c3
]]

Example A-3 shows the case for the ordm3 user and weblogic, where the obiee user belongs to application role: ordm_obiee_role (this role does not contain OLAP security filters and is also not a parent application role encompassing ordm_olap_role), the missing dimension is thus not inserted into the query internally (automatically) leading to a report query which joins fact to two dimensions and leaves the Organization dimension untouched. For this example you get multiple rows (corresponding to each Organization member - collected from across all Organization levels) for each "Total Product, <Bsns Yr>" combination. NOTE: The use of DISTINCT clause in obiee query removes duplicates within the resultset and we end up with a final figure for # of rows (67) which is not a multiple of 4.

Example A-3 Test Case for User ordm3

[2011-08-06T17:43:41.000+00:00] [OracleBIServerComponent] [TRACE:3] [USER-18] [] [ecid: ca92a5c9efc3ac16:592577f6:131950fbbf0:-8000-000000000000583c] [tid: 46aaa940] [requestid: 625e0004] [sessionid: 625e0000] [username: ordm3] -------------------- Sending query to database named RBIAII OLAP (id: <<263101>>), connection pool named ORDM_LOCAL: [[
WITH
SAWITH0 AS (select distinct T173737.SU as c1,
T173737.SU_LY_PCT_CHG as c2,
T173737.HOW_IS_SU_G_YOY as c3,
T171557.TPROD_LONG_DESCRIPTION as c4,
T171981.BSNS_YR_LONG_DESCRIPTION as c5,
T171557.HIER_ORDER as c6,
T171981.HIER_ORDER as c7
from
TIME_HTBSNS_VIEW T171981,
PROD_HPROD_VIEW T171557,
SLS_VIEW_FIN T173737
where ( T171557.DIM_KEY = T173737.PRODUCT and T171557.LEVEL_NAME = 'TPROD' and T171981.DIM_KEY = T173737.TIME and T171981.LEVEL_NAME = 'BSNS_YR' and (T171981.BSNS_YR_LONG_DESCRIPTION = 'BY 2008' or T171981.BSNS_YR_LONG_DESCRIPTION = 'BY 2009' or T171981.BSNS_YR_LONG_ DESCRIPTION = 'BY 2010' or T171981.BSNS_YR_LONG_DESCRIPTION = 'BY 2011') ) )
select distinct 0 as c1,
D1.c4 as c2,
D1.c3 as c3,
D1.c2 as c4,
D1.c1 as c5,
D1.c5 as c6,
D1.c6 as c7,
D1.c7 as c8
from
SAWITH0 D1
order by c7, c8, c5, c4, c3
]]

Figure A-19 Oracle BIAnswers Criteria Tab Sample for ordm1_test_rpt1