Creating a Repository Using the Oracle BI Administration Tool
Creating a Repository Using the Oracle Business Intelligence Administration Tool
This tutorial shows you how to use the Oracle BI EE Administration
Tool to build, modify, enhance, and manage an Oracle BI repository.
Approximately 4 hours
This tutorial covers the following topics:
Place the cursor over this icon to load and view all the screenshots for this tutorial.
(Caution: This action loads all screenshots simultaneously, so response
time may be slow depending on your Internet connection.)
Note: Alternatively, you can place the cursor
over each individual icon in the following steps to load and view only the screenshot
associated with that step. Click a screenshot to hide it.
This tutorial shows you how to build an Oracle BI metadata repository. You learn how to import metadata from databases and other data sources, simplify and reorganize the imported metadata into a business model, and then structure the business model for presentation to users who request business intelligence information via Oracle BI user interfaces, such as Oracle BI Answers and Oracle BI Interactive Dashboards.
Back to Topic List
Create a directory named SetupFiles under <InstallDrive>. Download and extract the SH.zip file from here to this directory named SetupFiles.
This tutorial is for a Windows environment only.
Note: If you are a Oracle employee and are using the
VMWare environment, you can skip the subtopic Create an ODBC
Data Source. Make sure you have 2 GB RAM and 15 GB hard disk space and have
VMWare 1.5.x installed on your machine. If you do not have VMWare software,
download a copy of VMWare Player from http://www.vmware.com/download/player/.
For all others, before starting this tutorial, you should
meet the following prerequisites:
| 1. |
Have access to or have installed Oracle
10g Database.
|
| 2. |
Have access to or have installed the sample schema. This tutorial uses the SH schema included in the Oracle10g
Database.
You can also install the schema by following the instructions
found in the Installing
the Sample Schemas and Establishing a Database Connection
OBE tutorial.
|
| 3. |
Have access to or have installed Oracle BI EE 10.1.3.2.
|
Back to Topic List
In this topic you use the Oracle BI Administration Tool to 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, spreadsheets, and XML documents. In this example, you import and configure tables from the sample SH schema included with the Oracle 10g database.
To build the Physical layer, you perform the following steps:
Create a New Repository
To create a new repository, perform the following steps:
1. |
Click Start > Programs > Administrative Tools > Services.
|
2. |
In the Services dialog
box, stop the Oracle BI Server service.
|
| 3. |
Click Start > Programs > Oracle Business Intelligence > Administration to open the Oracle BI Administration Tool.
|
| 4. |
Click File > New to open the New Repository dialog box.
|
| 5. |
In the New Repository dialog box, the Repository folder is selected by default. Name the file SH.rpd.
|
| 6. |
Click Save. The new repository opens in the Administration Tool and displays three empty layers: Presentation, Business Model and Mapping, and Physical. If necessary, select Tools > Options > Show toolbar to display the toolbar.
The Physical layer is where information on physical data sources is stored. The Business Model and Mapping layer is where measurements and terms used in business are mapped to the physical data sources. The Presentation layer is where the business model is customized for presentation to the user. You can work on each layer at any stage in creating a repository, but the typical order is to create the Physical layer first, then the Business Model and Mapping layer, and then the Presentation layer.
Leave the Administration Tool open. |
Back to Topic
Create an ODBC Data Source
An ODBC data source is needed to import schema information about a data source into an Oracle BI Server repository. To create an ODBC data source for importing the SH schema information, perform the following steps:
| 1. |
Click Start > Programs > Administrative Tools > Data Sources (ODBC) to open the ODBC Data Source Administrator.
|
| 2. |
Click the System DSN tab and click Add.
The Create New Data Source dialog box opens.
|
| 3. |
In the Create New Data Source dialog box, select the Oracle driver.
|
| 4. |
Click Finish to open the Oracle ODBC Driver Configuration dialog box. |
| 5. |
In the Oracle ODBC Driver Configuration dialog box, enter a Data Source Name (this can be any name), select the appropriate TNS Service Name from the drop down list (ORCL in this example), and enter SH as the User ID for the SH schema.
|
| 6. |
Click Test Connection to open the Oracle ODBC Driver Connect dialog box.
|
| 7. |
In the Oracle ODBC Driver Connect dialog box, enter the password SH for the SH schema and click OK.
|
| 8. |
You should see a "Testing Connection" message indicating the connection is successful.
|
| 9. |
Click OK to close the Testing Connection message. |
| 10. |
Click OK to close the Oracle ODBC Driver Configuration dialog box. |
| 11. |
Verify that the SH system data source is added in the ODBC Data Source Administrator and click OK to close the ODBC Data Source Administrator.
|
Back to Topic
Import SH Schema
To import the SH schema information into the repository, perform the following steps:
| 1. |
In the Oracle BI Administration Tool, click File > Import > from Database . . .
|
| 2. |
In the Select Data Source dialog box, select the SH ODBC DSN you created in a previous step, enter SH as the user name and password, and click OK to open the Import dialog box.
|
| 3. |
In the Import dialog box, locate the SH schema folder.
|
| 4. |
Expand the SH schema folder and use Ctrl + Click to select the following tables:
CHANNELS, COSTS, COUNTRIES, CUSTOMERS, PRODUCTS, PROMOTIONS, SALES, TIMES
|
| 5. |
Verify that only the Tables and Keys checkboxes are selected, which is the default, and click the Import button. The Connection Pool dialog box opens.
It is best practice to only import objects needed to support your business model. However, don’t worry if you import extra objects at this point. You can always delete objects later if they do not support your business model.
|
| 6. |
In the Connection Pool dialog box, on the General tab, verify that the call interface is set to Default (OCI 10g) and change the data source name to the appropriate tnsnames.ora entry ( ORCL in this example). Please note that this is the TNS service name, not the ODBC DSN.
|
| 7. |
Leave the rest of the settings as they are and click OK to close the Connection Pool dialog box. The import process starts. |
| 8. |
When the Import process completes, click Close to close the Import dialog box. |
| 9. |
In the Physical layer of the repository, expand the SH schema folder and verify that the correct tables are imported.
|
| 10. |
To verify connectivity, click Tools > Update All Row Counts. |
| 11. |
When update all row counts completes, verify that the row counts are displayed in the Physical layer of the Administration Tool:
|
Back to Topic
Create Physical Joins
To create physical joins in the Physical layer of the repository, perform the following steps:
| 1. |
In the Physical layer, right-click the SH schema folder and select Physical Diagram > Object (s) and All Joins. Alternatively, you can select the SH database object and then click the Physical Diagram button on the toolbar.
|
| 2. |
In the Physical Diagram, if the tables are too big, right-click in the white space, select Zoom, and adjust the size.
|
| 3. |
Rearrange the tables so they are all visible in the Physical Diagram.
|
| 4. |
Click the New Foreign Key button on the toolbar.
|
| 5. |
Click the Channels table and then the Sales table. The Physical Foreign Key dialog box opens.
It matters which table you click first. The join is creating 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. The Administration Tool makes a best "guess" and automatically determines which columns should be included in the join.
Make sure the join expression is:
CHANNELS.CHANNEL_ID = SALES.CHANNEL_ID
|
| 6. |
Click OK to close the Physical Foreign Key dialog box. The join between Channels and Sales is displayed in the Physical Diagram.
|
| 7. |
Continue to create the following joins:
PROMOTIONS.PROMO_ID = SALES.PROMO_ID
TIMES.TIME_ID = SALES.TIME_ID
PRODUCTS.PROD_ID = SALES.PROD_ID
CUSTOMERS.CUST_ID = SALES.CUST_ID
COUNTRIES.COUNTRY_ID = CUSTOMERS.COUNTRY_ID
Note that Countries joins to Customers and Costs in not joined for now. All other tables join directly to Sales.
When you are finished, click the X in the upper right corner to close the Physical Diagram.
|
| 8. |
Select File > Save or click the Save button on the toolbar to save the repository.
|
| 9. |
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. You learn more about consistency check later in this tutorial.
|
| 10. |
Leave the Administration Tool and the SH repository open for the next topic.
Congratulations! You have successfully created a new repository, set up an ODBC data source, imported a table schema from an external data source into the Physical layer, and defined keys and joins.
In the next topic you learn how to build the Business Model and Mapping layer of a repository. |
Back to Topic
Back to Topic List
In this topic you use the Oracle BI Administration Tool to build the Business Model and Mapping layer of a repository.
The Business Model and Mapping layer of the Administration Tool defines the business, or logical, model of the data and specifies the mappings between the business model and the Physical layer schemas. This is where the physical schemas are simplified to form the basis for the users’ view of the data. The Business Model and Mapping layer of the Administration Tool can contain one or more business model objects. A business model object contains the business model definitions and the mappings from logical to physical tables for the business model.
The main purpose of the business model is to capture how users think about their business using their own vocabulary. The business model simplifies the physical schema and maps the users’ business vocabulary to physical sources. Most of the vocabulary translates into logical columns in the business model. Collections of logical columns form logical tables. Each logical column (and hence each logical table) can have one or more physical objects as sources.
There are two main categories of logical tables: fact and dimension. Logical fact tables contain the measures by which an organization gauges its business operations and performance. Logical dimension tables contain the data used to qualify the facts.
To build the Business Model and Mapping layer, you perform the following steps:
Create a Business Model
To create a new business model, perform the following steps:
| 1. |
In the Business Model and Mapping layer, right-click the white space and select New Business Model.
|
| 2. |
In the Business Model dialog box, name the business model SH and leave the Available for queries box unchecked. The Description edit box is used to add a comment for yourself or another developer. Leave it empty.
|
| 3. |
Click OK to close the Business Model dialog. The new SH business model appears in the Business Model and Mapping layer. The red symbol on the business model indicates it is not yet enabled for querying. You enable the business model for querying later after the Presentation layer is defined and the repository passes a global consistency check.
|
Back to Topic
Create Logical Tables
To create a new logical table, perform the following steps:
| 1. |
In the Business Model and Mapping layer, right-click the SH business model and select New Object > Logical Table.
|
| 2. |
In the Logical Table dialog box, name the table Sales Facts.
|
| 3. |
Click OK to close the Logical Table dialog. The Sales Facts logical table appears in the SH business model in the Business Model and Mapping layer. The Sources folder is populated in the next step when you create a logical column.
|
Back to Topic
Create Logical Columns
To create a logical column, perform the following steps:
| 1. |
In the Physical layer, expand the Sales physical table.
|
| 2. |
Select the column AMOUNT_SOLD.
|
| 3. |
Drag the AMOUNT_SOLD column from the Physical layer to the Sales Facts logical table in the Business Model and Mapping layer. A new AMOUNT_SOLD logical column is added to the Sales Facts logical table.
|
| 4. |
Expand the Sources to see the logical table source that was created automatically when you dragged the AMOUNT_SOLD column from the Physical layer. Logical table sources define the mappings from a logical table to a physical table. A logical table’s Sources folder contains the logical table sources. Because you dragged a column from the Physical layer, the logical table source name, SALES, is the same name as the physical table. However, it is possible to change names in the Business Model and Mapping layer without impacting the mapping.
|
| 5. |
In the Business Model and Mapping layer, double-click the AMOUNT_SOLD logical column to open the Logical Column dialog box.
|
| 6. |
Click the Aggregation tab.
|
| 7. |
In the Default aggregation rule drop down list, select SUM.
|
| 8. |
Click OK to close the Logical Column dialog box. Notice that the logical column icon is changed to indicate an aggregation rule is applied.
|
Back to Topic
Create Logical Joins
To create logical joins in the business model, perform the following steps:
| 1. |
Select the following tables in the Physical layer. Select only these tables:
CHANNELS, CUSTOMERS, PRODUCTS, PROMOTIONS, TIMES
|
| 2. |
Drag the selected tables from the Physical layer onto the SH business model folder in the Business Model and Mapping layer. This automatically creates logical tables in the Business Model and Mapping layer. Notice that each logical table has a yellow table icon. In the Business Model and Mapping layer, this indicates a fact table. Because you have not yet created the logical joins, all table icons are yellow. The icon color for dimension tables changes to white in a later step when you create logical joins.
|
| 3. |
Right-click the SH business model and select Business Model Diagram > Whole Diagram.
|
| 4. |
Rearrange the table icons so they are all visible. Place the Sales Facts table in the middle. Adjust the zoom factor, if desired.
|
| 5. |
Click the New Complex Join button in the toolbar.
|
| 6. |
Click the Channels table icon first and then click the Sales Facts table icon in the Logical Table Diagram window. The order is important. The second table clicked is the many side of the relationship. The Logical Join dialog box opens. Leave the default values as they are, but note which properties you can set: name, business model, tables, driving table, join type, and cardinality. Also note which properties you cannot set: the join expression and the join columns. Typically, when defining logical joins, you leave the defaults as they are.
|
| 7. |
Do not change the default values, click OK, and verify your work in the Logical Table Diagram. Notice that the fact table, Sales Facts, is at the many end of the join:
|
| 8. |
Repeat the steps for the remaining tables. Your final result should look similar to the picture.
|
| 9. |
Click the X in the upper right corner to close the Logical Table Diagram. Notice that the color of the table icons for the dimension tables has changed to white in the business model. In a business model, a yellow icon indicates a fact table and a white icon indicates a dimension table. Defining the join relationships determined which tables are the logical dimension tables and which is the logical fact table. 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, Sales Facts, and five logical dimension tables: Channels, Customers, Products, Promotions, and Times.
|
| 10. |
Save the SH repository. Do not check global consistency. |
Back to Topic
Rename Business Model Objects
To use the Rename Wizard to rename the objects in the business model, perform the following steps:
| 1. |
Click Tools > Utilities.
|
| 2. |
In the Utilities dialog box, click Rename Wizard and then Execute.
|
| 3. |
In the Rename Wizard, click the Business Model and Mapping tab and select the SH business model.
|
| 4. |
Click the Add Hierarchy button.
|
| 5. |
Click Next.
|
| 6. |
Click Select None.
|
| 7. |
Check Logical Table and Logical Column.
|
| 8. |
Click Next.
|
| 9. |
Select All text lowercase.
|
| 10. |
Click Add.
|
| 11. |
Click Change specified text. In the Find box, type an underscore. In the Replace box, type a space.
|
| 12. |
Click Add.
|
| 13. |
Click First letter of each word capital and click Add.
|
| 14. |
Click Change specified text. In the Find box, type a space and the letters Id. In the Replace With box, type a space and the letters ID. Check Case sensitive.
|
| 15. |
Click Add.
|
| 16. |
Click Next and review changes.
|
| 17. |
Click Finish and verify that logical tables and logical columns in the Business Model and Mapping layer are changed as expected.
|
| 18. |
Save the repository. Do not check global consistency.
|
Back to Topic
Delete Unnecessary Business Model Objects
To delete logical columns that are not needed in the business model, perform the following steps:
1. |
For the Channels logical table in the Business Model and Mapping layer, use Ctrl + click to select the Channel Class ID and the Channel Total ID logical columns.
|
2. |
Right-click either of the highlighted columns and select Delete to delete the columns. Alternatively, you can use the Delete key on your keyboard.
|
| 3. |
Click Yes to confirm the delete.
|
| 4. |
Verify that the Channels logical table now has only four logical columns.
|
| 5. |
Repeat the steps to delete the following logical columns in the Customers table:
Cust City ID
Cust State Province ID
Country ID
Cust Main Phone Number
Cust Total ID
Cust Src ID
Cust Eff From
Cust Eff To
Cust Valid
|
| 6. |
Place the cursor over the icon to display the screenshot and verify that the Customers logical table has only the following logical columns:
|
| 7. |
Place the cursor over the icon to display the screenshot and repeat the steps to delete the highlighted logical columns in the Products table.
|
| 8. |
Place the cursor over the icon to display the screenshot and verify that the Products logical table has only the following logical columns:
|
| 9. |
Place the cursor over the icon to display the screenshot and repeat the steps to delete the highlighted logical columns in the Promotions table.
|
| 10. |
Place the cursor over the icon to display the screenshot and verify that the Promotions logical table has only the following logical columns:
|
| 11. |
Do not delete any columns in the Times table.
|
| 12. |
Save the repository. Do not check global consistency.
|
Back to Topic
Build Dimension Hierarchies
Dimension hierarchies introduce formal hierarchies into a business model, allowing Oracle BI Server to calculate useful measures and allowing users to drill down to more detail. In a business model, a dimension hierarchy represents a hierarchical organization of logical columns belonging to a single logical dimension table. Common dimension hierarchies used in a business model are time periods, products, customers, suppliers, and so forth.
Dimension hierarchies are created in the Business Model and Mapping layer and end users do not see them in end user tools such as Oracle BI Answers or Interactive Dashboards.
In each dimension hierarchy, you organize dimension attributes into hierarchical levels. These levels represent the organizational rules and reporting needs required by your business. They provide the structure that Oracle BI Server uses to drill into and across dimensions to get more detailed views of the data. Dimension hierarchy levels are used to perform aggregate navigation, configure level-based measure calculations, and determine what attributes appear when Oracle BI users drill down in their data requests.
To build the Channels dimension hierarchy in the SH business model, perform the following steps:
1. |
Right-click the Channels logical table and select Create Dimension.
|
2. |
Right-click the ChannelsDim object, which was created by the action in the previous step, and select Expand All.
|
| 3. |
Place the cursor over the icon to display the screenshot and verify that the ChannelsDim dimension hierarchy matches the picture.
|
| 4. |
Right-click the Channels Detail level and select New Object > Parent Level.
|
| 5. |
In the Logical Level dialog box, name the logical level Class and set the Number of elements at this level to 3. This number does not have to be exact. The ratio from one level to the next is more important than the absolute number. These numbers only affect which aggregate source is used (optimization, not correctness of queries).
|
| 6. |
Click OK to close the Logical Level dialog box. The new Class level is added to the hierarchy.
|
| 7. |
Right-click the Class level and select Expand All.
|
| 8. |
Drag the Channel Class column from the Channel Detail level to the Class level to associate the logical column with this level of the hierarchy.
|
| 9. |
Right-click Channel Class and select New Logical Level Key.
|
| 10 |
In the Logical Level Key dialog box, verify that Channel Class and Use for drilldown are selected. The level key defines the unique elements in each logical level. Each level key can consist of one or more columns at this level.
|
| 11. |
Click OK to close the Logical Level Key dialog box. The Channel Class column now displays with a key icon.
|
| 12. |
Right-click the Class level and select New Object > Parent Level.
|
| 13. |
In the Logical Level dialog box, name the logical level Channel Total Attribute and set the Number of elements at this level to 1.
|
| 14. |
Click OK to close the Logical Level dialog box. The Channel Total Attribute level is added to the hierarchy.
|
| 15. |
Right-click the Channel Total Attribute level and select Expand All.
|
| 16.. |
Drag the Channel Total column from the Channel Detail level to the Channel Total Attribute level.
|
| 17. |
Right-click Channel Total and select New Logical Level Key.
|
| 18. |
In the Logical Level Key dialog box, verify that Channel Total and Use for drilldown are selected.
|
| 19. |
Click OK to close the Logical Level Key dialog box. The Channel Total column now displays with a key icon.
|
| 20. |
Right-click the Channel Desc column and select New Logical Level Key.
|
| 21. |
In the Logical Level Key dialog box, notice that Use for drilldown is selected.
|
| 22. |
Click OK to close the Logical Level Key dialog. Both Channel Desc and Channel ID display with key icons.
|
| 23. |
Double-click the Channels Detail level to open the Logical Level dialog box.
|
| 24. |
Click the Keys tab.
|
| 25. |
Click Channels Detail_Key.
|
| 26. |
Click the Edit button.
|
| 27. |
In the Logical Level Key dialog box, uncheck Use for drilldown.
|
| 28. |
Click OK to close the Logical Level Key dialog box. Notice that the key icons are different. Channel Desc is used for drill down Channels Detail_Key is not. Later, when a user drills down in Answers or a dashboard, the default drill is to the level key that has Use for drilldown checked in the next lowest level. Based on this example, when a user drills down from the Channel Class column (the next highest level), the default is to drill down to the Channel Desc column, not the Channels Detail_Key column.
|
| 29. |
Click OK to close the Logical Level dialog box.
|
| 30. |
Place the cursor over the icon to display the screenshot and verify that the finished ChannelsDim hierarchy looks like the picture.
|
| 31. |
Save the repository. Do not check global consistency.
|
| 32. |
Optional: Build a dimension hierarchy for the Products logical table. Use the preceding steps, the screenshot, and the characteristics below as a guide. Don't worry if you cannot complete this step. In Part Two of this OBE, you use a different repository that already has the dimension hierarchies prebuilt.
The ProductsDim dimension hierarchy should have the following characteristics:
For the Subcategory level, set Number of elements at this level to 21.
For the Category level, set Number of elements at this level to 5.
For the Product Total Attribute level, set Number of elements at this level to 1.
For the Products Detail level, uncheck Use for drill down for Products Detail_Key (Prod ID).
Check Use for drill down for all other keys in the ProductsDim hierarchy.
If you complete this step, save the repository. Do not check global consistency.
|
Back to Topic
Back to Topic List
In this topic you use the Oracle BI Administration Tool to build the Presentation layer of a repository.
The Presentation layer is built after the Physical layer and Business Model and Mapping layer and adds a level of abstraction over the Business Model and Mapping layer. It is the view of the data seen by end users in client tools and applications, such as Oracle BI Answers. The Presentation layer provides a means to further simplify or customize the Business Model and Mapping layer for end users. For example, you can organize columns into catalogs and folders.
Simplifying the view of the data for users makes it easier to craft queries based on users’ business needs because you can expose only the data that is meaningful to the users, organize the data in a way that aligns with the way users think about the data, and rename data as necessary for the set of users.
You typically create Presentation layer objects by dragging objects from the Business Model and Mapping layer. Corresponding objects are automatically created in the Presentation layer. Presentation layer objects can then be renamed and reorganized.
To build the Presentation layer, you perform the following steps:
| 1. |
Drag the SH business model from the Business Model and Mapping layer to the Presentation layer to create the SH catalog in the Presentation layer.
|
| 2. |
Expand the SH catalog in the Presentation layer. Notice that the tables and columns in the Presentation layer exactly match the tables and columns in the Business Model and Mapping layer. Notice also that dimension hierarchies are not displayed.
|
| 3. |
Save the repository. Do not check global consistency. |
Back to Topic List
You have finished building the initial business model and now need to test the repository before continuing your development. You begin by checking the repository for errors using the check consistency option. You then test the repository by running queries using Oracle BI Answers. Finally, you examine the query log file to verify the SQL generated by Oracle BI Server.
To test and validate a repository, you perform the following steps:
|
Bookmark