As Published In July/August 2010

TECHNOLOGY: Business Intelligence

Deriving and Sharing Business Intelligence Metadata

By Mark Rittman

Integrate Oracle Warehouse Builder 11g Release 2 information with Oracle Business Intelligence Enterprise Edition.

If you’ve developed a repository for use with Oracle Business Intelligence Enterprise Edition, you’ve been creating the repository by using the Oracle Business Intelligence Enterprise Edition Administration tool. With the release of Oracle Warehouse Builder 11g Release 2, however, you now have the option of creating and maintaining a repository from within your Oracle Warehouse Builder project, giving you the ability to develop your business intelligence (BI) repository alongside your data warehouse, sharing the same metadata, business definitions, data lineage, and development environment.

In this column, you will see how you can use Oracle Warehouse Builder 11g Release 2 to create a repository for use with Oracle Business Intelligence Enterprise Edition 10g Release 3. The Oracle Warehouse Builder project for the column contains an Oracle Database module from which I will automatically derive BI metadata into a corresponding Oracle Business Intelligence module—complete with dimensions, facts, and hierarchies—ready for deployment in the Oracle Business Intelligence Enterprise Edition environment.

The examples in this article require the following software to be installed in your environment: 

• Oracle Warehouse Builder 11g Release 2, either embedded in an Oracle Database 11g Release 2 instance or installed standalone

• Oracle9i Database, Oracle Database 10g, or Oracle Database 11g

• Oracle Business Intelligence Enterprise Edition 10g Release 3

To execute the steps in this column, also download the Oracle Warehouse Builder 11g Release 2 metadata export file, which contains a file called obiee_intgr.mdl.

Feature Overview

Oracle Warehouse Builder 11g Release 2 can derive or manually create BI metadata for a wide range of datasources, but the example in this column is based on an Oracle Database module that contains dimensional metadata (dimensions, cubes, and hierarchies) that Oracle Warehouse Builder will automatically translate into the dimensional metadata that Oracle Business Intelligence Enterprise Edition uses in its metadata layer.

Oracle Business Intelligence Enterprise Edition stores its metadata in a repository file comprising three layers: 

• A physical layer, which contains metadata on physical sources, including table definitions, primary and foreign keys, and database types

• A business model and mapping layer, which holds the logical dimensional model used by tools such as Oracle Business Intelligence Answers, together with calculations and other derived data

• A presentation layer, which contains subsets of the business model and the mapping layer

When Oracle Warehouse Builder 11g Release 2 creates BI metadata for use with Oracle Business Intelligence Enterprise Edition, it can take the dimensions and cubes defined within an Oracle Database module and translate these into the dimensional metadata used by the Oracle Business Intelligence Enterprise Edition repository, in a process called derivation . Once your BI metadata has been derived, you can add to or enhance it with more calculations or logical structures to meet the needs of your end users.

Alternatively, if you do not have dimensions or cubes defined within your Oracle Warehouse Builder project, you can create the Oracle Business Intelligence module metadata you require manually, mapping this metadata to the relational structures in your Oracle Warehouse Builder project. This is a more complex process and is outside the scope of this article, but if you are interested in reading more about it, you can find detailed information on manual BI metadata creation in Oracle Warehouse Builder Sources and Targets Guide 11g Release 2 .

Creating an Oracle Business Intelligence Module

Before you can start creating metadata for use with Oracle Business Intelligence Enterprise Edition, you must create an Oracle Business Intelligence module within your Oracle Warehouse Builder project. 

1. Log in to the Oracle Warehouse Builder 11g Release 2 Design Center, connect as a workspace owner, and locate the Projects Navigator. From the Application menu, select File -> Import -> Oracle Warehouse Builder Metadata . When the Import dialog box is displayed, use the Browse button to select the obiee_intgr.mdl metadata export file contained in the zip file you downloaded earlier, and then click Import to load the project metadata into your workspace.

2. From within the Projects Navigator, open the OBIEE_INTGR project you have just imported. Within the project, locate the Business Intelligence entry, expand it, and then right-click the Oracle Business Intelligence entry. Select New Oracle Business Intelligence , and the Create Module dialog box will appear.

3. In the Create Module dialog box, name the module OBIEE_DEMO . Click Next to proceed to the Connection Information page in the wizard, and then enter obiee_demo.udml as the filename, together with the connection details for either a file, FTP, HTTP, or HTTPS location. This location is where Oracle Warehouse Builder will deploy the obiee_demo.udml metadata exchange file that will be used to create your Oracle Business Intelligence Enterprise Edition repository and typically either points to a directory on the same server on which Oracle Warehouse Builder is running or to a location that is accessible to Oracle Business Intelligence Enterprise Edition. Click Test Connection to check whether the location is valid, and then click OK , Next , and Finish to create the module.

In the Projects Navigator, expand the new OBIEE_DEMO Oracle Business Intelligence module you just created, and note that it has three subfolders: 

• Logical Tables , which contains the logical fact and dimension table metadata that will populate the business model and mapping layer in your Oracle Business Intelligence Enterprise Edition repository

