OBIEE Learning – Creating a Repository


Build the Physical layer of a repository

The Physical layer defines the data sources to which Oracle BI Server submits queries and the relationships between physical databases and other data sources that are used to process multiple data source queries. The recommended way to populate the Physical layer is by importing metadata from databases and other data sources. The data sources can be of the same or different varieties. You can import schemas or portions of schemas from existing data sources. Additionally, you can create objects in the Physical layer manually.

When you import metadata, many of the properties of the data sources are configured automatically based on the information gathered during the import process. After import, you can also define other attributes of the physical data sources, such as join relationships, that might not exist in the data source metadata. There can be one or more data sources in the Physical layer, including databases, flat files, XML documents, and so forth. In this example, you import and configure tables from the BISAMPLE schema.

To build the Physical layer of a repository, you perform the following steps:

  1. Creating a New Repository
  2. Importing Metadata
  3. Verifying Connection
  4. Creating Aliases
  5. Creating Physical Keys and Joins

Creating a New repository

Select Start > Programs > Oracle Business Intelligence > BI Administration to open the Administration Tool.

alt description here

Select File > New Repository.

alt description here

Select the Binary method.

alt description here

Enter a name for the repository. In this tutorial the repository name is BISAMPLE.

alt description hereLeave the default location as is. It points to the default repository directory.

Leave Import Metadata set to Yes.

Enter and retype a password for the repository. In this tutorial BISAMPLE1 is the repository password.

alt description here

Click Next.

Importing Metadata-

Change the Connection Type to OCI 10g/11g. The screen displays connection fields based on the connection type you selected.

alt description here

Enter a data source name. In this example the data source name is orcl. This name is the same as the tnsnames.ora entry for this Oracle database instance.

alt description here

Enter user name and password for the data source. In this example the username and password are both BISAMPLE. Recall that BISAMPLE is the name of the user/schema you created in the prerequisite section.

alt description hereClick Next.

Accept the default metadata types and click Next.

alt description here

In the Data source view, expand the BISAMPLE schema.

alt description here

Use Ctrl+Click to select the following tables from BISAMPLE schema:

SAMP_ADDRESSES_D
SAMP_CUSTOMERS_D
SAMP_PRODUCTS_D
SAMP_REVENUE_F
SAMP_TIME_DAY_D

alt description here

Click the Import Selected button to add the tables to the Repository View.

alt description here

The Connection Pool dialog box appears. Accept the defaults and click OK.

alt description here

The Importing message appears.

alt description here

When import is complete, expand BISAMPLE in the Repository View and verify that the five tables are visible.

alt description here

Click Finish to open the repository.

alt description here

Expand orcl > BISAMPLE and confirm that the five tables are imported into the Physical layer of the repository.

alt description here

Verifying Connections

Select Tools > Update All Row Counts.

alt description here

When update row counts completes, move the cursor over the tables and observe that row count information is now visible, including when the row count was last updated.

alt description here

Expand tables and observe that row count information is also visible for individual columns.

alt description here

Right-click a table and select View Data to view the data for the table.

alt description here

Close the View Data dialog box when you are done. It is a good idea to update row counts or view data after an import to verify connectivity. Viewing data or updating row count, if successful, tells you that your connection is configured correctly.

alt description here

Creating Aliases

It is recommended that you use table aliases frequently in the Physical layer to eliminate extraneous joins and to include best practice naming conventions for physical table names. Right-click SAMP_TIME_DAY_D and select New Object > Alias to open thePhysical Table dialog box.

Enter D1 Time in the Name field.

alt description here

In the Description field, enter Time Dimension Alias at day grain. Stores one record for each day.

alt description here

Click the Columns tab. Note that alias tables inherit all column definitions from the source table.

alt description here

Click OK to close the Physical Table dialog box.

Repeat the steps and create the following aliases for the remaining physical tables.

SAMP_ADDRESSES_D = D4 Address

SAMP_CUSTOMERS_D = D3 Customer

SAMP_PRODUCTS_D = D2 Product

SAMP_REVENUE_F = F1 Revenue

alt description here

 

Advertisements

One thought on “OBIEE Learning – Creating a Repository

  1. Pingback: OBIEE Learning – Build Business Model and Mapping layer of a repository | Oracle EPM/BI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s