Monthly Archives: August 2017

Fun with Oracle Analytics Cloud (OAC): Creating Essbase Cubes

August 16, 2017

Author: Andrew Tauro, Performance Architects

Just like there are multiple ways to skin a cat, there’s more than one way to create an Essbase cube in Oracle Analytics Cloud (OAC). While the best way to migrate on-premise Essbase cubes to OAC is to use the standalone “EssbaseLCMUtility” tool, to create cubes from scratch there are three ways that I have used so far: use the Web UI; build an Application Workbook by hand (or from a template); or use the Cube builder. The latter two are the focus of this blog.

The Application Workbook is essentially a Microsoft Excel workbook that contains a predefined set of tabs, with the contents arranged in a predetermined manner. What that means is the workbook has a bunch of tabs like this:

Each of these tabs serves a particular purpose, but from what I can tell only the first two are a “must” when creating the application:

The “Essbase.Cube” worksheet defines the application and database names, which are required information when creating a cube. In addition, this sheet is used to define the cube dimensions:

“Cube.Settings” and “Cube.Generations” define properties of the Essbase database. The former defines some crucial cube information, such as whether it is going to be a block storage option (BSO) or aggregate storage option (ASO) cube, and if it will allow for duplicate member names.

The remaining tabs populate the dimensions (“Dim” tabs), data (“Data” tabs) and/or define calculation scripts (“Calc” tabs) for the cube. If you are familiar with building Essbase dimensions or data files and/or writing calc scripts, these will look very familiar.

For those of you who are not familiar with these items, there is the option of using the Cube Designer.

This is an add-in for Microsoft Excel that you can download via Smart View from your OAC instance.

The “Cube Designer” menu item provides tabbed screens for creating the application workbook. Walking through the tabs allows to setup the application workbook, and the “To Sheet” and “From Sheet” options facilitate reading from, and pushing to, the active workbook:

Once complete, the cube can be created via the web user interface as an import.

This has greatly reduced the complexity of creating Essbase cubes, and is just one of the ways that OAC is redefining the way we perform analytics using Essbase.

As we explore the capabilities of OAC, we will continue to share our thoughts with you, so stay tuned. While you take this journey with us, if you have any questions on this, feel free to send us a note at communications@performancearchitects.com and we will be in touch.


© Performance Architects, Inc. and Performance Architects Blog, 2006 - present. Unauthorized use and/or duplication of this material without express and written permission from this blog's author and/or owner is strictly prohibited. Excerpts and links may be used, provided that full and clear credit is given to Performance Architects, Inc. and Performance Architects Blog with appropriate and specific direction to the original content.

Projection Process Case Study for Oracle Planning and Budgeting Cloud Service (PBCS)

August 9, 2017

Author: Mike McLean, Performance Architects

One of the requirements during a recent Performance Architects Oracle Planning and Budgeting Cloud Service (PBCS) implementation project was to create a solution for their projection process.   During discussions with the Budget Office, we learned that requirements included:

  • Projections occur three times a year:
    • After Q1 actuals are complete
    • After Q2 actuals are complete
    • After Q3 actuals are complete
  • Historical actuals and budget data must be used to seed the projection scenario
  • Revenues need to be seeded using one methodology, while expenses are seeded using another methodology
  • After the projection scenario is seeded, additional adjustments may need to be made by department, fund, program, etc.

The calculation to seed the “Projection” scenario contains several components:

  1. Q1 revenue and expense actuals were copied to “Q1 Projection”:
  1. Using prior year actuals, “% of YearTotal” was calculated for all revenue accounts for each month. See the example below for October (500 / 9,750 = 5.1%):
  1. The monthly “% of YearTotal” is then multiplied by the “Budget YearTotal” value to calculate each month’s revenue. See the example below for October (5.1% x 8,100 = 415):
  1. Expense accounts are calculated by taking the average of “Q1 Actuals” and loading that value into all of the out months:
  2. After the business rule is launched, the results are displayed:

After the “Q1 Projection” is calculated, the Budget Office wanted the option to make additional adjustments.  This was accomplished by leveraging the hierarchy in the “Version” dimension.  We created a sibling of “Projection Calculated” and named the member “Projection Adjustments.”  The parent of those two members is “Projection Total”.

Need help with your PBCS implementation project? Contact us at sales@performancearchitects.com and we can help you out!


© Performance Architects, Inc. and Performance Architects Blog, 2006 - present. Unauthorized use and/or duplication of this material without express and written permission from this blog's author and/or owner is strictly prohibited. Excerpts and links may be used, provided that full and clear credit is given to Performance Architects, Inc. and Performance Architects Blog with appropriate and specific direction to the original content.

Secure Dashboard Access in Oracle BI Cloud Service (BICS): Sales and Marketing Dashboard Example

August 2, 2017

 Author: Linda Stewart, Performance Architects

