The Business Model and Mapping layer defines the logical model of the data that maps the business rules to the Physical layer objects. There can be many business models that can be mapped with different kinds of data sources based on different technologies. Also dimensional modeling should be done in this layer. This can be achieved by creating dimension objects and their hierarchies. Also all about the calculation and aggregation rules will be defined in this layer.
As a summary, all the business rules are going to be created in this layer. It's probably the most important layer in the repository, and it'll definitely take longer to create the objects in this layer.
Creating business models can be done by two methods, manually or by drag-and-drop. If the manual object creation method is preferred, we'll have to create objects in the following order:
Create the business model.
Create the logical tables and map them to the physical tables.
Create the logical columns.
Define the logical joins.
Create the business model manually by right-clicking on the BMM layer and selecting New Business Model. (Give it a name, in this case
Sales.)Ensure that the Disabled option is checked because there's no other object in this business model. We're going to change this setting later.
After creating a blank and disabled business model, drag-and-drop the physical objects from the Physical layer onto the business model named
Sales.Automatically created objects are:
Logical dimension and fact tables
Logical columns and their mappings to Physical layer
Logical table sources for the dimension and fact tables
Logical keys and joins (can be accessed from the Business Model Diagram window)
Now it'll be good to make a clean-up because with this automated process all of the columns from the Physical layer are created as logical columns. Most probably we won't need all of the logical columns; we can easily delete the logical columns that are not required by right-clicking on the columns. Another task is renaming the logical tables and columns with meaningful names.
After making the clean-up, we'll see that some columns are deleted and some are renamed.
When we open the properties of the logical table source named
Fact_Orders, we'll find mappings from the Logical Column to the Physical column. Also if you need to access other details of the logical table source, you'll have to extend the logical table sources folder, so you'll find the logical table source definition.
The new business model is almost ready but still there is a missing step that is the aggregation rule for the columns in the fact table. By default, it's not set. The following are the options for the aggregation rule, so depending on the requirement you can use one of them:
SumandAvgCountandCount DistinctMaxandMinStdDevand so on
In order to set the aggregation rule for one logical column in the BMM layer, you'll have to open the properties window of that column and go to the Aggregation tab. You'll find the rules in the drop-down list and select one of them. The most common one will be
Sum. So we'll useSumas the aggregation rule in this demonstration. In the case of business requirements, we can use different aggregation rules. If you need to display the number of the rows, we can useCountas the aggregation rule.
So we've configured all of the measures in the fact table with the
Sumaggregation rule.
We created a simple business model in this section. Each logical table should have at least one logical table source. We might create multiple logical table sources as well, just in order to use summary tables or horizontal/vertical data federation. The important point in this layer is creating the logical joins. The Oracle BI Server will recognize the logical fact and dimension tables by checking the logical joins. Obviously, the logical joins are dependent on the physical joins, but we may have only one table in the Physical layer. So there won't be any physical join. Even in a single table scenario, we'll need to create fact and dimension tables in the BMM layer and create logical joins between them. At the end, all the objects will be referencing to only one single table. After working on the Business Model Diagram, we selected the aggregation rules that will be applied on the measure columns such as Sum, Count, and Avg. Is this a required step? Actually, it's not required. If you don't set the aggregation rule of the measures, you won't be able to see aggregated data in the reports. Let's assume that if the fact table contains one million rows, you'll see one million rows in the report. Although setting the aggregation rule is not a must, it's highly recommended to do it.
Regarding the business model, there is one more object type called logical dimensions. The Drill Down feature and aggregate (summary) tables can be used after dimensions are created. As you see, two important features are dependent on dimensions. We're going to focus on logical dimensions and their types in Chapter 2, Working with Logical Dimensions.