Automatic migration of a Discoverer EUL to an OBIEE Repository
October 23rd, 2008 by Borkur Steingrimsson
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 …
May 21st, 2009 at 4:28 pm
Bokur
I discovered this article via Google and found it most informative. Do you know if the export utility also accounts for APPS EULS?
Thanks.
Paul.
December 29th, 2010 at 3:43 pm
I cannot find the OracleBI/server/Repository/ in the server. Where can i find this?
December 30th, 2010 at 11:46 am
Are you looking at where OBIEE is installed? It’s usually at c:\oraclebi\server\Repository, if you’ve installed OBIEE to the root of the C: drive (in Windows).
Note that this is where OBIEE is installed, not Discoverer. The utility comes as part of OBIEE, not Discoverer.
regards, Mark
December 30th, 2010 at 1:33 pm
Hi Mark, Thank you very much for your reply.
I’ve already done the migration in my admin tool. When I tried to copy the rpd file in the BI server (installed in a linux env), I cannot find the OracleBI/server/Repository/ directory. How can I find this directory?
January 20th, 2011 at 12:17 pm
We would like to do discoverer to obiee migration , Can any one guide us .
April 19th, 2011 at 2:17 pm
Hi Mark, do we still have ‘MigrateEUL.exe’ as part of BI 11g? I have looked for this file on the system and also this menu option in the BI Admin tool (ver. 11.1.1.3) but could not find it.
April 20th, 2011 at 1:56 pm
I think it only shipped with OBIEE 10g, as the RPD it creates are 10g RPDs – if you wanted to use them with 11g, you’d need to upgrade them using one of the upgrade utilities. My understanding is that the 11g EUL upgrade utility will ship sometime in the future, will be a separate download (presumably from OTN), and may possibly upgrade the EUL workbooks as well.
regards, Mark
August 17th, 2012 at 8:57 pm
I saw this at Oracle World a few years ago; I asked the following question: It works with a simple 4 table join of a video store, but how is this going to work on a complex EUL joining tables across schemas with circular joins and concatinated joins when OBIEE only supports unary joins? His response was you will have to test; he should be a politician. The fact of the matter is most EUL implementations are against the transactional database, OBIEE does not handle a 3NF but only a star schema with little snowflaking. This tool will not build you dimensional warehouse nor you ETL’s. So simply put this example is nonsense
August 22nd, 2012 at 1:45 pm
@Anthony,
Please look here: http://www.rittmanmead.com/2012/08/using-obiee-against-transactional-schemas-part-2-aliases/#comment-18473
Are we talking 3NF here – or … ?
January 8th, 2013 at 4:33 pm
I seen the articles of using OBIEE against a transactional DB, but again when the transactional DB is Oracle EBS or any other ERP system, to get a star or a useable snowflake schema would mean bridging multiple tables and the performance and strain on the transactional system would be degraded. I would really like to see something like taking the Oracle Fixed Assets business area from Discoverer against the transactional source and like magic be migrated OBIEE star or snowflake schema.
January 8th, 2013 at 4:41 pm
My comments are basically, if you are running a Hot Dog Cart and have an accounting system with 5 tables then yes go ahead and use OBIEE against a transactional DB into a star schema with logical bridge tables. But face it, most likely if you plan to spend the capital for an Enterprise Reporting System (BI or Operational BI), then you are running a large ERP system as a source with tens of millions if not hundreds of million transactions stored in thousands of tables (to integrate GL, AP, AR, FA, PA, INV), I would think you would need a data warehouse and ETL’s.