Businesses have complex analytic,
forecast, and planning requirements that can be be served by online analytical
processing (OLAP). Using OWB you can design and create the OLAP environment
in a few easy steps.
This lesson will take approximately 30 minutes to complete.
Viewing Screenshots
Move your mouse over this icon to show
all screenshots. You can also move your mouse over each individual icon to see
only the screenshot associated with it.
Overview
A fundamental concept in OLAP processing is the cube.
A multidimensional cube is stored in an analytic workspace
(AW). Analytic Workspaces (AWs) can be generated from Warehouse Builder.
BI Beans is AW-ready, and can access AW data using its
built-in wizards.
The value of the data warehouse is its ability to support
business intelligence applications. In this lesson, you will use the OWB Bridge
for metadata export to design an OLAP analytical environment for the warehouse.
You will then build a customized BI Beans application to make it easier and
dynamic to analyze and forecast business decisions.
In order for this lesson to work successfully, you will need
to have performed the following:
1.
Have a basic understanding of relational and multidimensional
concepts.
2.
Have working knowledge of Oracle Jdeveloper10g.
3.
In Lesson 7 you should have deployed dimensions
PRODUCT, T_TIME, WAREHOUSE, cube INVENTORIES,
external tables CHANNELS_EXT, dimensions CHANNELS.
Deploy and execute mappings MAP_CHANNELS.
Make sure to,deploy and execute mappings MAP_T_TIME, MAP_PRODUCT,
MAP_WAREHOUSE, MAP_INVENTORIES.
Deploy dimensions PROMOTIONS and external table PROMOTIONS_EXT.
In this topic you will examine the predefined collection
SALES. Collections are areas in Warehouse Builder that store the metadata you
want to export to other tools and systems.
1.
Expand the OWB10g_DEMO
project and expand the Collections node.
2.
Double click the SALES collection.
In the Collection Properties window click the General
tab. In the Selected Objects list fully expand the collection
content. Click OK or Cancel to close
the window.
In this topic you will learn how using the Warehouse Builder
Transfer Wizard you can export the collections to other systems. You can use
the Warehouse Builder Transfer Wizard to deploy a collection and create the
OLAP metadata that populates the OLAP catalog and creates the AW.
1.
Select Project
> Metadata Export > Bridge. Starting the OWB Transfer Wizard
make take a while.
2.
Click Next on the Warehouse
Builder Transfer Wizard's welcome page.
3.
In the Metadata Source and Target Identification
page, the From drop-down list box displays Oracle9i Warehouse
Builder. Accept the default.
For the To drop-down list box select Oracle9i OLAP and
click Next.
4.
The Transfer Parameter Identification page appears. You
can tweak a number of parameters that influence the generation of OLAP.
Transfer Parameter Name
Transfer Parameter Value
OWB Exported Collections
SALES
OWB Translated Language
American English
Deploy to AW
Yes
AW Name
SALESAW
AW Object Prefix
SAL_
Load Cube Data
Yes
Aggregate Data in AW
Partial,every other
level
Generate surrogate keys for dimensions
No
Generate View Definitions
Yes
Generate Materialized Views
No
Generated View Directory
c:\SALESAW
Deploy PL/SQL in database
Yes
Username
target_schema
Password
target_schema
Hostname
localhost
Port
1521
Service Name
obeowb
PL/SQL Output File
c:\SALESAW\AW_create.sql
Log Level
Information
Click Next.
5.
If the directory specified for the transfer parameter
PL/SQL Output File does not exist, you will get an alert window. Click
Yes, to create the directory.
6.
The Transfer Wizard's Summary page appears. Click Finish.
It can take a few minutes to run the bridge.
7.
Click OK, when the transfer
operation is complete.
The transfer wizard moves objects grouped in a
Collection. All dimensional objects contained by the collection will be
generated into OLAP structures and populated in the AW if you run the
load. The transfer is the actual deployment of the OLAP cubes and dimensions.
The Transfer Wizard populates the OLAP catalog, creating the AW objects
within and relational views on top of the AW.
Grouping your files by workspace and project enables you to
manage your work logically and hierarchically. To create a new JDeveloper Workspace
and project, you perform the following steps:
1.
Start Oracle10g JDeveloper from where it is
installed on your machine.
The Oracle10g JDeveloper main window opens.
2.
Select File > New.
The New Gallery Dialog box opens.
3.
In the New Gallery
dialog box, under Categories, select General;
under Items, select Workspace and click OK.
The Create Workspace dialog box opens.
4.
In the Create Workspace dialog box, accept
the default workspace directory name and specify BIWorkspace
for the workspace file name. Ensure that the Add a New Empty Project
box is checked. Click OK.
The Create Project dialog opens.
5.
In the Create Project dialog box, accept the
defaults and click OK.
From the File Menu, click Save All to save your work.
Notice that the JDeveloper Applications-Navigator has been updated to
display your new workspace and project.
The BI Designer is used to aid in the visual development of
your business intelligence objects. It also contains references to the information
that is required for connecting to Oracle OLAP.
Follow these steps to create a BI Designer.
1.
In the JDeveloper Applications-Navigator
pane, select project Project1.jpr. From the File menu,
click New.
The New Gallery dialog opens.
2.
In the New Gallery dialog box, under Categories,
expand Business Intelligence Beans, select Business
Tier for OLAP. Under Items, select Designer.
Click OK.
This starts the BI Designer Wizard. Click Next in the
Welcome page.
3.
In the BI Designer Object screen, you specify
the name for your BI Designer object. Accept the default name and click
Next to continue.
4.
In the OLAP Data Source screen, you specify
the Oracle OLAP data source. Click New.
This launches the OLAP Connection wizard. Click Next
in the Welcome page.
5.
In Step1of the OLAP Connection wizard, you
specify the connection name and type. Accept the default Connection name
and type. Click Next to continue.
6.
In Step 2, you specify the access username
and password to your OLAP data source. Enter target_schema
as the username and target_schema as the password. Leave
the Role field blank and Deploy Password checkbox unchecked.
Click Next to continue.
7.
In Step 3, you specify the connection details
for the database machine. Accept the default thin driver,
enter localhost hostname, JDBC port 1521,
and SID of obeowb. Click Next.
8.
In Step 4, click Test Connection.
If the connection test is successful, then click Next
to proceed. If not verify the details from the previous two steps.
Click Finish to complete the OLAP Connection wizard.
In the BI Designer Wizard, click Next to continue
9.
In Step 3 of the BI Designer Wizard, you specify
the location where you want to save your Business Intelligence objects
(for example crosstab and graph definitions). Accept the default directory
and click Next. If the wizard prompts you to create the
specified directory, click Yes.
Review the summary and click Finish to complete the
BI Designer Wizard. From the File Menu, click Save
All to save your work.
A crosstab enables you to view complex query results
in user-friendly and formatted manner. It also allows you to navigate through
the data by drilling, paging, pivoting, and so on.
You will learn how to create a crosstab using the Presentation
wizard.
1.
In the Applications-Navigator pane, right-click
Project1BIDesigner1 and select New Crosstab from the
popup menu.
This launches the Presentation wizard. Click Next in
the Welcome page.
2.
Notice that the Crosstab
box is checked by default. You specify the presentation name and description
that will be associated with the crosstab as follows:
Presentation location and name: Sales Analysis Crosstab
Description: My New Sales Analysis Report
Keywords: sales report
Click Next to continue.
3.
The wizard displays a list of available measures
and dimensions that can be displayed in the crosstab. This list includes
the folder Sales Model that was created when setting
up Oracle9i OLAP.
In the available list, expand Sales Model folder and
select the measure Revenue.
4.
Use the Add Selected Items button (which is
represented by a single arrow that faces right '>')
to move measure Revenue to the Selected list.
Notice that measure Revenue and its related dimensions are moved to the
Selected list because the Automatically add/remove dimensions checkbox
is checked.
Click Next.
5.
On the Layout page, click Next.
To rearrange the items, you click an item and drag it to the desired
position.
6.
In the Dimensions page, move all the Channel
members to the Selected list using the Add all items button(which is represented by a double arrow facing right
'>>')
Click Next to continue.
7.
In the Dimensions page, move all the Customers
Region members to the Selected list using the '>>'
Click Next to continue.
8.
In the Dimensions page, move all the Product
members to the Selected list using the '>>'
Click Next to continue.
9.
In the Dimensions page, move all the Promotions
Category members to the Selected list using the '>>'
Click Next to continue.
10.
In the Dimensions page, move all the SALES_REP
Region members to the Selected list using the '>>'
Click Next to continue.
11.
In the Dimensions page, move all the TIMES
members to the Selected list using the '>>'
Click Finish to create the crosstab.
12.
Once created, the crosstab opens automatically
in the Presentation Editor.
You can also customize the crosstab to view reports with your selected
Item. For example, to view a report for only software products, use the
drop-down on Products item
From the File Menu, select Save All to save your work.
Summary
In this module should have learned how
To export metadata
from OWB to OLAP Server using the Metadata Transfer Wizard.
Analytical Workspaces can be generated from Warehouse
Builder.
Using BI Beans within
JDeveloper provides the most productive environment for analytical application.
Related Topics
Oracle University: Class
on Perform OLAP Integration
Training on OTN: Oracle-By-Example:
BI Beans
Move your mouse over this icon to hide all screenshots
Bookmark