Oracle Business Intelligence Cloud Service (BICS) is part of Oracle’s Platform as Service (PaaS) offerings.  The Oracle cloud offerings permit enterprise IT teams to rapidly build and deploy applications without the need to set up expensive infrastructure.  There’s still a need, however, for strong security capabilities in the cloud, and this blog post discusses how BICS security works and how to set up secure dashboard access in the solution via a sales and marketing dashboard sample business case.

How Oracle Cloud Security Works

When your business signs up for an Oracle Cloud account, Oracle Cloud creates an identity domain specific to your company. As users log in to an Oracle Cloud service, Oracle cloud identity management controls the user authentication and the features of the service users can access using Oracle Enterprise Single Sign-On (SSO).  SSO may be federated between on-premise and cloud SSO.  Oracle Cloud uses LDAP schemas for storing the identities.

User and Role Management Overview

BICS Security is comprised of two items:  1. Oracle Cloud Identity Domain Users and Roles and 2. Oracle BICS Application Users and Roles.

First, we add the user to Oracle Cloud Identity Management using the Identity Management Administrator credentials.  Click “Users,” then on the following form, click “Add”:

Typically, we use the email address as the user name.

A few things have changed in this form.  We can now set a role in the Cloud Service for each user.  If the new user just views reports, we will click on the “Service” drop down, select “BICS (Business Intelligence),” and then click the button with the two “greater than” signs. This pushes the roles to the “Selected Roles” box.  If the user is not a reports or dashboard author, then uncheck: “bics BI Cloud Service Advanced Content Authors” as shown above.

Click “Add to save the user.

Next, open BICS as an administrator to complete user creation.  Open “Console” and select “Users” and “Roles.”

In BICS, there are five predefined application roles.  We do not have to add our new user to any of the predefined application roles.

Sales and Marketing Dashboard Example

Let’s say we want to secure our data by sales region.  We have a global sales manager who should be able to see all sales regions.  In our fact table, we have the name of the sales region on each data row.  We want to secure the dashboard to only the members of the sales department, and then to filter the data where the salesperson may only see their own regional data and the sales manager should be able to see all regions.  Let’s also say there are marketing departmental dashboards in BICS and marketing should not be able to see the sales dashboard and sales should not be able to see the marketing dashboard.

To do this, we can establish two application roles to manage the dashboard level.  Add “Sales Dashboard.” Click “Save.”  Repeat for the marketing dashboard.

Add members (users) to “Sales” by selecting the button at the right end of the “Sales Application Role” and then by selecting “Manage Members:”

Search for “John Doe” and add this user to the “Sales Dashboard Role” by clicking the user name in the left box and then by clicking the single arrow to place the user in the “Selected Users” panel.  Click “OK” to save.

Create the marketing dashboard role and add users to that role using the same process.

Next, we will create roles to secure the data in the “Sales” fact. Open “Application Role” and add the following roles:

  • Global Sales Manager Data
  • Eastern Sales Data
  • Western Sales Data
  • Marketing Data

Use the same process as creating the “Sales Application Role,” and add a member to each role.

We can divide the sales regions as many ways as we want as long as the sales region data in our fact has data to support our scenario to slice up the data.  To simplify the example, we will establish two sales regions and then a sales manager role (who sees all sales regions).

Once the application roles are complete, we can navigate to the “BICS Modeler” to secure the data, and then to the “BICS Catalog” to secure the dashboards.

In the Modeler, we will need administrator rights so we can filter the data by role:

  • Open “Data Model” in the left panel
  • Select “Fact-Sales”
  • Select “Lock to Edit” button
  • Select “Data Filters” tab
  • Click “Add”
  • Select “Role” as “Eastern Sales Data”
  • Click the “FX” button
  • Select to filter the fact data on Region=’eastern’

Repeat for the Western region.  This secures our data.

Lastly, as demonstrated in the image below, we will secure the dashboards by opening the Catalog:

  • Under the “Company Shared Folder,” we established “Sales” and “Marketing” folders
  • Add both the sales and marketing dashboard roles to each folder
  • Apply permissions recursively
  • Select the “Sales” folder
  • In the “Tasks” pane, select “Permissions”
  • Click the ‘+’ button to add new application roles
    Select the sales and marketing dashboard roles
  • Set as custom permissions

Use the pencil icon on the “Marketing” role and uncheck all of the boxes to change the permissions to “No Access.”

Repeat the process for the “Marketing” dashboard, but set the “Marketing” role to “Full Control” and the “Sales” role to “No Access.”


© Performance Architects, Inc. and Performance Architects Blog, 2006 - present. Unauthorized use and/or duplication of this material without express and written permission from this blog's author and/or owner is strictly prohibited. Excerpts and links may be used, provided that full and clear credit is given to Performance Architects, Inc. and Performance Architects Blog with appropriate and specific direction to the original content.