SAP to Power BI

SAP to Power BI

Overview :Led the end-to-end migration of reporting infrastructure from SAP BusinessObjects (BO) Web Intelligence (WebI) to Power BI, aiming to modernize the reporting environment and enable self-service analytics for business users. This initiative resulted in significant cost savings by eliminating SAP licensing fees for Prudential users, optimizing the organization’s BI spending while improving report accessibility and scalability.

Technical Stack & Tools:

• Source Systems: SAP BO WebI Environment, SAP Information Design Tool (IDT)
• Target Platform: Power BI Desktop, Power BI Service (64 SKU licensing model), Power BI Workspaces, Report Builder, Power Automate
• Data Integration & Modeling:
  • Migrated metadata from SAP BO Universes to Power BI data models
  • Leveraged Databricks as a federated query layer integrating with SQL Server, the core transactional data source

• Power BI Concepts Implemented:
  • Data transformation using Power Query Editor
  • Advanced data modeling in Power BI
  • Incremental refresh for optimized performance
  • Creation of rich, interactive Power BI visuals
• Power Automate Concepts Implemented :
  • A Power Automate flow was created to act as a bridge between the ETL completion status and the Power BI dataset refresh trigger.
  • The ETL pipeline was configured to update a control or audit table (staging table) upon successful data load.
  • The Power Automate flow monitors this control table for a specific status flag or timestamp change, using a SQL Server trigger or scheduled poll via on-premises data gateway.
  • Once the load completion is detected, Power Automate executes a Power BI REST API call to trigger the dataset refresh for the associated semantic model.
  • Error handling and logging were built into the flow to capture failures and notify the support team via Teams or email alerts.

Key Responsibilities:

• Acted as the technical lead and SME throughout the migration lifecycle
• Designed and implemented a scalable data model in Power BI using metadata extracted from SAP Universes
• Rebuilt over 40 existing SAP BO reports in Power BI using self-service modeling principles
• Guided licensing and workspace setup in Power BI Service (64 SKU plan)
• Collaborated with cross-functional teams to ensure accurate data representation and user enablement

Skills Demonstrated:

• Deep knowledge of data warehousing concepts, SQL, and Power BIarchitecture
• Expertise in enterprise BI migrations, self-service BI design, and data governance
• Strong experience in metadata translation, performance optimization, and report reengineering.

 
Architectural Differences: SAP BO vs. Power BI (with Migration Context)
 

Challenges and Key Learnings in SAP BO to Power BI Migration

The migration from SAP BusinessObjects (BO) to Power BI, while strategically beneficial, presented several technical challenges due to the fundamental architectural and functional differences between the two platforms. Some of the critical challenges and the learnings derived from them are detailed below:

• Replicating SAP BO’s Merge Dimensions in Power BI

SAP BO allows the use of Merge Dimensions, a feature that enables joining datasets with different granularities or keys through a common dimension at the report layer. Recreating this behavior in Power BI required a fundamental re-engineering of the data model. Power BI does not support merge dimensions natively; hence, we had to carefully design data relationships, apply DAX functions (e.g., TREATAS, CROSSFILTER), and in some cases normalize disparate datasets to preserve the intended analytical outcome. This led to a deeper understanding of how data modeling can compensate for visual-layer limitations.

• Converting Combined Queries into Power BI Data Model

In SAP BO, combined queries (such as union, intersection, and minus operations) can be created directly at the query panel level to serve as multiple input sets in a single report. Power BI lacks a direct visual counterpart, and such logic must be handled within Power Query Editor or DAX, depending on the scenario. This required the team to redesign query logic using append/merge queries, custom columns, and conditional logic within Power BI, emphasizing the importance of ETL-layer transformations before loading data into the semantic model.

• Differences in Prompt vs. Slicer Architecture

One of the most significant architectural learnings involved understanding the difference between SAP BO prompts and Power BI slicers. In SAP BO, user-entered prompts dynamically generate backend SQL queries that fetch only the filtered data from the source. This means the actual data retrieval is conditional and runtime-optimized. In contrast, Power BI loads the dataset (or uses Direct Query if configured) and then allows filtering only on the in-memory or cached data through slicers.

This key difference meant that real-time data slicing based on user input required architectural adjustments—including:

• Using DirectQuery mode when dynamic, on-demand querying was essential
• Implementing parameter tables with custom filters and REST API-triggered dataset refreshes for simulating prompt-like behavior
• Educating users and stakeholders on functional differences in user input behavior between the two platforms

• Lack of Direct Context Handling in Power BI

SAP BusinessObjects provides a sophisticated feature called “Context”, which is used to resolve join path ambiguity in complex universe designs—especially in scenarios involving multiple fact tables with shared dimensions. Contexts allow BO to determine the correct combination of joins to use in generating SQL queries, depending on the objects selected in a report.

In contrast, Power BI does not support context explicitly. Power BI relies on a single active relationship between tables and evaluates queries based on the data model’s relationship graph and filter propagation. When faced with multiple valid join paths or ambiguous relationships, Power BI can either:

Use only the active relationship, or

Require manual intervention using DAX functions like USERELATIONSHIP() to activate alternate relationships contextually

• We had to redesign complex SAP Universes into star or snowflake schemas in Power BI to eliminate the need for contexts.
• In cases where multiple fact tables shared dimensions, we used composite models, role-playing dimensions, and DAX measures to simulate behavior similar to context resolution.
• This challenge highlighted the need for a clear and governed data modeling strategy in Power BI, ensuring correctness without the safety net of SAP BO’s built-in context resolution.

These challenges significantly enhanced our understanding of Power BI’s data architecture, pushed us toward more optimal data modeling and transformation strategies, and underscored the importance of setting stakeholder expectations during BI tool migrations.