BICS development life-cycle

Within an on-premise scenario, the OBIEE development life-cycle was quite simple:

  1. connect to a data warehouse and import the warehouse tables (metadata import to physical layer)
  2. build OBIEE metadata model (RPD)
  3. upload the RPD into Weblogic using the Deployment screen

Within a cloud scenario the BICS development life-cycle is quite the same, but requires some additional steps and some decisions on the development model: either using web-based BICS developer or upload of full RPD file developed using OBIEE Admin Tool.

While it’s possible to develop the complete metadata model within the cloud using the BICS web-based Modeller, like below:

image

the capabilities of this web-based Modeller are more limited. Due to this there is no option to edit or change the uploaded RPD file within the web-based BICS modeller afterwards. The process to upload a locally developed (OBIEE) metadata model (.rpd file) to the BICS Cloud is called “lift&shift”.

The required setup for this OBIEE to BICS development life-cycle using lift & shift is described in below diagram:

image

For the BICS development life-cycle setup the data of the Fusion Cloud e.g. HCM or ERP is assumed to be already present within a Oracle Database Cloud (DBaaS/DBCS). For this setup all components (formally probably referred to as “server” components) are provisioned on the Oracle Public Cloud behind a firewall and only the IT Development Tools (IDE) are installed on a developer machine like a desktop or laptop.

The following integrations need to be setup:

Integration point Integration point Description documentation/links
BICS DBaaS Connect the BI Cloud (BICS) to the Database Cloud (DBaaS) to analyse the stored data e.g. extracted Fusion HCM data How Does Oracle BI Cloud Service Integrate with Oracle Database Cloud Service?

Managing Database Connections

(local) SQL Developer DBaaS Connect the local SQL Developer e.g. installed on a Computer or Laptop to the Database Cloud (DBaaS) Accessing a Database Cloud Service instance using Oracle SQL Developer

Connecting to a Database Cloud Service (DBaaS) Instance Through an SSH Tunnel

(local) BI Admin Tool  (RPD file) BICS upload the local developed .rpd file (using the OBIEE Admin Tool) into the BI Cloud (BICS) Uploading Data Models from Oracle BI Enterprise Edition

“Lift and Shift” On-Premise RPD to BI Cloud Service (BICS)

How to Upload OBIEE RPD to Oracle BICS

BICS Lift and Shift of BIAPPS Content

To create Backup as part of the development life-cycle refer to the following article:

BIAPPS on PAAS – Backup and Restore – Introduction

To connect between DBaaS and the Fusion Application Cloud (SaaS):

BIAPPS on PAAS – Source Connectivity

The above scenario uses the Oracle Database Cloud for storing the data. If the Data is not stored in the Oracle Database Cloud, but the Oracle Schema Cloud Service which is bundled for-free with BICS then the data must be loaded into BICS using the BICS Data Sync tool:

image

Within the all above scenario the (“server”) components are provisioned on the Oracle Public Cloud. For a scenario where some components like the Database still reside on-premise the following tool needs to be installed on the server hosting the Oracle Database:

image

as described in this article from Oracle.

Advertisements

Oracle BI12c: placing custom images in BI Server and reference using fmap

For Oracle BI 12c custom content such as images or pdf help files needs to be placed in the following directory:

/domains/bi/servers/obips1/tmp/earmanager/analytics/../res/

where /../ is a dynamic folder name such as v-PLM12aFeu7Y.

Within a Report or Dashboard this path may be referenced using the following syntax:

fmap:file_name

or:

fmap:foldername/file_name (if a subfolder “foldername” was created below /res)

the below links provide further information on using fmap or placing custom resources within Oracle BI:
Storing Custom Files Locally and Using the fmap Function to Reference Them
How to configure static content in OBIEE 12c

changing the default data type of number formats

to change the default format used by the Oracle BI EE 11g the following configuration file needs to be changed:

/home/oracle/Middleware/instances/instance1/bifoundation/OracleBIPresentationServicesComponent/coreapplication_obips1/catalog/SampleAppLite/root/system/metadata

image
E.g. for two digits behind the number separator:

<dataFormat minDigits="2" maxDigits="2" commas="true" 
negativeType="minus" xsi:type="saw:number";

After saving and restarting the BI Server, the numbers are displayed with two digits behind the number separator.

Examining OBIEE 11g Presentation Service Privileges and precedence

The Official Documentation for the 11g Oracle Business Intelligence (Security Guide) mentions the following key rules about the evaluation of the Presentation Service Privileges:

“Presentation Services privileges control the rights that users have to access the features and functionality of Presentation Services. Privileges are granted or denied to specific application roles, individual users, and Catalog groups using a privilege assignment table.”

The Presentation Service privileges can be access from the following URL: http://hostname:port/analytics/saw.dll?PrivilegeAdmin

image

“privileges are either explicitly set or are inherited through role or group membership, explicitly denying a privilege takes precedence over any granted, inherited privilege. Meaning that even if Application roles are nested and thus forming a hierarchy any denied privilege takes precedence over inherited grants or directly assigned grants.”

Additionally, the following example is provided within the D. Appendix section of the same Documentation page:

inherit_dl_privscatgrp

With the following statements regarding the evaluation:

    • User1 explicitly has the Executive role, and thus implicitly has Finance role and also Sales role.
    • User1 also explicitly has the BI Author role, and thus also implicitly has BI Consumer role.
    • User1’s flattened list of application roles is: Executive, BI Author, Finance, Sales and BI Consumer.

The effective privileges from Executive Role are Denied Administration privilege […] the Sales’ Denied Administration privilege takes precedence over Executive’s granted privilege, as Deny always takes precedence.”

As a showcase, the above has example has been re-created in a simplified version with all Application roles and the Presentation Service privilege for “Access to Administration” within the 11.1.1.7 Oracle BI Version:

image

The five Application roles of the above diagram have been configured within the Enterprise Manager (EM) Application role configuration:

image
All five Application roles are also correctly displayed for the User1 within the Oracle Business Intelligence “My Account” view:
image
Afterwards, the privileges have been configured (with Granted and Denied) for the Application roles within the Presentation Service privileges Administration page:image
However, contrary to the rule and the example the “Administration Page” privilege is not denied:
image

The “Administration Page” privilege is granted for User1 due to the Membership of the Executive Application Role.

For testing purposes the five Application roles of the example have been enhanced to the following six:

image

With the following test cases and results:

# User Privilege Executive Finance Sales Sales North Result
1 User1 Admin
Page
Granted / Denied / Granted
2 User1 Admin
Page
/ Denied Granted / Denied
3 User1 Admin
Page
Denied Granted Granted / Denied
4 User1 Admin
Page
/ Granted / Denied Denied
5 User1 Admin Page / Denied / Granted Denied
6 User1 Admin Page / Granted Denied / Denied

