Creating a Repository Using the Oracle Business Intelligence Administration Tool ( part 4 )

LAB 2: Managing Logical Table Sources

In this set of steps you create multiple logical table sources for the D3 Customer logical table. To create multiple logical table sources you perform the following steps:

• Open the Repository in Offline Mode

• Add a New Logical Table Source

• Create Presentation Layer Objects

• Load the Repository

• Create and Run an Analysis

• Check the Query Log

Open the Repository in Offline Mode

1 .	Return to the Administration Tool, which should still be open. If not, select Start > Programs > Oracle Business Intelligence > BI Administration.
2 .	Select File > Open > Offline.
3 .	Select the BISAMPLE repository with the highest number extension. In this example it is BISAMPLE_BI0025.rpd. Yours may be BISAMPLE_BI0001, or something similar.
4 .	Click Open. You should receive a message that this repository can only be opened as Read-Only. Recall that this is the repository that is currently loaded into Oracle BI Server memory. You want to use this version of the repository moving forward because it has the user and logging level information you defined earlier.
5 .	Click Yes to open the Open Offline dialog box. The Repository Password dialog box opens.
6 .	Enter BISAMPLE as the repository password.
7 .	Click OK to open the repository in Read Only mode.
8 .	Select File > Save As to open the Save As dialog box.
9 .	Select BISAMPLE.rpd to enter it in the File name field.
10 .	Click Save.
11 .	You should receive the message: BISAMPLE.rpd already exists. Do you want to replace it?
12 .	Click Yes to open the BISAMPLE repository in offline mode. This action replaces the existing BISAMPLE repository with the BISAMPLE repository that contains the security information.
13 .	Select Manage > Identity to open Security Manager.
14 .	Select BI Repository in the left pane.
15 .	Confirm that your administrative user is visible in the right pane. In this example the administrative user is weblogic.
16 .	Double-click the administrative user to open the User dialog box. On the User tab, confirm that logging level is set to 2.
17 .	Click Cancel to close the User dialog box.
18 .	Select Action > Close to close Security Manager. The offline BISAMPLE repository now has a user with a logging level set to 2. This will allow you to check the query log as you complete the remaining exercises in this tutorial. You will not have to repeat the steps of saving an online repository.

Add a New Logical Table Source

1 .	In the BMM layer, expand Sample Sales > D3 Customer > Sources. Notice that the D3 Customer logical table has one logical table source named D3 Customer.
2 .	Rename the D3 Customer logical table source (not the logical table) to LTS1 Customer.
3 .	Double-click LTS1 Customer to open the Logical Table Source dialog box.
4 .	Click the Column Mapping tab and notice that all logical columns map to physical columns in the same physical table: D3 Customer. It may be necessary to scroll to the right to see the Physical Table column. Make sure "Show mapped columns" is selected.
5 .	Click OK to close the Logical Table Source dialog box.
6 .	In the Physical layer, expand orcl > BISAMPLE.
7 .	Drag D4 Address from the Physical layer to the D3 Customer logical table in the BMM layer. Notice this creates a new logical table source named D4 Address for the D3 Customer logical table. It also creates new logical columns that map to the D4 Address physical table.
8 .	In the BMM layer, double-click the new D4 Address logical table source to open the Logical Table Source dialog box.
9 .	On the General tab, enter LTS2 Customer Address in the Name field.
10 .	Click the Column Mapping tab and notice that all logical columns map to physical columns in the same physical table: D4 Address. If necessary, select Show mapped columns and deselect Show unmapped columns.
11 .	Click the red X next to ADDRESS_KEY to delete the column. This is a duplicate column and is not needed.
12 .	Click OK to close the Logical Table Source dialog box.
13 .	Confirm that the D3 Customer logical table now has two logical table sources: LTS1 Customer and LTS2 Customer Address. A single logical table now maps to two physical sources.
14 .	Use the Rename Wizard or a manual renaming technique to rename the address logical columns in D3 Customer. Your results should look similar to the screenshot. Hint: To use the Rename Wizard, select all of the new logical columns, then right-click any one of the highlighted columns and select Rename Wizard to launch the wizard. If you need help using the Rename Wizard, refer to these steps from earlier in this tutorial.
15 .	Rename the remaining logical table sources according to the following table. Recall that logical table sources are located in the Sources folder for a logical table. For example: D2 Product > Sources. Logical Table Source Rename D2 Product LTS1 Product F1 Revenue LTS1 Revenue Your results should look similar to the screenshot.

Create Presentation Layer Objects

