by Edelweiss KammermannPublished February 2012
Plan your migration project, step by step.
In January 2009, Oracle published a statement of direction for the Oracle Discoverer product strongly encouraging users to migrate to Oracle Business Intelligence Enterprise Edition Plus (OBIEE)
For organizations that have a significant investment in Discoverer and are considering such a migration, reusing as much of that work in OBIEE as possible important. Doing so can minimize the impact on IT staff and end users, and also allow them to take full advantage of the functionality in OBIEE.
In this article, we will review the steps required to perform this migration while reusing much of the code implemented in Oracle Discoverer. As an overview, to accomplish the migration from Discoverer to OBIEE, we need to address the following objects:
As of OBIEE 10g (10.1.3.4), Oracle includes the Oracle Discoverer Metadata Conversion Assistant, a utility that lets you generate the BI repository file (.Rpd) from a Discoverer EUL export (.EEx) . The file is called MigrateEUL.exe and is found in the directory OracleBI/server/bin for OBIEE 10g, and in the Oracle_BI1/bifoundation/server/bin directory with OBIEE 11g.
The steps to perform this migration are:
To demonstrate, we will migrate a Discoverer Version 10.1.2.1EUL to 11.1.1.5 Oracle BI repository. The EUL contains a typical star schema consisting of fact table, SALES, and five dimensions that are called CHANNELS, PRODUCTS, PROMOTIONS, CUSTOMERS, and TIME.
Export the EUL
In Oracle Discoverer Administrator, go to the File -> Export. When you open the Export Wizard select the “The Entire End User Layer” option to export the entire EUL
Press the Next button, and the “Step 2” dialog appears. Select the location and file name (.EEX) that will contain the export.
Then click Finish to create the export file. A progress window will be displayed, and you can capture the log as a text file to review at a later time.
Run the Oracle Discoverer Metadata Conversion Assistant
The Oracle Discoverer Metadata Assistant utility is run from command line. In the same directory or folder in which it is found (as described previously), there is also a configuration properties file called MigrationConfig.properties. We can refine the implementation of migration by settings in the properties file. (Note: the utility must be run on the same machine on which the OBIEE is installed.)
Documentation is found in file DiscovererMetadataConversionAssistant.pdf, which is located in the Oracle_BI1/bifoundation/server/document directory. This 30-page document provides more detail on migration and transformation of the respective objects.
Some flexibility is available in the migration tool, which can be adjusted using the configuration options found in the MigrationConfig.properties file. Some of the options are:
Property | Settings |
---|---|
CreateAggregatedCols | TRUE - Columns with aggregations like SUM, MIN, MAX, AVG and COUNT will be created for measure columns. FALSE - Aggregated Columns will be created for measure columns based on the DEFAULT AGGREGATION property set in the EUL |
CreateSeparateRPDs | TRUE - Separate repository is generated for each business area. FALSE - All the business areas are migrated to a single repository. |
ExcludeJoins | A comma separated list of JOIN_ID to be skipped during migration. To be used in conjunction with ConsiderMultiplePaths = TRUE |
ConsiderMultiplePaths | TRUE - The migration assistant will accommodate the joins that would be otherwise skipped during migration. FALSE - The migration assistant will not accommodate the joins skipped during migration. |
IncludePathsForFolders | A comma separated list of folder_id [available in the Filename.exception.log] for which the skipped joins must be accommodated during migration |
Connection pool parameters | DataSourceName, Username |
The following image shows an example of the configuration file:
After modifying the configuration file, run the utility on the .eex file to export the EUL.
Executing the Migration Assistant generates the BI Repository 11g (export_eul.rpd). In addition, it creates two log files: the export_eul.migration.log, which has information about the process of migration to high level; and export_eul.exception.log, which indicates the items that were not migrated.
To open the repository file we use the tool OBI Administration Tool and open the repository offline. The following figure shows how the repository is a result of migration.
Assigning a Value to the Repository Password
As mentioned previously, as of OBIEE 11g version 11.1.5, the repository file is encrypted. A repository password is required in addition to the username and password with administrator privileges.
The new repository export_eul.rpd is assigned a null password that is generated by default. There is no option in the configuration file to change this prior to migration.
Since our repository has been created with a null password, to enable end user queries we need to assign a value. To do this task we must use the Oracle BI Administration Tool and open the repository in the offline mode. Once opened, go to File -> Change Password.
Enable the repository
OBIEE requires that active repositories be in a registered directory – by default, in instances\instance1\bifoundation\OracleBIServerComponent\coreapplication_obis1\repository. Therefore, to enable the repository we have just created, first copy/move it to that directory as shown in the following figure:
Once the newly created repository is copied to that directory, we can start Oracle Enterprise Manager 11g Fusion Middleware Control to enable the repository:
On the left navigation panel choose the instance of “Business Intelligence: core application”.
In the right panel select the Deployment tab and Repository option. To load the new repository, click the Lock and Edit Configurationbutton, which will allow us to modify the settings.
Then press the Browse button under the Upload BI Server Repository section, select the repository, and enter the password. Then press Apply and then Activate Changes. This will apply the changes.
After applying these changes we will see a message that tells us that we must restart the BI services for these changes to take effect.
We press the Restart button to restart services, which activates our changes. Then the new repository becomes visible and ready to be accessed by the users. (ote: To enable the repository in OBIEE 10g, the repository has to be saved in the OracleBI\server\repository folder. It is 'registered' as available by changing the NQSConfig.ini found in OracleBI\server\config, adding the entry Star = export_eul.rpd , DEFAULT in the [REPOSITORY] section. )
Now that the EUL has been migrated, here are the elements that are affected:
Joins: Each join remains as a foreign key in the physical layer and a logical join in the Business Model layer, with some exceptions:
- In the case of Circular Joins and Multiple Joins Path, the utility generates an alias for the table, as OBI does not allow them natively.
- A join between two tables is not migrated when they share between them more than one dimension in common.
- Duplicate joins (Folder A has a join to the folder B and vice versa) will be reduced to a single join, selected at random.
- Joins with calculations in the join condition are not migrated.
- Mandatory: If the condition is over a Simple or Custom folder, the conditionis applied to the logical table source of that folder. For complex folders, conditions will be applied to OBIEE group “Everyone” as a security filter.
- Optional: There are not defined in the OBI repository and should be migrated manually to the catalog of OBI.
- Hierarchies based on Simple folders migrated; however, the ones based on Complex folders are not migrated.
- Date Hierarchies templates have no direct equivalent in Oracle BI. However the hierarchies result of use them are migrated.
- User privileges: All EUL users are migrated to the repository as part of the group "Everyone" and the initial password is the username in uppercase.
And here are the objects that are not affected:
Summary folders are not migrated.
Complex folders based on other complex folders are not migrated.
The system cannot migrate complex folders that are dimensions. A corresponding message will appear in the log.
Hierarchies based on Complex Folders are not migrated.
Optional conditions would need to be registered in the OBI catalog and are not part of the repository.
And here are the objects that are not affected:
And here are the objects that are not affected:
Summary folders are not migrated.
Complex folders based on other complex folders are not migrated.
The system cannot migrate complex folders that are dimensions. A corresponding message will appear in the log.
Hierarchies based on Complex Folders are not migrated.
Optional conditions would need to be registered in the OBI catalog and are not part of the repository.
As of this writing, there is no tool available that would make workbook migration automatic. According to Oracle documentation, you need to re-create queries from scratch. That would force us to re-do the requests, which doubles the previous work.
The alternative that this document proposes is to reuse the SQL queries generated by Discoverer and apply them directly into Oracle BI, significantly reducing the workload for creating queries. For this we apply the following steps for each of the Workbooks or Worksheets in Oracle Discoverer:
Open the workbook in Oracle Discoverer, using either Discoverer Plus or Discoverer Desktop.
Once opened, go to the Tools (Tools) -> Show SQL.
Choosing this option opens a window that displays the SQL statement that is generated by the workbook. Use the copy button in the window to copy the entire statement.
Go to OBIEE and select "Create Analysis" and then "Create Direct Database Request".
In the next window, enter the name of the Connection Pool and paste the SQL statement copied from Discoverer. Press the button Validate SQL and retrieve columns to select columns. (Note: the name and details of the Connection Pool are in the repository at the physical layer.)
Switching to the Results tab displays the data for this query. In our case it becomes:
To get the results in the same format we had in Discoverer, simply drag the fields Channel Name and Calendar_Year_Name to the Table Prompts area.
It is important to remember that a user requires special privileges to perform a query directly against a database. The admin of the catalog would select the Manage Privileges option from the menu in OBIEE Administration.
Then, in the privileges section for Answers, give the follow permissions to the user:
Your migration is now complete! However, there are some important additional considerations.
For example, there is a fundamental difference between OBIEE and Discoverer in the preferred relationship model. In the Business Model layer of the OBIEE repository, the relationships between tables should represent a star schema - as a minimum requirement there is a dimension table and a fact table. In comparison, Oracle Discoverer doesn’t demand any special type of relationship between data. For that reason, if the Discoverer data model doesn’t represent star schemas, changes may be needed after migration in order to respect this structure. Otherwise the repository will not be available and enabled for queries.
Note also: although Oracle indicates that the metadata conversion utility only migrates to version 10g and that the Upgrade Assistant must be run to obtain a repository for version 11g, the repository version generated using the utility is actually in the correct format for 11g with version 11.1.1.3 and 11.1.1.5.
Edelweiss Kammermann is a BI and BPM specialist with over 15 years of consulting and project management experience in Oracle technology. Her work with both private and public organizations has spanned much of South America, especially Uruguay, Bolivia, Panamá and Honduras. Edelweiss is a very active member of the Oracle community; she is co-founder and vice president of the Oracle User Group of Uruguay (UYOUG).