Overview of Practices for Lesson 5 Lesson Overview
Practice 5-1: Creating the Presentation Layer Goal
To create the Presentation layer of a repository Scenario
You have created the initial SupplierSales business model in the repository. You now create the Presentation layer of the repository. The Presentation layer exposes the business model objects in the Oracle BI Analysis Editor so that users can build analyses to analyze their data.
Outcome
In the Presentation layer of the repository, there is a SupplierSales subject area.
Time 10 minutes Tasks
1. In this step, you create the Presentation layer for the SupplierSales business model. The Presentation layer has three types of objects: subject area, presentation table, and
presentation column. In the Oracle BI Analysis Editor the subject area appears as a subject area, the presentation table appears as a folder, and the presentation columns appear as columns in the folders. The ABC repository should still be open in the Administration Tool from the previous practice.
a. Ensure that the Presentation layer is visible. If it is not, select View > Presentation.
b. Drag the SupplierSales business model into the Presentation layer. A subject area appears with the name SupplierSales.
c. Expand SupplierSales in the Presentation layer. When you create presentation objects by dragging a business model to the Presentation layer, the business model becomes a subject area, the logical tables become presentation tables, and the logical columns become presentation columns. Notice that all objects within a subject area must derive from a single business model.
2. In this step, you examine the properties of a subject area.
a. Double-click SupplierSales in the Presentation layer to open the Subject Area dialog box.
b. Click the General tab. You use this tab to create or edit a subject area.
c. Notice that it is possible to change the name of the subject area. For the purpose of these lessons, leave the name as SupplierSales. This is the name that appears as a subject area in the Analysis Editor. Also, as you will see in later lessons, the name of
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ฺ
the subject area can be used in the FROM clause of a logical SQL statement.
Therefore, short names are desirable. The tool prevents you from giving the same name to a subject area and a presentation table. It is also advisable to avoid using characters ($, %) that may cause illegal SQL syntax with particular client tools.
d. Click the Permissions button. This dialog box is used to assign user and application role permissions to this repository object. Permissions are discussed in more detail in the lesson titled “Security.”
e. Click Cancel to close the Permissions dialog box.
f. The custom display name is used if you are planning to present the name in local languages. You can ignore this for the purposes of this practice. You learn more about using custom display names in the lesson titled “Localizing Oracle BI Metadata”.
g. Notice that the business model is grayed out and cannot be modified. This is because all objects within a subject area derive from a single business model and cannot span multiple business models. After the business model is set, it cannot be changed and the tool prevents you from including objects from other business models.
h. Notice that “export logical keys” is deselected by default. This is irrelevant to users of the Analysis Editor, but may be advantageous to some third-party query and reporting tools. If selected, columns in the Presentation layer that are key columns in the
Business Model and Mapping layer will be presented as key columns to an ODBC client and will have a key icon in the Presentation layer.
i. Notice that the implicit fact column is not assigned. If you set an implicit fact column, this column is added to a query when it contains columns from two or more dimension tables and no measures. It is used to specify a default join path between dimension tables when there are several possible alternatives. You learn more about configuring implicit fact columns in the lesson titled “Setting an Implicit Fact Column”.
j. In the Description field, enter something similar to Analyze Sales and Shipment Data.
This information is visible under the corresponding subject area in Analysis Editor. You confirm this in the next set of practices.
k. Click the Presentation Tables tab.
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 5: Building the Presentation Layer of a Repository
l. Use the Up and Down buttons or drag objects to rearrange the presentation tables into the following order:
m. Click the Aliases tab. If you change the name of a subject area, the tool automatically creates an alias using the previous name. You can use this tab to specify or delete an alias for a subject area.
n. Click OK to close the Subject Area dialog box.
o. Expand the SupplierSales subject area and notice that the order of the presentation tables is now changed in the Presentation layer.
3. In this step, you explore the properties of a presentation table. You can use presentation tables to hold columns from multiple logical tables, thereby reducing the number of apparent logical tables. You can also use presentation tables to organize logical columns into smaller groupings, thereby increasing the number of apparent logical tables. For example, you might have a hundred measures in a logical fact table. You may want to create multiple presentation tables as containers for those measures and thus present them in an organized way (for example, all dollar measures in one folder and all unit measures in another folder). These measure folders could be nested within a single folder in the
Analysis Editor.
a. Double-click the Dim-Customer presentation table in the Presentation layer. The Presentation Table dialog box opens.
b. Click the General tab.
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ฺ
c. Notice that, by default, the presentation table name is the same as the logical table name. For Analysis Editor, the name can be anything, except that it should not contain single quotation marks, double quotation marks, or the “%” sign. For client tools that generate SQL, it is advisable to avoid names that might violate valid SQL syntax. For example, avoid SQL key words, spaces, single and double quotation marks, and other characters such as “$” or “%.” A presentation table name cannot be the same as the subject area name or any logical column name in that catalog. As in a subject area, changing a presentation table name does not have any effect on the logical table name in the Business Model and Mapping layer. The Alias tab keeps a record of any
changes.
d. Type Customer Data in the Description field. This will show up as a “tool tip” in the Analysis Editor when the user places the cursor over the object. You confirm this in the next set of practices.
e. Click the Columns tab.
f. Change the order of the columns, using the Up and Down buttons or by dragging, into the following order:
g. Click OK to close the Presentation Table dialog box.
4. In this step, you explore the properties of a presentation column. Presentation columns can come from multiple logical tables in a business model. By default, a presentation column uses the same name as its corresponding logical column in the Business Model and Mapping layer. If you rename the column in the Business Model and Mapping layer, corresponding presentation columns are automatically renamed wherever they appear in the Presentation layer. The reverse is not true. If you rename a presentation column, it does not impact the corresponding logical column in the Business Model and Mapping layer.
However, as with subject areas and tables, the repository stores an alias for the column using the previous name.
a. Expand the Dim-Customer presentation table.
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 5: Building the Presentation Layer of a Repository
b. Confirm that the columns are now in the order you specified in the previous step.
c. Double-click the District column. The Presentation Column dialog box opens.
d. Click the General tab.
e. Deselect Use Logical Column Name. The Name field can now be edited.
f. Change the column name by entering Sales District in the Name field.
g. Click the Aliases tab and observe that the original logical column name (alias) is stored for this presentation column.
h. Return to the General tab.
i. Which logical column does this presentation column map to?
j. Which business model does this presentation column map to?
k. Which logical table does this presentation column map to?
l. Click the Edit button. The Logical Column dialog box opens.
m. Click the General tab in the Logical Column dialog box.
n. Click the Column Source tab.
o. Which physical table and column does this presentation column map to?
p. Is this the original physical table that you imported, or the alias table that you created?
q. Click OK to close the Logical Column window.
r. Click OK to close the Presentation Column window.
5. In this step, you simplify the content by deleting unnecessary presentation columns. You may not want to expose all the logical columns of a business model in a subject area. You can delete columns from the Presentation layer safely without affecting the existence of the corresponding logical columns in the Business Model and Mapping Layer. For example, key columns in presentation tables can be deleted unless the client tools require that key
information be provided.
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ฺ
a. In the Presentation layer, in the Dim-Customer table, delete the Customer Key column.
b. Click Yes to confirm the deletion.
c. In the Dim-Product table, delete the Product Key column.
d. Click Yes to confirm the deletion.
e. In the Business Model and Mapping layer, expand Dim-Customer and Dim-Product and confirm that the Customer Key and Product Key logical columns are not deleted.
6. Rename presentation tables.
a. Rename the dimension presentation tables, but not the Fact-Sales table:
Presentation table Rename as:
Dim-Customer Customer Dim-Time Time Dim-Product Product
b. Double-click the Customer presentation table to open the Presentation Table properties dialog box.
c. Click the Columns tab and notice that changing the presentation table name does not impact the column mappings.
d. Click the Aliases tab and notice that the previous name for the presentation table has been stored.
e. Click OK to close the Presentation Table dialog box.
f. Notice also that changing the presentation table names in the Presentation layer has no impact on the logical table names in the Business Model and Mapping layer.
7. Save the repository.
8. Click No when prompted to check global consistency.
9. Leave the repository open for the next practice.
Congratulations! You have successfully built the Presentation layer of a repository.
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 5: Building the Presentation Layer of a Repository