OBIEE Modeler: Going Above and Beyond Existing Subject Areas
Oracle Business Intelligence Cloud Services (BICS) is a new breed of business intelligence (BI) products aimed at reducing implementation and maintenance costs, along with automating data recovery and migration procedures. It also attempts to provide advanced analytic tools, such as Visual Analyzer, to easily create new analyses with advanced calculations.
One of the BI tools in BICS that gives users a great deal of flexibility is the data modeler service. The data modeler service provides end users with the ability to upload data stored in Excel .csv files. Using a simple join, end users can create their own data models. This may make it sound as if business users can create their own subject area, as an OBIEE developer would do in the RPD. However, there are capabilities that exist only in the RPD that the modeler falls short of. For example, the three points below represent core RPD functionalities that are only possible in the RPD at this time:
- Complex and some other types of simple joins are not possible in the modeler.
- The ability to show and hide columns based on which user logs in.
- A full range of data security filters are possible only in the RPD.
If business requirements don’t call for complex joins and/or advanced types of object- and data-level security, the data modeler will allow the business user to break away from the chains of having to wait for a subject area to be developed or a column to be added or modified in the OBIEE repository, which then would have to go through testing cycles and a production release.
Let’s examine a quick BICS modeler example just to get the flavor of what business users can expect if their company signs up for BICS.
Locate and upload your .csv file. (We will be loading a Dim Date file.)
In the next step, the user can see that if the file holds data for an existing table, then the user can add data to that table. For our example, click on “New Table,” then give the table a proper name. Perform any data transformation, data type changes and column lengths at this stage, and click “Next.”
And you are done!
Creating joins and calculations:
Once the data loading is complete, the user can navigate to the modeler to create the proper joins between all the dimension and fact tables added. Click on “Model.”
The user will be directed to the modeling screen.
At this stage, the user needs to identify tables as dimensions or facts in order to prepare for modeling. Click on “Lock to Edit” to start editing the model.
On the left side panel, right-click on any table and click on “Add” to model as a fact, dimension or both, if needed.
Once a fact table is added, aggregation rules and/or calculations can be created. Click on any fact table in the list of fact tables below.
Select the aggregation rule needed.
The next stage in modeling is to create joins between the dimension and fact tables. Click on “Create Join.”
Select the fact and dimension tables to join and select the columns.
Once completed, click on the green check mark to verify the model before publishing and unlocking it.
Ready for reporting!
For our next blog session, we will be talking about Oracle Base Line Tool. This tool is used to perform different types of quality assurance testing in order to automate regression testing as much as possible when upgrading an OBIEE platform.