Practices for Lesson 4 Lesson Overview
CUSTKEY INVNBR
PERIODKEY PRODKEY
b. Expand the Dim-Time table and delete the following columns: Columns DAGO MAGO ORDERDAY_IN_YEAR WEEK_NUM_IN_YEAR YAGO
c. Expand the Dim-Product table and delete the PGCODE column.
d. Expand the Dim-Customer table and delete the REPNO and FACTOR columns.
6. Use the Rename Wizard to rename logical columns in the Dim-Time logical table. You use the Rename Wizard utility for renaming Business Model or Presentation layer objects. You can use it to replace text strings, change all letters to lowercase, use uppercase for the first letter of words, and so on. You can preview the new names before committing the changes. It is primarily used on Business Model logical columns after importing physical objects into the middle layer. The names that you give logical columns in the business model can be exposed via the Presentation layer to end-user tools such as the Analysis Editor. However, it is possible to override logical column names in the Presentation layer, as you will see later in this course.
a. Select Tools > Utilities > Rename Wizard and click Execute.
b. At the bottom of the middle pane, click Business Model and Mapping. c. In the middle pane, expand SupplierSales > Dim-Time.
d. Use Shift + click to select all columns and click Add to add the columns to the right pane.
e. Click Next. Notice that Logical Column is selected.
f. Click Next again.
g. Select Change specified text.
h. In the Find field, enter YYYYMMDD. i. In the “Replace with field,” enter Day. j. Select Case Sensitive.
k. Click Add.
l. Select Change specified text.
m. In the Find field, enter MONTHCODE.
Oracle Internal & Oracle Academy
Use Only
n. In the “Replace with field,” enter Month Code. o. Select Case Sensitive.
p. Click Add.
q. Select Change specified text.
r. In the Find field, enter FULL_GREGORIAN_DTE. s. In the “Replace with field,” enter Date.
t. Select Case Sensitive.
u. Click Add.
v. Select Change specified text.
w. In the Find field, enter an underscore. x. In the “Replace with field,” enter a space. y. Click Add.
z. Select All text lowercase and click Add.
aa. Select First letter of each word capital and click Add.
bb. Click Next.
cc. In the Rename Wizard – Finish window, preview the changes before committing.
dd. Click Finish.
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
ee. Examine the changes in the repository and verify that they are as expected.
ff. Save the repository.
gg. Do not check global consistency.
7. Rename columns in the remaining logical tables according to the tables below. You can use the Rename Wizard or rename the columns manually.
Fact-Sales columns Rename as:
DOLLARS Dollars
NETWGHTSHPD Net Weight Shipped
UNITORDD Units Ordered
UNITSHPD Units Shipped
Dim-Customer columns Rename as:
ADDRESS Address
CITY City
DISTRICT District
NAME Customer
NEWKEY Customer Key
PHONE Phone
REGION Region
ROUTECODE Route Code
SALESREP Sales Rep
STATE State
ZIP_CODE Zip Code
Dim-Product Columns Rename as:
DIETCODE Diet Code
GENERICDESCRIPTION Generic
Oracle Internal & Oracle Academy
Use Only
PACKAGE_WEIGHT Package Weight
PACKAGECODE Package Code
PRODUCTKEY Product Key
SPECIFICDESCRIPTIN Specific
SUBTYPECODE Subtype Code
SUPPLIERCODE Supplier Code
TYPECODE Type Code
8. In this step, you verify the logical table keys. For a business model to be valid, each logical dimension table must have a logical key. Logical keys can be composed of one or more logical columns. The logical key defines the lowest level (the most detailed level) of information of any source in the logical table. In this example, the logical table keys were built automatically when you dragged the tables from the Physical layer to the business model.
a. Double-click the Dim-Customer logical table in the business model to open the Logical
Table dialog box. b. Click the Keys tab.
c. Confirm that the Customer Key column is defined as the key for this table. d. Click OK to close the Logical Table dialog box.
e. Repeat these steps and ensure that the Day column is defined as the logical table key for the Dim-Time table and Product Key is defined as the logical table key for the Dim- Product table.
9. In this step, you verify the logical table joins. Another requirement for a valid business model is that the logical tables must be joined via logical joins. Logical joins express the cardinality relationships between the logical tables. Logical fact tables are always at the “many” end of these cardinality relationships. Logical joins help Oracle BI Server
understand the relationships between the various pieces of the business model. When a query is sent to Oracle BI Server, the server figures out how to construct physical queries by examining how the logical model is structured. Examining logical joins is an integral part of this process.
a. Right-click the SupplierSales business model and select Business Model Diagram > Whole Diagram. The Logical Table Diagram window opens.
b. Rearrange the table icons so that they are all visible. Right-click the white space and adjust the zoom factor, if desired. Recall that the logical joins are automatically
inherited from the joins defined in the Physical layer. Later in this course you learn how to build logical joins using the Business Model Diagram. The Business Model Diagram should look similar to the following screenshot.
c. Double-click one of the join connectors to open the Logical Join dialog box.
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
d. Leave the default values as they are, but notice which properties you can set in the Logical Join dialog box: name, business model, tables, driving table, join type, and cardinality. Also, notice which properties you cannot set: the join expression and the join columns.
e. Do not change the default values. Click OK. Typically, when defining logical joins, you leave the defaults as they are and click OK.
f. Close the Business Model Diagram window. The join relationships determine which tables are the logical dimension tables and which is the logical fact table. Recall that a fact table is always on the “many” side of a logical join. You now have a logical star schema consisting of one logical fact table, Fact-Sales, and three logical dimension tables: Dim-Time, Dim-Product, and Dim-Customer.
10. Save the repository.
11. Click No when prompted to check global consistency. 12. Leave the repository open for the next practice.