deriving the following rules for the determination of the Presentation Service privileges:

  • if a privilege is set (either with denied or granted) for a Application role directly assigned (member of/next) to the User this takes precedence over everything else (see #1+#3)
  • for siblings on the level (within the same hierarchy level) the more restrictive is applied (#2+ #6)
  • In case of inherited ancestor privileges the more restrictive is applied (#4+#5)

Links to the 11g Documentation:

Example of Determining a User’s Privileges with Application Roles 11.1.1.7

Example of Determining a User’s Privileges with Application Roles 11.1.1.9

Example of Determining a User’s Privileges with Application Roles 12.2.1.1

Oracle BI 12c: File locations

important file locations for Oracle BI 12c:

module location
scripts directory /opt/oracle/obiee/user_projects/domains/bi/bitools/bin
Repository (RPD) /opt/oracle/obiee/user_projects/domains/bi/bidata/service_instances/
ssi/metadata/datamodel/customizations
Web Catalog /opt/oracle/obiee/user_projects/domains/bi/bidata/service_instances/
ssi/metadata/content/catalog
NQSConfig.INI /opt/oracle/obiee/user_projects/domains/bi/config/fmwconfig/biconfig/
OBIS/
instanceconfig.xml /opt/oracle/obiee/user_projects/domains/bi/config/fmwconfig/biconfig/
OBIPS/
tnsnames.ora /opt/oracle/obiee/user_projects/domains/bi/config/fmwconfig/
bienv/core
AdminServer log /opt/oracle/obiee/user_projects/domains/bi/servers/
AdminServer/logs
Managed Server logs /opt/oracle/obiee/user_projects/domains/bi/servers/
bi_server1/logs
BI Server logs /opt/oracle/obiee/user_projects/domains/bi/servers/obis1/logs
Presentation server logs /opt/oracle/obiee/user_projects/domains/bi/servers/obips1/logs

define alias for starting/stopping Oracle BI

For starting and stopping of Oracle BI 11g the OPMN services was used. The usage of OPMN for starting and stopping the Oracle BI 11g services has also been described in a previous posting.

With Oracle BI 12c, OPMN is not used further anymore. The scripts to start Oracle BI can be found in the following directory:

/opt/oracle/obiee/user_projects/domains/bi/bitools/bin

image

To start the Oracle 12c instance the following command can be used:

./start.sh

image

Within linux a alias can be created to call the start script without navigating to the directory each time. To create the alias the bashrc needs to be edited using the following command:

gedit ~/.bashrc

image

this opens the bashrc file using the gedit editor to add the following lines at the bottom of the file:

alias bistart='/opt/oracle/obiee/user_projects/domains/bi/bitools/bin/start.sh'
alias bipsstart='/opt/oracle/obiee/user_projects/domains/bi/bitools/bin/start.sh -i obips1'
alias bistop='/opt/oracle/obiee/user_projects/domains/bi/bitools/bin/stop.sh'
alias bipsstop='/opt/oracle/obiee/user_projects/domains/bi/bitools/bin/stop.sh -i obips1'
alias bistatus='/opt/oracle/obiee/user_projects/domains/bi/bitools/bin/status.sh'

image

after this the file needs to be saved and the bashrc needs to be executed to take effect:

. ~/.bashrc

image

starting selective BI Services with OPMN

OPMN allows to control all BI 11g Services including starting and stopping.

The installer often installs two executables into the directory structure:

image

However only one is typically working right after the installation, the other will throw the following error message:

opmnctl: ORACLE_INSTANCE is not set.
opmnctl status requires that ORACLE_INSTANCE be correctly set 
in your environment.
Make sure you are using ORACLE_INSTANCE/bin/opmnctl, or set 
ORACLE_INSTANCE in your environment.

image

In this case the working opmn is installed in the following directory:

/home/oracle/Middleware/instances/instance1/bin

image

the status can be check with the following command:

./opmnctl status

image

To start an individual service the following command needs to be used:

./opmnctl startproc ias-component=coreapplication_obips1

image

After this, the Oracle BI Presentation Service is up alive.

fixing: Log Could Not Be Retrieved error

After trying to retrieve the SQL log, the BI Server showed the following Error message:image

Error
Log Could Not Be Retrieved

Odbc driver returned an error (SQLExecDirectW).
Error Details
Error Codes: OPR4ONWY:U9IM8TAC
State: HY000. Code: 10058. [NQODBC]
[SQL_STATE: HY000] [nQSError: 10058]
A general error has occurred. [nQSError: 43113]
Message returned from OBIS. i (HY000)

However, the loglevel was specified correctly for the login user and the server also correctly produced the respective Query log file on the server in the path:

/home/oracle/Middleware/instances/instance1/diagnostics/logs/
OracleBIServerComponent/coreapplication_obis1/nqquery.log

image

But, the file permission hasn’t been set correctly:

image

Instead of rw- for the owner it should have been rwx for the owner. After changing the permission using:

chmod 0744 nqquery.log

image

The query logs could also be retrieved from the web Browser again:

image

Correct syntax to access variables from Oracle BI Answers

For debugging it can be useful to access session variables of the Oracle BI Server. E.g. to display the current login user Id and the current loglevel of that user:
image

The syntax is not everywhere the same in the Oracle BI Server. The correct syntax to access the variables from Answers is the following:
image

current user: @{biServer.variables['NQ_SESSION.USER']} 
current loglevel: @{biServer.variables['NQ_SESSION.LOGLEVEL']}