Wipro: How Do You Convert a Fully Normalized ERP Dataset Into a Performance-Optimized Star Schema for Enterprise Reporting in Power BI?

Wipro works with large global enterprises running complex ERP systems like SAP ECC, SAP S/4HANA, Oracle EBS, Microsoft Dynamics, Infor, and legacy mainframe-based systems. These ERP systems typically store their data in a highly normalized OLTP schema with:

• hundreds of tables
• deep relationship chains
• transaction-level granularity
• composite keys
• multiple reference lookups

However, Power BI requires a very different modeling strategy — a denormalized star schema with:

• a few fact tables
• clean dimension tables
• single-direction relationships
• optimized cardinality
• highly compressed in-memory storage

Because Wipro handles real-time enterprise reporting with millions of records, they expect candidates to demonstrate mastery in converting:

“Normalized ERP Schema → Star Schema → Fast Power BI Model”

This long-form guide covers the exact steps Wipro expects in interviews.

For Microsoft’s official modeling guidance, refer to Power BI Star Schema Best Practices (Microsoft Docs) .

1. Why Wipro Asks This Question

Clients of Wipro rely on BI dashboards for:

• financial consolidation
• procurement insights
• inventory forecasting
• sales analytics
• HR reporting

Normalized ERP data does NOT perform well in Power BI:

• Too many joins
• Slow visuals
• Ambiguous paths
• Complex schema maintenance
• Huge memory consumption

Wipro expects candidates to:

• analyze the ERP schema
• identify facts and dimensions
• denormalize correctly
• design a correct star schema
• avoid snowflake and M2M relationships
• ensure high compression in VertiPaq
• create a BI-ready semantic layer

2. Understanding ERP Normalization

ERP systems are normalized to:

• avoid duplication
• maintain referential integrity
• improve transactional throughput
• support frequent writes

Typical ERP Characteristics:

• Tables named like LFA1, MARA, BSEG (SAP)
• Total 400–800 tables
• Deep relationships via foreign keys
• Composite keys like (MANDT, DOC_NO, ITEM_NO)
• Date, time, status fields in separate tables

These structures are NOT BI-friendly.

3. Identifying Fact Tables

Facts represent measurable business events:

• Sales Orders
• Invoices
• Deliveries
• Payments
• Inventory Movements
• Production Orders
• Time Sheets
• Budget vs Actual

A fact table typically has:

• Many rows (millions)
• Foreign keys referencing dimensions
• Numeric columns (Amount, Quantity, Cost)

Fact Table Example (ERP → BI):

-- ERP: Normalized tables
SalesHeader
SalesItem
CustomerMaster
MaterialMaster

-- BI: Single denormalized fact table
FactSales

4. Identifying Dimension Tables

Dimensions describe business entities:

• Customer
• Product
• Material
• Vendor
• Location
• Employee
• Date
• Region
• Organization

Dimension Table Characteristics:

• Low row count (hundreds or thousands)
• Attribute-rich columns
• Used for slicing and filtering
• Provide business context

5. The Denormalization Process (Wipro Method)

Wipro follows a standardized 5-step conversion method.

Step 1 — Identify Core Business Entities

For example, in SAP:

• Customer (KNA1)
• Material (MARA)
• Vendor (LFA1)

Step 2 — Flatten Related Tables

Join:

• MARA + MAKT → Product Dimension
• KNA1 + ADRC → Customer Dimension

Step 3 — Create Surrogate Keys

CustomerSK (Identity)
ProductSK (Identity)

ERP keys like MANDT + KUNNR must be replaced with simple numeric keys.

Step 4 — Build Fact Tables with Numeric FKs

FactSales:
 - DateSK
 - CustomerSK
 - ProductSK
 - RegionSK
 - Amount
 - Quantity

Step 5 — Build Role-Playing Date Dimensions

Common date roles:

• Order Date
• Shipment Date
• Invoice Date

6. Avoid Snowflake Schema

Snowflake = normalized dimensions.

Why Wipro avoids this:

• Multiple joins slow down VertiPaq
• Extra relationships cause ambiguity
• Mixed granularity issues

7. Optimize Fact Tables

To reduce memory footprint:

• Remove unnecessary columns
• Split DateTime into Date + Time
• Convert text to integers
• Avoid long codes
• Round decimals

Example Optimized Fact Table:

SELECT
   DateID,
   CustomerID,
   ProductID,
   RegionID,
   SUM(Amount) AS TotalAmount,
   SUM(Quantity) AS TotalQty
FROM ERP_Sales
GROUP BY DateID, CustomerID, ProductID, RegionID;

8. Relationship Design (Wipro Standard)

• One-to-Many (Dimension → Fact)
• Single direction filtering
• No bi-directional relationships unless necessary
• Avoid Many-to-Many relationships
• Use bridge tables where required

9. Star Schema Example (Wipro Template)

          DimDate
             |
       DimCustomer      DimProduct
              \         /
               \       /
               FactSales
                 |
             DimRegion

10. Wipro Interview-Ready Short Answer

“To convert a normalized ERP schema into a Power BI star schema, I identify facts, create denormalized dimensions, replace composite keys with surrogate keys, flatten ERP lookups, avoid snowflake schemas, and design single-direction one-to-many relationships. I optimize fact tables by reducing cardinality, removing unused columns, and ensuring correct data types. The result is a highly compressed, fast, and enterprise-ready star schema suitable for Wipro-level BI implementations.”

11. Conclusion

Converting an ERP dataset into a star schema is one of the most essential BI modeling skills Wipro expects. By following the steps in this guide — identifying facts and dimensions, flattening ERP tables, optimizing keys, designing clean relationships, and adopting a proper star schema — you will build high-performance Power BI models ready for enterprise analytics.