Automatic migration of a Discoverer EUL to an OBIEE Repository

October 23rd, 2008 by

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:

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.

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:

Sticking with the Discoverer Video Store Tutorial, here is what the business area looks like in the Administration tool:

Discoverer_1

 

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.

 

Presentation Layer 1 Logical_Layer_1 Physical_Layer_1

 

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.

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.

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.

 

Answers_1

 

 

Answers_2

 

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 …

Comments

  1. Paul Rooney Says:

    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.

  2. Eric Says:

    I cannot find the OracleBI/server/Repository/ in the server. Where can i find this?

  3. Mark Rittman Says:

    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

  4. Eric Says:

    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?

  5. Vignesh Says:

    We would like to do discoverer to obiee migration , Can any one guide us .

  6. Asim Says:

    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.

  7. Mark Rittman Says:

    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

  8. Anthony Ciliberto Says:

    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

  9. Niels Jessen Says:

    @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 … ?

  10. Anthony Ciliberto Says:

    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.

  11. Anthony Ciliberto Says:

    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.

Website Design & Build: tymedia.co.uk