Thursday, April 4, 2019

OAC―Working with Oracle BI Administration Tool

Video 1.  Create Simple Repository in OBIEE 12C

Video 2.  Working with Oracle BI Administration Tool

Administrators can upload data models built with Oracle BI Enterprise Edition (OBIEE) to Oracle Analytics Cloud (OAC). After uploading a data model file (.rpd) to the cloud, content authors can then build data visualizations, dashboards and analyses in the usual way.[1]

In this article, we will cover below three topics:
  1. Use the Administration Tool to view and modify the BI Repository
  2. Describe the different layers of the BI Repository
  3. Upload Data Models from a File (.rpd) Using OAC Console
To cover these topics, we will begin with Oracle BI Administration Tool in OBIEE (watch video 1&2); then we will switch to OAC to discuss how to upload a data model file (.rpd or BI repository) from OBIEE to the cloud.[1]

Figure 1.  Physical / Business Model and Mapping / Presentation (3 layers in BI Repository)

Figure 2.  How Administration Tool interacts with Repository and Oracle BI Server

Oracle BI Repository (OBIEE)

To view and edit the different layers of repository (see Figure 1), you use the Oracle BI Administration Tool.  The Administration Tool client is a Windows application that you can use to create and edit your Oracle BI repository. The Administration Tool can connect directly to the repository in offline mode, or it can connect to the repository through the Oracle BI Server. Some options are only available in online mode. See Using Online and Offline Repository Modes.

In Figure 2, it shows how the Oracle BI Administration Tool interacts with Oracle BI Server, data sources, and the Oracle Business Intelligence (BI) repository.   The Oracle BI repository contains the metadata that defines which columns you can include in analyses, and the source of that data. The repository is organized into subject areas, which contain folders with the columns.

Figure 3.  Import Metadata for the Physical Layer ― Select Data Source
Figure 4.  Mapping between the business model (or logical schemas) and the physical schemas


Layers in the Oracle BI Repository define the objects and their relationships.  An Oracle BI Repository has the following layers  (see Figure 1):
  1. Physical
    • Defines the objects and relationships that the Oracle BI Server needs to write native queries against each physical data source.
      • Include metadata representation of the physical data sources (Note that no data are included)
    • You create this layer by importing tables, cubes, and flat files from your data sources.
      • You can use Import Metadata from the File menu and setup data sources with appropriate connection information (see Figure 3).
    • Separating the logical behavior of the application from the physical model:
      • Provides the ability to federate multiple physical sources to the same logical object, enabling aggregate navigation and partitioning, as well as, dimension conformance and isolation from changes in the physical sources. 
      • Enables the creation of portable Oracle BI Applications.
  2. Business Model and Mapping
    • Specifies the mapping between the business model and the physical schemas.
      • Determines the analytic behavior seen by users, and defines the superset of objects and relationships available to users. 
      • Hides the complexity of the source data models.
    • At run time, the Oracle BI Server 
      • Evaluates Logical SQL requests against the business model
      • Uses the mappings to determine the best set of physical tables, files, and cubes for generating the necessary physical queries. 
        • The mappings often contain calculations and transformations, and might combine multiple physical tables.
    • In each logical schema
      • There is an extra folder named "Sources" which point to the physical schemas that the logical schema is coming from.
        • You can have multiple physical schemas (i.e. CUSTOMERS & COUNTRIES) in the Sources folder (see Figure 4).
      • Each column in business model maps to one or more columns in the Physical layer.
  3. Presentation 
    • The users view of the business model 
      • Adds a level of abstraction over the Business Model and Mapping layer
      • Provides a way to present customized, secure, role based views of a business model to users. 
    • The repository in presentation layer is organized into subject areas.  After you upload BI repository to the cloud (see the section below for more details), new subject areas will be shown in Analyses of OAC (see Figure 5&6).
Figure 5.  Creating a new Analysis

Figure 6.  Select Subject Area

Business Intelligence (BI) Repository

You use Oracle BI Administration Tool to view and modify the repository.  After finish, you can save the metadata into a data model file (the RPD file). Administrators can then upload data models built with OBIEE to Oracle Analytics Cloud (OAC).instead of using Data Modeler to build them from scratch.

The business intelligence (BI) repository contains the metadata that defines which columns you can include in analyses, and the source of that data. The repository is organized into subject areas:
  • Which contain folders with the columns (see Figure 7)
    • For example, Region column is in the Geography folder which, in turn, is in SampleApp subject area
  • Have one fact folder and a number of dimension folders. 
    • Folders can have subfolders.
When you create an analysis (see Figure 5), you do:
    • Select a subject area (see Figure 6) which contains columns related to a specific business object or business area. 
      • For example, SampleApp (see Figure 7).
    • Then open folders within the subject area to find the columns to include in your analysis.
      • For example, you can open the Geography folder and select the columns within it.
    • You can watch video 3 for more details.
