Microsoft Certified Power Bi Data Analyst Associate · Free Practice Question Medium
Question 2
Business Overview
NovaCare Health Analytics is a data-driven healthcare consulting firm that provides patient claims analysis, hospital performance tracking, and predictive insights for healthcare providers and insurance companies.
The company receives large volumes of structured and unstructured data from multiple hospitals, clinics, and insurance providers, making data integration and reporting a challenge. To improve decision-making and operational efficiency, NovaCare wants to implement a centralized Power BI dashboard that enables:
Trend Analysis on Patient Claims & Insurance Payouts
Operational Performance of Hospitals (KPIs on Treatment Efficiency, Costs, and Patient Recovery Times)
Predictive Modeling on High-Risk Patients using Machine Learning
Row-Level Security (RLS) for Role-Based Access (Doctors, Administrators, and Executives)
Automated Data Refreshing and API Integration for Real-Time Insights
Datasets & Schema
1. Patient Claims Data (Claims Table)
2. Hospital Performance Data (Hospital Table)
3. Patient Demographics (Patients Table)
4. Insurance Payments (Insurance Table)
Business Objectives & Key Challenges
1. Improve Financial & Claims Processing Analytics
Track total insurance payouts vs. claim amounts to identify reimbursement gaps.
Analyze claim processing times to optimize hospital operations.
Flag high-cost claims for auditing and fraud detection.
2. Optimize Hospital & Patient Performance Metrics
Compare hospital efficiency based on AvgTreatmentCost & AvgRecoveryTime.
Identify high-risk patients with chronic conditions.
Track patient satisfaction trends based on treatment effectiveness.
3. Implement Security & Role-Based Access (RLS)
Doctors should only see their hospital’s patient claims.
Insurance providers should only access claims from their policyholders.
Executives should have full dataset access for strategic decision-making.
4. Enhance Real-Time Data Integration & Reporting
Connect to external APIs for live insurance claim status updates.
Implement incremental refresh to optimize performance.
Develop predictive models for early detection of claim denials.
Question:
NovaCare's dataset consists of four key tables:
Claims Table (Tracks patient claims and payments)
Patients Table (Contains patient demographics and insurance details)
Hospitals Table (Stores hospital performance and location data)
Insurance Table (Contains insurance provider details and payments)
NovaCare wants to:
Link patient claims with their demographics to analyze treatment outcomes by age, gender, and insurance type.
Ensure each claim is correctly associated with the hospital that processed it.
Allow dynamic filtering by hospital, patient, and insurance provider.
What is the best way to structure relationships in Power BI?
-
A
Merge all tables into a single wide table in Power Query to eliminate relationships.
Combine Claims, Patients, Hospitals, and Insurance into a single table.
Avoid relationships and use direct column lookups instead.
-
B
Create a star schema with Claims as the fact table, linking it to Patients, Hospitals, and Insurance as dimension tables.
Claims (Fact Table)
One-to-Many relationship with Patients (
PatientID)One-to-Many relationship with Hospitals (
HospitalID)One-to-Many relationship with Insurance (
ClaimID) -
C
Establish a bidirectional relationship between Claims and Insurance to allow cross-filtering in both directions.
Claims → Bidirectional filter → Insurance
Claims → One-to-Many → Patients
Claims → One-to-Many → Hospitals
-
D
Use a Many-to-Many relationship between Claims and Patients since some patients have multiple claims.
Claims (Fact Table) → Many-to-Many → Patients
Claims (Fact Table) → One-to-Many → Hospitals
Claims (Fact Table) → One-to-Many → Insurance
Reveal correct answer
Correct answer: B
Explanation
Optimizing Data Modeling in Power BI: Star Schema and Relationships
Data modeling in Power BI is critical for ensuring efficient query performance, accurate aggregations, and intuitive filtering. The Star Schema is the most widely recommended approach for structuring relationships in analytical models.
Key Concepts in Power BI Data Modeling
✔ Star Schema for Performance Optimization
A Fact Table (central table) stores measurable transactions or events (e.g., Claims).
Dimension Tables provide descriptive information (e.g., Patients, Hospitals, Insurance).
One-to-Many Relationships between dimensions and the fact table ensure fast filtering and accurate aggregations.
✔ Avoiding Many-to-Many Relationships
Many-to-Many relationships can create ambiguous calculations, leading to unexpected results and performance issues.
In most cases, a bridge table or One-to-Many structure is preferred for clear relationship paths.
✔ Why Merging All Data into One Table is Inefficient
A single wide table increases data redundancy and negatively impacts storage efficiency and refresh performance.
Keeping tables separate yet related allows for better filtering, scalability, and model maintainability.
✔ Using Bidirectional Relationships Carefully
Single-direction relationships ensure clear filter propagation without causing performance slowdowns.
Bidirectional filtering should only be used in specific cases, such as role-based security or when working with bridge tables.
Comparison of Different Data Modeling Approaches in Power BI
Best Practices for Power BI Data Modeling
✔ Use a Star Schema with a clearly defined Fact Table (e.g., Claims) and Dimension Tables (e.g., Patients, Hospitals, Insurance).
✔ Ensure One-to-Many relationships for efficient filtering and performance.
✔ Avoid Many-to-Many relationships unless bridging tables are needed for complex joins.
✔ Keep relationships unidirectional unless bidirectional filtering is explicitly required.
✔ Optimize filtering by using proper index keys and relationship settings.
For further learning, refer to Power BI Data Modeling Best Practices and Understanding Relationships in Power BI.
By following Star Schema modeling principles, Power BI ensures fast, scalable, and accurate reporting, enabling users to efficiently analyze complex datasets while maintaining flexibility for future enhancements.
A.
Merging all tables into one wide table increases redundancy and reduces performance. Separating tables ensures scalability and flexibility in filtering without bloating the dataset.
B.
Using a Star Schema ensures that Claims acts as a central fact table, while Patients, Hospitals, and Insurance serve as dimension tables. This allows for efficient filtering, accurate aggregations, and optimal performance in Power BI.
C.
Bidirectional relationships can cause performance issues and ambiguous filtering. It is best practice to keep relationships unidirectional unless absolutely necessary.
D.
Many-to-Many relationships between Claims and Patients are unnecessary. Each claim is associated with a single patient, so a One-to-Many relationship is the correct approach.
Discussion
Think the marked answer is wrong, or have a better explanation? Share it below — comments appear after review.
