2. DESARROLLO
2.3. Metodología
To examine the logical-to-physical column mappings and to create simple measures
Scenario
The SupplierSales business model is now defined in the Business Model and Mapping layer. In this practice, you review the logical-to-physical table and column mappings to better understand the relationships that exist between logical tables and their logical table sources. You then create measures by setting aggregation rules for logical columns. Then you check the physical tables referenced by the business model.
Outcome
Measures defined in the Fact-Sales logical table
Time
5 minutes
Tasks
1. In this step, you examine a logical table source (LTS). Each logical table in a business model has a subfolder called Sources that contains the logical table sources. Logical table sources contain the mappings from the logical columns in the Business Model and Mapping (BMM) layer to the physical columns in the Physical layer. Every logical column maps directly (or indirectly via another logical column) to a column or columns in the Physical layer.
a. Expand the Sources folder of the Fact-Sales logical table. Notice that the name of the
logical table source for the Fact-Sales table is Fact_D1_ORDERS2. This logical table source was created automatically during the process of dragging the
Fact_D1_ORDERS2 physical table to the business model.
b. Double-click the Fact_D1_ORDERS2 logical table source to open the Logical Table
Source dialog box.
c. Click the General tab. By default, the logical table source name corresponds to the
name of the physical table that was dragged from the Physical layer, but this name could be changed to something more meaningful. For this exercise, leave the name as is. Notice also that the path to the physical table is identified in the “Map to these tables:” area.
d. Click the Column Mapping tab to review how the logical columns are mapped to the
Physical layer. If necessary, adjust the column widths and heights, or drag the entire dialog box window to make it larger or smaller. You should see three column headings: Logical Column, Expression, and Physical Table. For example, the Dollars logical column is mapped to the DOLLARS physical column in the Fact-D1_ORDERS2
Oracle Internal & Oracle Academy
Use Only
Copyright © 2010, Oracle and/or its affiliates. All rights reserved.
Practices for Lesson 4: Building the Business Model and Mapping Layer of a Repository
physical table.
e. The Content tab is discussed later in this course. Click Cancel to close the dialog box. 2. In this step, you create some measures by defining default aggregation rules on logical
columns defined in the Fact-Sales table. Measures are typically data that is additive, such as total dollars or total quantities. The Fact-Sales logical fact table contains the measures in your business model. You aggregate some of its logical columns by summing the column data.
a. Double-click the Dollars logical column in the Fact-Sales table. The Logical Column
dialog box opens.
b. Click the Aggregation tab.
c. Set the default aggregation rule for Dollars to Sum.
d. Click OK. Notice that the Dollars icon is changed to indicate that an aggregation rule is
defined.
e. Use Ctrl + click to select Net Weight Shipped, Units Ordered, and Units Shipped. f. Right-click and select Set Aggregation.
g. Set the default aggregation rule to Sum.
h. Click OK. You can use this method to set the same aggregation rule for multiple
columns at once.
i. Notice that the logical column icons are changed for all four columns.
3. In this step, you check which physical tables are referenced by a business model. In most situations, there are tables that are included in the Physical layer, but not in the Business Model and Mapping layer. Only the tables referenced in the Business Model and Mapping layer, that is, only the tables included in logical table sources, are used in queries. All other physical tables are ignored when Oracle BI Server queries the physical database.
a. Select Tools > Options.
b. Click the General tab.
c. Select Show row count in physical view.
d. Click OK.
e. Select the SupplierSales business model.
Oracle Internal & Oracle Academy
Use Only
f. Click the Physical Diagram icon on the toolbar.
g. The Physical Diagram displays all the physical tables currently referenced by the SupplierSales business model. It also displays the row count for tables with updated row counts. If you do not see the row counts, select Tools > Update All Row Counts.
h. Double-click the connector between the Dim_D1_CUSTOMER2 and the
Fact_D1_ORDERS2 tables. Notice that the physical join is displayed. You can determine that this is a physical join because there is a foreign key join expression. i. Click Cancel to close the join dialog box.
j. Close the Physical Diagram. k. Select Tools > Options.
l. Click the General tab.
m. Deselect Show row count in physical view.
n. Click OK.
4. Save the repository.
5. Do not check global consistency.
6. Leave the repository open for the next practice.
Congratulations! You have successfully built a business model in the Business Model and Mapping layer of a repository and created business measures.
Oracle Internal & Oracle Academy
Use Only
Copyright © 2010, Oracle and/or its affiliates. All rights reserved.
Practices for Lesson 4: Building the Business Model and Mapping Layer of a Repository