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.