Business Intelligence Modeling

To complete your business intelligence application, you should enable your data warehouse for querying, which is one of its main purposes. By implementing the concept of business intelligence objects, Oracle Warehouse Builder integrates with reporting environments, specifically Oracle's, more seamlessly.

The Business Intelligence node on the Project Explorer panel enables you to define sets of Warehouse Builder objects that embrace business reporting tools, for example, Oracle Discoverer and BI Beans (allowing you to build custom applications). The Business Intelligence node contains two additional nodes called Business Definitions and Business Presentations. Business Definition modules store the definitions to be deployed to Discoverer. Business Presentation modules store the report structures that you want to deploy to BI Beans.

In this lesson, you perform hands-on exercises to define Discoverer intelligent objects in Warehouse Builder. Using Oracle Warehouse Builder, you move your design metadata to the Discoverer End User Layer (EUL). Discoverer uses the EUL to hide from the end users the complexity, technical naming conventions, and physical structure of the database.

Approximately 20 minutes

Topics

This tutorial covers the following topics:

Creating and Deploying Business Definitions

Creating the Discoverer End User Layer

Generating and Deploying the Intelligent Objects
Examining the Business Area in OracleBI Discoverer Administrator

Viewing the Data in OracleBI Discoverer Plus

Integration with BI Beans