Figure 7.  Selecting  Region column within the Geography folder

Video 3.  Get Started with Analyses and Dashboards in OAC

Get Your Data Model File Ready

Before you upload data model file to Oracle Analytics Cloud, take some time to ready your data model for the cloud:[2]
  1. Verify that you’re using Oracle BI Enterprise Edition or later.
  2. Validate the data model file (the RPD file).
    • Run consistency checks using Consistency Check Manager in Oracle BI Administration Tool.
  3. Verify that the data model file includes connection details to Oracle Database Cloud Service.
    • Review the connection pool settings from Oracle BI Administration Tool:
      • Data source name must contain the full connect string for the Oracle Database Cloud Service where data is stored.
        • You can’t specify a net service name here.
      • Call interface must be Oracle Call Interface (OCI).
    • If your data model file connects to multiple Oracle Database Cloud Service instances, ensure that the settings for each connection pool are correct.
  4. Disable subject areas that you don't want to expose or that don't have a working connection.
    • If connection information is missing, users see the message Fetch subject areas failed error when they view subject areas in Oracle Analytics Cloud.
  5. Back up your Oracle Analytics Cloud service, including the current data model, in case you need to restore this version.
When the data model is ready, you can upload it to Oracle Analytics Cloud.

Figure 8.  Replace Data Model 

Upload Data Models from a File (.rpd) Using Console

When you upload data models from OBIEE, you delete existing data model information in Oracle Analytics Cloud and replace it with content in the data model file (.rpd). The data models you upload become available to content authors as subject areas.

You can also upload data model files from a file (.rpd) using Console in OAC:[3]
  1. Verify the data model file (.rpd) and associated database connections.
  2. In Oracle Analytics Cloud, click Console.
  3. Select Service Administration - Manage Snapshots.
  4. Take a snapshot of the current data model in case you need to restore this version (watch this video).
  5. Click Replace Data Model (see Figure 8).
  6. Click Browse and select the data model file (.rpd) that you want to upload.
  7. Enter the password for the file.
  8. Click OK.
  9. Go to the Home page, click Data, and then Data Sets to see the data models that you uploaded, available as subject areas.
  10. Optional: If the data model file includes permissions and data filters, create matching application roles in Oracle Analytics Cloud for the data security to work in the cloud.
    • Create application roles with exactly the same names as those defined in Oracle BI Administration Tool.
    • Assign users (and user roles) to the application roles as required.

Saturday, March 30, 2019

OAC―Knowing the Dimensional Modelling Basics (2/2)

Video 1. Create your initial data model from Relational Sources using Data Modeler

Video 2.  Create Time Dimension Tables Using Data Modeler

In a previous article "OAC―Knowing the Dimensional Modelling Basics (1/2)", we have covered basic concepts of dimensional modeling:
  • Transactional versus Analytical Systems
  • Data Warehousing
  • Dimensions, Facts and Hierarchies
In this article, we will cover how Dimensional Modelling works in Oracle Analytics Cloud (OAC).

Figure 1.  Open Data Modeler in Oracle Analytics Cloud by clicking the action menu

Data Modeler

Using Data Modeler (see Figure 1) you can model data from various source types, such as star and snowflake, in various ways that make sense to business users.
Although not all source objects have star relationships, Data Modeler presents data as a simple star structure in the data model. In other words, the data model represents measurable facts that are viewed in terms of various dimensional attributes.
If you modeled your business data with Oracle BI Enterprise Edition, you don't have to start from scratch with Data Modeler. You can upload your data model to the cloud.[14]
Figure 2.  Left Pane in Data Modeler

Figure 3.  Action menu associated with Objects in Data Modeler

Figure 4. Create Data Model in OAC

Video 3.  Create Hierarchies for Dimension Tables Using Data Modeler

Data Modeling Tasks

When building a data model in OAC, you perform the following tasks:[3]
  • Connect to the database containing your business data
    • In Data Modeler, the left pane shows the Database, Data Model, Variables, or Roles menu (see Figure 2).
      • Action menus contain actions that are relevant for a particular object or context, and are visible when the object is selected.
      • For example, if a source object in the Database menu is selected, its action menu shows: Add to Model, Inspect, and Duplicate (see Figure 3).
  • Add source tables or views to the model and classify them as either a fact table or a dimension table
    • After you have started modeling data, the right pane in Data Modeler shows the fact tables, dimension tables, and joins (see Figure 4)
      • The right pane in Data Modeler is a contextual pane that changes depending on what task you’re performing. 
  • Define joins between fact and dimension tables 
  • Ensure Correct Mapping 
    • Every dimension table maps to at least one fact table, and that every fact table maps to at least one dimension table. 
  • Edit object with its editor
    • Specify aggregation rules for different fact columns
    • Create derived measures based on expressions
    • Create dimension hierarchies to support drilling
      • See video 3
    • Create level-based measures
  • Publish data model 
    • To permanently save the changes and make the data available for use in analyses
      • When your HTTP browser session times out (after 20 minutes of inactivity), the lock is released and any unpublished changes are discarded.
      • Similarly, closing a browser ends the HTTP session and discards any unpublished changes. 

