Monthly Archives: May 2016

‘Unboxing’ Oracle Business Intelligence Cloud Service (BICS): Creating and Sharing Your Content

May 25, 2016

Author: John McGale, Performance Architects 

Service Workflow – Report Authoring

The next step in our “unboxing” of Oracle Business Intelligence Cloud Service (BICS) workflow is to create reports.

There are four basic concepts to understand when creating reports in BICS. A report is called an “Analysis,” has a drag-and-drop interface for querying data, and is the starting point for nearly all BICS content (besides Visual Analyzer Projects). You will select attributes from the model you have just created and place them in order in report. You will then create “Filters” to restrict your data by certain parameters.

blogger 1
A new Analysis with columns and filters

You will then preview them in a “View,” which is essentially a data visualization. By default, your data will appear in a table view. You can then choose other views and arrange them on the pallet on which they appear. Lastly you will take your Analyses and add them to a “Dashboard.”

blogger 2 blogger 3
Previewing a Funnel View in Analyses, BICS Catalog

Once you’re satisfied with your Analyses, you can save them to the BICS file system called the “Catalog.”  You can create a new Dashboard to house the Analyses and other objects like Dashboard Filters.

As mentioned previously, you will first select reports from the Catalog and drag them onto the dashboard.  A column and a section will be created automatically. You can then add multiple columns and sections and arrange them in any number of ways. You can also add other controls such as “Text,” “Folder,” “Action Links,” and “Action Link Menus.” Another common object to create and add is a “Dashboard Prompt,” which dynamically drives the content of the embedded Analyses.

blogger 4 blogger 5
Viewing the Dashboard Editor with embedded objects

Below you can see a completely finished dashboard from the user’s perspective.  You can have more than one visualization within a single Analysis to give your dashboard an even higher level of information density.

blogger 6
Viewing a completed dashboard

Now that you have created your Analyses and Dashboard, you’re ready to secure them in order to decide who can view them. To secure Analyses and Dashboards in BICS we use the Catalog.  As mentioned previously, the Catalog is similar to Microsoft Windows File Explorer. You can store files in the root directory or create folders to house them.

The first thing you’ll need to do is to navigate to the Application Console and choose “Manage Users and Roles.” You will then create an “Application Role,” or use an existing role, and then associate that role with your user and with your Catalog objects. BICS provides several standard roles and typically the “BI Consumer” role is used for provisioning end users.

blogger 7
Fig 31. Viewing Application Roles and Users

Once you have completed “User-to-Role” provisioning, you’ll then organize and secure your objects stored in the Catalog. Select a folder and then click on the “Permissions” control to change the “Application Roles” associated with the “Catalog Object.” Once this is completed, you’re ready to share your content.

blogger 8
Setting permissions on Catalog objects

Users will typically access your content indirectly through a Dashboard, instead of directly accessing the content through the Catalog. To open a Dashboard, navigate to the “Dashboards” menu and you’ll see a list of the dashboards you are allowed to see.  Click the dashboard you wish to view and it will open in another browser tab.

blogger 9
Selecting a Dashboard to view


© 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.

‘Unboxing’ Oracle Business Intelligence Cloud Service (BICS): Creating Your First Data Model

May 18, 2016

Author: John McGale, Performance Architects 

Once your data is created in Oracle Business Intelligence Cloud Service (BICS), the next step is to import the data into a model.  A model is a logical representation of your data that provides BICS with the information it will need in order to dynamically generate the SQL necessary to retrieve your data.

The modeling tool is based on a data warehousing best practice: a “star” model that optimizes data for query retrieval.  A star model is comprised of “facts” and “dimensions.” A fact table is a table that only contains measures.  A measure is any piece of data you can perform a mathematical function with.  In short, “If you can add it, then place the column in a fact.”  A dimension contains all the descriptive data related to the fact.  So, “How many of ‘X’ do we have, where “X” is the dimensional attribute?”

The SQL Data Loader Wizard imports information from a flat file and most likely puts that data into a single table. You take the one table definition and create “views,” which are logical versions of tables, and then join them together.  In a practical sense, you’re taking a table and joining it to itself over and over again.  The image below depicts the process:

blog 1
Example of Flat File Transition to Logical Star Model