Place the cursor over this icon to load and view all the screenshots for this tutorial. (Caution: Because this action loads all screenshots simultaneously, 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.

Overview

In this lesson, using Oracle Warehouse Builder you derive and define Business Intelligence (BI) objects that seamlessly integrate with analytical business intelligence tools, such as OracleBI Discoverer and OracleBI Beans. By deploying these BI definitions to your analytical tools, you can perform ad hoc queries on top of the relational or multidimensional data warehouse. The BI objects you derive or define in Warehouse Builder represent equivalent objects in Oracle Discoverer. For BI Beans, the objects are starting templates for your application to be used, for example, in JDeveloper. These definitions are stored under the Business Intelligence node in the Project Explorer of the Design Center.

Back to Topic List

Before starting this tutorial, you should have:

1.

Downloaded the owbdemo_files.zip file and extracted the files to the directory on your computer's hard drive

2.

Completed lessons 1, 2, 3, 4, 5, and 6

3.

OracleBI Discoverer Administrator and OracleBI Discoverer Plus installed on your computer to perform hands-on exercises.

Also, log in to SQL Plus as sys as sysdba and grant the following privileges:

grant create session, create table, create view, create sequence, create procedure to eul_from_owb;

grant select any table to expense_wh;

 

4. Working knowledge of Discoverer

Back to Topic List

Creating and Deploy Business Definitions

In this topic, you learn how to:

Create a Business Definition Module
Derive a BI Metadata for Discoverer
Create a Business Area

Back to Topic List

Creating a Business Definition Module

In this subtopic, you learn how to create a Business Definition module. Business Definition modules contain business definition objects equivalent to the Discoverer EUL objects.

1.

In the Design Center, under Project Explorer, expand the Business Intelligence node. Right-click Business Definitions and select New.

Place the cursor over this icon to see the image

The Create Business Definition Module Wizard is launched.

 

2.

On the Create Business Definition Module: Welcome page, click Next.

Place the cursor over this icon to see the image 

The Create Business Definition Module: Name and Description page appears.

 

3.

On the Create Business Definition Module: Name and Description page, enter EXPENSE_BDM as the name for the Business Definition module. Accept Development as the default module status. Enter Generated by Oracle Warehouse Builder as an optional description. Click Next.

Place the cursor over this icon to see the image

The Create Business Definition Module: Connection Information page appears.

 

4.

On the Create Business Definition Module: Connection Information page, click Edit.

Place the cursor over this icon to see the image

The Edit Discoverer Location page appears.

 

5.

On the Edit Discoverer Location page, specify the connection details.

Note that this is the location/schema that contains the Discoverer End User Layer.

Name: Rename the default location from EXPENSE_BDM_LOCATION1 to EXPENSE_BDM_LOCATION
Username: EUL_FROM_OWB
Password: EUL_FROM_OWB
Port: 1521
Schema: orcl
Version: 10g

Click Test Connection. If the connection is successful, click OK. If the connection is not successful, check the connection details and try again.

Place the cursor over this icon to see the image

On the Create Business Definition Module: Connection Information page, click Next.

 

6.

On the Create Business Definition Module: Summary page, review the module definition settings. Click Finish.

Place the cursor over this icon to see the image

After Warehouse Builder creates the Business Definitions module, observe that it appears in the Project Explorer under the Business Definitions node. Expand the module to see that Warehouse Builder provides a representation for the different object types that comprise the Discoverer End User Layer (EUL).

Place the cursor over this icon to see the image

 

Back to Topic

Deriving BI Metadata for Discoverer

Oracle Warehouse Builder now eliminates the duplication of effort and metadata by deriving the end user metadata from your design. This not only reduces the work required, it also reduces the risk of errors and the effort to keep the metadata in sync, thus increasing your metadata quality. In this subtopic, you derive Discoverer objects from the OWB dimensions and cubes that you created in the previous lessons. This is an example of how to reuse the OWB dimensional design in Discoverer.

1.

In the Project Explorer, expand OWB_DEMO > Oracle > Databases > EXPENSE_WH. Expand the Dimensions node. Expand the Cubes node.

 

 

2.

Select REL_CATEGORY dimension. Press and hold [Ctrl], and select REL_TIME dimension. With the CTRL key pressed, select REL_EXPENSE cube.

Place the cursor over this icon to see the image

In the Project Explorer, you have three objects selected.

 

3.

Keeping the Ctrl key pressed, right-click the selected objects, and select Derive.

Place the cursor over this icon to see the image

The Perform Derivation Wizard is launched.

 

4.

On the Perform Derivation: Welcome page, click Next.

Place the cursor over this icon to see the image

The Perform Derivation: Source Objects page appears.

 

5.

On the Perform Derivation: Source Objects page, accept the default. Click Next.

Place the cursor over this icon to see the image

The Source Objects page enables you to select additional objects for derivation. The Oracle module, or in this case, the objects that you selected before starting the wizard, is displayed in the Selected Objects column.

 

6.

On the Perform Derivation: Target page, select EXPENSE_BDM. Click Next.

Place the cursor over this icon to see the image

On the Target page, you select the business module in which you want to store the definitions for the derived objects.

 

7.

On the Perform Derivation: Rules page, accept the defaults. Click Next.

Place the cursor over this icon to see the image

On the Rules page, you specify the derivation rules and parameters. For example, you can set global rules, rules for table objects, or rules for dimension objects by using the Rules drop-down list.

 

8.

The Pre Derivation page displays the objects to be derived and the target or Business Definitions module for storing the derived definitions.

On the Pre Derivation page, review the information. Click Next.

Place the cursor over this icon to see the image

 

9.

The Derivation page displays a progress bar indicating the status of the derivation. The derivation was completed successfully. Click Next to view the list of derived objects.

Place the cursor over this icon to see the image

 

10.

On the Finish page, review the list of derived objects. Click Finish to accept the derivation.

Place the cursor over this icon to see the image

 

11.

As shown in the screenshot, the derivation results in six intelligence objects under Item Folders. The derivation process also creates the Drill Paths that implement the respective hierarchies on the dimensions. You will also see simple and complex folders. The complex folders are the folders visible to users.

Place the cursor over this icon to see the image

 

Back to Topic

Creating a Business Area

In this subtopic, you learn how to use Oracle Warehouse Builder to create a business area. Business areas contain references to Item Folders stored in your Business Definitions module. The Discoverer end users use these Business Areas as their starting point for building a query.

1.

In the Project Explorer, expand Business Definitions > EXPENSE_BDM. Right-click Business Areas > New > Using Wizard.

Place the cursor over this icon to see the image

The Create Business Area Wizard is launched.

 

2.

On the Create Business Area: Welcome page, click Next.

Place the cursor over this icon to see the image

 

3.

On the Create Business Area: Name and Description page, enter REL_EXPENSE_WH as the name for the Business Area. Click Next.

Place the cursor over this icon to see the image

 

4.

On the Create Business Area: Item Folders page, shuttle all the objects from the Available list to the Selected list by using >> (Add All). Click Next.

Place the cursor over this icon to see the image

 

5.

On the Create Business Area: Summary page, review the summary information. Click Finish.

Place the cursor over this icon to see the image

The Business Area definition is complete. After Warehouse Builder creates the business area, in the Project Explorer, under the Business Areas node, observe the REL_EXPENSE_WH business area with references to the selected Item Folders stored in it.

Place the cursor over this icon to see the image

In the Design Center, click Save All on the toolbar to commit your work.

 

Back to Topic

Creating the Discoverer End User Layer

This topic assumes you have OracleBI Discoverer Administrator (Discoverer Administrator) installed on your machine and you are a user of Discoverer. If you do not have Discoverer on your Desktop, you will not be able to perform the steps to create the End User Layer (EUL). Instead, read the instructions and view the screenshots of how the Discoverer EUL is created.

Discoverer uses the EUL to insulate end users from the complexity, naming, and physical structure of the database. Before you deploy the Business Definitions to Discoverer, you need to create the EUL. Now, you learn how to create a Discoverer EUL.

1.

Start OracleBI Discoverer Administrator. In the Connect to Oracle Business Intelligence Discoverer Administrator dialog box, enter the connection information as:

Username: eul_from_owb
Password: eul_from_owb
Connect: orcl

Place the cursor over this icon to see the image

Click Connect.

 

2.

When accessing Discoverer, you must have access to an EUL. Click Yes to create an EUL.

Place the cursor over this icon to see the image

 

3.

In the EUL Manager dialog box, click Create an EUL.

Place the cursor over this icon to see the image

The Create EUL Wizard is launched.

 

4.

In step 1of the wizard, select Select an existing user. In the User field, click Select and choose EUL_FROM_OWB. Deselect any other default selection.

Click Finish.

Place the cursor over this icon to see the image

 

5.

The EUL is successfully created. Click OK.

Place the cursor over this icon to see the image

Disconnect from OracleBI Discoverer Administrator.

 

Back to Topic List

Generating and Deploying the Intelligent Objects

In this subtopic, you generate and deploy the Business Area for Discoverer Administrator. This hands-on exercise deploys successfully with no errors only if you have created an EUL in Discoverer.

1.

In the Design Center, from the Tools menu, select Control Center Manager.

Place the cursor over this icon to see the image

The Control Center is launched.

 

 

2.

In the Control Center, right-click EXPENSE_BDM_LOCATION, and select Register to register the location.

Place the cursor over this icon to see the image

The Edit Discoverer Location page appears. Review the location parameters, which are the connection details of where you will deploy your business areas. Click Test Connection. If the connection is successful, click OK. If the connection is not successful, check the connection details and try again.

Place the cursor over this icon to see the image

 

3.

In the Control Center, expand EXPENSE_BDM_LOCATION > EXPENSE_BDM > Item Folders. Select all the Item Folders. Expand Drill Path, press and hold [Ctrl], and select the two drill paths.

Ensure that you have selected all the objects as seen in the screenshot.

Place the cursor over this icon to see the image

 

4.

In the Control Center, with the objects selected as mentioned above, click Default Actions. The Deploy Action column value changes from None to Create. On the toolbar, click Deploy.

Place the cursor over this icon to see the image

 

5.

In the Control Center, in the Deploy Status column, the value changes from Not Deployed to Success, for all objects deployed successfully.

Place the cursor over this icon to see the image

 

6.

In the Control Center, expand EXPENSE_BDM_LOCATION > EXPENSE_BDM > Business Areas. Select REL_EXPENSE_WH.

Click Default Actions. The Deploy Action column value changes from None to Create. Click Deploy on the toolbar.

The Business Area is deployed successfully.

Place the cursor over this icon to see the image

You are now ready to view the Business Area in Discoverer Administrator.

 

Back to Topic List

Examining the Business Area in OracleBI Discoverer Administrator

Now that you have designed and deployed the Business Area, you can view the resulting Business Area in OracleBI Discoverer Administrator. To perform this hands-on exercise in this topic, you need OracleBI Discoverer Administrator installed on your machine. However, if you do not have Discoverer Administrator, just read the instructions and see the screenshot on how to view the Business Area in Discoverer Administrator. This hands-on exercise assumes that you have working knowledge of Discoverer; therefore, the steps are not detailed.

 

1.

Start OracleBI Discoverer Administrator. You see the Business Area that you just deployed, REL_EXPENSE_WH.

Place the cursor over this icon to see the image

Click Open an existing business area. Select the REL_EXPENSE_WH business area. Click Finish.

 

 

2.

The Business Area was deployed successfully. You are now able to view the Business Area in OracleBI Discoverer Administrator.

Place the cursor over this icon to see the image

Disconnect from OracleBI Discoverer Administrator.

 

Back to Topic List

Viewing the Data in OracleBI Discoverer Plus

OracleBI Discoverer Plus (Discoverer Plus) is a BI tool that enables users to analyze data and retrieve information necessary to make business decisions. In this subtopic, you see a demo of how you can use OracleBI Discoverer Plus to create a workbook and view the report data. The steps below are for read-only, and not to be performed. With the help of the instructions and screenshot you can assimilate the concepts.

1.

Start OracleBI Discoverer Plus. Provide the Connection Details as:

UserName: eul_from_owb
Password: eul_from_owb
Database: orcl
End User Layer: EUL_FROM_OWB

Place the cursor over this icon to see the image

Click Go.

 

2.

On the toolbar, select New Workbook. This launches the Workbook Wizard. On the Worksheet Layout page, deselect all items, except Title, Page Items, and Table. Click Next.

Place the cursor over this icon to see the image

 

3.

On the Select Items page, move the following items from the Available list to the Selected List.

  • Expand REL_CATEGORY and shuttle TYPE_NAME.
  • Expand REL_EXPENSE > Expense and shuttle Sum.

Click Next.

Place the cursor over this icon to see the image

 

4.

On the Table Layout page, you can rearrange items to define the worksheet layout.

Place the cursor over this icon to see the image

 

5.

Click Finish. Your report is displayed.

Place the cursor over this icon to see the image

 

Back to Topic List

 

Integration with BI Beans

Warehouse Builder enables you to seamlessly integrate with OracleBI Beans by deriving the structures for Business Presentations or BI Reports directly from your warehouse design metadata. You can also create your own customized report structures in Warehouse Builder and deploy them to BI Beans.

This topic uses a different Oracle target module from those you have defined. This topic is read-only, that gives you an overview of Warehouse Builder's capability to predefine objects for BI Beans and use BI Beans Report Viewer to examine the report.

1.

Use the Create Business Presentation Module Wizard to create a Business Presentation module. The Business Presentation module stores the definitions for report structures in Warehouse Builder that you want to deploy to BI Beans.

Place the cursor over this icon to see the image

 

2.

Warehouse Builder provides the Presentation Template Wizard that creates the actual report structures that you can later deploy to BI Beans.

Invoke the Create Presentation Template Wizard.

Place the cursor over this icon to see the image

On the Type page of the wizard, you select the Crosstab or Graph report structure.

Place the cursor over this icon to see the image

On the Items page of the wizard, you select a cube and its members, such as measures, referenced dimensions, or hierarchies of referenced dimensions.

Place the cursor over this icon to see the image

Finally, you specify the report layout on the Layout page of the wizard. X and Y edge dimensions can be dragged from the Page Items. These relate to x/y for a crosstab report.

Place the cursor over this icon to see the image

 

3.

After generating and deploying the report definition in Warehouse Builder, you can invoke BI Beans report viewer and provide the connection details for the BI Beans catalog.

Place the cursor over this icon to see the image

 

4.

You now examine the BI Beans Report.

Place the cursor over this icon to see the image

The screenshot shows the contents of the catalog.

Place the cursor over this icon to see the image

 

Back to Topic List

Oracle Warehouse Builder becomes more of a Business Intelligence Factory as it integrates with more tools. Oracle Warehouse Builder provides a complete business intelligence solution by enabling you to integrate metadata from different data sources, designing and deploying it to a data warehouse, and then making that information available to analytical tools for decision making and business reporting.

In this lesson, you've learned how to:

Create business definition for OracleBI Discoverer Administrator, knowing that Warehouse Builder provides a representation for the different objects types that comprise the Discoverer End User Layer (EUL)

Eliminate the duplication of effort and metadata by deriving the metadata from your design

Create and deploy the Business Area

View the report data using OracleBI Discoverer Plus

   

Back to Topic List