F RANCESCO C ORFIATI *
2. EL COMIENZO DE LA CRISIS
Goal
To add the information from the price list table to the Product dimension Scenario
You have imported the product tables that store detailed information about ABC’s products into the Physical layer of the repository, and configured keys and joins for the tables. So far, the Dim-Product logical table in the Business Model and Mapping layer has only information from the root product table: Dim_D1_PRODUCTS. You now add the information from the price list table to the Dim-Product logical table. This will simplify the data structure, in effect, creating a denormalized logical table.
Outcome
In the Business Model and Mapping layer, the Dim_D1_PRICELIST physical table is added to the existing logical table source for the Dim-Product logical table. The Price logical column is added to the Dim-Product logical table and mapped to the appropriate physical column.
Time 10 minutes Tasks
1. In this step, you add the information from the price list table to the existing logical table source for the Dim-Product logical table. There are two methods to add multiple sources for an existing logical table source. In this practice, you use the Properties window of an
existing logical table source, which is a manual process and requires several steps. In the next practice, you use a more automated process.
a. In the Business Model and Mapping layer, expand the Dim-Product logical table and then the Sources folder, and then double-click the Dim_D1_PRODUCTS logical table source to view the properties.
b. Click the General tab and click Add. The Browse window automatically includes only those tables that are joined directly to the table already in the logical table source. In this case, it includes all tables that join to Dim_D1_PRODUCTS. Only tables that join to tables included in the logical source can be added to the logical source. For example, notice that Dim_D1_PRODUCT_TYPE is not visible in the browse list. This is because it does not have a direct join relationship with Dim_D1_PRODUCTS in the Physical
Oracle University and 9189 4972 Quebec Incฺ DBA Montreal CollegeฺCA use onlyฺ
Development Program (WDP) eKit materials are provided for WDP in-class use onlyฺ Copying eKit materials is strictly prohibited and is in violation of Oracle copyrightฺ All WDP students must receive an eKit watermarked with their name and emailฺ Contact OracleWDP_ww@oracleฺcom if you have not received your personalized eKitฺ
Copyright © 2011, Oracle and/or its affiliates. All rights reserved.
Practices for Lesson 7: Managing Logical Table Sources
layer.
c. In the Browse window, select the Dim_D1_PRICELIST table and click the Select button. The table is added to the logical table source and the join is displayed in the Joins section on the General tab.
d. Select the join in the Joins section. The View Details button becomes active.
e. Click View Details to open the Complex Join dialog box and view the read-only details of the join.
f. Click Cancel to close the dialog box.
g. To change a join to an outer join, you could use the drop-down list in the Type column.
This allows you to change the join type from inner to three kinds of outer joins. For the purpose of these practices, leave the type as Inner. You can think of the tables in a logical table source as being like a database view. When it formulates physical SQL,
Oracle University and 9189 4972 Quebec Incฺ DBA Montreal CollegeฺCA use onlyฺ
Development Program (WDP) eKit materials are provided for WDP in-class use onlyฺ Copying eKit materials is strictly prohibited and is in violation of Oracle copyrightฺ All WDP students must receive an eKit watermarked with their name and emailฺ Contact OracleWDP_ww@oracleฺcom if you have not received your personalized eKitฺ
Oracle BI Server will leave out the tables in this “view” that are not needed to satisfy the logical query (join elimination), but only if the join type is Inner. When the join type is Outer, however, Oracle BI Server will always include the tables.
h. Click OK to close the Logical Table Source dialog box.
2. In this step, you create a new logical column based on the modified logical table source. In the previous step you used the manual method to add a physical table to a logical table source. That method does not add any logical columns to the logical table, nor does it change the logical-to-physical mapping of any existing column. Now that the physical table that stores the pricing information has been added to the Dim-Product logical table source, you create a new logical column and map it to the appropriate physical table and column.
a. In the Business Model and Mapping layer, right-click Dim-Product and select New Object > Logical Column.
b. Enter Price in the Name field and click OK.
c. Double-click the Dim_D1_PRODUCTS logical table source to open its properties dialog box.
d. Click the Column Mapping tab.
e. If necessary, select the Show unmapped columns check box. Notice that the column you just created, Price, is not mapped to any physical column.
f. Use the drop-down list in the Expression field to map the Price logical column to the PRICE physical column in Dim_D1_PRICELIST.
g. Click OK to close the Logical Table Source dialog box.
Oracle University and 9189 4972 Quebec Incฺ DBA Montreal CollegeฺCA use onlyฺ
Development Program (WDP) eKit materials are provided for WDP in-class use onlyฺ Copying eKit materials is strictly prohibited and is in violation of Oracle copyrightฺ All WDP students must receive an eKit watermarked with their name and emailฺ Contact OracleWDP_ww@oracleฺcom if you have not received your personalized eKitฺ
Copyright © 2011, Oracle and/or its affiliates. All rights reserved.
Practices for Lesson 7: Managing Logical Table Sources
h. The Price logical column is added to the Dim-Product logical table.
i. Save the repository without checking consistency.