OBIEE to Power BI
Overview:Led the complete migration from Oracle Business Intelligence Enterprise Edition (OBIEE) 11g to Microsoft Power BI, transforming a legacy BI infrastructure into a modern, scalable, and self-service analytics platform. This migration involved redesigning over 100 reports from OBIEE’s RPD and BI Publisher layers into interactive, user-centric dashboards in Power BI, while preserving security, logic, and performance standards.
Technical Stack & Tools:
- Source Systems: OBIEE 11g Web Interface, RPD (Repository Development), BI Publisher
- Target Platform: Power BI Desktop, Power BI Service (Premium P3 Capacity), Power BI Workspaces, Power BI Apps
- Data Sources:
- OBIEE Source: Oracle Database
- Power BI Source: SQL Server (data dump from Oracle was ingested into SQL Server)
- Additional Tools & Accelerators:
- Python scripts for metadata extraction
- Log file parsers to capture backend OBIEE report SQL
- Custom scripts for data dictionary generation
Data Integration & Modeling:
- Reconstructed the OBIEE RPD semantic model into Power BI’s tabular data model
- ETL logic from OBIEE was translated and implemented using Power Query and SQL transformations
- Implemented dynamic and static Row-Level Security (RLS) in Power BI to mirror OBIEE’s security model
- Designed composite models and defined self-service layers in Power BI for flexible report building
Power BI Concepts Implemented:
- Star-schema-based modeling to simulate OBIEE subject areas
- DAX-driven logic to simulate union-based OBIEE reports
- Bookmark navigation for visual switching (to replace OBIEE prompt-based view switching)
- Power BI Apps for user-specific access segmentation across departments
Key Responsibilities:
- Acted as subject matter expert, technical lead, and primary developer for the migration
- Led the metadata translation from OBIEE’s RPD and web catalog to Power BI’s semantic model
- Designed and validated over 100 Power BI reports
- Guided architectural decisions around licensing, workspace design, and security
- Coordinated with DBAs and business teams for data model validation and user enablement
Skills Demonstrated:
- Deep expertise in OBIEE architecture, Power BI, SQL, and Data Warehousing principles
- Advanced DAX and Power Query usage to translate OBIEE-specific logic
- Experience in managing large-scale BI migrations and modernizing legacy systems
Architectural Differences: OBIEE 11g vs. Power BI (with Migration Context)
| Aspect | OBIEE 11g | Power BI | Impact on Migration |
| Architecture Type | Traditional, centralized enterprise BI platform | Modern, cloud-enabled, self-service BI platform | Required re-architecting reports from a centralized RPD-driven model to decentralized Power BI datasets |
| Semantic Layer | Uses RPD (Repository): Physical, BMM, and Presentation layers | Power BI uses Tabular Models with relationships, measures, and hierarchies | RPD metadata had to be deconstructed and rebuilt in Power BI using Power Query and DAX |
| Data Connectivity | Native Oracle DB connections; tightly coupled with Oracle ecosystem | Broad source support; SQL Server, Oracle, REST APIs, etc. | Oracle source data was offloaded to SQL Server for performance and integration with Power BI’s semantic model |
| Prompting Mechanism | Dashboard Prompts trigger dynamic SQL generation at runtime | Slicers filter data already loaded (Import mode); no dynamic SQL unless in DirectQuery | Required architecture redesign using DirectQuery or API-triggered refreshes to mimic prompt-like behaviour |
| Report Development | Dashboard-centric with drag-and-drop answers and report-level unions | Visual/report-driven, highly interactive canvas-based report design | Union-based logic had to be pushed to the SQL layer or simulated via combined datasets with conditional filtering |
| Security Model | Uses Initialization Blocks and Row-Level Filters | Uses Row-Level Security (RLS) with DAX roles and workspace-level security | RLS strategies were redesigned using Power BI’s dynamic/static RLS and role-based access through Power BI Apps |
| Visual Control | Prompts could control switching between sections or views | Bookmarks and selection panes enable similar visual control | Visual switching in OBIEE was re-implemented using bookmarks and conditional visibility controls |
| Publishing Model | Reports and dashboards are deployed to Web Catalogue | Reports are published to Power BI Workspaces, organized into Apps | Reports were grouped into targeted Apps for audience-specific access, replacing OBIEE’s catalogue folder permissions |
| Licensing Model | Per-user or CPU-based licensing (typically higher TCO) | Flexible licensing (Power BI Pro, Premium by capacity or per user) | Migrating to Power BI led to cost savings and better scalability using Premium P3 capacity and App-based content distribution |
Challenges and Key Learnings in OBIEE to Power BI Migration
The OBIEE to Power BI migration highlighted several architectural and functional differences that required creative problem-solving and deeper technical adaptation. Below are some of the major challenges and learnings:
- Prompt vs. Slicer Architecture Differences
In OBIEE, prompts dynamically generate SQL queries based on user input, pulling only the filtered data from the database. Power BI slicers, however, work on pre-loaded datasets (unless in Direct Query mode) and slice/filter only what’s already been fetched.
Workaround & Learning:
- Used Direct Query selectively where dynamic, real-time querying was critical
- Designed parameter tables and slicer logic with REST API triggers to simulate dynamic query behavior
- Educated users on the fundamental difference in user input flow between platforms
- Report-Level Union Queries
OBIEE allows union/intersect/minus queries directly at the report level, often with distinct filters per query block. Power BI doesn’t support union logic at the visual/report level with separate filters.
Workaround & Learning:
- Rewrote complex union queries as SQL views or Common Table Expressions (CTEs) in SQL Server
- Created unified datasets with marker keys to distinguish records from different logical blocks
- Applied conditional filtering logic via DAX to simulate distinct filter blocks
3. Visual Switching Based on Prompts
OBIEE supports switching between visuals/views based on prompt values, a feature not natively available in Power BI.
Workaround & Learning:
- Recreated visual switching behavior using Power BI bookmarks, selection panes, and synced slicers
- Provided users with a seamless experience while navigating between visual states
4. Metadata and SQL Conversion
OBIEE’s backend SQL often included Oracle-specific functions that were incompatible with SQL Server.
Workaround & Learning:
- Used Python-based accelerators to parse OBIEE logs and extract report SQL
- Translated Oracle syntax to SQL Server equivalents during transformation and modeling
5. Security Replication
OBIEE has robust security through initialization blocks and data-level filters. Power BI required careful setup of dynamic and static RLS to match this behavior.
Workaround & Learning:
- Modeled RLS rules using DAX and Power BI roles
- Created multiple Power BI Apps with scoped access based on user roles