This post intends to provide an assessment to determine if the Hyperion Translation Workbench could play a role in the migration of Hyperion IR assets to the target OBIEE 220.127.116.11.
Follow this link for more information on the Translation Workbench http://www.oracle.com/us/solutions/ent-performance-bi/business-intelligence/obiee-conv-txnwb-406022.pdf
Below are the technical characteristics of the software components involved in this exercise
- Hyperion Interactive Reporting (IR) version 18.104.22.168, and the EPM Workspace version 22.214.171.124
- OBIEE 126.96.36.199
- Oracle 11R2 used as the repository for OBIEE 188.8.131.52, and the sample source data schema for the test report used in this exercise (see script sales_dm_cr)
- Windows 2008R2 64bit Virtual Machine for the server side components listed above.
- Oracle VirtualBox Manager version 5.1.12 for the above VM
- Default FireFox Browser v50.0.2
- Windows 7 Professional Desktop for the Hyperion IR Client.
The diagram shown below depicts the topology of the setup used in this assessment of the Hyperion TW tool
1. Prepare software installations ⇒
2. Make ready a Hyperion BQY file for migration to OBIEE ⇒
3. Make ready an RPD file for Hyperion Translation Workbench ⇒
4. Execute Translation Workbench to generate OBIEE assets (a new RPD file, and various OBIEE Answers outputs).
4.1 Generate RPD file – You need to pause at this step, and switch to OBIEE to deploy the newly generated RPD file ⇒
4.2 Deploy RPD file ⇒
4.3 Generate OBIEE Answers ⇒
5. Run OBIEE to review generated TW assets against the Hyperion BQY file ⇒
Prerequisites – Server Side
1. EPM Foundation v184.108.40.206 is installed
2. Hyperion Interactive Reporting (IR) v220.127.116.11 is installed
3. Oracle 11gR2 is installed, and contains the test schema SALES_DM (see script sales_dm_cr)
See this post for information on setting up a sandbox for the above software components.
Sample IR Report
This section outlines the structure and contents of the IR report used as a test report in this assessment
Hyperion BQY Dashboard Content
The above dashboard exhibits the following characteristics that will be put through the TW tool:
1. Embedded graphics
2. Pivot table
3. Column chart
4. Line chart (two series)
5. Dropdown selection box
6. Hyperlink to an external web site
7. Navigation buttons
7.1.Show Report – go to a specific report
7.2 Show Data – go to a tabular data sheet
7.3 Refresh Data – run underlying SQL queries to fetch latest data
7.4 Explore – go to specific charts for further analysis
See the video shown below for a live showcase of the dashboard
1. Download the Hyperion Translation Workbench (TW) from this link http://www.oracle.com/technetwork/middleware/epm/downloads/interactive-reporting-1112x-2409231.html
2. You should now have the TW installed in C:\OracleBITranslation as shown below
3. Review the User Guide document Hyperion to OBIEE translation.pdf in the above directory
4. Create a working directory C:\TW_WIP
5. Copy the relevant BQY, and OCE files from your Hyperion IR installation to C:\TW_WIP in preparation for your translation to OBIEE. See sample steps shown below.
5.1 On your IR server, change directory to C:\Oracle\Middleware\EPMSystem11R1\products\biplus\data\Open Catalog Extensions
5.2 Copy the required BQY and OCE files to C:\TW_WIP (see the next section on how to create a test BQY)
Alternatively, assuming that you have set up the test schema SALES_DM, use this file Sales02 BQY instead.
You could build a simple BQY file from your own Hyperion IR installation, and use it for the migration steps in this post. Alternatively, assuming that you have cloned a test schema SALES_DM in your test Oracle database, download this file Sales02 BQY and edit its database connection to point to your test schema SALES_DM.
1. Create a new RPD file with only tables (no keys, no relationships) to contain the tables referenced in the BQY files to be migrated.
2. Locate your RPD file in C:\oracle\middleware\instances\instance1\bifoundation\OracleBIServerComponent\coreapplication_obis1\repository, and copy it to a working directory (e.g. C:\TW_WIP) for downgrading to the version 302 that is compatible with the Translation Workbench 18.104.22.168
3. Change directory to C:\TW_WIP, and run the downgrade utility from DOS command line as follows
C:\oracle\middleware\Oracle_BI1\bifoundation\server\bin\nqgenoldverrpd.exe -P rpd_password -I full_path_to_input_rpd -O full_path_to_output_rpt -V 302
C:\oracle\middleware\Oracle_BI1\bifoundation\server\bin\nqgenoldverrpd.exe -P mypassword -I C:\TW_WIP\sales_dm_17.rpd -O C:\TW_WIP\sales_dm_17_v302.rpd -V 302
where 302 is the only RPD version supported by the Translation Workbench
In order to find out what RPD version that the tool will support, start c:\OracleBITranslation\translation.bat, and load into your original RPD file. The tool will let you know if its version is compatible as shown below
1. On your Desktop, change directory C:\OracleBITranslation, run translation.bat as Administrator
2. Create a new project, and load in the RPD file
In the above example, the RPD file sales_dm_17_v302.rpd has been downgraded from OBIEE 22.214.171.124 to v302 for the Translation Workbench.
3. Configure BQY Root Folder to allow the TW tool to recognize the BQY file to be migrated
4. Run Review Catalogs in Phase 2 to verify the RPD file loaded
5. Run Generate BM from BQYs in Phase 2
6. Run Review Catalogs in Phase 3
7. Run Generate V11 RPD in Phase 4 with the output file named as sales_dm_17_tw.rpd
Below is the structure of the RPD file generated (notes. the missing objects Customers, and Promotions as depicted in the intended data model)
You will need to deploy the new RPD file generated by TW to your test OBIEE before running the last step Phase 5 in TW.
This section prepares the current OBIEE environment to receive the new RPD file.
8. Run BI Administration, and open the new RPD file to edit its database connection info (the RPD password in Admin123).
9. Test your RPD database connectivity by enabling Row Count, and performing Update Row Count.
10. Perform Consistency Check, and save the RPD file with the edited database connection (keep the existing default password Admin123 for ease of troubleshooting later).
11. Run Oracle Fusion Middleware Control at http://localhost:7001/em (replace localhost with your specific OBIEE server name) to deploy your new RPD file as saved in the above step.
12. Restart BI Services to apply all changes done above
13. Connect to your OBIEE Home Page (http://localhost:7001/analytics) to confirm that the new RPD file is now accessible, and the Subject Area ‘Sales Catalog’ matches the Presentation layer defined in the new RPD file.
14. Create a new folder, e.g. From_TW, from ‘Shared Folders’ to get ready to receive the outputs from the Translation Workbench
15. Switch to the Translation Workbench, and continue with the last Phase 5
16. Connect to OBIEE Home Page (http://localhost:7001/analytics), and the new report should be published to the folder /shared/From_TW
The Translation Workbench only partially migrated some of the BQY assets to the new OBIEE target. Below are noticeable shortcomings with the TW tool:
1. The new RPD model only includes objects that are explicitly referenced in the BQY query logic. All others hence will be ignored by the Workbench. Hence, you cannot use the tool to migrate a full data model that is currently supporting your Hyperion BQY assets.
2. Only BQY assets built on BQY tables are converted into Answers assets in OBIEE. Any other BQY sections must therefore be converted manually outside the TW tool.
3. Since OBIEE only supports a single RPD file, the new RPD file generated by the Translation Workbench, will either be deployed on its own OBIEE installation to support the migrated BQY assets, or must be merged into your current OBIEE RPD file. Merging RPD files, done in the BI Administration tool, could introduce additional complexities!
4. Inconsistent support of software versions could create compatibility issues that become very tricky to resolve in OBIEE as explained below
• Translation Workbench works best in OBIEE 126.96.36.199.
• It could handle OBIEE 188.8.131.52 but this is not field-tested yet.
• The TW tool can only read an RPD file of OBIEE version 184.108.40.206. Hence, the skeleton RPD file generated for TW from OBIEE 220.127.116.11 must be downgraded to OBIEE 18.104.22.168
• The current TW tool can only generate the final RPD file for OBIEE 22.214.171.124. Any higher OBIEE versions are not yet field-tested.
With the above shortcomings of the Translation Workbench tool, the migration process might be better handled manually via a thoroughly planned strategy instead. Below are the steps to consider
1. Perform a thorough assessment of the current Hyperion IR data models, and associated assets.
2. Review the impact to the current RPD file should the above migration introduce new data objects, data relationships, computed fields, drilldown hierarchies, etc.
3. Determine, and agree, a mapping of the following Hyperion IR asset types to OBIEE
a. IR Query objects
b. IR Table objects
c. IR Dashboard objects
d. IR Report objects
e. IR Pivot objects
f. IR Chart objects
h. IR Interactive objects such as Dropdown boxes, Buttons, Sliders, etc.
4. Remodel the existing RPD file to accommodate the additions from the Hyperion IR assets
5. Gradual rewrite/redesign of IR assets in OBIEE