Security Roles In OBIEE11g

Hi All,

By Default OBIEE11g provided the 3 default roles.

BI Consumer.
BI Author.
BI Administrator.

 Let's see the deference's.

1.BI Consumer: The base-level role that grants the user access to existing analyses, dashboards and agents, allows them to run or schedule existing BI Publisher reports, but not create any new ones. The Consumer can only view and run existing dashboards, analysis and reports provided to them. These objects will be published in a shared area with proper security rights. Consumers typically are the broadest user base across the institution.

2. BI Author: A role that is also recursively granted the Bi Consumer role that also allows users to create new analyses, dashboards and other BI objects. The Author can create and edit dashboards, analyses and reports. Authors will include a narrower user base than Consumers.

3. BI Administrator: Recursively granted the BIAuthor (and therefore BIConsumer) roles that allows the user to administer all parts of the system, including modifying catalog permissions and privilege. The Administrator can edit and create new repositories and catalogs. They also have full control over all aspects of the OBIEE tool suite.

OBIEE Security Groups
These roles correspond to a set of LDAP groups within the embedded Weblogic Server LDAP Server that have almost the same names (plural rather than singular) as these application roles:

1. BIConsumers 
2. BIAuthors 
3. BIAdministrators 

It’s these LDAP groups that you assign users to, not application roles, with Fusion Middleware then mapping these LDAP groups into their corresponding application roles. Later on, we’ll look at how and why you might want to create another LDAP group and corresponding application role like these, which we’ll call BIAnalyst; for now though, let’s look at how you create a new user and grant them one of the existing roles.

EBS Query to get segment structure description of an Chart of Account Code

SELECT

ST.ID_FLEX_STRUCTURE_CODE  "Chart of Account Code"

,SG.ID_FLEX_NUM            "Chart of Account Num"

,SG.SEGMENT_NAME               "Segment Name"

,SG.APPLICATION_COLUMN_NAME    "Column Name"

,SG.FLEX_VALUE_SET_ID          "Value Set Id"

,VS.FLEX_VALUE_SET_NAME

FROM

FND_ID_FLEX_STRUCTURES ST

INNER JOIN FND_ID_FLEX_SEGMENTS SG ON ST.APPLICATION_ID = SG.APPLICATION_ID AND ST.ID_FLEX_CODE = SG.ID_FLEX_CODE AND ST.ID_FLEX_NUM = SG.ID_FLEX_NUM

INNER JOIN FND_FLEX_VALUE_SETS VS ON SG.FLEX_VALUE_SET_ID = VS.FLEX_VALUE_SET_ID

LEFT OUTER JOIN FND_ID_FLEX_SEGMENTS SG1 ON VS.PARENT_FLEX_VALUE_SET_ID = SG1.FLEX_VALUE_SET_ID AND SG.ID_FLEX_NUM = SG1.ID_FLEX_NUM AND SG.APPLICATION_ID = SG1.APPLICATION_ID AND SG.ID_FLEX_CODE = SG1.ID_FLEX_CODE

WHERE

ST.APPLICATION_ID = 101

AND ST.ID_FLEX_CODE = 'GL#'

AND ST.ENABLED_FLAG = 'Y'

and SG.ID_FLEX_NUM =101

ORDER BY 1,2,3;

Masking Characters in OBIEE

In most of the cases like Bank Account Number, Personal Phone Number we need to mask few of the characters with 'X' for security purpose. Use below syntax for it.

REPEAT('x', LENGTH("Target Bank"."Target Bank Account Number")-4)||RIGHT("Target Bank"."Target Bank Account Number", 4)

Here "Target Bank"."Target Bank Account Number" is Bank account Number and I want to mask all the Characters with 'X' except last 4 digits. This will expose only last 4 digits as shown below.

Example: xxxxx8456

Payroll Details Query

select pbt.balance_name, pet.element_name, piv.name input_value_name,piv.effective_start_date
from pay_balance_types pbt,
pay_balance_feeds_f pbf,
Pay_input_values_f piv,
pay_element_types_f pet
where pbt.balance_type_id = pbf.balance_type_id
and trunc(sysdate) between pbf.effective_start_date and pbf.effective_end_date
and piv.input_value_id = pbf.input_value_id
and pbf.effective_start_date between piv.effective_start_date and piv.effective_end_date
and pet.element_type_id = piv.element_type_id
and pet.effective_start_date between piv.effective_start_date and piv.effective_end_date
order by 1,2

select pbt.balance_name, pbd.dimension_name, pbd.description
from pay_defined_balances pdf,
pay_balance_types pbt,
pay_balance_dimensions pbd
where pdf.balance_type_id = pbt.balance_type_id
and pdf.balance_dimension_id = pbd.balance_dimension_id
order by 1,2

Creating dblink to oracle database -Syntax

-- Rather than this.

CREATE DATABASE LINK hr_staff CONNECT TO hr_staff_link IDENTIFIED BY Password1 USING 'HRDEV1';

-- Use somethign link this.

CREATE DATABASE LINK hr_staff CONNECT TO hr_staff_link IDENTIFIED BY Password1 USING 'hr_staff_service';

The "tnsnames.ora" entry might look like this.

hr_staff_service=

  (DESCRIPTION=

    (ADDRESS=(PROTOCOL=TCP)(HOST=server1.example.com)(PORT=1521))

    (CONNECT_DATA=(SERVICE_NAME=HRDEV1))

  )

Grand Total in BI Publisher or RTF Templeate

By using below function you can get grand total
<?sum(//CF_AMT_CHILD[.!=''])?>

Query : Invoices on hold

select distinct
ai.invoice_num,ai.invoice_amount,ai.invoice_date
,ai.invoice_received_date,ah.hold_lookup_code,ah.hold_reason,pv.vendor_name,pv.segment1 vendor_number
,ph.segment1 po_number
,gcc.segment1||’.’||gcc.segment2||’.’||gcc.segment3||’.’||gcc.segment4||’.’||gcc.segment5||’.’||gcc.segment6
–,aid.AMOUNT
from ap_invoices_all ai
,ap_holds_all ah
,po_vendors pv
,po_headers_all ph
,ap_invoice_distributions_all aid
,po_distributions_all pda
,gl_code_combinations gcc
where 1=1
and ai.invoice_id = ah.invoice_id
and ai.vendor_id = pv.vendor_id
–and ai.po_header_id = ph.po_header_id(+)
and ph.PO_HEADER_ID = pda.PO_HEADER_ID
and ah.invoice_id = aid.invoice_id
and aid.po_distribution_id = pda.po_distribution_id
and aid.dist_code_combination_id = gcc.code_combination_id