Repoint Reports to a New Database¶
Move BusinessObjects connections, universes and reports from an old database server or schema to a new one, and prove that the reports still return the same data.
Goal¶
The DBA team migrates a reporting database to a new server (or a new schema, or another database engine). Every connection, universe, Web Intelligence data provider and Crystal Report that reads the old database must point to the new one, with no broken reports on Monday morning.
Who¶
BI administrators and universe designers, with the DBA who runs the migration.
Before you begin¶
- MetaMiner collects universe, Web Intelligence and Crystal Reports detail for the environment. See Document Processing.
- Your license includes universe remapping, and you have the MMC permissions Remap Universe, Modify Connection and Modify Crystal Logon Info as needed. See Permissions Reference.
- The new database is loaded and reachable from the BusinessObjects servers.
- You know the old and new server, database and schema names.
Steps¶
1. Find everything that uses the old database¶
- In the Impact Analysis - DB tab, find the old database and schema, right-click and choose View Impacted Documents. Export the list. See Impact Analysis.
- Run MM-C16 Data Source Use for Crystal Reports and MM-W17 Data Provider SQL to find free-hand SQL that names the old server or schema.
2. Take a baseline¶
- Back up the affected connections, universes and reports with a Backup/Promote job.
- Create a one-sided Report Validation report set with representative reports and run it once. This output is your "before" picture.
3. Change the connections¶
- In the CMS Explorer, select the relational connections that point to the old database.
- Right-click and choose Modify > Modify Connection..., then enter the new server and database details.
Image of the Modify Connection dialog with the new server entered here
../images/use-cases/repoint-modify-connection.png
4. Change universes and Web Intelligence data providers¶
Pick the action that matches what changes. All are under Modify on the right-click menu of the CMS Explorer:
| What changes | Select | Action |
|---|---|---|
| A UNX universe must use another connection | UNX universes | Remap Connections... |
| Web Intelligence documents must use another universe | Web Intelligence documents | Remap Universes... |
| Same, with an object-by-object mapping between old and new universe | Web Intelligence documents | Remap Universes with mapping |
| Web Intelligence free-hand SQL data providers must use another connection | Web Intelligence documents | Remap Connections... (Free-Hand SQL) |
| Table names or owners in a UNX universe change | UNX universe | Update Tables... |
See Universe Remap and Connection Remap for the dialogs.
Work on copies first
The remap dialog can create a copy of each document and remap the copy, leaving the original untouched. Use it for a trial run on a few documents.
TODO: confirm the copy option label and the name pattern placeholders in the remap dialog
The server supports a copy with a name pattern using %NAME%, %ID% and %CUID%. Confirm the labels on a current build.
5. Change Crystal Reports¶
Follow Crystal Bulk Database Change for the reports found in step 1.
6. Validate¶
- Press F5 in the MMC so MetaMiner reloads the changed objects, then repeat step 1. The old database should no longer appear.
- Run the Report Validation report set again and use Compare with Previous Output on the new output. Differences point to reports that need attention.
Expected result¶
All connections, universes and reports read from the new database, the impact analysis for the old database is empty, and Report Validation shows no unexpected differences between before and after.
Variations¶
- New database engine, not just a new server: check free-hand SQL and Crystal command text for engine-specific SQL with MM-W17 and MM-C17 before you switch.
- Move between BusinessObjects environments as well: see Export and Promotion.
- Roll back: restore the objects from the backup taken in step 2. See Disaster Recovery.