Oracle BI Cloud Service : What is it and Where Will it be Useful? Francesco Tisiot, Principal Consultant, Rittman Mead OUG Ireland 2015, Dublin


Full text


Oracle BI Cloud Service : What is it and

Where Will it be Useful?


About the Speaker

Francesco Tisiot

Principal Consultant at Rittman Mead

Based in Verona, Italy

Writer for Rittman Mead Blog

Over 7 years of experience in Oracle technology





About Rittman Mead

Oracle BI and DW Gold partner

Winner of five UKOUG Partner of the Year awards in 2013 and 2014 - including BI

World leading specialist partner for technical excellence, 

solutions delivery and innovation in Oracle BI

Approximately 80 consultants worldwide

All expert in Oracle BI and DW

Offices in US (Atlanta), Europe, Australia and India

Skills in broad range of supporting Oracle tools:




Oracle Cloud Business Intelligence

BICS v1 available since September 2014

Part of the wider PaaS (platform-as-a-service)

offering including database, Java, documents etc

Aimed at a very specific set of use-cases initially

Adding BI and analytics to cloud-based apps


Oracle Cloud Business Intelligence - What is it

Runs in Oracle’s public cloud environment (

No upfront license cost

Pay monthly as OpEx

Only reports against a single Oracle database 

schema (Oracle Cloud Schema-as-a-Service)

“Standard” OBIEE - but will possibly preview new

functionality before on-premise versions

Patched automatically by Oracle

Customer able to be max one version behind?

Comes with two instances - pre-prod and prod

Code promotion through BI Archive export/import

Cloud On-Premise

Admin access
 over HTTP

Oracle Schema-as-a-service
 Database (single source) Oracle BI-as-a-Service


Oracle Cloud Business Intelligence - What’s new?

New component: Thin Client Data Modeller

Replacement for OBIEE On Premise Admin


Simplifies Metadata Layer creation and


Accessible via browser

No need of Windows only client

Contains a subset of the features


Oracle Cloud Business Intelligence - What is it

Oracle Schema-as-a-service
 Database (single source) Oracle BI-as-a-Service

Dashboards and Analysis

Administration and Maintenance Data Modeler Oracle Database Schema + Application Express


Oracle BI Cloud Services v.1


Logging In and Providing Identity Domain

Login separate to login; provided during Cloud service provisioning process


Oracle Business Intelligence Cloud Service : Dashboard

View basic metrics on system usage 

(users, author count over period)

Subscription details; Company name etc

Click on Service Instance URL 

to launch OBIEE Presentation Services


Oracle BI Cloud Services v.1


BI Cloud Service Home Page and Dashboards

Answers and Dashboards (with new Oracle Cloud look-and-feel)


Standard Dashboard and Analysis Editor Experience

Regular Presentation Services interface - Dashboard, Analysis Editor, Catalog view etc

All data visualisations available + preview of new visualisations


Dashboard and Analysis Limitations in v1

Can create analyses, dashboards, use filters, selection steps, calculations etc

Can create dashboard prompts, filters, use repository and session variables etc

No access to Agents, Scorecards, BI Publisher (anything that uses NQ_ tables)

Only Navigation Actions

No Essbase, SOA / EBS integration etc


Oracle BI Cloud Services v.1


Oracle Database Cloud Service

Each instance of BICS comes with Oracle Database Cloud Service (Schema-as-a-Service)

ApEx environment with 50GB storage attached

Able to run PL/SQL packages (with 5m timeout)

Either create tables, views etc from ApEX,

or use data uploader in BICS

Click to launch 
 ApEX Home Page


ApEx Development Environment

Full ApEx environment for application building, table creation, SQL queries

Can be used to build supporting applications, administration screens for OBIEE application

Make use of PL/SQL functionality

Data cleansing

Call Web Service REST APIs

More complex processing

and calculations

DB Cloud Service dashboard has tools

for data and application exports


Options for Uploading Data to BI Cloud Service (DB Cloud)

Use ApEx front-end and tools within it (data upload, data modeller etc)

Use SQL*Developer and SFTP data upload facility


BI Cloud Services Data Uploader

Custom ApEx application shipped with DB Cloud Service for BICS

Upload CSV, text, xls and xlsx files to Database Cloud

Paste data from clipboard and upload

Limited data re-formatting features

Preferred method for uploading data

Click to launch 
 Data Uploader


BICS Data Uploader Step 1 : Select File to Upload

Press Upload to upload txt, csv, xlsx or xls file to application

Contents of file then appear in

Preview window

Or just paste data from clipboard

into same preview window

Option to ignore headers line


BICS Data Uploader Step 2 : Select Target or New Table

Either select existing table to upload to, or enter details for new table

Incoming columns in file mapped to table columns


BICS Data Uploader Step 3 : Preview Content, and Upload

Preview file contents with transformations and final column names

Upload file to Database Cloud Service - is the used to load a regular DB table


Oracle BI Cloud Services v.1


Thin-Client Data Modeller

New thin-client data modeller, works with RPD online

Single repository per tenant

Regular RPD, but modeller only supports 

subset of BI Administration features

Single LTS per logical table

No Alias, have to create DB views

Single business model

Only logical calculations

But otherwise - an RPD

Click to launch 
 Data Modeller


Thin-Client Data Modeller - New Features since v1

Pre-Post Aggregation calculations

No concept of Physical and Business Model in Data Modeller

Expressions with indirectly joined tables

Cross Fact calculations based on shared dimensions

Creation of Aliases

Tables and Views are duplicated

Same object can be used in multiple roles

Auto suggest Fact and dimensions


Data Model Overview

List of Tables and Views available in DB

List of Fact Tables and Dimension

Number of KPI and joins highlighted

List of Joins

Lock and Publish buttons

List of Columns


Data Modeller Repository Creation Workflow

1. Add tables to Database Cloud Service via

data uploader

2. Lock Model (RPD)

3. Select either a dimension or fact to create

4. Select table to base fact or dimension on

5. Format logical column names, values

6. Add calculations, aggregations

7. Add hierarchies

8. Add security


Create Fact and Dimensions from Flat Table

Facts and Dimensions can be created directly in Data Modeller from Flat Tables






Can create static and dynamic repository variables

Also create session variables, including access to system session variables


Oracle BI Cloud Services v.1


Manage Users and Application Roles

Click on Manage link on Homepage, launches BICS Service Console

View users, assign to roles

Map roles to application roles defined by tenant administrator

Click to launch 


Snapshots (BI Archives)

Entire system (RPD, catalog) can be snapshotted with versions stored online

Download and upload snapshots

Test to production migration

Restore snapshots for versioning

Note - overwrites entire RPD, catalog

Does not include DB Cloud elements,

need to be archived separately


Oracle BI Cloud Services v.1


Oracle BI Cloud Service - Use Cases

Departmental sandbox-type analysis

Data Upload from On-Premise DB, Local XML or CSV files

Metadata layer created from Flat tables

Analysis/Dashboards created in minutes

No need of IT involvement

Adding custom BI and analytics to cloud-based apps


Oracle BI Cloud Services v.1


Example Application #1 : Cloud CRM Reporting - Scenario

Many customers use SaaS applications for CRM, sales automation, talent management etc

Often silos of information, and hard to report against

Access via REST APIs rather than SQL / ETL

BI Tools needed, but prefer if SaaS deployed too

Solution : Oracle BI Cloud Services,

but how to access SaaS data sources?


SaaS Data Access Solution : DB Cloud PL/SQL REST Access

Database Cloud Service supports PL/SQL packages, procedures

ApEx has APEX_WEB_SERVICE API, supports RESTful and SOAP web service calls provides REST API for retrieving objects, data

PL/SQL package retrieves

Salesforce instance data in XML

Stages into Database Cloud

Schema for OBIEE access

Need to work around 

5 min timeout for PL/SQL prods

DB Cloud limitation

Break retrieval into 

BEGIN apex_web_service.g_request_headers(1).name := 'Accept'; apex_web_service.g_request_headers(1).value := 'application/xml'; l_clob := apex_web_service.make_rest_request( p_url => ' grant_type=password&client_id=' ||clientId||'&client_secret='||clientSecret||'&username='||username|| '&password='||password, p_http_method => 'POST' , p_body => '');

XMLData := XMLType(l_clob); FOR r IN



Build Repository Against Database Tables

PL/SQL Package calling REST API populates Oracle tables

Oracle Tables then mapped into BI repository

Facts and dimensions created

Variables defined


Supporting SaaS Security Models

Superuser full import of SaaS dataset

Full load of Data in DB

Apply SaaS security settings in BICS

User related data retrieval on the fly

Connection Parameters passed to REST-API call

Table Functions called on the fly

Analysis response time slower

REST-API call limits

Superuser REST-APIs Database

Security Managed in

Repository Front-End


Supporting SaaS Security Models

Full import of SaaS dataset presents the problem - how do we then respect app security?

Users will connect into OBIEE as themselves, no online access to SaaS security

Solution : download user > account security objects,

join those to facts to perform row-level filtering

Needs knowledge of underlying

SaaS row-level security setup

Join security access table to model

to apply data restrictions

But then allows BICS app to work

disconnected from SaaS application,

but still apply correct security rules


Rittman Mead - BICS Application

Enter Connection Detail and

Choose Full/Incremental Load

Create BICS Users and

Assign to Salesforce Users View/Build Dashboards and Analysis using Salesforce Security Settings


Details REST-APIs are common for all applications

Objects Metadata REST-APIs calls available

Mapping of Objects->Tables can be automated

Data retrieved in XML can be natively parsed in the Oracle DB11g

Oracle DB12c can also parse JSON

ApEx Application used to

Load Data

check PL/SQL Full/Incremenal Load


Thank You for Attending!

Thank you for attending this presentation, and more information can be found at http://

Contact us at or

Look out for our book, “Oracle Business Intelligence Developers Guide” out now!


Oracle BI Cloud Service : What is it and

Where Will it be Useful?



Related subjects :