Relationships between Fact tables

[brokencornets]

I'm looking for a bit of advise on how to structure a model that seems, logically, to have multiple fact tables.

Table one: Tasks (most granular level of detail)

Table two: Work Orders (which can be made up of one or more tasks)

Table three: Jobs (which can be made up of one or more work orders)

Then various dim tables - trade, location, date, etc...

Reporting will be mostly at 'Job' level but would like the ability to drill-through to see the underlying Work Order/Task level data.

I appreciate the detail is a bit sketchy, but if anyone has any experience of working with similar datasets or useful links that would be great. Everything in Google just tells me to avoid relationships between fact tables, but they're related by their very nature.

[brokencornets]

Hi - just thought I'd bump this up to see if anyone has any further advice as I'm still a bit stuck.

In terms of how I've currently structured the data, here is a very simplified version of the Data View:

Everywhere I read tells me this isn't the right thing to do, having these 4 fact tables directly related to each other, but I can't work out how else to do it other than create a giant fact table - which I have tried, but performance of measures is hugely affected as I'm having to use DISTINCTCOUNT rather than COUNTROWS.

[Anand24]

Hi @brokencornets ,
It would be much better if you can tell what fields are present in all the tables.

Ideally, you should connect Dimension tables to Fact Tables in uni-directional filtering in which Dim table would filter fact table.
Example:
1. Dim Table with "JobID" should be joined with Fact table with Jobs in which you have "JobID".
2. Join Date Table to Date fields in all fact tables. Use Date Table's Date in slicers, charts, graphs, etc.

Give a Thumbs Up if this post helped you in any way and Mark This Post as Solution if it solved your query !!! Proud To Be a Super User !!! LinkedIn

[brokencornets]

Thanks for the advice. Are you saying I should create a 'Jobs_Dim' table holding all Job IDs which would link to both 'Jobs_Fact' and 'WO_Fact' tables?

[Idrissshatila]

Hello @brokencornets ,

its better to work it as fact and dimensions  as the star schema data model 

https://learn.microsoft.com/en-us/power-bi/guidance/star-schema

and when the dimensions that are related to all three tables are linked to them, you could easily visualise the values from the three tables alongside each others by the dimensions.

another thing, and this if it's feasable, you can combine the fact tables with each others using merge

check it out https://learn.microsoft.com/en-us/power-query/merge-queries-overview

If I answered your question, please mark my post as solution, Appreciate your Kudos 👍

Follow me on Linkedin
Vote for my Community Mobile App Idea 💡

[brokencornets]

Thanks for this. I've tried merging all four fact tables (jobs, WOs, tasks, labour) together and while it works, it seems to be much slower in terms of performance - presumably this is because I'm having to use DISTINCTCOUNT in my measures rather than COUNTROWS? Or is it just because of the size of the new table?

For instance, a measure I had for counting oustanding jobs was as follows:

Outstanding =
CALCULATE (
COUNTROWS ( 'Jobs' ),
FILTER (
'Jobs',
( 'Jobs'[Date Reported] <=LASTDATE('Date'[Date])
&& ('Jobs'[Date Completed] > LASTDATE('Date'[Date])
)
)),REMOVEFILTERS()) +
CALCULATE (
COUNTROWS ( 'Jobs' ),
FILTER (
'Jobs',
( 'Jobs'[Date Reported]<=LASTDATE('Date'[Date])
&& (ISBLANK('Jobs'[Date Completed])
)
)),REMOVEFILTERS())

And I've replaced it in the new star schema model with this:

Outstanding =
CALCULATE (
DISTINCTCOUNT('Fact'[Job Number]),
FILTER (
'Fact',
( 'Fact'[Job Reported Date] <=LASTDATE('Date'[Date])
&& ('Fact'[Job Finish Date] > LASTDATE('Date'[Date])
&& not(ISBLANK('Fact'[Job Number]))
)
)),REMOVEFILTERS()) +
CALCULATE (
DISTINCTCOUNT('Fact'[Job Number]),
FILTER (
'Fact',
( 'Fact'[Job Reported Date] <=LASTDATE('Date'[Date])
&& (ISBLANK('Fact'[Job Finish Date])
&& not(ISBLANK('Fact'[Job Number]))
)
)),REMOVEFILTERS())

and it works, but it's 10x slower when sorting tables, etc. How can I get around this?