Migrating Discoverer between two Databases

on Thursday, April 8, 2010

Discoverer consists of the End User LayerTM (EUL), Business Areas (BA), and workbooks. The EUL is essentially a set of application tables, views, and sequences that store the metadata information. This is organized into one or more logical Business Areas. For example, an EUL may contain a Human Resources Business Area and a Marketing Business Area.

Workbooks are reports that have been defined using metadata stored in the EUL. Workbooks can either be saved as files on your local machine or stored centrally in the database.

Please Note: When you save a workbook, Discoverer refers to the folders and items in the workbook using an internal identifier (id) rather than the folder name or item name. This means that the administrator can change folder names and item names without affecting any existing workbooks.

Moving a Complete EUL and Associated Workbooks

1.1 When to use

  • Want to move a complete EUL and all workbooks saved in the database.
  • The EUL are the only tables stored under a database username.

1.2 Advantages

  • Simplest method of moving the Discoverer environment.
  • Keeps all the internal identifiers between the EUL and the saved workbooks intact.

1.3 Disadvantages

  • When you import, you have to be aware of any possible issues with other EUL's that may already exist in the database. (Details below)

1.4  Steps

  1. Take a database backup of the EUL Owner's schema.  This backup contains all of the business areas, the EUL tables and workbooks saved to the database.
  2. Restore from the backup into the new EUL owner on the new database.  It is recommended that this database user owns no other tables.

1.5 Potential Issues

  1. Discoverer Release 3.0.8 supports only one public EUL per instance. If there is another public EUL already existing on the target database, when you import the new EUL, it will make this new imported EUL the new public EUL
  2. Release 3.1 supports multiple EUL's. If there is more than one EUL available on the target database, the first time you log into the User Edition, you should go to the Tools -> Options -> EUL tab and ensure your default EUL is pointing to the correct EUL.
  3. If the owner of the data tables is different in the new database, you may need to go into the Discoverer Administration Edition and do a File -> Refresh.
  4. Any registered PL/SQL functions are still registered in the EUL. If the function doesn't exist in the target database, then the item will obviously be unavailable.

 Moving Individual Business Areas and Workbooks

2.1 When to use

  • When you want to move one or more Business Areas between EULs.
  • When you want to move Business Areas to a new database.
  • When you want to move an EUL and the original EUL owner also owns other database objects you don't wish moved.

2.2 Advantages

  • Enables fine control over movement of business areas and workbooks.

2.3 Disadvantages

  • Need to be methodical about moving workbooks.

2.4 Moving individual business area(s)

  1. In the Administration Edition, open the business area(s) you wish to move.
  2. Select the Business Area(s).
  3. Click on File -> Export.
  4. Re-connect the Administration Edition to the new EUL in the new user/database.
  5. Click on File -> Import.

Note that a business area export does not export any workbooks that use the Business Area. If you also wish to move workbooks, either saved in the database or saved on the file system, then you should perform the following steps. As mentioned before, workbooks store the folders and items used with internal IDs. If you now export a business area and import it into a new EUL, the IDs of the folders and items are changed during the import process. (This is because the original IDs may be already used by other folders and items already existing in the target EUL). When you run an existing workbook against a "moved" Business Area, it matches folders, items, joins, etc., on names, not IDs. If this workbook is then saved again, the IDs within the workbook are matched with the new IDs in the "moved" Business Area, and everything is synchronized again.

NOTE. With Discoverer 3.0.8.x, any PL/SQL functions registered within the Business Area are not exported as part of the Business Area. With Release 3.1, registered PL/SQL functions are exported. If the function itself doesn't exist in the target database, then the item will obviously be unavailable.

2.5 Moving workbooks

  1. {Optional} Open all existing workbooks saved to the file system and save them again to the file system. (The reason for this is that the administrator may have changed folder, item, or join names since this workbook was saved. At the moment, this is not an issue, as the workbook uses internal IDs to match. However, when we move the workbook, we need to match on names; therefore, we need to ensure that the names in the workbook match the latest names in the business area).
  2. Open all existing workbooks stored in the database and save them to the file system.
  3. Export the Business Area(s) as outlined above.
  4. Create the new EUL on the target database
  5. Import the Business Area(s).
  6. Open the workbooks against the new EUL, but don't run them. (This matches up the IDs again.) Save them, either to the database or to the file system, whichever is preferred.
  7. Refresh the Business Area. (File -> Refresh)

2.6 Potential Issues

  1. If you have folders that cross multiple Business Areas, you should export all the Business Areas it refers to in one export file.
  2. Note that you cannot have more than one folder with the same name in an EUL. If you are importing a Business Area into an EUL that is already populated, be careful of renaming folders. The workbooks you are moving will look for the folder name the first time you open it, until the IDs get back in synchronization. If the folder name is changed during the import process, there is a risk that the workbook may pick up the wrong folder.

If a workbook has an associated bitmap (in the title or as part of the parameter dialogue), the location of the bitmap is hard-coded to a directory and so may need to be modified.