Data Shaping for Maximum Efficiency

[Nashville1986]

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.?

[vigneshba]

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