Data model design - many to many relationship?

[ReadTheIron]

Hello! This is more of a big-picture question. I have the chance to design how data is entered for a report, and I want to make it as robust and simple to work with as possible.

I have two tables: RepairTable and FailureTable. The FailureTable records every time any asset fails. The RepairTable is a list of selected problem assets (generated manually) and the dates they're inspected and repaired. I had these tables set up in a one-to-many relationship (RepairTable to FailureTable), and could use that to answer questions like "How many times has Q1-701 failed in the last year?" or "Has Q1-703 failed after its repair date"?

Now we want to go into a second repair cycle, and we've identified a new list of problem assets to inspect and repair. But what do we do about assets that appear on the first as well as the second list?

We could retire the first list altogether and just operate off the new list. All of the existing visuals would continue to work. However, I would then have no way to answer questions like "How many times has Q1-702 been repaired"?

We could add the new list to the same table as the old list and add a field to show which cycle an asset belongs to. However, this would break the one-to-many relationship with FailureTable. I don't know if the report can be made to work with a many-to-many relationship or not.

Are there other options I'm not thinking of? I would love to be able to toggle the report between "current list" and "all time" to answer questions like "which assets failed after being repaired" and "which assets failed in the last week".

[ReadTheIron]

Thanks! I ended up making the distinct asset table in DAX since I'm still shaky in PowerQuery, but it's working very nicely so far.

[ReadTheIron]

Thanks! This direction makes sense. What would be the advantages of using DAX over Power Query or vice versa? (For what it's worth, I have multiple RepairTables for different asset classes.)

[bcdobbs]

So in import mode there is no difference in the compression/performance of a table calculated in DAX and a table calculated in Power Query. That said conventional wisdom is to push as ETL steps as close to source as feasibly possible so I would tend to lean towards Power Query. (Note that calculated columns are not compressed as well as none calculated columns). If you go Power Query it also lets you separate the logic out into a dataflow which tends to make things more scalable.

In terms of multiple Repair Tables I would also merge it all into one and have a dimension that describes Asset Classes.

[bcdobbs]

I think I would create an Asset dimension which is just a distinct list of all assets. Can do it in PowerQuery or straight in DAX with a calculated table:

DISTINCT (
	UNION (
		DISTINCT ( RepairTable[AssestName] ),
		DISTINCT ( FailureTable[AssetName] )
		)
)

That can then have a 1 to many relationship to both your tables.

If you then had a custom date table which had a column that indicated repair cycle you could filter any way you need.

If you wanted to supply a set of data for each table I could try and mock it up if you needed more help.

[bcdobbs · Accepted solution]

Just thinking it through I might go further than this.

• Have 1 table which is a list of all distinct assets and include a column to flag problem assets.
• Comine your existing tables into a single fact table with: 

Date, AssetName, EventType

Where EventType is one of, Inspected, Repaired, Failed.

• Finally have a date table which contains columns flagging repair cycle.

Thay should then be very flexible.