Automatic migration of a Discoverer EUL to an OBIEE Repository
The latest release of OBIEE, version 10.1.3.4 comes with a brand new demo repository and dashboards, that Mark already talked about, in this post. This is not the only new thing to come with this latest release. For the first time now, Oracle makes available a tool that allows the BI administrator to automatically generate an OBIEE repository from an exported Discoverer End-User Layer (EUL) file (.EEX) ! For some time now, OBIEE customers have been able to get access to this tool via Metalink, but this release is the first one to include this handy tool in to the wild.
The tool is a part of the Administration toolset, so it’s only available on the Microsoft Windows platform. The program file is called MigrateEUL.exe and can be found in the OracleBI/server/Bin/ directory. The migration tool must be run on a machine with an existing OBIEE Administrator installation. This is a command line utility, so get ready to do some work in the Windows shell …
In this first release the migration utility only supports the generation of a repository file (.RPD) based on the business areas (BAs) that are contained in the exported .EEX file. This means that no Discoverer Workbooks can be migrated yet. This is planned for a later release. But what what we do get is a complete physical, business and presentation layer. This also includes join definitions, hierarchies for dimensions and calculations. When workbook migration will be made available we should expect to get a presentation services web catalog as well. Also due to this limitation, some things in the Discoverer EUL can not be migrated, such as optional conditions that would normally be represented as saved filters in OBIEE web catalog.
First, to be able to do this migration you must make sure you are already running on the latest release of Oracle Discoverer, 10.1.2.X (if your production environment is not running in this version, I’d suggest you get the latest release of the Discoverer Desktop tools, create a dummy EUL in your test environment and use that to quickly migrate your EUL, so that you can still go on with testing this migration utility). Using the Discoverer Administrator tool, we must first export the desired BAs to a file, as I have already mentioned. Once that is done, you simply run the command line utility, passing the name of the .EEX file as an argument:
- MigrateEUL.exe vidstr.eex
The generated results will be placed in the same folder as the input .EEX file
- vidstr.rpd - This is the resulting OBIEE repository
- vidstr.migration.log - Progress of the migration
- vidstr.exception.log - Any exceptions such as folders and joins that could not be migrated
The Logs
The migration.log will look something like this:
Oracle BI SE - EE Migration Assistant Version 10.1.3.4.0 Repository creation started... **************************************************************** Business Area : Video Store Tutorial **************************************************************** Custom Folder : Alternate Sort Store Size Custom Folder : Alternate Sort Days -- Processing EUL Joins.... -- Processing Folder(s) based on EUL Joins.... ## List of Folder(s) with SKIPPED JOINS / PATHS FOLDER_ID SELECTED = ### FOLDER_NAME JOIN_NAME -------------------------------------------- ### FOLDER CONTAINER MAPPING ### Mapping For Folder : Alternate Sort Days : Alternate Sort Days Mapping For Folder : Alternate Sort Store Size : Alternate Sort Store Size Mapping For Folder : Calendar Date : Calendar Date Mapping For Folder : Fiscal Date : Fiscal Date Mapping For Folder : Products : Products Mapping For Folder : Sales Facts : Sales Facts Mapping For Folder : Store : Store Mapping For Folder : Target Sales : Target Sales -- Processing Complex Folder(s).... ** Complex Folder : Monthly Sales Analysis Complex Folder is not based on any folder in the current Business Area : Video Store Tutorial Complex Folder cannot be migrated!!! -- Processing Dimension(s).... ** Dimension Video Analysis Calendar Time Hierarchy based on a Complex Folder is not supported!!! ** Dimension Video Analysis Store Hierarchy based on a Complex Folder is not supported!!! ************* CREATING PHYSICAL LAYER ************* -- Creating Database...[DONE] -- Creating Connection Pool...[DONE] -- Creating Physical Table(s)... -- Physical Table(s) creation...[DONE] -- Creating Physical Join(s)... -- Physical Join(s) creation...[DONE] ************* PHYSICAL LAYER CREATION DONE ************* ************* CREATING LOGICAL LAYER ************* -- Creating Subject Area...[DONE] -- Creating Logical Table(s)... -- Creating Logical Join(s)... -- Logical Join(s) creation...[DONE] -- Creating Calculation(s)... - Creating Complex Folder Calculation(s)... -- Calculation(s) creation...[DONE] -- Logical Table(s) creation...[DONE] -- Creating Dimension(s)... Dimension : Calendar Date Hierarchy Dimension : Fiscal Calendar Hierarchy Dimension : Product Hierarchy Dimension : Store Hierarchy -- Dimension(s) creation...[DONE] ************* LOGICAL LAYER CREATION DONE ************* ************* CREATING PRESENTATION LAYER ************* -- Creating Presentation Folder(s)... Folder : Video Analysis Information *** Admin Optional Condition Year is 1998 or 1999 will not be migrated to a repository!!! *** Admin Optional Condition Department is Video Rental or Video Sale will not be migrated to a repository!!! Folder : Stores and Sales Details Folder : Products Folder : Store Folder : Calendar Date Folder : Fiscal Date Folder : Sales Facts Folder : Target Sales -- Presentation Folder(s) creation...[DONE] ************* PRESENTATION LAYER CREATION DONE ************* ------------------- All Business Area(s) processed ----------------- -- Creating Security Filter(s)... -- Security Filter(s) creation...[DONE] -- Creating User(s)... EUL PUBLIC VIDEO5 -- User(s) creation...[DONE] The migrated repository is saved at C:\TEMP\vidstrEUL.rpd ------------------------------------------ EUL MIGRATION SUCCESSFUL ------------------------------------------
Anything that doesn’t get migrated or fails during the execution of the tool will get looged in to the the migration log as well as the execption log. The exception.log will only list, as the name suggests, the exceptions that occurred during the pass through.
Oracle BI SE - EE Migration Assistant Version 10.1.3.4.0 **************************************************************** Business Area : Video Store Tutorial **************************************************************** ## List of Folder(s) with SKIPPED JOINS / PATHS FOLDER_ID SELECTED = ### FOLDER_NAME JOIN_NAME —————————————————————— ** Complex Folder : Monthly Sales Analysis Complex Folder is not based on any folder in the current Business Area : Video Store Tutorial Complex Folder cannot be migrated!!! ** Dimension Video Analysis Calendar Time Hierarchy based on a Complex Folder is not supported!!! ** Dimension Video Analysis Store Hierarchy based on a Complex Folder is not supported!!! *** Admin Optional Condition Year is 1998 or 1999 will not be migrated to a repository!!! *** Admin Optional Condition Department is Video Rental or Video Sale will not be migrated to a repository!!!
The interesting things we see here are:
- Complex folders that are based on other complex folders are not supported (yet?)
- Hierarchies based on Complex Folders are not supported
- Optional Conditions are not supported (as mentioned above) as they’d need to be placed in the web catalog
Configuration Parameters
The migration tool reads configuration parameters from a file called MigrationConfig.properties that is stored in the same directory as the executable. SInce the only command line argument the tool expects is the name of the Discoverer .EEX, all other configurations need to be stated in this properties file.
| Parameter | Description |
|---|---|
| CreateAggregatedCols | True = Aggregated Columns with functions 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 set in the EUL |
| CreateSeperateRPDs | True = Each Business Area in the EUL export file will be migrated to a seperate repository False = All the Business Areas in the EUL export file will be migrated to a single repository |
| ExcludeJoins | Valid values are comma seperated JOIN_IDs available for every join created in Discoverer. These Joins will not be migrated |
| ConsiderMultiplePaths | True = The Joins skipped for eliminating the Circular and Multiple Join paths will be considered for migration False = The Joins skipped for eliminating the Circular and Multiple Join paths will not be migrated |
| IncludePathsForFolders | Valid values are comma seperated TABLE_ID values generated in the log file listing the folders having joins that are skipped for eliminating the Circular and Multiple Join paths. IF ConsiderMultiplePaths property is TRUE and there are no values specified fIncludePathsForFolders then all the Skipped Joins will be migrated |
| DataSourceName | This property value is the name of the System Datasource Name used for connecting to the Database |
| Username | This property value is the Username for connecting to the Database |
Looking at the MigrationConfig.properties file we can see that the following is the default setting:
CreateAggregatedCols=FALSE CreateSeperateRPDs=FALSE ExcludeJoins= ConsiderMultiplePaths=TRUE IncludePathsForFolders= DataSourceName= Username=
Sticking with the Discoverer Video Store Tutorial, here is what the business area looks like in the Administration tool:
After we run the migration tool, we get a new .RPD called vidstr.rpd. Opening this repository in the OBIEE Administration tool, we must first set the properties of the connection pool, so that the BI Server can connect to the database, namely the TNS connection name, username and password.
Validate and save the repository. Notice the warning that the Administrator user has been created using a blank password.
Now would be a good time to shut down the BI Server as well as the BI Presentation Server.
Copy the vidstr.rpd file to the OracleBI/server/Repository/ directory. Change the OracleBI/Server/Config/NQSConfig.ini configuration file to point to the new repository.
#Star = samplesales.rpd, DEFAULT; Star = vidstr.rpd, DEFAULT;
Before we go on, we might also want to create a new web catalog to test the migration in Answers. Edit the OracleBIData/web/conf/instanceconfig.xml file to point to a new catalog. Simply giving it a new name will create a new blank catalog once the BI Presentation Service has been started.
<CatalogPath>E:/OracleBIData/web/catalog/vidstr</CatalogPath>
Restart the BI Server and the BI Presentation Server services and log in to the Oracle BI web application and we will see that the “Business Area: Video Store Tutorial” Subject Area is now available for querying.
To conclude this, we can see that it is quite straight forward to migrate a Discoverer EUL to OBIEE. However, there are still some fundamental differences between the architecture of the two products that make it difficult to automatically migrate everything. But what we do get is a very good head start. The Video Store Sample I used here is a very simple business area, where as your EUL might contain quite a lot more complicated logic. Things can get hairy very fast when your Discoverer join configurations are slightly more complicated than a simple star. But don’t hesitate to give it a go, even if just to demonstrate what your current Discoverer reporting environment might look like, in the spiffy new OBIEE …