1 .	In the Presentation layer, right-click the Sample Sales subject area and select New Presentation Table to open the Presentation Table dialog box.
2 .	On the General tab, enter Customer Regions in the Name field.
3 .	Click OK to close the Presentation Table dialog box. Confirm that the Customer Regions presentation table is added to the Sample Sales subject area in the Presentation layer.
4 .	In the BMM layer, expand Sample Sales > D3 Customer.
5 .	Drag the following logical columns from D3 Customer to Customer Regions in the Presentation layer: Address 1 Address 2 Area City Country Name Estab Name Postal Code Region State Province State Province Abbrv Your column names may be slightly different depending on how you renamed them.
6 .	Reorder the Customer Regions presentation columns in the following order, from top to bottom: Region Area Country Name State Province State Province Abbrv City Postal Code Address 1 Address 2 Estab Name
7 .	Double-click the Sample Sales subject area in the Presentation layer to open the Subject Area dialog box.
8 .	Click the Presentation Tables tab.
9 .	Reorder the presentation tables so that Customer Regions appears after Customers.
10 .	Click OK to close the Subject Area dialog box. Confirm that the presentation tables appear in the expected order. You now have two presentation tables, Customers and Customer Regions, mapped to the same logical table, D3 Customer. The D3 Customer logical table is mapped to two physical sources: D3 Customer and D4 Address.
11 .	Save the repository and check global consistency when prompted. You should receive a message that there are no errors, warnings, or best practice violations to report. If you do receive any consistency check errors or warnings, fix them before proceeding.
12 .	Click OK to close the consistency check message.
13 .	Close the repository. Leave the Administration Tool open.

Load the Repository

1.	Return to Fusion Middleware Control Enterprise Manager, which should still be open. If not, open a browser and enter the following URL to navigate to Fusion Middleware Control Enterprise Manager: http://<machine name>/:7001/em In this tutorial the URL is http://localhost:7001/em
2.	If your session has timed out, you will need to log in again. Log in as an administrative user. Typically you use the administrative user name and password provided during the Oracle BI installation. In this example the user name is weblogic.
3 .	In the left navigation pane, expand Business Intelligence and click coreapplication.
4 .	Click the Deployment tab.
5 .	Click the Repository sub tab.
6 .	Click Lock and Edit Configuration.
7 .	Click Close when you receive the confirmation message Lock and Edit Configuration - Completed Successfully.
8 .	Click Browse and navigate to the directory with the BISAMPLE repository.
9 .	Select the BISAMPLE.rpd file and click Open.
10 .	Enter BISAMPLE as the repository password and confirm the password.
11 .	Click Apply.
12 .	Confirm that the default RPD is now BISAMPLE with an extension. In this example the file name is BISAMPLE_BI0025.
13 .	Click Activate Changes.
14 .	Click Close when you receive the confirmation message Activate Changes - Completed Successfully.
15 .	Click Restart to apply recent changes to navigate to the Overview page (this may take a moment).
16 .	On the Overview page, click Restart.
17 .	Click Yes when you receive the message Are you sure you want to restart all BI components?
18 .	Allow the processing to complete.
19 .	Click Close when you receive the message Restart All - Completed Successfully.

Create and Run an Analysis

1.	Return to Oracle BI, which should still be open. If not, open a browser or browser tab and enter the following URL to navigate to Oracle Business Intelligence: http://<machine name>/:9704/analytics In this tutorial the URL is http://localhost:9704/analytics.
2.	If your previous session has timed out, sign in as an administrative user. Typically you use the administrative user name and password provided during the Oracle BI installation. In this example the user name is weblogic.
3 .	In the left navigation pane, under Create... Analysis and Interactive Reporting, select Analysis.
4 .	Select the Sample Sales subject area.
5 .	In the left navigation pane, expand the folders and confirm that the Customer Regions folder and corresponding columns appear.
6 .	Create the following analysis by double-clicking column names in the Subject Areas pane: Customer Regions.Region Customers.Customer Name Products.Type Base Facts.Revenue
7 .	Click Results to view the analysis results. Use the buttons at the bottom of the results screen to see more rows.

Check the Query Log

1 .	Return to Fusion Middleware Control Enterprise Manager, which should still be open.
2 .	Click the Diagnostics tab.
3 .	Click the Log Messages sub tab.
4 .	Scroll to the bottom of the window to the View / Search Log Files section.
5 .	Click Server Log to navigate to the Log Messages screen.
6 .	In the Log Messages screen, leave the data range set to Most Recent, 1 Days. Deselect all message types except for Trace.
7 .	In the Message field, enter sending query to database.
8 .	Click Search.
9 .	Select the last message in the list. This is the most recent query sent to the database.
10 .	In the bottom pane, click the Collapse Pane button (arrow on the right side) to view the log message. Your results should look similar to the screenshot. The screenshot shows only a partial view of the log. Notice that both D3 Customer and D4 Address are accessed. The presentation columns included in the analysis were selected from two different presentation tables: Customers and Customer Regions. Both of these presentation tables map to the same logical table, D3 Customer. The D3 Customer logical table maps to two physical sources: D3 Customer and D4 Address.
11 .	Click the Restore Pane button.
12 .	Leave Enterprise Manager open.