Start with an empty model, go to your list of databases, and right-click on a “Source” to import.  You have the option of specifying a “Fact” or “Dimension,” or you can choose the “Add as Fact and Dimension Tables” option and BICS will make the best possible choice for you.  All joins will also be auto-created.  You may need to review these and correct the joins.

blog 2
Adding new “views” to model and menu of choices

Once you finish reviewing the joins, go into your fact table and set the aggregation rules for all your measures.  After this is completed, you’re ready to publish your model.

blog 3
Setting aggregation rules on measures

To publish a model, navigate back to the main model window and select “Publish Model” from the left-hand side of the window.

blog 4
Publishing the new BICS model

There are two main ways to secure data in your new data model, “Object Permissions” and “Data Filters.”  Object Permissions control the visibility of tables or columns of data.  Data Filters control the visibility of data rows.  To set Object Permissions, open a table of your choice and navigate to the “Permissions” tab.  In this tab, select visibility of the object by role.

blog 5
Setting Object Permissions

To set “Data Filters” for the same table, simply click on the “Data Filters” tab.  Once again, select a role, however this time, add a condition that evaluates to “true” or “false.”  If the condition is met, then the user is allowed to view the row of data, otherwise it remains hidden to the user.

blog 6
Setting Data Filters

 


© 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.

‘Unboxing’ Oracle Business Intelligence Cloud Service (BICS): Loading Data into Your Environment

May 11, 2016

Author: John McGale, Performance Architects

All data in Oracle Business Intelligence Cloud Service (BICS) must first be uploaded to the service, whether from a relational source or text file / spreadsheet.

Data is stored in a cloud version of Oracle Database, called Oracle Database Cloud Service, which is a single database schema with ability to create tables and views. The database schema is administered via the Oracle Application Express (APEX) interface. For purposes of this discussion, we will use the Data Loader Wizard, which is a quick-start way of loading flat file data to BICS (an Oracle tutorial is provided here if you want more details).

jmc 1
BICS Data Loader Wizard Summary and Start Icon

The BICS data loader can be accessed through the home page or the dropdown menu that appears under your login. You will be redirected to a new web page tab, where you will choose your flat file and then click on the “Load Data” button:

jmc 2

jmc 3
BICS Data Loader Wizard, Step 1

Your file will be processed, and you will need to click the “Next” button to select the destination for the data.  You will see a preview of the data in the window below. You can select a delimiter and also specify a certain number of rows to skip.

During the next part of the data load, you can either create a new table or use an existing table. If you elect to create a new table, you can define the table name and BICS will create the table for you instantly. There are options to perform light data transformations and cleansing as well.

jmc 4
BICS Data Loader Wizard, Step 2 (Showing Options for Data Transformation)

During the final phase of the Data Loader Wizard steps, you are shown a summary of the data you have loaded and you can hit “Save” to commit the data to the database table.

jmc 5
BICS Data Loader Wizard, Step 3

The BICS Data Loader Wizard is the easiest way to dig in right away and “kick the tires” on loading data into BICS. It allows you to rapidly prototype your data without the need to invest in a technical project. You can stage all your data in a spreadsheet and then quickly upload it to the cloud. 


© 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.

‘Unboxing’ Oracle Business Intelligence Cloud Service (BICS): Logging into Your Environment

May 4, 2016

Author: John McGale, Performance Architects

After you have procured your Oracle Business Intelligence Cloud Service (BICS) environment, you should receive an email from Oracle that contains a direct link to your service.  If for any reason you misplace the link, you can simply go to cloud.oracle.com and click the sign-in button.

You will then be presented with a list of choices.  Select “My Services” and then pick your data center region. Your region will be indicated in the email. The region is determined based on the geography you indicated during sign up. You will then be prompted to enter an identity domain and your login credentials.

john m 1 

After logging in, you will be presented with the BICS homepage. For those of you familiar with Oracle Business Intelligence Enterprise Edition (OBIEE), you may have expected a similar user interface. BICS uses the “simplified interface” and there is no option to change the interface at this time.

john m 2

john m 3
BICS Home Page, Create Menu, Alternate Horizontal Task Menu

A quick tour of BICS features will play when you login.  You can opt out of this. You can go directly to creating content from the “Create” menu or follow one of the links to begin working.  We highly recommend you take the tutorial which can be found in the “BI Cloud Service Academy.”


© 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.