Migrating Oracle Business Intelligence Discoverer Metadata to Oracle Business Intelligence Suite Enterprise Edition

In this tutorial, you learn how to migrate Oracle Business Intelligence Discoverer (Discoverer) metadata to Oracle Business Intelligence Suite Enterprise Edition (Oracle BI EE), using the Oracle Discoverer Metadata Conversion Assistant.

This tutorial covers the following topics:

Place the cursor over this icon to load and view all the screenshots for this tutorial. (Caution: This action loads all screenshots simultaneously, so response time may be slow depending on your Internet connection.)

Note: Alternatively, you can place the cursor over an individual icon in the following steps to load and view only the screenshot associated with that step. You can hide an individual screenshot by clicking it.

Oracle BI EE is an innovative and comprehensive business intelligence (BI) platform that delivers the full range of BI capabilities on a next-generation architecture designed for true enterprise deployment. It enables organizations to define a single, logical view of all enterprise data, whether in a single data warehouse or across multiple operational and analytic sources. Business users benefit from new levels of self-sufficiency to access, interact with, and utilize this information to increase effectiveness. Oracle BI EE provides three major strengths:

Oracle Business Intelligence Answers (Answers) is the Oracle Business Intelligence interface within Oracle BI EE that you use to query your organization’s data. The results of your request can be saved, organized, shared, and integrated with other content. Answers allows you to explore and interact with information, and present and visualize your data using charts, pivot tables, and reports. You can save, organize, and share the results.

Requests that you create with Answers can be saved in the Oracle Business Intelligence Presentation Catalog (Presentation Catalog) and integrated into any Oracle Business Intelligence home page or Interactive Dashboard. Further, your results can be enhanced through charting, result layout, calculation, and drill-down features.

Oracle Business Intelligence Presentation Services (Presentation Services) generates the user interface (UI) in the Answers and Interactive Dashboards components used to visualize data from the Oracle Business Intelligence Server (BI Server). When a user begins a session, Oracle Business Intelligence Presentation Server (Presentation Server) submits the user's identity to the BI Server, authenticates the user, and then requests the BI Server to provide the databases, tables, columns, and so forth that the user is entitled to see. These objects are displayed in the UI as subject areas, folders, and columns. The BI Server also provides metadata information to the Presentation Server about column properties, such as data types, aggregation rules, and so forth.

You use the Oracle Business Intelligence Administration Tool (Oracle BI Administration Tool) to build, manage, and maintain repositories. This tool is a Windows application that separates the Oracle Business Intelligence Repository (BI Repository) into three distinct panes or layers. These three layers are named Physical, Business Model and Mapping, and Presentation. This repository stores the BI metadata within a file (.rpd) that is rendered inside the Answers and Interactive Dashboards clients, as well as other clients.

Specifically, the Physical layer contains information about the physical data sources to which BI Server submits queries. The most common way to populate the Physical layer is by importing metadata from databases and other data sources. When you import metadata, many of the properties of the data sources are configured automatically based on the information gathered during the import process. These physical objects are displayed in a tree structure and contain mappings to the objects that hold the data—that is , a database, a Microsoft Excel spreadsheet, an XML file, and so forth. Additionally, this layer also contains information about how the mappings relate to each other in the form of primary and foreign keys.

The Business Model and Mapping layer is a logical layer that contains mappings of how objects in the physical layer relate to one another. Important differences between Discoverer and Oracle BI EE are highlighted in this layer—specifically, the creation of dimensions and facts. Measures are identified by their placement and default aggregation settings in the Discoverer metadata. Any attribute having placement value as data point and default aggregation value that is neither none nor detail is defined as a measure. Any nonmeasure is considered a dimension.

The Presentation layer contains the view of the metadata that the end user sees. It is possible to view a diagrammatic representation of both the physical model (that is, the database model) and logical business model layers. The benefit of this is a clear and quick understanding of how the metadata objects relate to each other.

This tutorial provides the steps to:

Back to Topic List

In this OBE, you learn how to export an End User Layer (EUL) to create an .eex file by using Discoverer Administrator, access and run the Discoverer Metadata Conversion Assistant, and verify your metadata results by using the Oracle BI Administration Tool.

Back to Topic List

The following is a list of software requirements:

 

Back to Topic List

Before starting this tutorial, you should ensure that:

1.

The following components are installed and configured properly:

Discoverer Administrator (10.1.2 or later) and the sample data, Video Store Tutorial, that was shipped with Discoverer

