Search This Blog

Wednesday, 28 November 2012

Setup Oracle BIA 7963 – Data Security


Setup Oracle BIA 7963 – Data Security

Customer wants to secure their data based on things like Ledger, Company, Operating Unit, etc. All users who login via Oracle eBS should inherit (based on the current responsibility) the same security settings in Oracle BI. The following is a possible solution.
Environment
§  Oracle eBS R12.1.1
§  Oracle BI EE 11.1.1.6.0
§  Oracle BIA 7.9.6.3
Setup
The setup consists of a few different parts
§  Data Security
Data Security
The Data Security is based on different Roles and a Profile Option assigned to the responsibilities in Oracle eBS
Oracle eBS
§  Create ‘BI Type User’-profile option
§  Assign ‘BI Type User’-profile option to Responsibility
§  Assign Responsibility to User
Each Responsibility has either a specific ‘BI Type User’-profile option or a ‘BI Type User’-profile option on Site level. A view (xx_obia_user_groups_v) in Oracle eBS ‘holds’ the profile option information.
01
select fpov.level_value responsibility_id
02
 , fpov.level_value_application_id application_id

03
 , 'OBIA '
04
 || fpov.profile_option_value autorisatierol_code

05
 , fl.meaning autorisatierol
06
 from applsys.fnd_profile_option_values fpov

07
 , apps.fnd_profile_options_vl fpo
08
 , apps.fnd_lookups fl

09
 where fpo.profile_option_id = fpov.profile_option_id
10
 and fpo.application_id = fpov.application_id

11
 and fpov.profile_option_value = fl.lookup_code
12
 and fl.lookup_type = 'BI_TYPE_GEBRUIKER'

13
 and fpo.profile_option_name = 'XXBI_TYPE_GEBRUIKER'
14
 and fpov.level_id = 10003
Oracle BI
In Oracle BI, there is a Initialization Block which populates the; ROLES Session Variable
01
select ( select sector
02
 from apps.xx_obia_user_groups_v

03
 where responsibility_key = 'VALUEOF(NQ_SESSION.OLTP_EBS_RESP_KEY)'
04
 and responsibility_id = valueof ( nq_session.oltp_ebs_resp_id ) )

05
 || ';'
06
 || ( select autorisatierol_code "ROLES"

07
 from apps.xx_obia_user_groups_v
08
 where responsibility_key = 'VALUEOF(NQ_SESSION.OLTP_EBS_RESP_KEY)'

09
 and responsibility_id = valueof ( nq_session.oltp_ebs_resp_id ) )
10
 || ';'

11
 || ( select responsibility_key "ROLES"
12
 from apps.xx_obia_user_groups_v

13
 where responsibility_key = 'VALUEOF(NQ_SESSION.OLTP_EBS_RESP_KEY)'
14
 and responsibility_id = valueof ( nq_session.oltp_ebs_resp_id ) )

15
 "ROLES"
16
 from DUAL
http://obibb.files.wordpress.com/2012/10/100212_1017_setuporacle7.png?w=630
http://obibb.files.wordpress.com/2012/10/100212_1017_setuporacle8.png?w=630
Oracle Enterprise Manager (EM)
In the EM ‘all’ the different Application Roles, related to the Data Security are created.
There are a few different Application Roles;
§  Out-of the box
§  Data Security
§  eBS Profile
Out-of-the-Box
§  BIAdministrators
§  Administration privileges.
§  BIAuthors
§  Create, use or consume content.
§  BIConsumers
§  Use / consume content,
§  Every authenticated user.
§  BISystem
§  Component connections between products.
http://obibb.files.wordpress.com/2012/10/100212_1017_setuporacle9.png?w=630
Data Security & eBS Profile
http://obibb.files.wordpress.com/2012/10/100212_1017_setuporacle10.png?w=630
Oracle BI Administrator (Identity Manager)
In the Identity Manager, the Business Model Filters are applied to the Data Security Application Roles
http://obibb.files.wordpress.com/2012/10/100212_1017_setuporacle11.png?w=630
http://obibb.files.wordpress.com/2012/10/100212_1017_setuporacle12.png?w=630
http://obibb.files.wordpress.com/2012/10/100212_1017_setuporacle13.png?w=630
The Business Model Filters are based on the Initialization Blocks. Some out-of-the-box, others custom.
This setup should be sufficient to apply Data Security to all queries, which query the Logical Tables with the Business Model Filters applied to it.
Note: Application roles data filters won’t apply for users with BI Administrator role.
By definition the BIAdministrator application role is granted the “oracle.bi.server.manageRepositories” permission, which is equivalent to the 10g “Administrator” user who also had unrestricted access. Hence, data filters won’t affect users with BIAdministrator Role. (source: Oracle Support)

