Working with Physical Layer in OBIEE 12c Repository
At end of the previous article obiee-12c-repository-introduction, we saw the 1st step to Create New Repository: Repository Information.
Now we will move forward with
Step 2: Select Data Source
- Change the Connection Type to OCI 10g/11g. The screen displays connection fields based on the connection type you selected.
- Enter a data source name. In this example the data source name is xxx.yyy.zz.ttt/pdborcl. This name is the same as the tnsnames.ora entry for this Oracle database instance.
- Enter a user name and password for the data source.
- Click Next
Step 3: Select metadata Types
Accept the default metadata types and click next.
Step 4: Importing Data Source
- In the Data source view, expand the SUP_V2 schema.
- Use Ctrl+Click to select the required tables.
- Click the Import Selected button to add the tables to the Repository View.
Step 5: Setting up connection pool
- The Connection Pool dialog box appears.
- Change the Name to Sup_V2_Con.
- Other fields keep default values
- Click Ok
- The Importing message appears.
- When import is complete, expand Sup_V2 in the Repository View and verify that the required tables are visible.
Step 6: Click Finish to open the repository.
Double Click xxx.yyy.zz.tt/pdborcl. Change the name to Supermarket & Click ok.
Expand orcl > Sup_V2 and confirm that the tables are imported into the Physical layer of the repository.
Examining physical layer object
- Select Tools > Update All Row Counts.
- 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.
- Expand tables and observe that row count information is also visible for individual columns.
- Right-click a table and select View Data to view the data for the table
Creating alias table
- 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 Dim_Brand and select New Object > Alias to open the Physical Table dialog box.
- Enter D1 Brand in the Name field.
- Click the Columns tab. Note that alias tables inherit all column definitions from the source table.
- Repeat the steps and create the aliases for the remaining physical tables.
Defining joins and keys
- Select the alias tables in the Physical layer.
- Right-click one of the highlighted alias tables and select Physical Diagram > Selected Object(s) Only to open the Physical Diagram.
- Rearrange the alias table objects so they are all visible. If desired, right-click in the white space of the diagram and select Zoom to change the size of the objects in the Physical Diagram.
- Click the New Join button on the toolbar.
- Click the F6 Sales table and then the D17 Product.
- The Physical Foreign Key dialog box opens. It matters which table you click first.
- The join creates a one-to-many (1:N) relationship that joins the key column in the first table to a foreign key column in the second table.
- Click OK to close the Physical Foreign Key dialog box. The join is visible in the Physical Diagram.
- Repeat the steps to create joins for the remaining tables.
- Click the X in the upper right corner to close the Physical Diagram.
- Select File > Save or click the Save button on the toolbar to save the repository.
Click No when prompted to check global consistency. Checking Global Consistency checks for errors in the entire repository. Some of the more common checks are done in the Business Model and Mapping layer and Presentation layer. Since these layers are not defined yet, bypass this check until the other layers in the repository are built.
In next tutorial we will see how to Build the Business Model and Mapping Layer.