cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
rzavgazaryan
Helper III
Helper III

Data Modeling Approach Question | Consultant to Client Model

Hello Power BI Experts,

 

Looking for some help on how to approach this project. I was given a flat CSV file with a small amount of data on clients and consultants in my firm.

 

On the far left, the first column contains client and 1 client can be listed multiple times to signify they are being serviced by one of our consultants. The the right of the first column are all attributes of the client (which again has duplicates)

 

On that same CSV file, there is a column for consultant name, and that shows all the consultant attributes (columns to the right), and of course shows which client they are assigned to. 1 consultant can be repeated multiple times because they are assigned to more than 1 client.

 

How do I correctly data model this. Considering the small volumn and small CSV file, do I just keep it the way it is? Is it fine as a flat file? Or is there a way to break this out into a data model and correctly be able to assign each consultant to a client? Not sure how to go about this, any thoughts would be appreciated.


Thanks!

1 ACCEPTED SOLUTION
DataInsights
Super User II
Super User II

@rzavgazaryan,

 

It’s best to model your data into a star schema, even if data volume is low. Power BI works best with a star schema, and it’s easier to create calculations. Also, if data volume grows, the star schema is scalable.

 

You can use Power Query to convert the CSV file into fact and dimension tables. To create the DimClient table, you can extract all client attributes into a separate table, and then remove duplicates. The same can be done for DimConsultant. You could create FactAssignment which associates clients with consultants, and any other attributes such as project name, start date, and end date. Once the tables are created, create relationships.

View solution in original post

1 REPLY 1
DataInsights
Super User II
Super User II

@rzavgazaryan,

 

It’s best to model your data into a star schema, even if data volume is low. Power BI works best with a star schema, and it’s easier to create calculations. Also, if data volume grows, the star schema is scalable.

 

You can use Power Query to convert the CSV file into fact and dimension tables. To create the DimClient table, you can extract all client attributes into a separate table, and then remove duplicates. The same can be done for DimConsultant. You could create FactAssignment which associates clients with consultants, and any other attributes such as project name, start date, and end date. Once the tables are created, create relationships.

View solution in original post

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

R2 (Green) 768 x 460px.png

Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.

Top Kudoed Authors