Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Nashville1986
Frequent Visitor

Data Shaping for Maximum Efficiency

Hello!  I am new to Power BI and am in the process of developing dashboards around healthcare information.  I need guidance on best practices for importing data via SQL, particularly maximizing efficiency around data shaping.  After reading on article on star schema methodology it sounds like I should break up my data and not just import one or two large tables.  Let’s say for simplicity sake I have two tables, one with member information and one with claim information as follows:

Member table:

Policy ID

Member ID

Gender

Date

Resident State

Family Type

Agent Type

Product Type

Premium

12345

1

Male

1/1/2022

Texas

Husband and Wife

Broker

Dental Plan

$10.00

12345

2

Female

1/1/2022

Texas

Husband and Wife

Broker

Dental Plan

$10.00

12345

1

Male

2/1/2022

Texas

Husband and Wife

Broker

Dental Plan

$10.00

12345

2

Female

2/1/2022

Texas

Husband and Wife

Broker

Dental Plan

$10.00

12345

1

Male

3/1/2022

Florida

Husband and Wife

Broker

Dental Plan

$10.00

12345

2

Female

3/1/2022

Florida

Husband and Wife

Broker

Dental Plan

$10.00

12345

1

Male

4/1/2022

Florida

Husband, Wife and Child

Broker

Dental Plan

$10.00

12345

2

Female

4/1/2022

Florida

Husband, Wife and Child

Broker

Dental Plan

$10.00

12345

3

Child

4/1/2022

Florida

Husband, Wife and Child

Broker

Dental Plan

$5.00

 

Claims table:

Policy ID

Member ID

Claim Date

Claim ID

Claim Line

Claim Type

Procedure Code

Claim Cost

12345

1

02/17/2022

17854

1

Preventive

D1045

$17.52

12345

1

02/17/2022

17854

2

Basic

D9305

$29.31

12345

1

02/17/2022

17854

3

Major

D1700

$85.68

 

Let’s say that every row in the member table is unique for a given Policy ID, Member ID, and Date.  Should each attribute in the member table be split into its own table with their corresponding Policy ID, Member ID and Date?  And then I would join the tables in Power BI on these three values?

Similarly for the Claims Tables the Policy ID, Member ID, Claim ID and Claim Line are the unique identifiers.  Should I have a separate table for Claim Type, Procedure Code, Claim Cost, etc.?

1 REPLY 1
vigneshba
Frequent Visitor

Hi @Nashville1986 - You query is more about Data Warehousing your data with Star Schema. Yes it is advisable to create Star Scheme Model before push the data to Reporting enviroment like Power BI. Few advantanges of doing that.

 

  1. Your data size will be reduced
  2. Joining will be more optimised and search will be based on join rather than searching the values in each and every table
  3. Filtering will be more efficient if you need to apply filters on Dimensions

How we can achieve this? In this case, you are currently using 2 datasets ideally 2 tables. First member information, you mentioned Policy ID, Member ID, and Date combined together brings a unique record. You need to create unique column based on concatinating these columns and generate a sequence number for every new combination. so each and every new concatinating key now will have a new sequence number. Second step, whenever you have member information in another fact table or you need to lookup those values and assign respective sequence number to this fact table. So now fact table will have only member key column instead of all the information from the member table. So while you use these 2 tables in the data model, you will use this number key column for relationship.
Coming your other question regarding Should I have a separate table for Claim Type, Procedure Code, Claim Cost, etc.? Claim cost should be in Fact Table if it varies for every patient. Claim Type and Procedure code can be kept as Separate table if it has unique values for every Claim Type and Procedure Code independently.
But keeping Member Information as Separate dimension and using Member Information Key to Claim table is recommended.

Let me know if you need more details




Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Kudoed Authors