Business Layer defines the commercial enterprise or logical version of objects and their mapping between enterprise model and Schema within the physical layer. It simplifies the Physical Schema and maps the consumer business requirement to physical tables.
The commercial enterprise model and mapping layer of OBIEE system management tool can comprise one or extra enterprise version gadgets. A commercial enterprise model item defines the commercial enterprise version definitions and the mappings from logical to physical tables for the commercial enterprise model.
The business model is used to simplify the schema structure and maps the customers’ enterprise requirement to physical facts supply. It entails introduction of logical tables and columns inside the business model. Each logical table can have one or greater bodily gadgets as sources.
There are two classes of logical tables − reality and size. Logical fact tables include the measures on which analysis is completed and Logical size tables incorporate the information approximately measures and objects in Schema.
While growing a new repository using OBIEE administration device, when you outline the bodily layer, create joins and perceive overseas keys. The subsequent step is to create a commercial enterprise model and mapping BMM layer of the repository.
Steps concerned in defining Business Layer −
- Create a business version
- Examine logical joins
- Examine logical columns
- Examine logical desk resources
- Rename logical table gadgets manually
- Rename logical table items the usage of the rename wizard and delete pointless logical item
- Creating measures (Aggregations)
- Create Business Layer in the Repository
To create a business layer inside the repository, right-click on → New Business Model → Enter the name of Business Model and click OK. You also can upload description of this Business Model if you want.
Logical Tables and Objects in BMM Layer
Logical tables in OBIEE repository exist in the Business Model and Mapping BMM layer. The business version diagram should include at the least two logical tables and also you want to outline relationships among them.
Each logical table ought to have one or more logical columns and one or greater logical table resources related to it. You can also trade the logical desk name, reorder the objects in logical table and define logical joins the usage of primary and overseas keys.
Create Logical Tables Under BMM Layer
There are two ways of making logical tables/gadgets in BMM layer −
First method is dragging bodily tables to Business Model that is the quickest manner of defining logical tables. When you drag the tables from the bodily layer to BMM layer, it additionally preserves the joins and keys robotically. If you want you can exchange the joins and keys in logical tables, it doesn’t affect items in the bodily layer.
Select physical tables/alias tables below the bodily layer that you want to add to Business Model Layer and drag the ones table under BMM layer.
These tables are known as logical tables and columns are called Logical gadgets in Business Model and Mapping Layer.
Second technique is to create a logical desk manually. In the Business Model and Mapping layer, proper-click on the commercial enterprise version → Select New Object → Logical Table → Logical Table dialog box appears.
Go to General tab → Enter call for the logical table → Type a description of the table → Click OK.
Create Logical Columns
Logical columns in BMM layer are robotically created whilst you drag tables from the bodily layer to the business model layer.
If the logical column is a primary key, this column is displayed with the important thing icon. If the column has an aggregation feature, it's far displayed with a sigma icon. You also can reorder logical columns inside the Business Model and Mapping layer.
Create a Logical Column
In BMM layer, right-click on logical desk → choose New Object → Logical Column → Logical Column conversation field will seem, click on General tab.
Type a call for the logical column. The name of the commercial enterprise model and the logical table seem in the “Belongs to Table” area just beneath column call → click on OK.
You can also apply Aggregations on the logical columns. Click Aggregation tab → Select Aggregation rule from the dropdown listing → Click OK.
Once you follow Aggregate function on a column, logical column icon is modified to reveal Aggregation rule is implemented.
You also can circulate or copy logical column in tables −
In the BMM layer, you could choose more than one columns to move. In the Sources for moved columns conversation container, inside the Action place, pick out an movement. If you pick out Ignore, no logical source may be delivered within the Sources folder of the table.
If you click on on Create new, a copy of the logical supply with the logical column will be created in the Sources folder. If you select Use current option, from the drop-down list, you have to pick a logical source from the Sources folder of the desk.
Create Logical Complex Joins / Logical Foreign Keys
Logical tables in BMM layer are joined to every different the use of logical joins. Cardinality is one of the key defining parameter in logical joins. Cardinality relation one-to-many approach that every row in first logical size table there are zero, 1, many rows in second logical desk.
Conditions to Create Logical Joins Automatically
When you drag all of the tables of the bodily layer to enterprise version layer, logical joins are mechanically created in Repository. This circumstance rarely takes place best in case of simple commercial enterprise models.
When logical joins are equal as bodily joins, they're robotically created. Logical joins in BMM layer are created in methods −
- Business Model Diagram (already covered even as designing repository)
- Joins Manager
Logical joins in BMM layer can't be distinct using expressions or columns on which to create the join like in the bodily layer in which expressions and column names are proven on which bodily joins are described.
Create Logical Joins/Logical Foreign keys Using Join Manager Tool
First let us see a way to create logical foreign keys using Join Manager.
In the Administration Tool toolbar, go to Manage → Joins. The Joins Manager conversation container seems → Go to Action tab → New → Logical Foreign Key.
Now inside the Browse conversation container, double-click on a desk → The Logical Foreign Key dialog field seems → Enter the call for the overseas key → From Table drop-down listing of the dialog box, pick the desk that the foreign key references → Select the columns inside the left table that the foreign key references → Select the columns inside the right desk that make up the foreign key columns → Select the be part of kind from the Type drop-down list. To open the Expression Builder, click on the button to the proper of the Expression pane → The expression shows within the Expression pane → click OK to save the paintings.
Create a Logical Complex Join the usage of Join Manager
Logical complicated joins are recommended in Business Model and mapping layer in comparison to the usage of logical overseas keys.
In the Administration Tool toolbar, visit Manage → Join → Joins Manager dialog field appears → Go to Action → Click New → Logical Complex Join.
It will open a logical Join conversation box → Type a call for the complicated join → In the table drop-down lists at the left and right side of the dialog container, pick out the tables that the complicated join references → Select the join kind from the Type drop-down listing → Click OK.
Note − You also can outline a table as riding table from the drop-down list. This is used for performance optimization whilst the desk size is simply too huge. If the desk length is small, less than one thousand rows, it shouldn’t be defined as driving table as it can result in performance degradation.
Dimensions and Hierarchical Levels
Logical dimensions exist in BMM and Presentation layer of OBIEE repository. Creating logical dimensions with hierarchies lets in you to define aggregation regulations that modify with dimensions. It additionally provides a drill-down choice on the charts and tables in analyses and dashboards, and define the content of aggregate sources.
Create logical measurement with Hierarchical level
Open the Repository in Offline mode → Go to File → Open → Offline → Select Repository .Rpd report and click on on open → Enter Repository password → click OK.
Next step is to create logical size and logical stages.
Right click on on Business model call in BMM layer → New Object → Logical Dimension → Dimension with degree-primarily based hierarchy. It will open the dialogue container → Enter the name → click OK.
To create a logical degree, proper-click on on logical size → New Object → Logical Level.
Enter the call of logical stage instance: Product_Name
If this degree is Grand total degree, pick out the checkbox and the system will set wide variety of element at this degree to one through default → Click OK.
If you need the logical level to roll as much as its parent, pick out the Supports rollup to parent elements checkbox → click OK.
If the logical stage isn't the grand total degree and does not roll up, do not select any of the checkbox → Click OK.
You also can upload parent-child hierarchies in logical level by following these steps −
To define baby logical stages, click Add inside the Browse conversation field, choose the kid logical degrees and click OK.
You can also proper-click on logical level → New Object → Child level.
Enter the name of toddler level → Ok. You can repeat this to feature multiple toddler levels for all logical columns as in step with requirement. You can also add Time and Region hierarchies in a comparable way.
Now to add logical columns of a table to logical stage → pick logical column in BMM layer and drag it to logical level baby name to that you need to map. Similarly you can drag all the columns of logical table to create parent-baby hierarchies.
When you create a baby level, it could be checked by way of a double-click on on the logical degree and it's far displayed under child stages listing of that degree. You can upload or delete toddler ranges by the usage of ‘+’ or ‘X’ choice on top of this box.
Add Calculation to a Fact Table
Double-click on on the column call in logical Fact table → Go to Aggregation tab and select the Aggregate function from the drop-down list → Click OK.
Measures represents records this is additive, such as overall sales or general amount. Click on save choice at the pinnacle to store the repository.
There are diverse Aggregate functions that can be used like Sum, Average, Count, Max, Min, etc.