Note: For additional information about creating an EUL, refer to Developing Sales History Business Area by Using OracleBI Discoverer Administrator.

Oracle BI EE 10.1.3.4 or later

 

These components can be downloaded from here.

Note: After you have logged in to the E-Delivery site, select Oracle Business Intelligence as the Product Pack and Microsoft Windows (32-bit) for the platform, and then click Go.

2.

You have the proper permissions for configuring the BI Repository on your company's system

3. You have a solid level of familiarity with Discoverer and Discoverer Administrator concepts
4. MS Windows is installed (versions listed above in Software Requirements)
5.

Java SDK 1.5.0 or later is installed

6.

Your screen resolution is set to at least 1024x768

7.

You run the Oracle Discoverer Metadata Conversion Assistant on the same machine where Oracle BI EE is installed

Note: The Oracle Discoverer Metadata Conversion Assistant is provided as a command-line executable. The executable file is named MigrateEUL.exe and is located in <installdrive>\OracleBI\server\bin.

 

Miscellaneous Notes:

Screenshots for this tutorial were taken in a Windows 2000 Professional environment; therefore, Start Menu options will vary.

This tutorial uses Discoverer Administrator 10.1.2 for the creation of the .eex file and Oracle BI Administration Tool 10.1.3.4 as the repository tool.

 

Back to Topic List

To access the most current documentation for the Oracle Discoverer Metadata Conversion Assistant, click this link.

To access documentation for Discoverer Administration, click this link.

To access all Discoverer documentation, click this link.

Back to Topic List

This topic describes how to export Discoverer Metadata and use the Oracle Discoverer Metadata Conversion Assistant to create a repository file for Oracle BI EE.

Exporting Discoverer Metadata
Running the Oracle Discoverer Metadata Conversion Assistant

Exporting Discoverer Metadata

This subtopic shows you how to export Discoverer metadata by using Discoverer Administrator.

1.

Start the Discoverer Administrator application. When Discoverer Administrator appears, enter your credentials and click Connect.

In the Load Wizard dialog box, click Cancel.

2.

Select File > Export from the Discoverer Administrator menu bar. The Export Wizard: Step 1 dialog box appears.

Select The entire End User Layer and click Next. A brief message appears, informing you that a list of all available EUL objects is being prepared.

 

3.

In the Export Wizard: Step 2 dialog box, click Browse to identify the folder that contains your exported file.

Create a new top-level folder named DiscoMigration on the installation drive, name the export file video.eex, and click Save.

 

4.

Click Finish.

Note: If you receive a message that video.eex already exists, click Yes to overwrite the file.

The EUL export is processed. When the Export Log window appears, scroll to review the results and to ensure that no errors were encountered.

Click Close. Alternatively, you can save the log to review it at a later time.

 

5.

Select File > Exit to close Discoverer Administrator.

 

Back to Topic

Running the Oracle Discoverer Metadata Conversion Assistant

The Oracle Discoverer Metadata Conversion Assistant is a command-line utility that can accelerate the creation of Oracle BI EE metadata by using Discoverer metadata as an initial starting point. There are several types of systems that Discoverer can report against:

This subtopic shows you how to run the Oracle Discoverer Metadata Conversion Assistant.

1.

As mentioned in the prerequisite topic, you must run the migration assistant on the same machine as the Oracle BI EE installation.

Navigate to the <installdrive>\OracleBI\server\Bin directory. There are two important files in this directory: the migration assistant executable file named MigrateEUL.exe and a properties configuration file named MigrationConfig.properties.

 

2.

The MigrationConfig.properties file allows you to control the migration behavior. The following table provides the available configuration options:

CreateAggregatedCols
CreateSeperateRPDs
ExcludeJoins
ConsiderMultiplePaths
IncludePathsForFolders

Sample of the MigrationConfig.properties file:

This tutorial accepts defaults.

 

3.

A. The MigrateEul.exe file is the migration assistant. Click Start > Run. When the Run dialog box appears, enter cmd and click OK to open a command-prompt window.

B. At the <installdrive> prompt, enter cd DiscoMigration and press Enter. This positions you within the proper subdirectory on the installation drive.

C. Enter MigrateEul.exe video.eex and press Enter.

 

4.

Your metadata is migrated successfully and a repository file is created in the same directory that contained the .eex file.

The repository file assumes the same name as the .eex file and has an .rpd extension. For this tutorial, the name of the .rpd file is video.rpd.

Note: The migration assistant generates two log files during conversion, examples of which follow the table below. These files are also placed within the same directory. The <filename>.migration log file captures the migration progress at a high level and the <filename>.exception log file captures the names of the items that could not be migrated.