Figure 5.  Create project in Oracle Analytics Cloud to use newly published data model (see video 1&2)

Figure 6.  Published Cust Rev Model displayed as a Subject Area

Visualizations / Dashboards / Analyses

After publishing your data model, you can start visualizing your data from your enterprise reporting Home page (see Figure 5 and watch video 1). Your data model displays as a subject area that you can use in visualizations, dashboards, and analyses (see Figure 6). The name of the subject area matches the name of your data model.

When you model source objects with multiple star relationships, they’re all part of the same data model and are included in the same subject area. 

Video 4.  Manage What Users Can See and Do in Oracle Analytics Cloud

Figure 7.  Use Console to create and manage application roles

Figure 8.  Configure application roles from the Administer Users and Roles page in the Console

Figure 9.  Application Roles tab shows predefined and custom application roles

Figure 10.  Add new members or remove members from an application role

Application Roles

You must have the BI Data Model Author role (including table access permissions) to author data models.

Predefined Application Roles in Oracle Analytics CloudDescriptionDefault MembersPredefined Application Role in Oracle Identity Cloud Service Foot 1
BI Service Administrator
Allows users to administer Oracle Analytics Cloud and delegate privileges to others using the Console.
Administrator who created the service
BI Data Model Author
Allows users to manage data models in Oracle Analytics Cloudusing Data Modeler
BI ServiceAdministrator

Administrators configure what users see and do in Oracle Analytics Cloud from the Administer Users and Roles page in the Console (see Figure 7&8). This page presents user information in 3 different views:

Users and Roles PageDescription
Users tab
Shows users from the identity domain associated with your service.
You can add users, delete users, and assign users one or more application roles in Oracle Analytics Cloud.
Roles tab
Shows roles from the identity domain associated with your service.
You can add and remove roles (groups of users), and assign them to one or more application roles in Oracle Analytics Cloud.
From the Roles tab you can also see who belongs to each role.
Application Roles tab
Shows predefined application roles for Oracle Analytics Cloud together with any custom application roles you define.
From the Application Roles tab you can assign application roles to multiple users, roles, and other application roles. You can also create application roles of your own and assign privileges to them through other application roles.

You select members for an application role or change parent privileges using the Console.
  1. Click Console (see Figure 7).
  2. Click Service Administration, and then click Administer Users and Roles (see Figure 8).
  3. Click the Application Roles tab (see Figure 9).
  4. To display all available application roles, leave the Search field blank and Show Members: All.  To filter the list by name, enter all or part of an application role name in the Search filter and press Enter. The search is case-insensitive, and searches both name and display name.
  5. Look in the Members area to see who belongs to each application role:
    • The number of users, roles, and application roles that are members displays on the page. Click a number, such as 5 in this image, to see those members in more detail (either users, roles or application roles).
  6. To add new members or remove members from an application role (see Figure 10):
    1. Click Members.
    2. Select either users, roles, or application roles from the Type box and click Search to show the current members.
    3. Use the shuttle controls to move members between the Available and All Selected list.
      • Some application roles aren't eligible to be members and these are grayed. For example, you can’t select a parent application role to be a member.
    4. Click OK.
  7. To see whether an application role, such as Sales Analyst, inherits privileges from other application roles:
    1. Click the action menu.
    2. Select Manage Application Roles.
  8. To add or remove privileges:
    1. Click Search to display all available application roles.
      • Alternatively, enter all or part of an application role name and click Search.
    2. Use the shuttle controls to move application roles between the Available Application Roles list and the Selected Application Roles list.
      • You can’t select application roles that are grayed out. Application roles are grayed out so you can’t create a circular membership tree.
    3. Click OK.
Figure 11.  User Ed Ferguson was assigned with the Sales Analysts application role


  1. OAC―Knowing the Dimensional Modelling Basics (1/2)
  2. Model Data for Reports (OAC)
  3. Use Data Modeler (OAC)
  4. Oracle® CloudPreparing Data in Oracle Analytics Cloud
  5. OAC―Knowing Machine Learning Basics
  6. Data Visualization (Forum)
  7. Oracle Data Visualization Desktop (Documentation)
  8. OAC―Loading Data with Data Sync
  9. Edit Hierarchies and Levels (OAC Data Modeler)
  10. Get Started with Oracle Data Visualization V5 (YouTube)
  11. Explore Data in Oracle Data Visualization V5 (YouTube)
  12. Create Data Flows in Oracle Data Visualization V5 (YouTube)
  13. Data Science for Business (Safari)
  14. Upload Data Models from Oracle BI Enterprise Edition
  15. Learn Modern Data Visualization with Oracle Analytics