| Oracle® Business Intelligence Standard Edition One Tutorial Release 10g (10.1.3.2.1) E10312-01 |
|
 Previous |
 Next |
| Oracle® Business Intelligence Standard Edition One Tutorial Release 10g (10.1.3.2.1) E10312-01 |
|
Previous |
Next |
In this chapter, you use Oracle Warehouse Builder (OWB) to create the logical and physical design of the data mart.
Prerequisites: There are no prerequisites for this chapter.
This chapter contains the following topics:
To set up the data mart, you use OWB components to:
Create the logical design for the data mart star schema.
Map the logical design to a physical design.
Generate code to create the objects for the data mart.
Create a process flow for populating the data mart.
Execute the process flow to populate the data mart.
In addition to a powerful graphical interface, OWB provides a metadata repository that holds detailed design information about your databases. The repository is implemented as a set of tables in an Oracle Database. The data you enter in the repository is available to any user who has at least read access to the repository application system.
To access the OWB repository, you use an OWB component called Design Center, the main OWB client in which you design sources, targets, ETL mapping and transformations. Using the OWB Design Center, you import data source definitions, define target structures, validate the structures, generate and deploy the code to implement the structures, and execute the process flows to run the ETL mappings that load data into the target structures. Once deployed, Warehouse Builder assists in the daily maintenance as well as monitoring of the deployed system.
Let us begin by taking a tour of the completed data warehouse project called GEC_DW (Global Electronics Corporation Data Warehouse). This is a data warehouse using a star schema, with one cube (SALES) and five dimensions (CHANNELS, GEOGRAPHY, PRODUCTS, PROMOTIONS, and TIME).
This section contains the following topics:
To access the OWB repository using the OWB Design Center, follow these steps:
From the Windows Start menu, select Programs > Oracle - BISE1Home1_WarehouseBuilder > Warehouse Builder > Design Center to invoke Warehouse Builder. The Design Center Logon window takes a few seconds to appear.
Before logging on with the username and password, you must establish connect information. Click Show Details.
The Design Center Logon window expands. Provide the host name, port number, and service name that were defined during installation:
Host: localhost
Port: 1521
Service Name: bise1db
In the top portion of the Design Center Logon window, enter the OWB repository user name and password.
User Name: owbrepos_user
Password: welcome1 (or, use whatever password you specified during BISE1 installation)
Click OK in the Design Center Logon window. The Design Center appears.
The Design Center is divided into three windows: Project Explorer, Connection Explorer, and Global Explorer. In Project Explorer, you see two projects, GEC_DW (Global Electronics Corporation Data Warehouse), the completed project for your use as a reference, and GEC_DW_TUTORIAL, the project you will be modifying as you work through the guided steps in this tutorial.
To explore the completed OWB project:
Select and expand the GEC_DW project.
A project is a container to manage your design work. Warehouse Builder contains wizards, object editors, property sheets, and object finding tools that assist you in designing your business intelligence system. Under the GEC_DW project, various object types appear in the tree: Databases, Files, Applications, Data Profiles, and so on.
In this tutorial, you will be working with the Databases, Files, and Process Flows object types.
Expand the Databases node. You can see various object types that can participate in your design: Oracle, Non-Oracle, and Transportable Modules.
Expand the Oracle node. You can see two modules defined in the GEC_DW project: BISE1_SALESWH and BISE1_SALES. Modules are logical groupings of source or target definitions. Source modules hold metadata describing source systems from which you extract data. Target modules hold metadata describing your target data warehouse or data marts. Every target module must be mapped to a target user schema. Make sure that the target module references this target schema by assigning an appropriate location.
The BISE1_SALES module is the source module. Expand the BISE1_SALES module, then expand the Tables node. These are the operational data table definitions used to construct the target data warehouse object definitions stored in the BISE1_SALESWH module.
The BISE1_SALESWH module is the target module. Expand the BISE1_SALESWH module, then expand the Mappings, Dimensions, Cubes, Tables, and Sequences nodes. These are the object definitions used to create and populate the data warehouse.
View the property settings for the BISE1_SALESWH module by right-clicking the module, then selecting Properties from the pop-up menu. Notice that objects have both a Business Name and a Physical Name, which can be different. Usually, you should synchronize them. Close the Properties window.
Double-click the BISE1_SALESWH module to invoke its Editor window. Almost all objects have both a Properties window, and an Editor window. You can set the object definitions and configurations using the Editor window. Close the Editor window.
Now that you have examined a completed data warehouse project, you are ready to complete your own data warehouse project called GEC_DW_TUTORIAL. The GEC_DW_TUTORIAL project will build the same data warehouse as the GEC_DW project. The only difference between these two projects is the data warehouse target. While the GEC_DW project is deployed to the BISE1_SALESWH target, you will deploy the GEC_DW_TUTORIAL project to the BISE1_TUTORIALWH target.
In Warehouse Builder, you can define dimensions and cubes using a wizard or an editor. Use the wizard to create dimensional and cube objects easily. The wizard creates a fully functional dimensional or cube object, including the implementation objects that store the dimensional or cube object data but with predefined defaults for certain features. Alternatively, you can use the editor to create or edit dimensional or cube objects. Use editors instead of wizards when you want to specify settings different from the default settings of the wizards, or to access additional settings not provided in the wizards.
This section contains the following topics:
Section 3.3.1, "Create the CHANNELS Dimension Using the Wizard"
Section 3.3.2, "Create the PRODUCTS Dimension Using the Editor"
Section 3.3.3, "Create the TIMES Dimension Using the Time Wizard"
To create the CHANNELS dimension using the wizard:
In the Design Center Project Explorer, expand GEC_DW_TUTORIAL > Databases > Oracle > BISE1_TUTORIALWH.
Right-click Dimensions, then select New > Using Wizard. The Create Dimension Wizard launches. Click Next on the Welcome page.
On the Name and Description page, enter the following:
Name: CHANNELS
Description: Channels Dimension
Click Next.
On the Storage Type page, choose ROLAP: Relational storage and click Next.
|
Note: The ROLAP storage type is best if you have high volumes of data combined with high refresh rates, or if you have detailed high-volume data. When you store data in a relational form, OWB by default uses the star schema implementation method. |
On the Dimension Attributes page, you will find three predefined columns: ID, NAME, and DESCRIPTION.
Delete the DESCRIPTION column. To do this, right-click the number 3, in the column to the left of DESCRIPTION, and select Delete, as shown in Figure 3-9.
For the Name attribute, select empty value from the Identifier drop-down list, and change Length to 60.
Add a new attribute, SOURCE_ID (Business, Varchar2, 40), as shown in Figure 3-11, and click Next.
On the Levels page, you create the three levels as shown in Figure 3-12.
Click Next.
On the Level Attributes page, specify the attributes for each level, as follows:
For the TOTAL level, select attributes as shown in Figure 3-13.
For the CLASS level, select attributes as shown in Figure 3-14.
For the CHANNEL level, select attributes as shown in Figure 3-15.
|
Note: For the lowest level, all the attributes are selected by default. |
Click Next.
On the Data Policy page, accept the default choice of Type 1: Do not keep history, then click Next.
|
Note: A slowly changing dimension (SCD) is a dimension that stores and manages both current and historical data over time in a data warehouse. Examples of SCDs include product package sizes, package types, prices, and country names. If you need to track the evolutionary history of the data values, you can choose Type 2 or 3, which require a separate license of the Oracle Warehouse Builder Enterprise ETL Option. |
Examine the Pre Create Settings page and click Next.
The Dimension Creation Progress implements the dimension, and the dimension is successfully created. Click Next.
The Summary page lists the dimension definition. Notice a database table and sequence will be created as well. Click Finish.
Save your work by clicking the Save All icon on the Design Center toolbar.
Click Yes in the Warehouse Builder Warning dialog box.
|
Note: The warning dialog box appears every time you click Save All. |
To create the PRODUCTS dimension using the editor:
In the Design Center Project Explorer, expand GEC_DW_TUTORIAL > Databases > Oracle > BISE1_TUTORIALWH, if it is not already expanded.
Right-click Dimensions, then select New > Using Editor. The Data Object Editor launches.
In the Dimension Details window, ensure that the Name tab is selected. In the Name field, change DIMENSION_1 to PRODUCTS. In the Description field, enter Product dimension.
Click the Storage tab. Accept the default option of ROLAP: Relational storage. For Implementation, choose Star.
Click the Attributes tab. Click Select. In the Available Sequence dialog box, expand BISE1_TUTORIALWH and select PROD_DIM_SEQ. Click OK.
The sequence will populate the Dimension Key. Enter attribute information as shown in Figure 3-23.
Click the Levels tab. This tab has two sections: Levels and Level Attributes for level_name.
In the Levels section, enter information as shown in Figure 3-24.
In the Level Attributes for each level, specify the following:
Set level attributes for TOTAL as shown in Figure 3-25.
Set level attributes for CATEGORY as shown in Figure 3-26.
Set level attributes for SUBCATEGORY as shown in Figure 3-27.
Set level attributes for PRODUCT as shown in Figure 3-28.
Click the Hierarchies tab. Select Level values from the drop-down list as shown in Figure 3-29.
Click the SCD tab. Note the default selection, Type 1: Do not keep history.
Create the dimension table and bind it to the repository. On the canvas, right-click the PRODUCTS dimension and select Auto Bind. When you perform auto binding, Warehouse Builder automatically maps the measures and dimension references of the cube to the database columns that store their data.
You have now created the PRODUCTS dimension. Figure 3-31 shows what you will see after you complete the binding process.
Save your work by clicking Save All on the Data Object Editor toolbar.
Close the Data Object Editor.
To create the TIMES dimension using the Time Wizard:
In Project Explorer in the Design Center, expand GEC_DW_TUTORIAL > Databases > Oracle > BISE1_TUTORIALWH. Right-click Dimensions, then select New > Using Time Wizard. The Create Time Dimension Wizard launches. Click Next on the Welcome page.
On the Name and Description page, enter the following:
Name: TIMES
Description: Times Dimension
Click Next.
On the Storage Type page, accept the default ROLAP: Relational storage and click Next. The Data Generation page appears.
Enter the following information:
Start year: 2003
Number of years: 4
Ensure that the Calendar option is selected.
|
Note: On the Data Generation page, you specify the range of time data that is required for your warehouse. This information will be used to generate a mapping that will populate the time dimension. Within this mapping, the dates you enter are added as parameters, allowing you to rerun this mapping with dates at a later stage. |
Click Next.
On the Levels page, select Normal Hierarchy, and select Calendar Year, Calendar Quarter, and Calendar Month as shown in the Figure 3-33, then click Next.
On the Pre Create Settings page, examine the details and click Next. A Progress bar will show the progress as the wizard creates the objects.
On successful completion, click Next. On the Summary page, examine the details again and click Finish.
In Project Explorer, observe that the wizard has generated four objects necessary for a fully functional TIMES dimension:
Expand GEC_DW_TUTORIAL > Databases > Oracle > BISE1_TUTORIALWH > Dimensions. You can see the TIMES dimension.
Expand GEC_DW_TUTORIAL > Databases > Oracle > BISE1_TUTORIALWH > Sequences. You can see the TIMES_SEQ sequence that populates the surrogate ID of the time dimension levels.
Expand GEC_DW_TUTORIAL > Databases > Oracle > BISE1_TUTORIALWH > Tables. You can see the TIMES table, which supports the relational implementation of the time dimension that will physically store the time data.
Expand GEC_DW_TUTORIAL > Databases > Oracle > BISE1_TUTORIALWH > Mappings. You can see the TIMES_MAP mapping, which populates the time dimension.
Click Save All to save your work.
To create the SALES cube using the editor:
From the Project Explorer in the Design Center, expand GEC_DW_TUTORIAL > Databases > Oracle > BISE1_TUTORIALWH. Right-click Cubes, then select New > Using Editor. The Data Object Editor window is displayed.
In the Cube Details window, ensure that the Name tab is selected. In the Name field, change CUBE_1 to SALES. In the Description field, enter Sales Cube.
Click the Storage tab. Choose settings as follows:
Accept the default selection, ROLAP: Relational data structures. Using this option, the cube definition and its data are stored in the relational form in the database.
Deselect Create bitmap indexes.
|
Note: Bitmap indexes can enhance performance. To make use of bitmap indexes, a separate license for Oracle Database Enterprise Edition is required. |
Click the Dimensions tab. Select the dimension from the drop-down list in the dimension column and the corresponding levels in the Level column, as shown in Figure 3-37.
Click the Measures tab and enter the information as shown in Figure 3-38.
Click the Aggregation tab. The default Aggregation function SUM is already selected for all the dimensions. This is used to aggregate the cube data, and you can accept this default setting. As an example, Figure 3-39 shows that the Cube Aggregation Method is SUM for the GEOGRAPHY dimension.
After defining the cube structure, you will now specify the details of the database tables or views that store the cube data.
On the canvas, in the Data Object Editor, right-click the SALES cube and select Auto Bind. When you perform auto binding, Warehouse Builder automatically maps the measures and dimension references of the cube to the database columns that store their data.
Figure 3-40 shows what you will see on the canvas after selecting Auto Bind.
Save your work and close the Data Object Editor.
In this section, you will perform the following tasks:
Design the mapping to load the PRODUCTS dimension. Note that the GEOGRAPHY and PROMOTIONS mappings have been pre-created for you.
Run the tcl script to create the mappings to load the CHANNELS dimension and SALES cube.
The TIMES mapping was automatically created when you created the TIMES dimension using the time dimension wizard.
This section contains the following topics:
Section 3.4.1, "Design the Mapping to Load the PRODUCTS Dimension"
Section 3.4.2, "Use Tcl Scripts to Create Mappings for the CHANNELS Dimension and the SALES Cube"
To design the mapping to load the PRODUCTS dimension:
From the Project Explorer in the Design Center, expand GEC_DW_TUTORIAL> Databases > Oracle > BISE1_TUTORIALWH. Right-click the Mappings node and select New. The Create Mapping window is displayed.
In the Create Mapping window, enter LOAD_PRODUCTS as the name of the mapping. Click OK. The Mapping Editor appears.
In the Explorer panel, ensure that the Available Objects tab is selected. Expand Databases > Oracle > BISE1_SALES > Tables. Drag the CATEGORIES and the PRODUCTS tables to the canvas.
|
Note: If you dragged both tables simultaneously to the canvas, you may initially see only one table displayed in the Mapping canvas. The second table is likely hidden behind the table you see. To see the second table, click and drag the visible table slightly to the top. |
On the canvas, double-click the header area of the PRODUCTS table.
In the Table Editor, ensure the Name tab is selected. In the Name field, change PRODUCTS to PRODUCTS_IN. Click OK.
From the Explorer panel, collapse the BISE1_SALES module and expand BISE1_TUTORIALWH > Dimension.
Drag the PRODUCTS dimension to the canvas.
Double-click the header area of the PRODUCTS dimension. In the Table Editor, in the Name field, change PRODUCTS to PRODUCTS_OUT and click OK.
Add the CONSTANT and FILTER operators to the canvas. From the Palette, drag the Constant operator to the canvas. You need two filters, so drag the Filter operator to the canvas twice.
Click the Auto Layout icon on the Mapping Editor toolbar to arrange the objects on the canvas.
Your canvas now appears as shown in Figure 3-45.
Double-click the CONSTANT operator's header and change its name to TOTALS. Select the Output Attributes tab in the CONSTANT Editor.
Click the Add button to add the following two attributes:
TOT - VARCHAR2(20)
SRC_ID – VARCHAR2(20)
Click OK to close the CONSTANT Editor window.
On the Canvas, click the TOTALS constant operator and select the TOT attribute. In the Property Inspector window on the left, click the field next to Expression and enter 'Product Total'.
On the Canvas, with the TOTALS constant operator selected, click the SRC_ID attribute. In the Property Inspector window, click the field next to Expression and enter '99999'.
Double-click the FILTER operator header on the canvas. Change the name FILTER to CATS. Click OK to close the Filter Editor.
Maximize the CATEGORIES operator so that all the attributes are clearly visible.
Add a connection line from attributes in operator CATEGORIES.INOUTGRP1 to INOUTGRP1 of operator CATS, as shown in Table 3-1.
Table 3-1 CATEGORIES.INOUTGRP1 to CATS Mapping
| CATEGORIES.INOUTGRP1 | CATS |
|---|---|
|
ID |
INOUTGRP1 |
|
NAME |
INOUTGRP1 |
|
DESCRIPTION |
INOUTGRP1 |
|
CATEGORY_ID |
INOUTGRP1 |
To draw the connection line, click the right arrow for each CATEGORIES attribute, hold down the mouse button, and drag the line to the left arrow of CATS.INOUTGRP1.
Figure 3-51 shows what you should see when you are done adding the connections.
Specify the filter condition for the CATS filter operator as: INOUTGRP1.CATEGORY_ID IS NULL.
To do this, click the header area of the CATS filter operator on the canvas. On the left-hand side, in the Filter Properties: CATS window, click the field next to Filter Condition. Click the ellipsis (…). The Expression Builder for the filter condition is launched. OWB enables you to build the condition, or lets you enter the filter condition.
In the Expression builder on the left-hand side, expand INOUTGRP1. Double-click CATEGORY_ID. You see that INOUTGRP1.CATEGORY_ID is pasted in the Filter condition field on the right-hand side.
In the lower right area, from the Others drop-down list, select IS NULL and click Paste. You see the expression INOUTGRP1.CATEGORY_ID IS NULL in the Filter Condition for CATS field.
Click Validate. You will see the result in the Validation results field. If the validation is successful, click OK.
Change the other FILTER operator to SUBCATS. Double-click the FILTER operator header on the canvas. Change the name FILTER0 to SUBCATS. Click OK to close the Filter Editor.
Maximize the CATEGORIES operator so that all the attributes are clearly visible.
Add a connection line from the attributes in operator CATEGORIES.INOUTGRP1 to INOUTGRP1 of operator SUBCATS as shown in Table 3-2.
Table 3-2 CATEGORIES.INOUTGRP1 to SUBCATS Mapping
| CATEGORIES.INOUTGRP1 | SUBCATS |
|---|---|
|
ID |
INOUTGRP1 |
|
NAME |
INOUTGRP1 |
|
DESCRIPTION |
INOUTGRP1 |
|
CATEGORY_ID |
INOUTGRP1 |
Figure 3-55 shows what you should see when you are done adding the connections.
Specify the filter condition for the SUBCATS filter operator as INOUTGRP1.CATEGORY_ID IS NOT NULL.
To do this, click the header area of the SUBCATS filter operator on the canvas. On the left-hand side, in the Filter Properties: SUBCATS window, click the field next to Filter Condition. Click the ellipsis (…). The Expression Builder for the filter condition is launched.
In the Expression builder on the left-hand side, expand INOUTGRP1. Double-click CATEGORY_ID. You see that INOUTGRP1.CATEGORY_ID is pasted in the Filter condition field on the right-hand side.
In the lower right area, from the Others drop-down list, select IS NOT NULL and click Paste. You see the expression INOUTGRP1.CATEGORY_ID IS NOT NULL in the Filter Condition for SUBCATS field.
Click Validate. You will see the result in the Validation results field. If the validation is successful, click OK.
Draw connection lines from the attributes in OUTGRP1 of TOTALS constant operator to attributes in groups in the PRODUCTS_OUT dimension operator, as shown in Table 3-3.
Table 3-3 TOTALS.OUTGRP1 to PRODUCTS_OUT Mapping
| TOTALS.OUTGRP1 | PRODUCTS_OUT |
|---|---|
|
TOT |
TOTAL.NAME |
|
TOT |
TOTAL.DESCRIPTION |
|
SRC_ID |
TOTAL.SOURCE_ID |
|
SRC_ID |
CATEGORY.TOTAL_SOURCE_ID |
Figure 3-57 shows what you should see when you are done adding the connections.
Draw connection lines from the attributes in INOUTGRP1 of CATS filter operator to attributes in the CATEGORY group of dimension operator PRODUCTS_OUT, as shown in Table 3-4.
Table 3-4 CATS.INOUTGRP1 to PRODUCTS_OUT Mapping
| CATS.INOUTGRP1 | PRODUCTS_OUT |
|---|---|
|
ID |
CATEGORY.SOURCE_ID |
|
NAME |
CATEGORY.NAME |
|
DESCRIPTION |
CATEGORY.DESCRIPTION |
Figure 3-58 shows what you should see when you are done adding the connections.
Draw connection lines from the attributes in INOUTGRP1 of SUBCATS filter operator to attributes in the SUBCATEGORY group of dimension operator PRODUCTS_OUT, as shown in Table 3-5.
Table 3-5 SUBCATS.INOUTGRP1 to PRODUCTS_OUT Mapping
| SUBCATS.INOUTGRP1 | PRODUCTS_OUT |
|---|---|
|
CATEGORY_ID |
SUBCATEGORY.CATEGORY_SOURCE_ID |
|
ID |
SUBCATEGORY.SOURCE_ID |
|
NAME |
SUBCATEGORY.NAME |
|
DESCRIPTION |
SUBCATEGORY.DESCRIPTION |
Figure 3-59 shows what you should see when you are done adding the connections.
Draw connection lines from the attributes in INOUTGRP1 of table operator PRODUCTS_IN to attributes in the PRODUCT group of dimension operator PRODUCTS_OUT, as shown in Table 3-6.
Table 3-6 PRODUCTS_IN.OUTGRP1 to PRODUCTS_OUT Mapping
| PRODUCTS_IN.OUTGRP1 | PRODUCTS_OUT |
|---|---|
|
SUBCATEGORY_REFERENCE |
PRODUCT.SUBCATEGORY_SOURCE_ID |
|
IDENTIFIER |
PRODUCT.SOURCE_ID |
|
NAME |
PRODUCT.NAME |
|
DESCRIPTION |
PRODUCT.DESCRIPTION |
|
PACK_SIZE |
PRODUCT.PACK_SIZE |
|
LIST_PRICE |
PRODUCT.LIST_PRICE |
Figure 3-60 shows what you should see when you are done adding the connections.
|
Note: You can collapse objects on the canvas by clicking the Minimize icon: |
Validate the LOAD_PRODUCTS mapping by selecting Mapping > Validate from the menu.
The Generation Results panel appears. The warnings on implicit datatype conversion can be ignored. The Oracle Database will perform automatic datatype conversion.
Select Generate from the Mapping menu. This will generate the code required to implement the design. Ignore the warnings (7 warnings). Close the Generation Results window.
Save your work and close the Mapping Editor.
Tcl (Tool Command Language) is a scripting language that can automate and simplify tasks. Oracle Warehouse Builder includes a rich Tcl interface called OMB*Plus. OMB*Plus can be invoked from the command line, or from within the OWB Design Center.
To create mappings for the Channels Dimension and the Sales Cube:
In the OWB Design Center, select Window > OMB*Plus from the menu.
In the OMB*Plus panel, at the OMB+> prompt, enter the following:
OMB+> cd BISE1_installation_location/tutorial/owb/tcl
OMB+> source allinit.tcl
|
Note: The slash (/) character is specific to the OMB*Plus TCL interpreter and must be typed in as specified. |
Enter the password for the OWBREPOS_OWNER and SYS database accounts. Enter the location of the tcl files (it should be BISE1_installation_location/tutorial/owb/tcl). Click OK.
At the OMB+> prompt, enter the following:
OMB+> source loadmaps.tcl
Using the Project Explorer, verify that the LOAD_SALES and LOAD_CHANNELS objects have been created.
Save your work. Now, you have all the mappings needed to complete the design of the warehouse. In the next section, you will use the Generate option to generate the code to implement the design.
You have already designed and generated the LOAD_PRODUCTS mappings. Now, you need to generate mappings for the other dimensions, as well as the SALES cube.
To generate mappings for the other dimensions and SALES cube:
In the Design Center, expand BISE1_SALESWH > Mappings.
Right-click the LOAD_CHANNELS mapping and select Generate.
The Generation Results window displays the results. Ignore the warnings. There should not be any errors. Close the Generation Results window.
Repeat Steps 2 and 3 for each of the remaining mappings. You can ignore the warnings:
LOAD_PROMOTIONS returns 3 warnings
LOAD_GEOGRAPHY returns 9 warnings
LOAD_SALES returns 5 warnings
TIMES_MAP returns no warnings
Depending on the business intelligence needs, you can specify how you want to deploy your design. You can run individual mappings to populate target tables. However, you will more likely want to control dependencies and sequencing among multiple mappings by organizing multiple interdependent mappings within a process flow. When working with process flows, you will use an Oracle Workflow location. An Oracle Workflow location has been pre-defined for the purposes of this tutorial.
This section contains the following topics:
To create a new process flow:
In the Design Center, from the Connection Explorer panel, expand Locations > Process Flows and Schedules > Oracle Workflow.
Double-click OWF_MGR_LOCATION. Enter the password for OWF_MGR (specified during installation). Click Test Connection. If successful, click OK.
In the Design Center, from the Project Explorer, expand GEC_DW_TUTORIAL > Process Flows. Right-click Process Flow Modules and select New. The Create Module Wizard is launched. Click Next on the Welcome page.
On the Name and Description page, enter the following information:
Name: PF_BISE1_TUTORIALWH
Description: Process Flow module for BISE1_TUTORIALWH
Then, click Next.
On the Connection Information page, select OWF_MGR_LOCATION from the Location drop-down list. Click Next.
On the Summary page, review the summary details and click Finish. The Create Process Flow Package dialog box appears.
In the Create Process Flow Package dialog box, enter PK_SALES as the name of the process flow package and click OK. The Create Process Flow dialog box appears.
In the Create Process Flow dialog box, enter LOAD_BISE1_TUTORIALWH as the name of the process flow and click OK. The process flow is created and the Process Editor is launched.
To add a Fork activity, in the Process Editor, from the Palette, drag a Fork activity to the canvas. Place the FORK activity after the START activity and before the END activity.
To add the mappings:
In the Process Editor, from the Window menu, select Explorer (if not already selected). The Explorer panel should be visible.
In the Explorer panel, click the Available Objects tab. Expand Mappings > BISE1_TUTORIALWH. Drag the following mappings to the canvas:
LOAD_CHANNELS
LOAD_PROMOTIONS
LOAD_GEOGRAPHY
LOAD_PRODUCTS
TIMES_MAP
LOAD_SALES
Arrange the activities on the process flow canvas such that the following mapping activities appear after the Fork activity and before the END activity, and in the following order, from top to bottom:
LOAD_GEOGRAPHY, LOAD_CHANNELS, LOAD_PRODUCTS, LOAD_PROMOTIONS, TIMES_MAP
To move an activity within the canvas, select the activity. Place the cursor in the header area of the activity. The cursor changes to a single-headed arrow. Click in the header area and keep the left mouse button clicked and drag to the desired location.
Figure 3-73 shows the order of the activities.
To add an AND and control activities:
From the Palette, drag the AND activity to the canvas. If the Palette is not visible, from the Window menu, select Palette.
On the Process Editor, from the Palette panel, drag the End with Errors and End with Warnings activities to the canvas, and position them as shown in Figure 3-74.
To add transitions between activities:
Place the cursor (it will change to a single-headed arrow) inside an activity that acts as the source (for example, START). Keeping the left mouse clicked, drag and release the left mouse pointer when you are in the center of the target activity. Create transitions as shown in Table 3-7.
Table 3-7 Transitions for Activities
| From | To |
|---|---|
|
START |
FORK |
|
FORK |
LOAD_CHANNELS |
|
FORK |
LOAD_PROMOTIONS |
|
FORK |
LOAD_GEOGRAPHY |
|
FORK |
LOAD_PRODUCTS |
|
FORK |
TIMES_MAP |
|
LOAD_CHANNELS |
AND |
|
LOAD_PROMOTIONS |
AND |
|
LOAD_CUSTOMERS |
AND |
|
LOAD_PRODUCTS |
AND |
|
TIMES_MAP |
AND |
|
AND |
LOAD_SALES |
|
LOAD_SALES |
END_SUCCESS |
|
LOAD_SALES |
END_ERROR |
|
LOAD_SALES |
END_WARNING |
When you finish adding the transitions, your process flow should look like the one shown in Figure 3-75.
Click the transition line from the LOAD_SALES activity to the END_SUCCESS activity. From the Object Details panel, click Condition. Click the colon (:) button.
In the Transition Condition Editor, select SUCCESS from the Enumerated drop-down list. Click OK.
Click the transition line from the LOAD_SALES activity to the END_ERROR activity. From the Object Details panel, click Condition. Click the colon (:) button. In the Transition Condition Editor, select ERROR from the Enumerated Conditions drop-down list. Click OK.
Click the transition line from the LOAD_SALES activity to the END_WARNING activity. From the Object Details panel, click Condition. Click the colon (:) button. In the Transition Condition Editor, select WARNING from the Enumerated Conditions drop-down list. Click OK.
Your error handling process flow should appear as shown in Figure 3-78.
Save your work.
To validate and generate the process flow:
In the Process Editor, select Validate from the Process Flow menu. The validation results are displayed in the Compilation Results panel.
In the Process Editor, select Generate from the Process Flow menu. The script is generated and displayed in the Compilation Results window.
Save your work and close the Process Editor.
Until now, you have designed and configured the logical definitions of your target system. Now, you learn how to deploy and create the physical instance of your target. You will deploy the relational and dimensional objects. You will then deploy the ETL mappings. Finally, you will deploy and execute the process flow.
Instead of separately deploying the objects, mappings, and process flow, you can also deploy them all in one step. For the purposes of this tutorial, we are deploying them individually so that you can pause after each deployment and view the results.
This section contains the following topics:
Locations are specific to a type of module, such as the Oracle Database, SAP application, or flat file. In a flat file module, location is the path or the drive and directory in the file system where the flat files reside. In an Oracle Database module, location contains the database connection information.
To register the locations:
In the Design Center, select Control Center Manager from the Tools menu. The Control Center Manager is launched.
In the Control Center, from the navigation tree on the left, right-click BISE1_TUTORIALWH_LOCATION and select Register.
The Edit Oracle Database Location dialog box appears. Review the details, enter the password, and click Test Connection. If the connection is successful, click OK.
Repeat Steps 2 and 3 for OWF_MGR_LOCATION and BISE1_SALES_LOCATION.
To deploy sequences:
In the Control Center, in the left navigation tree, expand BISE1_TUTORIALWH_LOCATION > BISE1_TUTORIALWH. Select Sequences. On the right, in the Object Details panel, you see the sequences listed. Click the Default Actions button.
Click the Deploy button on the toolbar.
Monitor the progress in the Control Center Jobs panel. The Deployment tab is selected. In the Control Center Jobs panel, monitor the deployment progress. The deployment progress moves from Generation to Running to Completed Successful.
Note the value in the Deploy Status column. It displays Success against each object that has been deployed successfully.
Deploy the Tables in the BISE1_SALESWH target module. To do this:
In the Control Center, from the navigation tree, expand BISE1_TUTORIALWH_LOCATION > BISE1_TUTORIALWH. Select Tables. In the Object Details panel, the tables are listed. Note the values in the Design Status column, Deploy Action column, and Deploy Status column.
Click Default Actions.
|
Note: Clicking Default Action changes the value in the Deploy Action column from None to Create. |
Click the Deploy button on the toolbar. In the Control Center Jobs panel, monitor the deployment progress. The deployment progress moves from Generate to Run to Successful. Wait until the Deploy Status column displays Success.
To deploy the mappings:
In the Control Center, from the navigation tree, expand BISE1_TUTORIALWH_LOCATION > BISE1_TUTORIALWH. Select Mappings. In the Object Details panel, the mappings are listed. Note the values in the Design Status column, Deploy Action column, and Deploy Status column.
Click Default Actions.
|
Note: Clicking Default Action changes the value in the Deploy Action column from None to Create. |
Click the Deploy button on the toolbar. In the Control Center Jobs panel, monitor the deployment progress. The deployment progress moves from Generate to Run to Successful. Wait until the Deploy Status column displays Success.
Deploy the LOAD_BISE1_TUTORIALWH process flow. Expand OWF_MGR_LOCATION to find the LOAD_BISE1_TUTORIALWH process flow, as follows:
In the Control Center, from the navigation tree, expand OWF_MGR_LOCATION > PF_BISE1_TUTORIALWH. Select PK_SALES. In the Object Details panel, the process flow package is listed.
|
Note: When you deploy a process flow, all process flows listed in the package are deployed. |
Click Default Actions.
Click the Deploy button on the toolbar. In the Control Center Jobs panel, monitor the deployment progress. The deployment progress moves from Generate to Run to Successful. Wait until the Deploy Status column displays Success.
To execute the process flow:
Select the LOAD_BISE1_TUTORIALWH process flow.
To execute a process flow, click the Start button on the toolbar.
When you click the Start button, the Control Center Jobs panel in the lower section switches to the Execution tab.
In the Control Center Jobs panel, the Execution tab is selected. Monitor the execution until it has completed successfully.
|
Note: The executions are performed asynchronously, which means that you can close the Control Center and OWB client while the Process Flow or Mapping are being executed. |
In the Control Center Jobs panel, ensure that the Execution tab is selected. Double-click LOAD_BISE1_TUTORIALWH from the Job column. The Job Details window is displayed.
Click the Execution Results tab and monitor the Row Activity.
|
Note: You get a count of how many rows were inserted in respective dimensions and the cube. |
Close the Control Center Manager.
To check that the SALES cube has been populated, use the OWB Design Center Relational Data Viewer to view the data, as follows:
Select View > Refresh from the Design Center menu.
Expand GEC_DW_TUTORIAL> Databases > Oracle > BISE1_TUTORIALWH > Tables.
Right-click SALES and select Data. The Relational Data Viewer window appears, displaying the data in the SALES cube. When you are done viewing the data, close the Data Viewer window and exit the Design Center.
Congratulations! You have successfully set up your data mart.