Using the Discoverer Metadata Conversion Assistant is an iterative process, with the exceptions file providing a listing of the areas that need attention. Exceptions can be generated depending on the types of join configuration in the Discoverer metadata (for example, multiple join paths or circular join paths). The joins that are identified will be listed in this file to enable you to decide how to handle these scenarios.

For each Discoverer business area that is converted, these files contain the following details:

The table below highlights the details of the migration assistant output and describes the corresponding items between Discoverer and Oracle BI EE:

(Note: For summarized property migration information, refer to the Oracle Discoverer Metadata Conversion Assistant documentation, topic 3.4, Description of output from the conversion assistant, at this link.)

Examples of both log files for the video migration appear below.

Sample of the Video.migration file:

Sample of the Video.exception file:

This exception file indicates that there is a complex folder based on items from another complex folder— it is not possible to automatically create a complex folder of this nature in this version of the Discoverer Metadata Conversion Assistant. In addition, it is not possible to migrate hierarchies based on Discoverer complex folders because in Oracle BI EE, the hierarchies are based on dimension tables as defined in the Business Model and Mapping layer.

The final exceptions listed in this image concern optional conditions defined in Discoverer Administrator. Oracle plans to migrate these optional conditions to filters in a future version of the Discoverer Metadata Conversion Assistant after the workbook conversion is supported.

The next topic focuses on the newly created repository file and how to view it within Oracle BI EE.

 

Back to Topic List

This topic describes how to review the migrated metadata, open the Oracle BI Administration Tool, and use it to ready your metadata for use in queries.

Evaluating the Migrated Metadata
Verifying the Video Repository

Evaluating the Migrated Metadata

This subtopic shows you how to open the newly created repository file.

1.

Now that you have successfully created a migration file, you need to review the content. Navigate to the <installdrive>\DiscoMigration subdirectory that houses the video.rpd file created above in step 4, and double-click the video.rpd file.

Accept the default User, Administrator, and click OK to open the repository in offline mode.

2.

All three layers of metadata required by Oracle BI EE were created during migration.

A. Expand Video Store Tutorial in the Physical layer.

B. Expand VIDEO5.

These Physical layer objects were derived from the Discoverer metadata properties: owner and object.

Additionally, Join properties from Discoverer were also used during the conversion.

 

3.

Next, review the Business Model and Mapping layer objects. This layer is a logical layer.

Expand Video Store Tutorial in the Business Model and Mapping layer.

This layer has been derived in a similar manner. However, the dimensions were derived from Discoverer hierarchies.

 

4.

Review the Presentation layer objects. Expand Business Area: Video Store Tutorial in the Presentation layer.

This layer not only contains objects that were defined in Discoverer as visible to the end user, but also contains generated complex folders.

The following image illustrates what the Product folder from Discoverer looks like across the repository layers in Oracle BI EE.

The migration assistant converts Discoverer metadata as follows:

 

5.

Next, you need to enter the parameters for the connection pool so that you can test your access to the data. Right-click ConnectionPool in the Physical layer pane and select Properties.

The Connection Pool Properties dialog box appears.

 

6.

Select OCI 10g/11g from the Call interface drop-down list, enter your Data source name (for this tutorial on this machine, the data source name is ORCL), and enter your User name and Password to access the data source.

Your dialog box should look like this:

Click OK and confirm your password when prompted. You can now test the connection by viewing the data.

 

7.

Right-click the Product folder and select View Data.

The View Data dialog box for the Product table appears, illustrating that your connection works correctly.

Click Close.

 

8.

Before you can use this repository for queries and so forth, you need to run a consistency check. The Oracle BI Administration Tool enables you to ensure that a repository meets specific requirements, such as all logical columns are mapped directly or indirectly to one or more physical columns, all logical dimension tables have a logical key, all logical tables have a logical join relationship to another logical table, and a Presentation catalog exists for the business model, and so forth.

If the repository or an object within the repository is not consistent, a detailed error message appears. The Consistency Check Manager delivers three types of messages:

Select File > Check Global Consistency. Consistency Check Manager appears with all error, warning, and best practice information messages.

Because a warning message is not a critical error, click Close to exit Consistency Check Manager and select File > Save to save the repository. (Note: If you receive a message that requires you to check in your changes before saving the repository, click OK.)

 

9.

To complete the final stage in preparing this repository for use with queries, you must copy the repository file to an appropriate location for repositories and edit the NQSConfig.ini file.

