Translate

Saturday, 1 December 2012

Shahed

Hyperion BRIO BQY to OBIEE 11g



Translation Workbench Translating BQY to OBI EE RPD

Tried and Tested on OBIEE 11.1.1.6+ 

Converting BQY files into OBIEE in 5 phases.




The Translation Workbench (referred to as the TW in this document) is an application that merges information about database schemas (the Physical Layer found in an RPD) with Query Request model and reporting information found in BQY files to creates Business and Presentation Models for an OBIEE Repository (RPD file) plus the requests and views that implement broadly equivalent BQY reports using Answers.
Utility can sometimes give undesirable results but logs are provided and corrections can be made.
The utility provides a 5 phase approach to the migration of all BQY files and the end point are reports within OBIEE Analysis and not BI Publisher.

Phase 1

The process starts by importing into TW an RPD that contains just a Physical Layer, the
tables and primary keys from the data sources used by the BQY Query Requests that are
to be translated. The BQY files are then added to TW, possibly modified using an Impact
Manager script

Phase 2

TW analyzes the BQYs and expands them into Query Requests and from the tables these
use it builds the stars required to support them.

Phase 3

The stars can be fine tuned through the TW’s point and click features
_ Set Logical Table Sources (LTS) from dimension to fact and vice versa.
_ Merge different stars with common facts.
_ Delete stars and repeating Phase 2 in cases where join direction conflicts exist.
_ Group and Ungroup Dimensions.
_ Add Multi Source Columns to composite logical facts tables.
_ Combine logical column formulae and sub-queries.
_ Edit names

Phase 4

The tuned stars can them be turned into a full RPD with Physical, Business and
Presentation layers. The RPD will require review and editing
_ Configure initialization blocks and variable for unjoined tables and sub-queries.
_ Review the multi-table formulas.
Dashboard
BQY Data Model
Request / Filters
Table / Results
Views (Chart / Pivot) OBI EE
Presentation
Objects
OBI EE
Presentation
Objects
OBI EE
Repository
Generate RPD
Generate PSO
Presentation
Services
5
Migration
Engine
Harvest DM
BI
Information
Model
Tune BIM
Import RPD
1
2 3 4
Report
Phase 5

The BQY files are then re-analyzed to build equivalent Answers reports in the form of
Criteria, with Chart and Pivot views. 



About Authors

Shahed Munir

Krishna Udathu

Shahed and Krishna are Oracle / Big Data Experts