Setting up my model

[magnusks]

Hi all, 

I'm struggling a bit with setting up my datamodel properly. 

About the data: 

I have one big flat file (SOURCE below) that I want to split into dimensiontables and one fact table. To make it easier to get my question across, Im only listing a couple of the EmployeeID's and have excluded the coloumns that are not relevant for this question. 

The file contains data of employees and amount of hours they have been working on various projects. The projects are assigned an activity code, however, the same project can have different activity codes based on the department that is issuing the invoice for the amount hours worked on the project. However, the employee can also be working on a project with two different acitivity codes from the same department (the acitivty codes are NOT exclusive to a single department). 

My approach to this was to start by duplicating the fact tables multiple times, and start to trim them down into dimension tables. I created one for employees, one for activity, one for projects and one for department. However, I started to run into problems when I started to merge the queries back to my fact table using Keys (index's). Since the employee can be listed several times, but for different departments, and or for several projects and or several activities. 

I handled the department dimension by snowflaking out the department-dimension from the employee-dimension using a 1-* where the 1-side was the department-dim and the "many" was the employee-dim. I the created another key in the employee-dim with a 1-* to the fact-table. So far so good (see picture below)! 

When I now tried to merge the querie from the project-dimension to the fact-table I struggled, since the project is related to both the activity and to the department issuing the invoices. If I simply try to merge I get many duplicates of rows in my fact table. I try to work around this by merging queries from the project table to the fact table based on both acitivty and department (in addtion to the project-key) but (as expected) when I deleted these coloumns from my project-dim, the query in the fact table failed. 

Does anyone know if there are some steps Im not doing, or if there are some branching out from my dimension tables that I should be doing to overcome this? This is a table that I will be updated each month, and the plan is to expand to include data from more data sources. Im not sure if there is an easy way to fix this, or to explain a potetional solution in a forum-post, but I'd be greatful for any tips.

I have included a picture of how I imagine the fact table (simplified) to look in the end. 

Thanks,
M

[magnusks]

Hi @Anonymous, 

Thank you very much for your reply. I will follow your tips and advice and see if there are anything I can do to improve my model.

Thanks again,

Magnus

[Anonymous · Accepted solution]

Hi @magnusks 

When setting up a data model in Power BI, make sure that you are correctly handling and associating dimension and fact tables.

Based on your description, you seem to be doing this correctly, but you are experiencing some issues when merging queries.

This could be due to a many-to-many relationship between different tables, or keys not being used correctly during the merge process.

You may want to consider the following points:

Make sure that each dimension table has a unique primary key.

Use foreign keys in the fact table to refer to the dimension table's primary key. This way, you can associate the fact table with each dimension table through these foreign keys.

If you find duplicate rows after a merge query, this may mean that you need to rethink your key and relationship settings.

Use Power BI's Query Editor to clean and prepare data. You can use the Merge Query feature in the Query Editor to merge tables, but make sure the merge is based on the correct keys.

Consider creating bridged tables. If you have a many-to-many relationship, such as an employee who can work in more than one department, you may need to create a bridging table to solve this problem.

Depending on your problem, you may need to create a bridge table with projects, activities, and departments so that you can associate these dimensions with the fact table without creating duplicate rows.

Regards,

Nono Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.