• Dimension Drill Paths , which contains the dimensions that will also go into the business model and mapping layer

• Catalog Folders , which holds the metadata that will be used for the presentation layer in your Oracle Business Intelligence Enterprise Edition repository

  Right-click the Catalog Folders subfolder within your Oracle Business Intelligence module, and select the New Catalog Folder option. Name the new folder DW , and click Next , Next , and Finish to create it.

Deriving Oracle Business Intelligence Enterprise Edition Metadata

Now that you have a suitable Oracle Business Intelligence module, you can create metadata for it. 

1. In the Projects Navigator within the Oracle Warehouse Builder 11g Release 2 Design Center, locate the DW Oracle Database module that contains the data warehouse metadata on which you want to base the BI metadata, right-click it, and select Derive . The Perform Derivation wizard will appear. 

2. In the wizard, ensure that all of the dimensions, the relational tables, and the cube are selected for derivation, as shown in Figure 1. 

   Figure 1: Selecting Oracle Warehouse Builder database objects for derivation

 

3. Click Next , and then select the DW catalog folder within the OBIEE_DEMO Oracle Business Intelligence module as the derivation target.

 

4. Click Next on the next four pages of the wizard, accepting the default values, and then click Finish . When the wizard steps are complete, Oracle Warehouse Builder will derive the corresponding Oracle Business Intelligence Enterprise Edition metadata objects for the objects in this database module.

5. Navigating to the Business Intelligence -> Oracle Business Intelligence entry in the Projects Explorer, expand all the entries in the OBIEE_DEMO module you created earlier. You will see logical tables, dimension drill paths, and catalog folder tables corresponding to the dimensions, tables, and cube you selected for derivation in the previous steps, as shown in Figure 2.

    

   Figure 2: Derived business intelligence metadata objects in the Projects Explorer

   
   
   

   Figure 2 shows BI metadata objects at the table and dimension levels. To see the individual columns, levels, and hierarchies contained within them, you can double-click them to open them for viewing and editing.

6. Double-click the SALES_FACT logical table within the OBIEE_DEMO Oracle Business Intelligence module, and click the Items tab to see the logical columns contained within it. This list contains two columns, AMOUNT and DISCOUNT , that you can use to create an additional derived logical column within this logical table. With the SALES_FACT table open for editing and with the Items tab active, navigate to the end of the list of logical columns and enter as the name of a new logical column at the end of the column list.
   

   Then, with this new column selected, locate the PropertiesInspector dialog box (typically on the right-hand side of the Oracle Warehouse Builder user interface) and find the Expression Text property. Click the ellipsis button next to the Property text field, and then use the Expression Builder dialog box to enter the expression AMOUNT - DISCOUNT.

   Click Validate to check that the expression is valid, and then click OK to save the logical column expression.

7. Finally, from the Oracle Warehouse Builder Application menu, select File -> Save All to save your project changes.

Deploying the Model to Oracle Business Intelligence Enterprise Edition

Deploying your Oracle Business Intelligence module is a two-stage process: first you deploy the module to create a metadata definition file, and then you use an Oracle Business Intelligence Enterprise Edition utility to turn the file into a new repository file. The repository file can then be used on its own or merged into an existing repositor

1. Within the Projects Navigator, locate the OBIEE_DEMO Oracle Business Intelligence module you created previously, right-click it, and select Deploy .

2. In the Log, check that no errors were reported in the deployment. If there are errors, correct them and then redeploy the module. Next, locate the obiee_demo.udml file that was generated as part of the module deployment, and if necessary, copy or transfer it to the location where Oracle Business Intelligence Enterprise Edition is installed.

3. In the location where Oracle Business Intelligence Enterprise Edition is installed and where the obiee_demo.udml metadata definition file is now located, run the following command from the command line, using {ORACLE_BI} for the location where your Oracle Business Intelligence Server client tools are installed: 

   nqudmlexec -I c:\obiee_demo.udml -O OBIEE_DEMO.rpd
   

The third step will generate a new Oracle Business Intelligence Enterprise Edition repository that you can open and view, using the Oracle Business Intelligence Enterprise Edition Administration tool. Figure 3 shows the new repository.

Figure 3: Oracle Business Intelligence Enterprise Edition repository in the Oracle Business Intelligence Enterprise Edition Administration tool

You can now use this repository on its own or use the File -> Merge feature within the Oracle Business Intelligence Enterprise Edition Administration tool to merge it into another repository, merging metadata from your Oracle Warehouse Builder data warehouse into an existing semantic model.

Summary

Oracle Warehouse Builder 11g Release 2 delivers tight integration between your data warehouse design and the metadata model used by Oracle Business Intelligence Enterprise Edition. Creating BI metadata within Oracle Warehouse Builder means that you have to define data warehouse facts, dimensions, and calculations only once, and any changes you make to your data warehouse project can be quickly and easily reflected in the BI metadata model your reports use.  

Mark Rittman is an Oracle ACE director and cofounder of Rittman Mead Consulting, a U.K.-based Oracle partner providing specialized business intelligence, data warehousing, and performance management solutions.

Send us your comments