Measure prompt



Taking the reference of this OTN Post I thought of playing around prompts and measure columns.
The exercise is based on Sample Sales Lite SA ( XML Based SA )
Environment- OBIEE 11.1.1.6.0 ( Windows 7 -32 bit /Tested on IE 8 ) 

SA- Sample Sales Lite 

Create an analysis of 3 columns namely
1> "Time"."Per Name Year" 
2>"Base Facts"."Revenue"  
3>"Base Facts"."Revenue"
I would rename the columns ( no. 2 & 3 ) as shown in the snapshot below.

http://1.bp.blogspot.com/--67NAfzBTYw/UJ38aFAkxtI/AAAAAAAABFI/mJrUF3v3XKk/s320/Pic1.png




Column formula of column no 2
http://4.bp.blogspot.com/-J3SHNUiljVY/UJ3875GeRtI/AAAAAAAABFQ/y5UfmGYbtCs/s200/Pic2.png












Column formula of column no 3 ( You can have complex calculation - In the given example I have used a very simple one )
http://1.bp.blogspot.com/-G8RoWx5sMTw/UJ39regy4nI/AAAAAAAABFY/I_-XSGNbPHk/s200/Pic3.png









Save the report. [R1]

Go back to home page and once again navigate to Sample Sales Lite SA.Take 2 to 3 measures for example in the analysis and capture their names from the respective column formulae.We would need this while creating a dashboard prompt. Look at the below snapshot.


http://1.bp.blogspot.com/-YNJJVZ_K_nQ/UJ3_-ExXOtI/AAAAAAAABFg/CEao5N6ykt0/s200/Pic4.png








"Base Facts"."Revenue"
 "Base Facts"."Billed Quantity"
   "Base Facts"."Discount Amount"

Now go to dashboard prompt editor.Select the option Variable Prompt.Add the details as shown in the below snapshot.
http://4.bp.blogspot.com/-J4kjp1croFY/UJ4BewKv1xI/AAAAAAAABFo/xJwPgUMY0Ls/s320/Pic5.png









Save the prompt and pull it on the dashboard with the report R1.

Select the first measure ( Revenue ) and hit apply.You will see the Revenue measure values getting populated in the second column while values of 3rd column would base upon the 2nd column that we select from the prompt.
http://4.bp.blogspot.com/-HgrKfIzYkfE/UJ4C2VlyU3I/AAAAAAAABFw/YpdRUJuRYzQ/s320/Pic6.png




http://1.bp.blogspot.com/-xDDZfyQCUjY/UJ4DNsICKOI/AAAAAAAABF4/_NTnpEoybRI/s320/Pic7.png






You may find blogs on changing the column name dynamically.

 I have to test this solution against an SA based on a relational database and perform multiple rounds of testing. Try it out - You may find some even more interesting facts. Good luck.

OBIEE 11.1.1 - How to Install Windows Services For OBIEE 11g AdminServer And BI Managed Server?


obiee-11g-auto-start-all-with-windows

OBIEE 11.1.1 - How to Install Windows Services For OBIEE 11g AdminServer And BI Managed Server?

Following Fusion Middleware 11g components are configured by default as MS Windows Services:
  • Weblogic Node Manager
    (configured as a MS Windows Service if Custom Install route is chosen when installing Weblogic)
  • FMW 11g Process Manager (OPMN)
    (installed as a MS Windows Service if a System Component Instance is configured)
This article shows how to install the WebLogic AdminServer and BI Managed Server as MS Windows Service for the OBIEE 11g installation.



a. For Administration Server
Note: If you install OBIEE 11g version 11.1.1.5 with the Simple Install type, then you will only have an AdminServer and not a BI Managed Server; therefore, the Managed Server section would not apply in that specific version and installation type.
1.   Configure the boot.properties to create an encrypted method to automatically pass the username and password

Create a text file in the location: 
Note: With OBIEE 11.1.1.5, if you have used the Start BI Services from the menu, then it would have automatically created a boot.properties file the first time it was invoke.
%middleware_home%\user_projects\domains\bifoundation_domain\servers\AdminServer\security\boot.properties 

For example, if the middleware_home is c:\BI11g, then the boot properties needs to be here:

C:\BI11g\user_projects\domains\bifoundation_domain\servers\AdminServer\security\boot.properties

Add the following lines:

username=[the Administrator username created during install, e.g weblogic]
password=[the weblogic username password e.g welcome1]
Note: the above credentials are the same as the one provided when the AdminServer is started from Command line.
Do not use or leave the brackets [ ] in the file. They are only used to show what should be entered.
As soon as you start the Admin Server the username and password values in this file will be encrypted
2.   Create a command script called installAdmServer_Service.cmd.

Open a text file and input the following lines [edit for your install]:  
echo off
SETLOCAL
 set MW_HOME=D:\Oracle\Middleware
set OPMN_PORT=9500
set wls.host=hostmane
set wls.admin.port=7001
set wls.mgd.port=9704
set wls.mgd.name=bi_server1
set BI_URL=http://IP:9704/analytics
set JAVA_HOME=D:\Oracle\Middleware\Oracle_BI1\jdk
set PRODUCTION_MODE=true
set DOMAIN_HOME=D:\Oracle\Middleware\user_projects\domains\bifoundation_domain
set WLS_HOME=%MW_HOME%\wlserver_10.3
set INSTANCE_HOME=D:\Oracle\Middleware\instances\instance1
set ORACLE_BI_HOME=D:\Oracle\Middleware\Oracle_BI1
set ANT_HOME=%MW_HOME%\modules\org.apache.ant_1.7.1
set DOMAIN_NAME=bifoundation_domain
set USERDOMAIN_HOME=D:\Oracle\Middleware\user_projects\domains\bifoundation_domain
set SERVER_NAME=AdminServer
call "D:\Oracle\Middleware\user_projects\domains\bifoundation_domain\bin\setDomainEnv.cmd"
call "D:\Oracle\Middleware\wlserver_10.3\server\bin\installSvc.cmd"
ENDLOCAL

      Please note that the middleware home values need to be specific to your install
3.   For troubleshooting / debugging purposes it is helpful to redirect standard out and error to a text file.
      Although most information is captured in the AdminServer server log files, you will not see all standard out and error when the server is started via a MS Windows Service (unlike when you start an AdminServer in console modefrom the command prompt using startWebLogic.cmd). To redirect standard out to a text file, backup and edit installSvc.cmd file and change the line at the bottom of the file so it includes the -log parameter. For example:
rem *** Install the service
"%WL_HOME%\server\bin\beasvc" -install
-svcname:"beasvc %DOMAIN_NAME%_%SERVER_NAME%"
-javahome:"%JAVA_HOME%"
-execdir:"%USERDOMAIN_HOME%"
-maxconnectretries:"%MAX_CONNECT_RETRIES%"
-host:"%HOST%"
-port:"%PORT%"
-extrapath:"%EXTRAPATH%" -password:"%WLS_PW%"
-cmdline:%CMDLINE%
-log:"D:\Oracle\Middleware\user_projects\domains\bifoundation_domain\AdminServer-stdout.txt"

4.     Next,  run "installAdmServer_Service.cmd" and the service should be installed, it will have a name like "beasvc %DOMAIN_NAME%_%SERVER_NAME%" (e.g. beasvc bifoundation_domain_AdminServer)  

The Service "Startup Type" will be 'Automatic'. Just like any other MS Windows Service you can change the 'Startup Type' to 'Manual'.

Start the Service. The Service will come back fairly quickly to say it is started. The actual time taken for AdminServer to start and reach a state of 'RUNNING' will be longer - perhaps two or three minutes. The state of the server can be monitored by reviewing the stdout txt file.

Notes: An alternative to the boot.properties approach to specifying the Admin Server weblogic username / password is to add the following environment variables to your wrapper cmd script - installAdmServer_Service.cmd
set WLS_USER=[user]
set WLS_PW=[password] 

Note: Do not use or leave the brackets [ ] in the file. They are only used to show what should be entered.  
To quote the documentation, "The beasvc utility encrypts the login credentials and stores them in the Windows registry."
This is one of two possible methods for avoiding the username/password prompt when a server instance starts. The disadvantage to this method is that changing the username or password for the server instance requires you to delete the Windows service and set up a new one with the new username and password. Instead of this method, you can use a boot identity file. With a boot identity file, you can change the login credentials without needing to modify the Windows service.
b. For Managed Server bi_server1  
1. Configure the boot.properties to create an encrypted method to automatically pass the username and password

Create a text file in the location:

%middleware_home%\user_projects\domains\bifoundation_domain\servers\bi_server1\security\boot.properties

For example:

C:\BI11g\user_projects\domains\bifoundation_domain\servers\bi_server1

where the BI11g is your middleware home.

Add the following lines:

username=[the Administrator username created during install, e.g weblogic]
password=[the weblogic username password e.g welcome1]
Note: the above credentials are the same as the one provided when the AdminServer is started from Command line.
As soon as you start the BI Managed Server the username and password values in this file will be encrypted.
The alternative approach - like for AdminServer - is to specify WLS_USER and WLS_PW in the wrapper cmd script. See next point

2.     Create a command script called installManagedSrvr_Service.cmd which has lines like:

SETLOCAL
set DOMAIN_NAME=bifoundation_domain
set USERDOMAIN_HOME=D:\Oracle\Middleware\user_projects\domains\bifoundation_domain
set SERVER_NAME=bi_server1
set PRODUCTION_MODE=true
set ADMIN_URL=http://IP:7001
set JAVA_HOME=D:\Oracle\Middleware\Oracle_BI1\jdk
set PRODUCTION_MODE=true
set DOMAIN_HOME=D:\Oracle\Middleware\user_projects\domains\bifoundation_domain
set WLS_HOME=%MW_HOME%\wlserver_10.3
set INSTANCE_HOME=D:\Oracle\Middleware\instances\instance1
set ORACLE_BI_HOME=D:\Oracle\Middleware\Oracle_BI1
set ANT_HOME=%MW_HOME%\modules\org.apache.ant_1.7.1
set DOMAIN_NAME=bifoundation_domain
call "D:\Oracle\Middleware\user_projects\domains\bifoundation_domain\bin\setDomainEnv.cmd"
call "D:\Oracle\Middleware\wlserver_10.3\server\bin\installSvc.cmd"
ENDLOCAL
Notes:
o    Before running, change the -log parameter in the installSvc.cmd file to the new location. Fore example:

-log:"[middleware_home]\user_projects\domains\bifoundation_domain\servers\bi_server1\bi_server1-stdout.txt"
o    The ADMIN_URL value should reference the AdminServer hostname and listen port
o    The SERVER_NAME value is case sensitive. For example, if you are creating a MS Windows service for a different managed server such as 'wls_ods1' then the value needs to match the case of the server name otherwise the startup of the server via the MS Windows service will fail.
o    Be careful that there are no trailing spaces after each line in the command file - trailing spaces will cause the managed server to fail at startup. For example a trailing space in the ADMIN_URL value will result in the error

<19-Jan-2010 11:37:58 o'clock GMT> <Error> <EmbeddedLDAP> <BEA-171524> <Cannot determine the Listen address for the Admin server
3.   Now run "installManagedSrvr_Service.cmd". The Service should be installed, it will have a name like "beasvc %DOMAIN_NAME%_%SERVER_NAME%" (e.g. - beasvc bifoundation_domain_bi_server1)

The Service "Startup Type" will be 'Automatic'. Just like any other MS Windows Service you can change the 'Startup Type' to 'Manual'.

Start the Service. The Service will come back fairly quickly to say it is started. The actual time taken for the managed server to start and reach a state of 'RUNNING' will be longer - perhaps two or three minutes. The state of the server can be monitored by reviewing the stdout txt file.
4.   Ensure you make the managed Server to a manual startup so this service can be started after the AdminServer is started.
5.  You may choose to start the managed server from the AdminServer instead of from creating the MS Windows service which is the recommended practice.
6.  First start the Node Manager, Admin Server and the Managed Server in this Order.
You can start the BI Server components either from FMW Control or from the OPMN Command line.
Note: If you start the servers as a service, then any parameters, such as JVM parameters need to be modified in the registry at:
If you want to view these parameters, open up the MS Windows Registry Editor (regedit) and navigate to:

HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\beasvc <your domain>_<managed server name>\Parameters

Be sure and double-check the parameter settings such as the the path for the JVM, the hostname and the port; otherwise the service may not start properly

Otherwise, the parameters can be modified in the command shell scripts

See the WebLogic documentation for installing the NodeManager as a service. 





For more Reference:

1) http://blogs.oracle.com/pa/entry/obiee_11_1_1_how3

2) How to Install MS Windows Services For FMW 11g WebLogic Domain Admin and Managed Servers? [ID 1060058.1]
3) How to Install Windows Services For OBIEE 11g AdminServer And Managed Server [ID 1214693.1]



5) http://deliverbi.blogspot.com/2010/10/obiee-11g-auto-start-all-with-windows.html

6)http://download.oracle.com/docs/cd/E14571_01/web.1111/e13708/winservice.htm#i1186180

7) How to Install MS Windows Services For FMW 11g WebLogic Domain Admin and Managed Servers? [ID 1060058.1]