Forum

Forums
Power BI
Q&A
Infosys: How Do You...
 
Share:
Notifications
Clear all

Infosys: How Do You Design a Star Schema in Power BI When the Source Data Is Fully Normalized in an OLTP System?

 
Q&A
Last Post by Anonymous 4 months ago
1 Posts
1 Users
0 Reactions
111 Views
RSS
Posts: 5
 Kalyan
Topic starter
Nov 18, 2025 3:12 am
(@Kalyan)
Joined: 4 months ago

Infosys: How Do You Design a Star Schema in Power BI When the Source Data Is Fully Normalized in an OLTP System?

This is one of the most frequently asked questions in Infosys Power BI, Data Analyst, and BI Developer interviews. The interviewer wants to evaluate your understanding of data modeling, dimensional design, and how well you convert transactional source systems into analytical structures suitable for reporting.

✅ What the Interviewer Wants to Hear

Infosys works heavily with enterprise-level clients where the backend systems are usually OLTP databases such as SQL Server, Oracle, SAP, or MySQL. These systems follow high normalization and are not optimized for reporting. Your job in Power BI is to convert this normalized data into a clean, scalable, and fast-performing star schema.

📌 Step-by-Step Process for Designing a Star Schema

1. Understand the Business Process First

Before designing any schema, identify the primary business process such as:

  • Sales order processing
  • Inventory movement
  • Customer transactions
  • Financial reporting

Clearly define what metrics or KPIs the report will measure. This forms the foundation for building the fact table.

2. Identify the Fact Table

A fact table typically contains:

  • Transaction-level data
  • Foreign keys to dimension tables
  • Numeric, aggregatable measures (Amount, Quantity, Profit, Time Spent, etc.)

Example fact table: FactSales


FactSales
-----------
SalesID
OrderDate
CustomerID
ProductID
Quantity
SalesAmount
Discount

3. Build Dimension Tables

Unlike the normalized OLTP structure, dimensions in a star schema must be:

  • Denormalized
  • Descriptive
  • Human-readable
  • Slowly changing where needed (SCD Type 1 or 2)

Common dimensions include:

  • DimCustomer
  • DimProduct
  • DimDate
  • DimGeography
  • DimEmployee

4. Flatten Normalized Tables

An OLTP system may have tables like:


Customer
CustomerAddress
CustomerContact
CustomerSegment

These should be joined into a single dimension table in Power Query or SQL view:


DimCustomer
-----------
CustomerID
CustomerName
Email
Phone
City
State
Country
Segment

5. Create Relationships (One-to-Many)

In the star schema:

  • Dimensions sit on the “one” side
  • Fact tables sit on the “many” side
  • Relationships are single-direction (from dimension → fact)

This ensures better performance, predictable DAX behavior, and faster refresh.

6. Optimize the Data Model

  • Remove unused columns
  • Use surrogate keys instead of natural keys
  • Enable date table with proper hierarchy
  • Disable auto date/time
  • Ensure data types are correct (very important for Infosys interviews)

💡 Simple Interview-Friendly Answer

“When the source data is fully normalized in an OLTP system, I first understand the business process and identify the fact table that holds the core transactions. Next, I build denormalized dimension tables for customers, products, dates, and other descriptive attributes. I flatten the normalized tables using Power Query or SQL views and ensure the relationships follow a proper one-to-many structure with single-direction filters. Finally, I optimize the model by removing unnecessary columns, defining correct data types, and ensuring a clean star schema for fast query performance.”

💬 Why Infosys Asks This Question

Infosys works with global clients where reporting systems must handle millions of rows and strict performance requirements. They want to validate whether you can design efficient, stable, and scalable data models that work in enterprise analytics environments.


Leave a reply

Author Name

Author Email

Title *

 
Preview 0 Revisions Saved
Forum Jump:
  Previous Topic
Next Topic  
Share: