| Oracle® Warehouse Builder Sources and Targets Guide 11g Release 2 (11.2) Part Number E10582-02 |
|
|
View PDF |
| Oracle® Warehouse Builder Sources and Targets Guide 11g Release 2 (11.2) Part Number E10582-02 |
|
|
View PDF |
Warehouse Builder provides an end-to-end business intelligence (BI) solution by enabling you to import, design, and deploy metadata from different sources into a data warehouse, and by making that data available to business analytical tools for decision making and business reporting. It fully integrates relational, dimensional, and business metadata and provides access to business analytical tools for decision making and business reporting.
Warehouse Builder contains BI objects that enable you to integrate seamlessly with BI applications to perform data analysis. You can define BI objects that enable you to store definitions of business metadata. You can then deploy these definitions to Oracle's BI tools, such as Oracle Business Intelligence Suite Enterprise Edition (Oracle BI Suite EE) and Oracle BI Discoverer, thereby extending the functionality of your data warehouse.
This section contains the following topics:
Business definitions are the equivalent of Discoverer End User Layer (EUL) objects. They are BI objects that enable you to integrate Warehouse Builder data with Oracle BI Discoverer. Business definitions facilitate data analysis of the data stored in a data warehouse. You can define and then deploy business definitions to Oracle BI Discoverer. You can either create new business definitions or derive them from existing schemas. For information about creating business definitions, see "Creating Oracle Discoverer Module". For information about deriving business definitions, see "Deriving BI Objects".
This section contains the following topics:
Before you derive business definitions to deploy to Discoverer, you must create an Oracle Discoverer module to store your business definitions.
To create an Oracle Discoverer module:
From the Projects Navigator, click to expand the Project node.
Expand the Business Intelligence node.
Right-click Oracle Discoverer and select New Oracle Discoverer.
Warehouse Builder opens the Create Module Wizard.
Follow the wizard steps by clicking Next.
In the Name and Description page, enter a name and an optional description for the Oracle Discoverer module. Also, indicate the type of module you are creating.
For more information about naming conventions, see Oracle Warehouse Builder Data Modeling, ETL, and Data Quality Guide.
On the Connection Information page, define the location to deploy the Oracle Discoverer module. For example, this may be the system where you are currently running Oracle BI Discoverer.
If you want to use a deployment location you previously created, you can select it from the Location list. The connection information for this location displays on the wizard page.
You can also choose to create this location later and skip to the next page. Note that you cannot deploy the Oracle Discoverer module unless you provide the connection information for the target location.
The wizard initially creates a default target location for the module you are creating. For example, if your module is named DISCOVERER_OBJECTS, then the location will be called DISCOVERER_OBJECTS_LOCATION1. You can choose to provide the connection information for this location by clicking Edit. The Edit Oracle Discoverer Location dialog box is displayed. Provide the required information to connect with your target system and click OK. For more information about the Edit Oracle Discoverer Location dialog box, see "Defining Discoverer Locations".
A Discoverer location provides connection details of the system where you deploy the Oracle Discoverer modules. Oracle BI Discoverer EUL Release 10.1.2 or later should be installed on this system.
To define a Discoverer location, enter the following details on the Edit Oracle Discoverer Location dialog box:
Name: The name of the Discoverer location. Warehouse Builder assigns a default name for the location. You can edit this name.
Description: An optional description for the Discoverer location.
User Name: The name of the EUL owner to which you want to deploy your business definitions. You can also specify a user who has administrator privileges.
Password: The password for the user.
Note that user password is required only for direct integration.
Connection Type: The type of connection used to connect to the Discoverer EUL. The options you can select are Host:Port:Service or SQL*Net Connection.
When you select SQL*Net Connection, specify the net service name in the Net Service Name field. When you select Host:Port:Service, specify the following additional details.
Host: The host name of the system on which the EUL exists.
Port: The default port number is 1521.
Service Name: The service name of the Oracle Database installation.
Integration Type: Direct or Indirect depending on the connection. Direct indicates that the deployment will be made directly to the schema. Indirect provides file transfer options through file, FTP, HTTP, or HTTPS.
Depending on the mode of file transfer, you must provide the following details:
FILE
Root Path: Directory of the .eex file.
File Name: The name of the .eex file.
FTP
Host Name: The system credentials where the Oracle Discoverer server resides.
Host Login Port: Login port number, which is initially set to 0. You must change this according to your local configuration.
Transfer Format: Select from ASCII and IMAGE.
Host Login User: User name to run FTP.
Host Login Password: User password to run the FTP command.
File Name: The name of the .eex file along with the complete path.
HTTP and HTTPS
Host Name: The system credentials where the Oracle Discoverer server resides.
Host Login Port: Login port number, which is initially set to 0. You must change this according to your local configuration.
Host Login User: User name for the HTTP/HTTPS command.
Host Login Password: User password for the HTTP/HTTPS command.
File Name: The name of the .eex file along with the complete path.
Version: Represents the version of Discoverer to which the business definitions should be deployed. The list contains only one value, 10.1. Use this option to deploy to Oracle BI Discoverer 10g Release 2. This includes all Oracle BI Discoverer 10.1.x versions.
After you specify these details, click Test Connection to verify the connection.
In the Summary page, review the name and location information for the Oracle Discoverer module. Click Back if you want to make any changes or click Finish to finish creating the Oracle Discoverer module.
After the Oracle Discoverer module is created, you can locate it under the Oracle Discoverer node on the Projects Navigator. Expand the module to see that Warehouse Builder provides a representation for the different objects types that comprise the Discoverer EUL. You can define the following types of Discoverer EUL objects:
Item Folders
Business Areas
Drill Paths
Lists of Values
Alternative Sort Orders
Drills to Detail
Registered Functions
Item Folders are equivalent to Folder objects in Oracle BI Discoverer that map to database tables, external tables, or views. They represent a result set of data, similar to a database view. Item Folders store information just like tables. For example, they can be used to store details of employees or customers of an organization. An Item Folder contains entities called Items that correspond to columns in a table. Each item has a name and contains a specific type of information. For example, the Item Folder that contains details about employees may include items such as employee name, start date, and department.
There are two types of Item Folders: Simple and Complex. Simple Item Folders contain items that reference a single table in an Oracle module. Complex Item Folders, like database views, provide a method to group items from multiple Item Folders within the same Oracle Discoverer module. Item Folders also contain joins, calculated items, and conditions.
Note:
Warehouse Builder does not support the Discoverer custom folders.Warehouse Builder creates Item Folders when you derive business definitions from warehouse design objects in your Oracle module, as described in "Deriving BI Objects". You can also manually create a customized Item Folder using the Create Item Folder Wizard or the Graphical Navigator. The Graphical Navigator can also be used to edit Item Folders.
The following sections contain more information related to Item Folders:
After you derive your design object definitions, an Item Folder is created as part of the derived business definitions.
Warehouse Builder provides the document editors that enable you to edit the name and description of an Item Folder, view its source design objects, edit the items it contains, and specify or edit any joins or conditions.
To edit an Item Folder:
From the Projects Navigator, expand the Oracle Discoverer module node, then expand the Item Folders node.
Right-click the Item Folder name and select Open. Or double-click the Item Folder name. This displays the Item Folder editors.
Click each of the editors to edit the Item Folder using the guidelines described in the subsequent sections.
The Name editor enables you to edit the name and description for the Item Folder.
The Source Items editor displays the available source items for your Item Folder.
When you are editing an existing item folder, the Selected column displays the source items that were selected at the time of creating the Item Folder. To select different items as the source, use the left arrow to return the items from the Selected column to the Available column. Then use the right arrow to move the new source item from the Available column to the Selected column.
Your selected objects can contain related items from multiple Item Folders.
If you want to change the selected items, then use the left arrow to return the previously selected items. Now select an initial folder item from any of the available Item Folders within the same Oracle Discoverer module. You can then select additional folder items that have a relationship with the previously selected item. You cannot select items from unrelated Item Folders. The relationship between Item Folders are defined by the joins between them. To create a join between Item Folders, use the Joins editor to specify the relationships between the two Items Folders.
The Items editor displays the details and properties of all items in an Item Folder. You can view, create, and edit the following for an item:
Name: Represents the name of an item. If you want to change the current item, double-click the name and retype the new name.
Description: Optionally enter a description for this item.
If you select an item name, the property inspector displays the following properties for that item:
Alignment: The default alignment used for this item in a Discoverer report.
Business Name: Business name of item.
Case Storage: Select the case storage method.
Content Type: Describes the content of multimedia data in this item when used in drilling. If the column contains file names, set this property to FILE. Else set it to the file extension (avi,wav,jpg) to define the application that should process the data.
Created By: Created by.
Creation Time: Time of creation.
Database Column: Specifies the schema, table, and column based on which the item was created or derived.
Datatype: Select the data type for the item. All the data types are supported by Discoverer.
Default Aggregate: Indicate if the item will default to an aggregate in the Discoverer report.
Default Position: Select the position of this Item on a Discoverer report.
Default Width: The default width of the item when it is displayed in a Discoverer report. The width is in characters.
Display Case: Select in what case the item information will be displayed in a Discoverer report.
Format Mask: The format mask for this item when it is used in a work sheet.
Formula: You can provide a formula for any calculated items you want to specify. Click the Ellipsis button in this field to open the Formula dialog box. This dialog box contains a subset of the options in the Expression Builder. Use the Formula dialog box to create your calculation. This field is populated after you close the Formula dialog box. For more information about the Expression Builder, see Oracle Warehouse Builder Data Modeling, ETL, and Data Quality Guide.
Heading: The title for the item in a Discoverer report.
Item Class: Assign an Item Class that enables you to define properties for the item. The Item Class list contains Lists of Values, Alternative Sort Orders, and Drills to Detail. You can also remove a reference to an Item Class.
Last Update Time: Last updated time.
Max Char Fetched: The maximum amount of data that will be fetched from LONG, LONG RAW, and BLOB data types.
Replace NULL With: The value to use instead of the Item value if the value is NULL.
Updated By: Updated by.
Visible: Specifies whether the item is visible to a Discoverer user.
Word wrap: The default word wrap setting used for this item in a Discoverer report.
To add an item by using the Items editor:
Double-click the Item Folder in the Projects Navigator. The item folder editor is displayed. Click Items to open the Item Details section.
In the Item Details, add items by entering the names of the items.
Click Composition to open the composition editor.
The editor displays the item folder and the source object for the item folder. The editor can be used to associate an item with a column or another item.
For all the items that you added, map the appropriate element from the source object to the item on the item folder.
Joins enable you to associate data between two Item Folders. During data analysis, you may require information that resides in multiple folders. Joins enable users to perform business analysis and run reports across multiple Item Folders. After you create joins between Item Folders and deploy them to your Discoverer EUL, they are available for analysis in Discoverer Plus and Discoverer Viewer.
The Joins editor displays the relationships or joins between two Item Folders. You can define new joins by clicking on a new row and providing the required information. You can delete a join by right-clicking the box at the left of each join row that specifies the join's number and selecting Delete.
Figure 9-1 shows the contents of the Joins editor.
On the Joins page, click a row in the Join Name field. Provide the following information:
Join Name: Enter a name for the join you are creating.
Master Item Folder: Select the Item Folder that will be the Master. In the above example, DEPARTMENTS is the local item folder and COUNTRIES is the master item folder. A join is created between these two item folders.
Detail always has Master: Select this to indicate if your detail Item Folder will always have this master.
One to one: Select this to indicate a one-to-one relationship between the two Item Folders.
Outer join: Indicate from the list if there is an outer join in this relationship and its type.
Description: Optionally describe the join.
For each join, you can specify the Join Components by clicking in the field below and providing the following information:
Local Item: This list is populated with the items contained in the current Item Folder. Select an item from this list.
Operator: Select the relationship between the Local Item you selected and the Remote Item you will select from the Master Item Folder.
Remote Item: Select an Item from your Master Item folder to join with the Local Item from your current Item Folder.
If you select a Join name, the property inspector displays the following values for the join:
Business Name: Business name of the Join.
Description: Description of the Join.
External Foreign Key: Specifies the external foreign key for the join.
Physical Name: Provide a physical name if you want this to be different from the default.
Also see "Adding Joins Using the Graphical Navigator" for an alternate way to add joins.
The Conditions editor enables you to define or edit a condition that restricts selection on the chosen Item Folder. Use this editor to provide or edit a condition. This editor contains the following:
Condition Name: The name of the condition.
Condition: Click the Ellipsis button in this field to display the Expression Builder. Use this to create or edit a condition. For more information about the Expression Builder, see Oracle Warehouse Builder Data Modeling, ETL, and Data Quality Guide.
Description: Optionally describe the condition.
Mandatory: Select this to specify if the condition is mandatory. A mandatory condition is always applied to filter data that is being retrieved for this item folder. Non-mandatory conditions can be switched on and off by the user.
Case Sensitive: Specifies whether the case should match for character data types.
The Composition editor enables you to view the components of an Item Folder. It displays the objects that contribute to the composition of this item folder. These will be database objects for Simple Item Folders and contributing Item Folders for Complex Item Folders.
To add an item through the Composition editor:
Double-click the Item Folder in the Projects Navigator. The item folder editor is displayed. Click Composition.
The editor canvas displays the item folder and the source object from which the item folder was created. If no source object was selected while creating the item folder, then the canvas displays only the item folder.
Right-click anywhere on the canvas, and select Add, and then select the type of object (for example, Table, View, or External Table).
The Add a New or Existing Object dialog box is displayed. Select the object you want to reference and click OK.
The selected object is now visible on the canvas.
Drag the referenced element to an item in the item folder to create the composition for that item. For each column, an item is added in the item folder.
When you derive intelligence objects, Item Folders are created as part of the derived business definitions. However, if you want to define a customized Item Folder, you can create an Item Folder using the Create Item Folder Wizard.
Item Folders are Discoverer objects and may be Simple or Complex. Each Item Folder contains items that you can delete or edit, as described in "Editing an Item Folder".
To create an Item Folder using the Create Item Folder Wizard:
Expand the Oracle Discoverer module in which you want to create an Item Folder.
Right-click Item Folders and select New Item Folder.
Warehouse Builder opens the Create Item Folder Wizard.
Follow the wizard steps by clicking Next.
In the Name and Description page, enter a name and optional description for the Item Folder.
Warehouse Builder distinguishes Simple Item Folders from Complex Item Folders in the same way as Discoverer. A Simple Item Folder is directly based on columns from a single table in an Oracle module and calculated items based on constants or items from that Item Folder. A Complex Item Folder can contain items from multiple Item Folders within the same Oracle Discoverer module, as well as calculated items.
Select items for your Item Folder.
For a Simple Item Folder, you can select exactly one table, view, or external table from any module or user folder in the Oracle module, to be referenced by the Item Folder. Expand the selected object and proceed to select columns within the selected object, to your selected items. You can multi-select these referenced items by pressing the Ctrl key and using the right arrow to move them to the list of selected Items.
A Complex Item Folder can contain items from multiple Item Folders within the same Oracle Discoverer module. You can select the initial folder items from Item Folder A within an Oracle Discoverer module. You can then select additional folder items from another Item Folder B within the same module. However, the two Item Folders A and B must be related. You cannot select items from unrelated Item Folders. Thus, complex Item Folders combine multiple Item Folders that must be joined. You can define the joins using the Graphical Editor for an Item Folder. For more information about creating joins, see "Joins Editor".
For a Complex Item Folder, if there are multiple join paths between the item folders selected as the item sources, then the Join Selection page is displayed. The list on this page displays all the joins between the item folders. Select the join to be used for the Complex Item Folder being created.
In the Summary page, review the name and type of your Item Folder as well as items to be included in your Item Folder. Click Back if you want to make any changes or click Finish to create the Item Folder.
You can locate the Item Folder on the Projects Navigator under the Item Folders node in the Oracle Discoverer module. This Item Folder contains all the selected items. You can edit the Item Folder properties, create joins and conditions, and edit item properties using the Graphical Editor, as described in "Editing an Item Folder".
After creating the Item Folder, you can use the following editors from the graphical editor:
For more information about how you specify the details on each editor, refer to the description of these editors in the "Editing an Item Folder" section.
An alternate way of creating item folders is to use the Graphical Navigator. You can use the editor menu or the editor canvas of the Graphical Navigator to create an item folder.
To create an item folder using the menu, click anywhere on the Graphical Navigator. The main menu on the toolbar changes dynamically. From the main menu, select Graph, Add, Oracle Discoverer, Item Folder. The Add a New or Existing Item Folder dialog box is displayed. Follow the steps listed in "Steps to Create an Item Folder".
To create an Item Folder using the canvas, drag and drop an Item Folder icon from the Component Palette onto the canvas. Or right-click a blank area on the canvas and from the shortcut menu, select Add, Oracle Discoverer, Item Folder. The Add a New or Existing Item Folder dialog box is displayed. Follow the steps listed in "Steps to Create an Item Folder".
Use the following steps to create an Item Folder:
Select the Create a New Item Folder option.
In the New Item Folder Name field, specify a name for the Item Folder.
In the Oracle Module list, select the name of the Oracle Discoverer module to which the Item Folder should belong.
Click OK.
The Item Folder is added to the editor canvas. Double-click the item folder to open the editors for the item folder. For more information about the contents of these editors, see "Editing an Item Folder" .
An alternate way to create a Join is to use the Graphical Navigator.
To add a Join:
Ensure that the item folder is available on the Graphical Navigator.
Right-click Joins, and select Add a Join.
The Add Join dialog box is displayed.
Specify a name for the join, and click OK.
The Joins editor can be used to complete the definition of the Join or you can continue to use the graphical navigator for this.
Similarly, you can also drag an Item from the Items node to the Joins node to create a local item. You can also drag an Item to the Joins node of another Item folder to create a remote item.
Simple Item Folders are defined based on existing tables, views, or external tables. When the definition of the underlying object changes, you can update the Item Folder definition by synchronizing it with the object on which it is based.
To synchronize an Item Folder:
Expand the Item Folders node in the Projects Navigator.
Right-click the Item Folder and select Open.
The editors for the Item Folder are displayed.
Click the Composition editor to view the Item Folder.
On the canvas, right-click the Item Folder and select Synchronize.
The Synchronize Item Folder dialog box is displayed.
Review the details displayed on this dialog box and click OK.
Warehouse Builder synchronizes the item folder with the data object on which the item is based.
The Synchronize Item Folder dialog box enables you to update the Item Folder with any changes made to the data types used in the database object on which the Item Folder is based. This dialog box displays the details of the changes to be made to the Item Folder.
The Synchronize Item Folder dialog box contains three columns: Object, Reason, and Action. The Object column lists the component in the underlying database object that has changed. The Reason column displays a brief description of the reason for the synchronization. The Action column displays the action that will be taken to synchronize the Item Folder. The available actions are Update and None. If you select None for a component, no synchronization is performed for that object. Only definitions that have an Action set to Update are synchronized.
For example, the Item Folder DEPT_ITMF is derived from the DEPT table. After the Item Folder is created, you modify the DEPT table and change the data type of the column LOCATION from VARCHAR2 to NUMBER. When you synchronize the Item Folder DEPT_ITMF, the Synchronize Item Folder dialog box displays LOCATION in the Object column. The Reason column displays "Datatype mismatch". The Action column displays Update.
Click OK to perform the actions listed on the Synchronize Item Folder dialog box and update the Item Folder definition. If you do not want to perform the actions listed on this dialog box, click Cancel.
Warehouse Builder enables you to create a Business Area to deploy to a Discoverer EUL. Business Areas contain references to Item Folders stored in your Oracle Discoverer module and are used to group information about a common subject, for example, Sales Analysis, Human Resources, or Stock Control. The Discoverer users use these Business Areas as their starting point for building a query.
Business Areas only contain references to Item Folders and not the actual Item Folder definitions. Thus, a Business Area can contain a collection of unrelated Item Folders and the same Item Folder can appear in multiple Business Areas. This enables you to set up multiple Business Areas with different levels of detail, for example, Sales Analysis area containing one Item Folder, Sales Details area containing six Item Folders, and a Sales Transaction area with 30 Item Folders. When you delete an Item Folder, the reference to it from the Business Area is also deleted.
When you deploy a Business Area using the Control Center, the dependencies of the Business Area are not automatically deployed. For example, if a Business Area BUSN_AREA contains two Item Folders, IF1 and IF2, then when you deploy BUSN_AREA using the Control Center, IF1 and IF2 are not deployed.
You can create a Business Area using the Create Business Area Wizard or from the Graphical Navigator. You also use the editor to edit a business area.
To create a Business Area using the Create Business Area Wizard:
Expand a Oracle Discoverer module.
Right-click Business Areas and select New Business Area.
Warehouse Builder opens the Create Business Area Wizard.
Follow the wizard steps by clicking Next.
In the Name and Description page, enter a name and optional description for the Business Area.
In the Source page, all the Item Folders available within the Oracle Discoverer module are displayed. You can multi-select the Item Folders by pressing the Ctrl key and using the right arrow to move them to the list of Selected Item Folders.
In the summary page, review the Item Folders you selected. Click Back if you want to make any changes or click Finish to finish creating the Business Area.
After the Business Area is created, you can locate it on the Projects Navigator under the Business Areas node with references to the selected Item Folders stored in it.
To make changes to your Business Area definitions after you create them, use the Edit Business Area dialog box. For details, see "Editing a Business Area".
Alternatively, from the Graphical Navigator you can use the main menu or the canvas to create a business area.
To create a business area using the menu, click anywhere on the Graphical Navigator. The main menu on the toolbar changes dynamically. From the main menu, select Graph, Add, Oracle Discoverer, Business Area. The Add a New or Existing Business Area dialog box is displayed. Select Create a new Business Area and specify the name of the business area and the module to which it belongs. Click OK. The newly created business area is available on the Project Navigator. Double-click the business area to open the editors for the business area. See "Editing a Business Area" for details of the editors.
To create a business area from the Graphical Navigator, right-click a blank area on the editor canvas and select Add, Oracle Discoverer, Business Area. The Add a New or Existing Business Area dialog box is displayed. Select Create a new Business Area and specify the name of the business area and the module to which it belongs. Click OK. The newly created business area is now available on the Project Navigator. Double-click the business area to open the editors for the business area. Using the Source editor, move item folders from the Available list to the Selected list. On the Contents editor, you can view existing item folders and add new ones to the business area. See "Editing a Business Area" for details of the editors.
Warehouse Builder enables you to edit the definitions for a Business Area using the Edit Business Area dialog box.
To edit a Business Area:
From the Projects Navigator, expand the Business Area node.
Right-click a Business Area name and select Open.
Warehouse Builder opens the Edit Business Area dialog box containing the following editors: Name, Source, and Contents:
The Name editor enables you to edit the name and description of a Business Area.
The source editor displays the source of those Item Folders that have been included in the Business Area. You can add new item folders to the Business Area or remove existing ones using this editor.
The contents editor enables you to add item folders to a Business Area. To do this, right-click anywhere on the editor and select Add, Item Folder. The Add a New or Existing Item Folder dialog box is displayed. Follow the steps listed in "Steps to Create an Item Folder". You can also add an item folder by dragging and dropping the item folder from the Projects Navigator into the Contents editor. This creates a shortcut to the item folder. You can double-click the item folder to access it directly from the Contents editor.
Warehouse Builder enables you to create a Drill Path to deploy to a Discoverer EUL. Drill Paths define a hierarchy relationship between the items in your Oracle Discoverer module. For example, Region, Sub-region, Country, and State. Warehouse Builder creates these drill paths for derived dimensions. You can also create your own customized drill path definitions if you are familiar with your data.
To create a Drill Path:
Expand the Oracle Discoverer module.
Right-click Drill Paths and select New Drill Path.
Warehouse Builder opens the Create Drill Path Wizard.
Follow the wizard steps by clicking Next.
In the Name and Description page, enter a name and optional description for the Drill Path.
Use the Drill Levels page to define a drill level and specify the Item Folder it references. Optionally, you can provide a description for the Drill Levels. To define drill levels, click a row and provide the following information:
Drill Level: Enter a name for the drill level.
Item Folder: From the field, select the Item Folder it references.
Description: Provide an optional description for the drill level.
When you select a referencing Item Folder for the Drill Level, the wizard lists the available Items within that Item Folder under the Drill Level Items field at the bottom.
In this field, you can specify one or more items to act as drill items. Select the Use as Drill Item option for each Item you want to include as a drill item in the level.
Figure 9-2 displays the Drill Levels page of the Create Drill Path Wizard.
If there are multiple join paths between the Item Folders referenced by the drill levels, then the Join Selection page is displayed. The list displays the existing joins between the selected Item Folder. Select the join to use for the drill path.
In the summary page, review the drill levels you are creating. Click Back if you want to make any changes or click Finish to create the drill path.
You can locate the drill path on the Projects Navigator under your Oracle Discoverer module. Warehouse Builder enables you to edit a drill path using the Edit Drill Path dialog box.
Warehouse Builder enables you to edit drill paths using the Edit Drill Path dialog box.
To edit a drill path:
From the Projects Navigator, expand the Drill Paths node.
Right-click the Drill Path and select Open.
Warehouse Builder displays the Name and Levels editors.
The Name editor enables you to edit the name and the description of the drill path.
Use the Levels editor to edit the drill levels that you defined. The Drill Levels section lists the drill levels along with the item folders that they reference. The Item Folder column displays the item folder that a drill path references. You can modify this by selecting the new item folder from the list.
The Drill Level Items section displays the items that act as drill items. You can modify this list by selecting more items that act as drill items.
The Structure panel can be used to manipulate hierarchies. For example drill levels can be moved up in the same subtree, moved out of the current tree, or moved to the root level. However, multiple roots for Discoverer modules are not allowed and will subsequently fail validation. You can also remove a level. In this case, sub levels of deleted levels will move up to the root level.
In Discoverer, Lists of Values (LOVs) represents a set of valid values for an item. These are the values in the database column on which the item is based. LOVs enable end users to easily set conditions and parameter values for reports. An example of an LOV can be names of different countries that a user can pick from a list to view a report on the quantities of a product sold in four specific countries.
You can create lists of values for Item Folders using the Create List of Values Wizard as described below.
To create a List of Values:
Expand the Oracle Discoverer module.
Right-click Lists of Values and select New List of Values.
Warehouse Builder opens the Create List of Values Wizard.
Follow the wizard steps by clicking Next.
In the Name and Description page, enter a name and optional description for this list of values. Select the Set as Drill to Detail box if you also want to set this as a Drill to Detail. When you deploy these definitions to Discoverer, an Item Class that you can use both as a List of Values and as a Drill to Detail is created.
The Defining Items page enables you to select the item that will generate your LOV in Discoverer. This page displays all the Items available in your Discoverer module. Expand the nodes to select an item and click Next.
The Referencing Item page enables you to associate your LOV with different items. The Available Items column displays all the Items available in your Discoverer module. Expand the nodes to select the items that will reference your list of values. Use the right arrow to move your selections to the Selected Items column and click Next.
In the summary page, review the defining and referencing items selected for the LOV. Click Back if you want to make any changes or click Finish to finish creating the LOV.
You can locate the LOV on the Projects Navigator in the Oracle Discoverer module under the Lists of Values node. Warehouse Builder enables you to edit the name, description, and defining and referencing items associated with an LOV using the Edit List of Values dialog box.
Warehouse Builder enables you to edit a list of values using the Edit List of Values dialog box.
To edit a list of values:
From the Projects Navigator, expand the List of Values node.
Right-click the List of Values and select Open.
Warehouse Builder displays the Edit List of Values dialog box, where you can edit the following: Name, Defining Item, Referencing Items, and Options.
Use the Defining Item editor to edit the item that generates the list of values in Discoverer. The item that is the defining item is highlighted. To edit this and specify that another item should be used to generate the LOV, select the new item.
Use the Referencing Items editor to edit the items that reference the list of values. The Selected column lists the items that the list of values references. To add more items to which the list of values references, select the item in the Available column and use the right arrow to move it to the Selected column. To remove items that the list of values currently references, select the item from the Selected column and use the left arrow to move it to the Available column.
Use the Advanced editor to specify advanced options for the list of values. The advanced options are as follows:
Retrieve Values in groups of: Use this option to specify the number of values that are retrieved in group. The default value is 100 which means that the values are retrieved in groups of 100.
Sort the values and remove duplicates: Select this option to remove duplicate values from the list of values and to order the values. This ensures that the LOV always shows unique, ordered values.
Show values in "Select Items" page of Worksheet Wizard: Select this option to enable users to expand the List of Values when selecting items to include in a query.
Require user to always search for values: Select this option to display the Search dialog box every time the List of Values is expanded.
Cache List of Values during each connection: Select this option to store the list of values when the List of Values is expanded for the first time. This improves performance because otherwise, every time the List of Values is expanded, the values are fetched from the database.
In Discoverer, alternate sorts enable end users to display values in a non-standard sequence.For example, by default the values of the Description item are sorted alphabetically. If you want to sort the description according to the values of the Product Key item, you need to define an alternate sort item and link the two items together. One item defines the sort order and the other defines the item to be sorted.
Define how you want to order the information in your Discoverer EUL using the Create Alternative Sort Order Wizard.
To create an Alternative Sort:
Expand the Oracle Discoverer module.
Right-click Alternative Sort Orders and select New Alternative Sort Order.
Warehouse Builder opens the Create Alternative Sort Order Wizard.
Follow the wizard steps by clicking Next.
In the Name and Description page, enter a name and optional description for the alternative sort order.
Select the Set as Drill to Detail box if you also want to set this as a Drill to Detail. When you deploy these definitions to Discoverer, an Item Class that can be used both as an Alternative Sort Order and as a Drill to Detail is created.
The Defining Item page enables you to select the Item that contains the values to be sorted. Expand the nodes to select an item and click Next.
Use the Defining Order Item page to select an Item, in the same Item Folder, that defines the order in which the values of the Item you selected on the Defining Item page are displayed. Expand the nodes to select the item and click Next.
The Referencing Items page enables you to associate your Alternative Sort Order with different items. The Available column lists all the Items in the Discoverer module. Expand the nodes to select the items that will reference your Alternative Sort Order. Use the right arrow to move your selections to the Selected column and click Next.
This panel enables you to shuttle across an item that already references an item class. You can either change the reference or decide not to shuttle the item across.
In the summary page, review the alternative sort order definition. Click Back if you want to make any changes or click Finish to finish creating the alternative sort order.
You can locate the alternative sort order on the Projects Navigator in the Oracle Discoverer module under the Alternative Sort Order node. Warehouse Builder enables you to edit the name, description, as well as the defining and referencing items associated with an alternative sort order using the Edit dialog box.
The Edit Alternative Sort Order dialog box enables you to edit an alternative sort order.
To edit an alternative sort order:
Expand the Alternative Sort Order node in the Projects Navigator.
Right-click the Alternative Sort Order and select Open.
The Edit Alternative Sort Order dialog box containing the following tabs is displayed: Name, Defining Item, Defining Order Item, Referencing Items, and Options.
Use the Name tab to edit the name and description of the alternative sort order.
Use the Defining Item tab to edit the item that contains the values to be sorted. This editor displays the Item that currently defines the alternative sort order highlighted. To change this selection, click the item that you now want to use to define the alterative sort order.
The Defining Order Item tab displays the Item Folder with the item that currently defines the order in which the values of the Item selected on the Defining Item editor are displayed. You can change this selection by clicking a new item from the tree.
The Referencing Items tab lists the items that will reference your Alternative Sort Order in the Selected column. To add more items to this list, select the item in the Available column and use the right arrow to move the item to the Selected column. To remove an item that is already selected, move the item from the Selected column to the Available column using the left arrow.
Use the Options tab to specify advanced options for the alternative sort order. The options you can set are as follows:
Retrieve values in groups of: Use this option to specify the number of values that are retrieved in group. The default value is 100 which means that the values are retrieved in groups of 100.
Sort the values and remove duplicates: Select this option to remove duplicate values from the alternative sort order and to order the values. This ensures that the alternative sort order always shows unique, ordered values.
Show values in "Select Items" page of the Worksheet Wizard: Select this option to enable users to expand the alternative sort order when selecting items to include in a query.
Require user to always search for values: Select this option to display the Search dialog box every time the Alternative Sort Order is expanded.
Cache list of values during each connection: Select this option to store the Alternative Sort Order when it is expanded for the first time. This improves performance because otherwise, every time the Alternative Sort Order is expanded, the values are fetched from the database.
In Discoverer, drills to detail enable you to analyze your data thoroughly by navigating through your data and performing drill down operations to obtain detailed information. When you define drills to detail, you define relationships between items. These drills enable you to interactively drill up or down through your data to see a different level of detail. For example, you can move from actuals to budgets for the same department, then look at the department employee details, then drill down to their salary and training histories, then drill to their job grades structure, and so on.
You can define a drill to detail using the Create Drill to Detail dialog box.
To create a Drill to Detail:
Expand the Oracle Discoverer module.
Right-click Drills to Detail and select New Drill to Detail.
Warehouse Builder opens the Create Drill to Detail dialog box.
Name: Enter a name for the drill to detail definition.
Description: Provide an optional description for the drill to detail.
The Available column at the bottom of the dialog box lists the Item Folders in the Oracle Discoverer module. Select a referencing item from this set and use the right arrow to move it to the Selected column.
Use the Edit Drill to Detail dialog box to edit a Drills to Detail.
To edit a Drills to Detail:
Expand the Drills to Detail node in the Projects Navigator.
Right-click the name of the Drill to Detail and select Open.
The Edit Drill to Detail dialog box is displayed. The contents of this dialog box are the same as the Create Drill to Detail dialog box. In addition to modifying the name and description of the drill to detail, you can edit the referencing items. for more details on the contents of the Drill to Detail dialog box, see "Create Drill to Detail Dialog Box".
In Discoverer, you can use custom PL/SQL functions to perform operations or calculations on values in an Item. To access these functions in Discoverer, the user-defined functions are registered in the EUL. If you want to use any of those registered user-defined functions in Discoverer, you need to include that information in your object definitions.
You can define a registered function using the Create Registered Function Wizard as described below.
To create a Registered Function:
Expand the Oracle Discoverer module.
Right-click Registered Function and select New Registered Function.
Warehouse Builder opens the Create Registered Function Wizard.
Follow the wizard steps using the guidelines below.
In the Name and Description page, enter a name and optional description for the registered function.
From the Select the return type of the function list, select a return type for the function. Select Available to User to indicate if a Discoverer end-user can use this registered function in calculations.
Specify the function parameters by clicking on a row and entering a name for the parameter. From the Data Type list, select the data type for the parameter. Use the Description field to enter an optional description.
In the Summary page, review the registered function definition. Click Back if you want to make any changes or click Finish to finish creating the registered function.
You can locate the registered function on the Projects Navigator in the Oracle Discoverer module under the Registered Functions node. Warehouse Builder enables you to edit the name, description, and parameters of the registered function using the Edit dialog box.
Use the Edit Registered Function dialog box to edit a registered function.
To edit a registered function:
Expand the Registered Functions node in the Projects Navigator.
Right-click the registered function and select Open.
The Edit Registered Function dialog box containing the following tabs is displayed: Name and Parameters.
Use the Name tab to edit the name and the description of the registered function.
Use the Parameters tab to edit the parameters of the registered function. You can edit the name, type, and description of a parameter. Add new parameters by clicking on an empty row and specifying the name of the parameter and its data type. You can move the parameters using the arrow keys.To delete a parameter, right-click the gray cell (which displays the number) to the left of the parameter name and select Delete.
During the design phase, you create definitions for the BI objects. After you design objects, you can assign physical properties to these design objects by setting configuration parameters.
To configure a BI object, right-click the object in the Projects Navigator and select Configure. The Configuration Properties dialog box is displayed. Click the object name on the left side of this dialog box to display the configuration parameters on the right.
All BI objects have a configuration parameter called Deployable. Select Deployable if you want to generate scripts and deploy the business object. Warehouse Builder only generates scripts for objects marked deployable.
The following sections describe additional configuration parameters for different types of BI objects.
You can set the following configuration parameters for a Oracle Discoverer module.
Object Matching: Indicates how object matching during deployment to Discoverer should be performed. When you deploy business definitions, an .eex file is first created and then this file is imported into the Discoverer EUL.
The options you can select for Object Matching are By Identifier or By Name. Warehouse Builder uses this setting to check if an object similar to one that is being deployed already exists in the EUL. If a similar object is found, in Create mode the objects are not deployed and in Upgrade mode the objects are refreshed.
MLS Deployment Language: Represents the language used for deployment to Discoverer.
Location: Represents the Discoverer location to which the Oracle Discoverer module is deployed.
You can set the following configuration parameters for item folders.
Optimizer Hint: Represents the optimizer hint to be added when the item folder is used in a query. Click the Ellipsis button on this field to specify the optimizer hint.
Location: Represents the location of the database object that the item folder references.
For registered functions, you can set the following configuration parameters.
Package: Represents the name of the package that contains the registered function.
Location: Represents the location of the database object that the registered function references.
After you create your business definitions, you can deploy them to Oracle BI Discoverer. The method used to deploy business definitions depends on the version of Oracle BI Discoverer to which the business definitions are being deployed and the licensing option used.
Note:
The method of deploying business definitions depends on the Warehouse Builder licensing option and the version of Oracle BI Discoverer to which you want to deploy business definitions.You can create a direct or indirect connection to the Oracle BI Discoverer EUL. With a direct connection, you must specify the location of the Oracle BI Discoverer and the business definitions are directly deployed to that location. With an indirect connection, you must specify an .eex file to store the business definition. You must also specify the mode of transferring the .eex to the Discoverer EUL. See "Defining Discoverer Locations" for information about providing these connection details.
Table 9-1 summarizes the combinations possible when you deploy business definitions to Oracle BI Discoverer using the different licensing options.
Table 9-1 Different Methods of Deploying Business Definitions
| Discoverer Version | Warehouse Builder Core Functionality | Warehouse Builder Enterprise ETL Option |
|---|---|---|
|
Versions Lower than Oracle BI Discoverer 10g Release 2 |
Use Indirect mode of connection. Deploy an See "Indirectly Deploying Business Definitions to Oracle BI Discoverer". |
Use Indirect mode of connection. Deploy an See "Deploying Business Definitions to Earlier Versions of Oracle BI Discoverer". |
|
Oracle BI Discoverer 10g Release 2 and later |
Use Indirect mode of connection. Deploy the See "Indirectly Deploying Business Definitions to Oracle BI Discoverer". |
Use the Direct mode of connection. Use the Control Center to directly deploy to Oracle BI Discoverer. Note however that you can make a direct deployment to Oracle BI Discoverer only if you installed a standalone version of Oracle Warehouse Builder 11g Release 2 (11.2). If you are using Oracle Warehouse Builder directly from Oracle Database 11g, then you can use only the indirect mode of connection. See Oracle Warehouse Builder Installation and Administration Guide for Windows and Linux for more information about the different installation options. See "Directly Deploying Business Definitions to Oracle BI Discoverer". |
You can directly deploy business definitions to Oracle BI Discoverer, just like you deploy other data objects, using the Control Center or Projects Navigator. See Table 9-1 for information about the scenarios when you can directly deploy to Oracle BI Discoverer.
The business definitions are deployed to the Discoverer location associated with the Business Definition module that contains these business definitions. Before you deploy business definitions, ensure that a valid Discoverer location is associated with the Business Definition module. For information about how to associate a Discoverer location with a Business Definition module, see "Setting the Connection Information".
When you deploy business definitions directly to Oracle BI Discoverer 10g Release 2 and later, the following steps are performed:
An .eex file that contains the definitions of the business definitions is created.
A connection is established to the EUL specified in the Discoverer location.
Note:
If the EUL is in a different database from your object definitions, then a database link is created.The .eex file is imported into Oracle BI Discoverer.
During the import, new business definitions are appended on top of the existing definitions. You must validate the EUL and remove redundant definitions. For example, if you deployed an item folder with four items. Subsequently, you deleted one item from the item folder. When you redeploy the item folder, it still contains four items. This is because while new definitions are appended, old definitions are not removed.
You cannot directly deploy business definitions to versions of Oracle BI Discoverer earlier than 10g Release 2. However, you can use the indirect mode of connection to deploy business definitions. See Table 9-1 for more information about the different scenarios when you can make an indirect deployment to Oracle BI Discoverer.
While creating the location of the business definition module, use the Indirect mode, and specify an .eex file to store the details of the business definition. When you deploy the business definition, the relevant details are captured in the .eex file. You can connect to the EUL using Oracle BI Discoverer and import this .eex file.
When you use the core functionality of Warehouse Builder, you cannot directly deploy business definitions to Oracle BI Discoverer. Instead you must use the indirect method of deployment to deploy the .eex file and then manually import it from Oracle BI Discoverer. You can specify the indirect method of deployment by selecting this option in the connection information page while creating a Discoverer location.
Note:
Indirect deployment takes place through the run time service running under a user in the database. This database user must have privileges to write to the destination.Once you successfully deploy the BI objects that you create, these objects are available in Oracle BI Discoverer. You can use these objects to perform analysis on your warehouse data.
After you deploy the business definitions that you create, these objects are available in the EUL to which they were deployed. Log in to Oracle BI Discoverer Administrator using the user name that you used to deploy the business definitions.
Figure 9-3 displays the Discoverer Administrator interface with the business definitions that you deployed.
Figure 9-3 Discoverer Administrator Showing BI Objects
You can now use Oracle BI Discoverer to create reports based on the BI objects that you deployed.
You can create business definitions from various database objects and integrate these business definitions with OBIEE. The various database objects that can be derived include tables, dimensions, cubes, views, and MVs. These objects can be derived from Oracle and other non-Oracle databases. The data exported as business definitions into OBIEE facilitates data analysis and report generation, which is then used in decision making by businesses.
You must create an Oracle Business Intelligence (OBI) module to store business definitions before you can deploy them to OBIEE.
To create an Oracle Business Intelligence module:
In the Projects Navigator, under Business Intelligence, right-click Oracle Business Intelligence and select New Oracle Business Intelligence.
The Create Module wizard is displayed.
In the Name and Description page, provide a name and description (optional) for the module. Also specify the type of module.
In the Connection Information page, select a location from the Location list. To edit the connection information for the location, click Edit. The Edit Oracle BI location dialog box is displayed. See "Defining Oracle Business Intelligence Location" for details of connection information to be provided.
Once you complete the steps in the wizard, the new Oracle Business Intelligence module is available under the Business Intelligence node. The module consists of the following objects:
Logical Tables
Catalog Folders
Dimension Drill Paths
The OBI module location refers to a UDML file on the OBIEE server. At the time of specifying the location, specify a UDML file in the OBIEE server as the location. You must also specify the mode of file transfer from the local system to the OBIEE server. Provide the following details in the Edit Oracle BI Location dialog box:
Name: Provide a location name.
Description: Provide an optional description.
Transport Type: Select from FILE, FTP, HTTP, and HTTPS.
Version: Select the OBIEE version.
Depending on the mode of file transfer, you must provide the following details:
FILE
Root Path: Directory of the UDML file.
File Name: The name of the UDML file.
FTP
Host Name: The system credentials where the OBIEE server resides.
Host Login Port: Login port number, which is initially set to 0. You must change this according to your local configuration.
Transfer Format: Select from ASCII and IMAGE.
Host Login User: User name to run FTP.
Host Login Password: User password to run the FTP command.
File Name: The name of the UDML file along with the complete path.
HTTP and HTTPS
Host Name: The system credentials where the OBIEE server resides.
Host Login Port: Login port number, which is initially set to 0. You must change this according to your local configuration.
Host Login User: User name for the HTTP/HTTPS command.
Host Login Password: User password for the HTTP/HTTPS command.
File Name: The name of the UDML file along with the complete path.
Logical Tables are equivalent to objects in OBIEE that map to database tables, external tables, or views. They represent a result set of data, similar to a database view. Logical Tables also store information just like tables. A logical table contains items that map to columns in a table. Each item has a name and contains specific type of information. For example, the logical table containing details about employees may include items such as employee name, start date, and department.
Warehouse Builder creates Logical Tables when you derive business definitions from warehouse design objects in the database modules, as described in "Deriving BI Objects". You can also manually create a customized Logical Table using the Create Logical Table Wizard or the Graphical Navigator. The Graphical Editor is used to edit Logical Tables.
The following sections contain more information related to Logical Tables:
After you derive your design object definitions, a Logical Table is created as part of the derived business definitions.
Warehouse Builder provides the document editors that enable you to edit the name and description of a Logical Table, view its source design objects, edit the Items it contains, and specify or edit any joins or conditions.
To edit a Logical Table:
From the Projects Navigator, expand the OBI module node, then expand the Logical Tables node.
Right-click the Logical Table and select Open. Or double-click the Logical Table. This displays the Logical Table editors.
Click each of the editors to edit the Logical Table using the guidelines below.
The Name editor enables you to edit the name and description for the Logical Table.
The Source Items editor displays the available source items for the Logical Table.
The Available column displays database tables in the current project as well as logical tables in the OBI module.
When you are editing an existing logical table, the Selected column displays the source items that were selected at the time of creating the logical table. To select different items as the source, use the left arrow to return the items from the Selected column to the Available column. Then use the right arrow to move the new source item from the Available column to the Selected column.
Your selected objects can contain items from multiple Logical Tables.
If you want to change the selected items, then use the left arrow to return the previously selected items. Now select an initial folder item from any of the available Logical Tables within the same OBI module. You can then select additional folder items with the previously selected item.
To add an item through the Source Items editor:
Double-click the Logical Table in the Projects Navigator. The logical table editor is displayed. Click Source Items.
The Selected section displays the items that are currently included in the logical table. If you drill down the Available section, it displays the items that can be added.
Select and move the required items from the Available to the Selected section.
Items Editor displays the details and properties of all Items in a Logical Table. You can view, create, and edit the following for an Item:
Name: Represents the name of an Item. If you want to change the current Item, double-click the name and retype the new name.
Description: Optionally enter a description for this Item.
If you select an item name, the property inspector displays the following properties for that item:
Business Name: The business name of the item.
Created By: Created by.
Creation Time: Time of creation.
Database Column: The database column that the item maps to.
Datatype: The data type of the item.
Default Aggregate: The aggregation function for the items.
Description: Description of the item.
Formula: This is the expression for a calculated item.
Last Update Time: Last updated time.
Physical Name: Physical name of the item.
Updated By: Updated by.
Visible: Select this option if the item is to be made visible to OBIEE Report Builder.
To add an item through Items Editor:
Double-click the Logical Table in the Projects Navigator. The logical table editor is displayed. Click Items to open the Item Details section.
In the Item Details, add items by entering the names of the items.
Click Composition to open the composition editor.
Use the composition editor to define the referencing items or use the formula property to enter the expression for this item.
Joins enable you to associate data between two logical tables. During data analysis, you may need to retrieve information that resides in multiple logical tables. Joins enable end users to perform business analysis and run reports across multiple logical tables. After you create joins between logical tables and deploy them to your OBIEE Repository, they are available for analysis in OBIEE Answers or Dashboards.
The Joins editor displays the relationships or joins between two logical tables. You can define new joins by clicking on a new row and providing the required information. You can delete a join by right-clicking the box on the left of each join row and selecting Delete.
Figure 9-4 shows the contents of the Joins editor for the logical table EMP.
On the Joins page, click a row in the Joins field. Provide the following information:
Join Name: Enter a name for the join you are creating.
Master Logical Table: Select the Logical Table that will be the Master. In the above example, you select the logical table DEPARTMENTS as your master. This implies that you will select an item from the DEPARTMENT Logical Table to join with the two items you selected from the Logical Table EMP.
Detail always has Master: Select this box to indicate if your detail Logical Table will always have this master.
One to one: Select this box to indicate a one-to-one relationship between the two Logical Tables.
Outer join: Indicate from the list whether there is an outer join in this relationship and its type.
Description: Optionally describe the join.
For each join, you can specify the Join Components by clicking in the field below and providing the following information:
Local Item: This list is populated with the Items contained in the current Item Folder. Select an Item from this list.
Operator: Select the relationship between the Local Item you selected and the Remote Item you will select from the Master Logical Table.
Remote Item: Select an Item from your Master Logical Table to join with the Local Item from your local Logical Table.
Also see "Adding Joins Using the Graphical Navigator" for an alternate method to add joins.
Double-click on a Join name to view the following properties of the join:
Business Name: The business name of the item.
Created By: Created by.
Creation Time: Time of creation.
Description: Description provided at the time of creation.
External Foreign Key: If the join was derived from a foreign key, then that foreign key is listed here.
Last Update Time: Last updated time.
Physical Name: Physical name of the join.
Updated By: Updated by.
The Composition editor enables you to view the components of a Logical Table. It displays the Logical Table as well as the objects from which it is referencing.
To add an item through the Composition editor:
Double-click the Logical Table in the Projects Navigator. The logical table editor is displayed. Click Composition.
The editor canvas displays the logical table and the source objects from which the logical table was created. If no source object was selected while creating the logical table, then the canvas displays only the logical table.
Right-click anywhere on the canvas, and select Add, and then select the type of object (Table, View, Materialized View, Logical Table).
The Add a New or Existing Object dialog box is displayed. Select the object from which you want to add the items and click OK.
The selected object is now visible on the canvas.
Map the required columns or items from the object to the logical table.
When you derive intelligence objects, Logical Tables are created as part of the derived business definitions. However, if you want to define a customized Logical Table, you can create a Logical Table using the Create Logical Table Wizard.
Each logical table contains items that you can delete or edit, as described in "Editing a Logical Table".
To create a Logical Table using the Create Logical Table Wizard:
Expand the OBI module in which you want to create a logical table.
Right-click Logical Tables and select New Logical Table.
Warehouse Builder opens the Create Logical Table Wizard.
Follow the wizard steps by clicking Next.
In the Name and Description page, enter a name and optional description for the Logical Table.
You can select from tables, views, or materialized views from any of the database modules, to be referenced by the Logical Table. If you select multiple tables, then these must be joined by a foreign key. Expand the selected object and proceed to select columns within the selected object, to your selected items. You can multi-select these referenced items by pressing the Ctrl key and using the right arrow to move them to the list of selected Items.
You can also select from other Logical tables within the same OBI module. You can select the initial items from Logical Table A within an OBI module. You can then select additional folder items from another Item Folder B within the same module.
If there are multiple foreign keys between the tables selected as the item sources, the Foreign Key Selection page is displayed. The list on this page displays all the joins between the tables. Select the join to be used for the table being created.
In the Summary page, review the details you have provided for the Logical Table including the items to be included in the table. Click Back if you want to make any changes or click Finish to create the Logical Table.
You can locate the Logical Table on the Projects Navigator under the Logical Tables node in your OBI module. This Logical Table contains all the selected items. You can edit the Logical Table properties, create joins, and edit item properties. The Graphical Navigator can also be used to edit a Logical Table.
After creating the Logical Table, you can use the following editors:
For more information about how you specify the details on each editor, refer to the description of these editors in the "Editing a Logical Table".
An alternate way of creating logical tables is to use the Graphical Navigator. You can use the editor menu or the editor canvas of the Graphical Navigator to create a logical table.
To create a logical table using the menu, click anywhere on the Graphical Navigator. The main menu on the toolbar changes dynamically. From the main menu, select Graph, Add, Oracle Business Intelligence, Logical Table. The Add a New or Existing Logical Table dialog box is displayed. Follow the steps listed in "Steps to Create a Logical Table".
To create a Logical Table using the canvas, drag and drop a Logical Table icon from the Component Palette onto the canvas.
Or right-click a blank area on the canvas and select Add, Oracle Business Intelligence, Logical Table. The Add a New or Existing Logical Table dialog box is displayed. Follow the steps listed in "Steps to Create a Logical Table".
Use the following steps to create a Logical Table:
Select the Create a New Logical Table option.
In the New Logical Table Name field, specify a name for the Logical Table.
In the OBI Module list, select the name of the OBI module to which the Logical Table should belong.
Click OK.
The Logical Table is added to the editor canvas. Double-click the logical table to open the editors for the logical table. For more information about the contents of these editors, see "Editing a Logical Table".
After you add a Logical Table to the Graphical Navigator, you can add individual items to it. On the Graphical Navigator, right-click Items and select Add an Item. On the Add Item dialog box provide a name for the new item.
At all times, for any Logical Table, the property inspector displays a list of properties for that Logical Table. You can configure the following:
Bridge Table: Select this option to create a bridge table. A bridge table is an intermediate table between a fact table and a dimension table, and is used to resolve a many-to-many association.
Distinct Values: Select this option if you want only distinct values to be selected from the source physical table.
Visible: Select this option to make the logical visible to OBIEE users.
An alternate way to create a Join is to use the Graphical Navigator. To add a Join:
Ensure that the logical table is available on the Graphical Navigator.
Right-click Joins, and select Add a Join.
The Add Join dialog box is displayed.
Specify a name for the join, and click OK.
Open the Joins editor for the logical table and specify the conditions for the join. See "Joins Editor" for more details.
Using the Graphical Navigator, you can also create a join between items in two Logical Tables. Ensure that the Logical Tables are visible on the Graphical Navigator. To create a join between two Items, drag an Item from one Logical Table to the required Item in the other Logical Table. Similarly, you can also drag an Item from the Items node to the Joins node to create a local item. You can also drag an Item to the Joins node of another Item folder to create a remote item.
Logical Tables are defined based on existing tables, views, or materialized views. When the definition of the underlying object changes, you can update the Logical Table definition by synchronizing it with the object on which it is based.
To synchronize a Logical Table:
Expand the Logical Tables node in the Projects Navigator.
Right-click the Logical Table and select Open.
The editors for the Logical Table are displayed.
Click the Composition editor to view the Logical Table.
On the canvas, right-click the Logical Table and select Synchronize.
The Synchronize Item Folder dialog box is displayed.
Review the details displayed on this dialog box and click OK.
Warehouse Builder synchronizes the Logical Table with the data object on which the item is based.
The Synchronize Logical Table dialog box enables you to update the Logical Table with any changes made to the data types used in the database object on which the Logical Table is based. This dialog box displays the details of the changes to be made to the Logical Table.
The Synchronize Logical Table dialog box contains three columns: Object, Reason, and Action. The Object column lists the component in the underlying database object that has changed. The Reason column displays a brief description of the reason for the synchronization. The Action column displays the action that will be taken to synchronize the Logical Table. The available actions are Update and None. If you select None for a component, no synchronization is performed for that object. Only definitions that have an Action set to Update are synchronized.
For example, the Logical Table DEPT_ITMF is derived from the DEPT table. After the Logical Table is created, you modify the DEPT table and change the data type of the column LOCATION from VARCHAR2 to NUMBER. When you synchronize the Logical Table DEPT_ITMF, the Synchronize Logical Table dialog box displays LOCATION in the Object column. The Reason column displays "Datatype mismatch". The Action column displays Update.
Click OK to perform the actions listed on the Synchronize Logical Table dialog box and update the Logical Table definition. If you do not want to perform the actions listed on this dialog box, click Cancel.
Warehouse Builder enables you to create a Dimension Drill Path to deploy to an OBIEE repository. Dimension Drill Paths define a hierarchy relationship between the items in your OBI module. For example, Region, Sub-region, Country, State, etc. Warehouse Builder creates these dimension drill paths for derived dimensions. You can also create your own customized dimension drill path definitions if you are familiar with your data.
To create a Dimension Drill Path:
Expand the Oracle Business Intelligence module.
Right-click Dimension Drill Paths and select New Dimension Drill Path.
Warehouse Builder opens the Create Dimension Drill Path Wizard.
Follow the wizard steps by clicking Next.
In the Name and Description page, enter a name and optional description for the Dimension Drill Path.
Use the Drill Levels page to define a drill level and specify the Logical Table it references. Optionally, you can provide a description for the Drill Levels. To define drill levels, click a row and provide the following information:
Drill Level: Enter a name for the drill level.
Logical Table: From the field, select the Logical Table it references.
Description: Provide an optional description for the drill level.
When you select a referencing Logical Table for the Drill Level, the wizard lists the available Items within that Logical Table under the Drill Level Items field at the bottom.
In this field, you can specify one or more items to act as drill items. Select the Use as Drill Item option for each Item you want to include as a drill item in the level. Note that an Item can be a drill item in a single level only.
Figure 9-2 displays the Drill Levels page of the Create Dimension Drill Path Wizard.
In the Drill Level Keys section, select a Drill Level and specify a name for Drill Level Key and optionally provide a description for the key. In the Drill Level Key Items, select the Item that must used as the key.
In the summary page, review the drill levels you are creating. Click Back if you want to make any changes or click Finish to create the dimension drill path.
You can locate the dimension drill path on the Projects Navigator under your OBI module. Warehouse Builder enables you to edit a dimension drill path using the Edit Dimension Drill Path dialog box.
Warehouse Builder enables you to edit dimension drill paths using the Edit Dimension Drill Path dialog box.
To edit a dimension drill path:
From the Projects Navigator, expand the Dimension Drill Paths node.
Right-click the Dimension Drill Path and select Open.
Warehouse Builder displays the Edit Dimension Drill Path dialog box containing the following editors: Name, Levels, and Level Keys.
The Name editor enables you to edit the name and the description of the dimension drill path.
Use the Drill Levels editor to edit the drill levels that you defined. The Drill Levels section lists the drill levels along with the logical tables that they reference. The Logical Table column displays the logical table that a dimension drill path references. You can modify this by selecting the new logical table from the list.
The Drill Level Items section displays the items that act as drill items. You can modify this list by selecting more items that act as drill items. You can also move a level up in same subtree, move a level out of the current tree, move a level to the root level, copy a level out of the current tree, remove a level, remove a level cascade, and remove a level instance, for example when there are multiple instances of the level in the network.
Use the Level Keys editor to select the Drill Level, and edit the Drill Level Keys and Drill Level Key Items.
Warehouse Builder enables you to create a Catalog Folder to deploy to an OBIEE repository. Catalog folders contain references to Logical Tables and Dimension Drill Paths stored in your OBI module and are used to group information about a common subject, for example, Sales Analysis, Human Resources, or Stock Control. The OBIEE end users use these Catalog Folders as the starting point for building a query.
Catalog folders only contain references to an object and not the actual object definition. Thus, a Catalog Folder can contain a collection of unrelated Logical Tables and the same Logical Table can appear in multiple Catalog Folders. This enables you to set up multiple Catalog Folders with different levels of detail: Sales Analysis area containing one Logical Table, Sales Details area containing six Logical Tables, and a Sales Transaction area with 30 Logical Tables. When you delete a Logical Table, the reference to it from the Catalog Folder is also deleted.
You can create a Catalog Folder using the Create Catalog Folder Wizard or from the Graphical Navigator. You can also use the editor to edit a catalog folder.
To create a Catalog Folder using the Create Catalog Folder Wizard:
Expand an OBI module.
Right-click Catalog Folders and select New Catalog Folder.
Warehouse Builder opens the Create Catalog Folder Wizard.
Follow the wizard steps by clicking Next.
In the Name and Description page, enter a name and optional description for the Catalog Folder.
In the Source page, all the Logical Tables and Dimension Drill Paths available within the OBI module are displayed. You can multi-select the objects by pressing the Ctrl key and using the right arrow to move them to the list of Selected objects.
In the summary page, review the Logical Tables or the Dimension Drill Paths that you selected. Click Back if you want to make any changes or click Finish to finish creating the Catalog Folder.
After the Catalog Folder is created, you can locate it on the Projects Navigator under the Catalog Folders node with references to the selected Logical Tables stored in it.
To make changes to your Catalog Folder definitions after you create them, use the Edit Catalog Folder dialog box. For details, see "Editing a Catalog Folder".
Alternatively, you can use the main menu or the canvas to create a catalog folder.
To create a catalog folder using the menu:
Click anywhere on the Graphical Navigator. The main menu on the toolbar changes dynamically.
From the main menu, select Graph, Add, Oracle Business Intelligence, Catalog Folder.
The Add a New or Existing Catalog Folder dialog box is displayed.
Select Create a new Catalog Folder and specify the name of the catalog folder and the module to which it belongs. Click OK.
The newly created catalog folder is available on the Project Navigator. Double-click the catalog folder to open the editors for the catalog folder. See "Editing a Catalog Folder" for details of the editors.
To create a catalog folder
From the Graphical Navigator, right-click a blank area on the editor canvas and select Add, Oracle Business Intelligence, Catalog Folder.
The Add a New or Existing Catalog Folder dialog box is displayed.
Select Create a new Catalog Folder and specify the name of the catalog folder and the module to which it belongs. Click OK.
The newly created catalog folder is now available on the Project Navigator. Double-click the catalog folder to open the editors for the catalog folder. See "Editing a Catalog Folder" for details of the editors.
Warehouse Builder enables you to edit the definitions for a Catalog Folder using the Edit Catalog Folder dialog box.
To edit a Catalog Folder:
From the Projects Navigator, expand the Catalog Folder node.
Right-click a Catalog Folder name and select Open.
Warehouse Builder opens the Edit Catalog Folder dialog box, which contains the following editors: Name Editor, Source Editor, and Contents Editor.
Source Editor displays the source of those Logical Tables and Dimension Drill Paths that have been included in the Catalog Folder. You can add new Logical Tables and Dimension Drill Paths to the Catalog Folder or remove existing ones using this editor.
Contents Editor displays the contents of the Catalog Folder. You can add Logical Tables and Dimension Drill Paths using the Content editor. For example, to add a Logical Table, right-click anywhere on the editor and select Add, Logical Table. The Add a New or Existing Logical Table dialog box is displayed. Follow the steps listed in "Steps to Create a Logical Table". You can also add a Logical Table or a Dimension Drill Path by dragging and dropping it from the Projects Navigator into the Contents editor. This creates a shortcut to the object. You can double-click the object to access it directly from the Contents editor.
During the design phase, you create definitions for the OBIEE objects. After you design objects, you can assign physical properties to these design objects by setting configuration parameters.
To configure a BI object, right-click the object in the Projects Navigator and select Configure. The Configuration Properties dialog box is displayed. Click the object name on the left side of this dialog box to display the configuration parameters on the right.
All BI objects have a configuration parameter called Deployable. Select Deployable if you want to generate scripts and deploy the business object. Warehouse Builder only generates scripts for objects marked deployable.
The following sections describe additional configuration parameters for different types of BI objects.
You can set the following configuration parameters for an Oracle Business Intelligence module:
Location: Represents the location to which the module is deployed.
MLS Deployment Language: Represents the language used for deployment to OBIEE.
Once you deploy objects within Oracle Business Intelligence modules, OBIEE can then use this data to generate reports. OBIEE repositories are represented in the repository data (RPD) format. Warehouse Builder cannot directly store files in the RPD format. Instead, the file is stored in the UDML format, which is later converted to the RPD format. These are the steps involved in moving data from Warehouse Builder to OBIEE:
Create an OBI module and derive Warehouse Builder objects into this module.
Define the location of the OBI module such that it points to a UDML file on the OBIEE server.
When the object is deployed, the UDML file is transferred to the OBIEE server using ftp or any other method of transfer, as specified while defining the location.
At the OBIEE server side, convert the UDML file to an RPD file. To do this, navigate to the following path: OBIEE_HOME\server\Bin and execute the command:
nQUDMLExec -I x.udml -O x.rpd
where x.udml is the file name specified while defining the location of the OBI module, and x.rpd is the target RPD file.
The RPD file can now be accessed by the OBIEE server.
After you transfer an RPD file to the OBIEE server, there might be changes made to the OBIEE module within Warehouse Builder. Similarly changes could also be made to the RPD file at the OBIEE server side. In such scenarios, you can merge the changes made to the files and create a single updated RPD file. OBIEE provides a merge mechanism to merge an existing RPD file with a newly modified file. Let us look at the following scenarios and the possible solutions:
Example Scenario
You create an OBIEE module, for example sales, within Warehouse Builder and associate it with a location that points to the UDML file sales.udml. You then derive the warehouse definitions from a cube into this module and deploy it. On the OBIEE server, you create an RPD file from the UDML file using the following command:
nQUDMLExec –I sales.udml –O sales_original.rpd
The sales_original.rpd can now be used by Answers/Dashboard to generate the required reports.
Suppose you now rename the cube in Warehouse Builder. To update this change at the OBIEE server side, you must rederive the cube and redeploy the OBIEE module sales. Ensure that you select the create action for all objects. After you deploy this object, create a new repository file in OBIEE server:
nQUDMLExec –I sales.udml –O sales_modified.rpd
Note that if the original file sales_original.rpd has not yet been used to generate reports, then you can overwrite this file with the newly created file sales_modified.rpd. If however, the file has been used to generate reports, then the repositories have to be merged. OBIEE provides a three-way repository merge of the following repository files:
Original repository (sales_original.rpd in this case)
The current repository (the original RPD file with modifications, if any, made at the OBIEE server side)
The modified repository (sales_modified.rpd in this case)
You can use the OBIEE admin tool to merge the three files into a single updated file. If no changes have been made to the original file, then the current repository is the same as the original repository. In such a scenario, create a copy of the original file, sales_original.rpd, and name it as sales_current.rpd. Now from the admin tool, perform the following steps:
Open the current repository file sales_current.rpd.
Click File/Merge.
The Merge repositories dialog box is displayed.
Select the original repository file sales_original.rpd. Also select a file to save the merged repositories.
Click Merge to merge the current and original files.
Now select the modified repository file sales_modified.rpd. Also select a file to save the merged repositories.
Click Merge to merge the modified file.
This creates a new RPD file which contains the merges from all the three files.
Warehouse Builder enables you to directly derive BI objects from your data warehouse design definitions. You can derive these objects into an Oracle Discoverer module or an OBI module. When you run the Perform Derivation Wizard on a warehouse module, it generates objects for business intelligence tools such as item folders from tables and drill paths from dimension hierarchies enabling you to quickly build reports from an integrated metadata platform. For example, the Perform Derivation Wizard organizes the metadata into Item Folders and Drill Paths ready to be integrated with a Discoverer EUL.
A Discoverer module can hold only those objects that are derived from an Oracle data source. However, OBI modules can also hold objects derived from non-Oracle data sources as well.
To derive BI objects:
From the Projects Navigator, select a module to derive. This indicates that you are deriving all the objects contained in that module. Alternatively, you can also choose to derive one object definition at a time. For example, you can select an individual table or dimension to derive.
Right-click the name of the warehouse module or object and select Derive.
Warehouse Builder opens the Perform Derivation Wizard.
Follow the wizard steps using the guidelines below.
You can also start the Perform Derivation Wizard from the Graphical Navigator using the following steps:
Drop the source object into the navigator.
Right-click the source object and select Derive.
Follow the wizard steps using the guidelines below.
The Source Objects page enables you to select additional objects for derivation. The Available column displays all the derivable objects available in your project for deployment. These objects can belong to different warehouse modules. You can also select a collection for derivation. The Oracle module or object you selected before starting the wizard displays in the Selected Objects column.
Expand the nodes in the Available column and use the right arrow to select the objects you want to derive. Select the Automatically add the Dimensions option to derive the dimension objects that are associated with the selected cube objects.
In the Target page, indicate the Oracle Discoverer module or the OBI module in which you want to store the definitions for the derived objects. For example, if you created a Oracle Discoverer module called DISCOVERER_OBJECTS, then the name of that module will display on this page. Select DISCOVERER_OBJECTS and click Next.
For Discoverer modules, you can also select a Business Area as the target. In this case, shortcuts are created to the item folders in the business areas. It is recommended that you deploy to a business area. Otherwise, when you deploy objects, the objects will not belong to any Business Area and thus will not be shown to end-users of BI tools. Similarly, for OBI modules, it is recommended that you deploy to a Catalog Folder.
When you select a collection for derivation, if the target is a business area, the individual objects contained in the collection are derived. Shortcuts are created to these item folders from the business area. If the target is a Oracle Discoverer module, Warehouse Builder creates a business area with the same name as the collection, stores the objects in the collection as item folders in the Oracle Discoverer module, and creates shortcuts to these item folders from the business area. Note that this is applicable to Catalog Folders as well, when the target is an OBI module.
In the Rules page, specify the derivation rules and parameters. Warehouse Builder loads, configures, and executes these rules to derive the BI definitions from the selected design object definitions. You can set parameters for different rule types by selecting the type of objects from the Rules list. For example, you can set global rules, rules for relational objects, rules for dimension objects, or rules for cube objects. The rules and parameters that you can set are displayed on the page.
Select Show advanced parameters to display certain advanced rules for an object. You can also set parameters for multiple rule types.
You can specify the following parameters:
Preserve user changes: Select to preserve any manual changes to the display properties name and description.
Log level: Specify the level of detail you want to see in the message log by selecting one of the options from the list. You can choose to record only errors, warnings, information, or trace debug information.
Validate before derive: Select the box if you want to validate the selected objects before deriving them.
Abort on error: Select the box if you want to stop the derivation if it encounters an error.
Capitalize: To capitalize the names of the derived objects, select from the list based on your requirements.
Replace underscores with spaces: Select the box if you want to replace the underscores in the names with spaces after derivation.
You can specify the following rule for Relational objects:
Bound Table Suffix: Specify a suffix for the bound tables you want to derive.
Default Aggregate (Oracle Discoverer only): Specify the default aggregate function to be applied to numeric measures.
Remove Column name prefixes: Select the option if you want to remove the text immediately before an underscore in the column name. The prefix is removed provided the same prefix is used for all columns.
Sort items by name: Select this option to sort the items alphabetically.
You can specify the following rules for Dimensions:
Always build Item Folders/Logical Tables for the dimension: Select this option to force the Perform Derivation Wizard to create Item Folders for the derived dimension definitions.
Build Item Folders/Logical Tables for the levels: Select this option to force the Perform Derivation Wizard to create Item Folders for the derived dimension levels.
Drill Paths on Item Folders/Logical Tables for the levels: Select this option if you want the Perform Derivation Wizard to create Drill Paths on Item Folders being created for each dimension level. This option applies only if item folders are created for each level.
Prefix Items with Level Name: Select this option if you want to prefix the item names with the dimension level names.
Prefix separator: If you choose to prefix the item names with the dimension level names, then indicate a prefix separator. The default is an underscore.
Sort Items by name: Select this option to sort the items alphabetically.
Derive Dimension Roles: Select this option if you want the Perform Derivation Wizard to derive additional item folders for each role.
You can specify the following rules for Cubes:
Sort items by name: Select this option to sort the items alphabetically.
The Pre Derivation page displays the objects to be derived and the target or Oracle Discoverer module for storing the derived definitions.
Review this information and click Next to perform the derivation.
The Derivation page displays a progress bar indicating the status of the derivation. When the progress bar displays 100%, the Message Log field displays any errors or warnings. At the end, the log indicates if the derivation was completed successfully.
Click Next to view the list of derived objects.
The Finish page displays the list of derived objects. Click Finish to accept the derivation. If you are not satisfied and you want to perform the derivation again, click Back to repeat the process.
Warehouse Builder displays the derived definitions in the appropriate BI module (Oracle Discoverer or Oracle Business Intelligence). You can edit the definitions of the derived objects or create additional definitions for deployment to Discoverer or OBIEE.
|
 Copyright © 2000, 2009, Oracle and/or its affiliates. All rights reserved. Legal Notices |
|
