TAGS

Recent Posts

Archives

‘Unboxing’ Oracle Business Intelligence Cloud Service (BICS): Creating Your First Data Model
Posted on 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:


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.


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.


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.


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.


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.

 

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

Leave a Reply

Your email address will not be published. Required fields are marked *