Forum Discussion
Best Practice - Factless Fact Table, Bridge or Something else?
Hi everybody
since this is quite a long question, lets try to structure it a little bit
Introduction
I've been building data models and DWHs for quite some time, but I would like to know your opinion about a special use case and how to model it in Power BI.
We have got a pretty big dimensional model already, however one new requirement really got me thinking. So this is one simplified version of our model
We've got projects, we've got employees working on these projects and we've got times for this (and much more in RL, but as I said - simplified). Now problem, standard Star I think.
Problem
But now we have got a new requirement. Every project has responsibilties. These could be things like Project Enabler, Project Manager, Sales Manager. Each project can have each role more than once or not at all, each employee can be in several projects. Each of these people are already in the Employee table.
My question now: What would be the best way to model this into my reports, to keep it clean and consistent and of course enable my users to filter Project Role , but also see things like "what role do people of my department have?".
Approach 1 - Factless Fact
Make Project Roles a Fact Table, connected to Projects and Employees
I don't like that I would have to use bi-directional filtering to enable users to filter by Role - Dim Employee now has to paths to filter Project Times. My tests seem to work fine, however I don't understand it completely - is the some Power BI magic working here?
Approach 2 - use a bridge table (kind of)
I created a reference to my Employee table and used my Project Roles as a bridge table
This seems to be the textbook approach (at least from some of the books I read). However, it bloats up my model by "snowflaking" it. Also, to make it perfectly valid, I would also need to have a dimension table for Project Roles, so I can hide this bridge for my users.
Approach 3 - Something else
My third approach would be to create a table called roles, which first of all references my employee table, but then merges with my old Project Roles
This way, still every information from Employees is still available (like filtering by department), but I have got one less table than in the Bridge Approach. However, it is still a Snowflake and therefore not as elegant in my opinion.
Other thoughts
I was also thinking about using the roles directly in my Dim Projects and then switching to m:n relations to every related fact table. However, I think this would bring more problems
Another thought was to pivot the project roles, so I can use [Sales Manager], [Project Manager] as columns. However, since each role can be filled more than once in each project, this would not help with my problems.
However, thank you already for working through this wall of text. It would be cool if you could give me your thoughts and maybe also some refrences.
Best regards
Patrick
2 Replies
- CNENFRNL
Community Champion
As to me, factless fact table fits well in the datamodel; but I never use bi-directional relationship to avoid unexpected ambiguity. I always resort to expanded table to propagate filtering from * side to 1 side.
- AnonymousNot applicable
Thank you. An expanded table would propably mean for me, that I have to create surrogate keys in my fact tables to connect to respective Dimension 'Project & Project Roles' - however, since the data model is a little bit more complex in real life, I cannot see this happening