How to manage over constrained data model relationships?

[s1]

I am using snowflake schema, but in this one area of my data, I am unsure what to do.

My fact table of events is being filtered by various dim tables, two of these being Users and Projects. However, both Users and Projects have an associated Company, and so the Companies dim table filters both of these.

From events, I would mainly be counting rows (as actions), which could be a measure (in which case I would have USERELATIONSHIP, and there would be no issues). However, sometimes I want to group by action type or have the first/last action and the duration of a project, or other things like that.

Because of this, I cannot have all relationships enabled. I guess this makes sense, because I would be overconstraining my filtering if there is an inconsistency if the User and Project for an event did not have the same company.

Is the problem with my attempted data structure? Is my goal misguided? Or is there a tool/function that I am missing?

Thanks in advance!

[Jeanxyz]

Why both User table and Project Table are filtered by Company table? This is ok if  the User A and Project A are always assigned to Company A (case 1), but there will be a problem if User A is assigned to Company B, Project A are assigned to Company A (case 2). In that case, if you slicer chooses User A, and Company A, there will be not Action/Events pass the filter.  Is this what you want?

So what's the logic between User-Company and Project-Company? If case 2 is true, maybe it's better to create two company tables, dim_user_company, dim_project_company and link them to User table and Company table respectively. 

[s1]

It is a fair point. There are a few edge cases where one company does work for another project. I was planning to avoid the case by filtering those out, and having a separate report about those cases. But I guess it would be good to learn best-practices for not havign to do work-arounds like that.

[s1]

Ah, I guess this makes me realize there is another piece of information to share.

The way we often use the data, is we will drilldown by company into a company page. From here, we will look at actions/events, and sometimes want to filter by project, or sometimes want to filter by user (both being tables available on the page as well)

[Shahfaisal]

---

@s1 wrote:

The way we often use the data, is we will drilldown by company into a company page. From here, we will look at actions/events, and sometimes want to filter by project, or sometimes want to filter by user (both being tables available on the page as well)

---

This should not be a problem at all. You can filter by one dimension or all the 3 dimensions at the same time - this is the beauty of a dimensional model

[bcdobbs]

You should still be able to do that. If all projects have events the relationship between your dimensions is automatically held in your fact table.

At worst you describe the relationship between company and project in a second fact table related to the same dimensions.

Having a measure like the following to act as a visual level filter on slicers helps:

Slicer Filter =
INT ( NOT ( ISEMPTY ( FactTable ) ) )

If you're stuck I'll mock a demo up but will be late tomorrow before I get to it.

[s1]

Ah, I guess I never fully appreciated that things filter backwards - I always assumed that the relationship direction meant that filters could only apply that way.

So when adding the Company Id to the fact table, I filter the company dim table, it filters the event fact table, which in turn filters the user and project dim tables. It works!

I guess the only remaining thing to sort out is how to show users or projects with 0 actions that are associated to a company. I guess I could add a set of rows to the fact table for each user being created, and leave the action row null or something as it relates to counting actions?

[bcdobbs · Accepted solution]

I think you'd find life a lot easier if you could put the CompanyId on your events fact table. That way you have three dimensions (User, Project and Company) that describe the grain of your event.

[Shahfaisal]

Here are two options for you:

1. Modify your dimensional model so that you have the company id as a surrogate key in your Events fact table. Then you will be able to link the company dimension to your fact table directly. This is my recommendation. 

2. Add company information (company name, revenue, etc.) to your project and user dimension tables. Get rid of the company dimension table from Power BI model. This  is not what I recommend but go for it only if you can't implement option #1.