A. Navigate to <installdrive>\DiscoMigration. Select the video.rpd file and select Edit > Copy.

B. Navigate to <installdrive>\OracleBI\server\Repository. Select Edit > Paste. The video.rpd file is copied to the repository subdirectory.

C. The NQSConfig.ini file is an initialization file. By updating the Repository section of this file, you are instructing the BI Server to load this specific repository into memory upon startup.

Navigate to <installdrive>\OracleBI\server\Config. Double-click the NQSConfig.ini file (you can use a text editor, such as Notepad to edit this file) and scroll down to locate the [REPOSITORY] section.

D. In the [REPOSITORY] section, enter Star = video.rpd, DEFAULT;. Enter a # sign in front of the active repository file (in this example, the active repository is paint.rpd), and select File > Save to register the repository. The NQSConfig.ini file should look like this:

Close the NQSConfig.ini file.

 

10.

After the changes have been made to the NQSConfig.ini file, you must restart the Oracle Business Intelligence services that support the repository.

A. Click Start > All Programs > Administrative Tools > Services.

B. Scroll to locate and select Oracle BI Server. Click the Restart () icon to restart the services.

While the Services window is still open, ensure that Oracle BI Presentation Server and Oracle BI Java Host services are also started. (These services are required for Answers.)

Close the Services window.

 

Back to Topic

Verifying the Video Repository

This subtopic shows you how to verify your results using the Oracle BI Administration Tool and Answers.

1.

To ensure that your repository has been correctly configured, open the Oracle BI Administration Tool online.

Click Start > All Programs > Oracle Business Intelligence > Administration. The Oracle BI Administration Tool appears.

Click the Open Online () icon to open the Video repository. The Open Online AnalyticsWeb dialog box appears.

 

2.

Click Open. The Video repository opens, ensuring that you can now use this repository to run queries.

 

3.

To further verify that you are able to use the repository, create and run a query against the Video repository by using Answers.

A. From the Start menu, select All Programs > Oracle Business Intelligence > Presentation Services. The Oracle Business Intelligence Log In window appears.

B. Enter Administrator in the User ID field and click Log In.

The My Dashboard page appears.

 

4.

Click the Answers link. The Answers Start page appears.

 

5.

The Answers Start page is composed of two panes: Selection and Workspace.

The Selection pane on the left contains the Catalog and Dashboard tabs from which you select items to manipulate.

The Presentation Catalog (Catalog) stores the content created with Answers and Interactive Dashboards. The content can be organized into folders that are either shared or personal. Types of content that can be stored in the Catalog include requests created with Answers, HTML content, and links to other images, documents, and sites.

A dashboard is made up of sections of information that can contain items, such as results from Answers requests, external Web content, HTML text, graphics, links to other sites, embedded objects such as requests, and so on. Dashboard content is organized into pages. Pages appear as tabs across the top of the screen in Interactive Dashboards.

The Workspace pane on the right contains a list of the actions you can perform and the subject areas that are available to you. Oracle Business Intelligence presents data in subject areas. A subject area contains columns that represent information about the areas of your organization’s business, or about groups of users within your organization. Subject areas have names that correspond to the types of information they containfor example, Marketing Contracts, Service Requests, and Orders. Columns also have names that indicate the types of information they contain, such as Account and Contact.

The subject areas that you see in the image are the same ones represented in the Oracle BI Administration Tool.

The "feature links" listed at the top of the Workspace pane provide access to other Oracle Business Intelligence functions.

 

6.

Click the Business Area: Video Store Tutorial subject area.

The Answers workspace page appears.

The Answers workspace displays the following tabs for working with a request:

Criteria: Provides access to the columns selected for the request, and buttons to access the most common view types

Results: Allows you to work with the results of the request

Prompts: Allows you to create prompts to filter the request

Advanced: Allows advanced users to work with the XML and logical SQL for the request

Each tab contains on-screen information and buttons to help you create, access, and manage requests.

 

7.

These steps show you how to create a simple query that provides product sales information.

A. Expand the Products and Sales Facts folders to view their respective columns.

B. Click Product Category in the Products folder to add it to the Criteria workspace.

C. Click Product Description in the Products folder to add it to the Criteria workspace.

D. Click Sales Sum in the Sales Facts folder to add it to the Criteria workspace.

Your workspace should look like the following:

Click Display Results. Your query appears.

The corresponding query in Discoverer appears below:

 

Back to Topic List

In this tutorial, you should have learned how to:

 

Place the cursor over this icon to hide all screenshots.