Data Modeling Approach Question | Consultant to Client Model

[Anonymous]

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!

[DataInsights · Accepted solution]

@Anonymous,

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.