combine 2 facts into one?

My problem is relating to manufacturing. We make widgets. Each widget may have 0 to M tests performed on it. When we perform a test, we get a value back that is recorded. There are different types of tests. Each test type has "limits", meaning, we expect the test to return a value in a range.


Example:
We make a widget named ABC that weighs 20 pounds. We perform 2 tests on it, TestType1 and TestType2. TestType1 comes back with a value of 5. The limits for TestType1 are 3 to 8. TestType2 comes back with -0.5. The limits for TestType2 are -1 to 0.

I need to be able to query the widget, the test results and the limits. Currently, I done this by having a fact_widget table that tracks when the widget was made and information about it weight, links to relevant dimensions and so on. I have a fact_TestResult table that has the test result information including a link to dim_testType that contains the test type and limit information. Then there is a "bridge" table that handles the link between fact_widget and fact_testResult.I know that bridge tables are supposed to link dimensions to dimensions, so this bridge table may not be named appropriately.

I know this approach isn't ideal, but it works OK. I do run into problems when trying to aggregate widget weight information while including test result information.

I have considered merging fact_TestResult into fact_Widget. I would then have 10 columns to store the test results. Another field, TestTypeGroup_ID would then link to a new table (TestTypeGroup) that would contain 10 links to the dim_TestType to define which testType's are stored in fact_Widget. Each time a new combination of tests was run, we would insert a new row into TestTypeGroup

I don't know if this is an appropriate solution...or if there are better alternatives. Any ideas are appreciated.

You are right with two fact tables. To query the facts you need to perform two separate queries on each fact grouping on the common dimensions and then join the results of the two queries, this is called drilling across. The obvious one in this case is the widget dimension, which should be common between fact.widget and fact.testresult, but there are probably others.

You don't need a special bridge table, the dimensions common between the fact tables provide the 'bridge'. Bridge tables are a data warehouse feature, but mainly used to solve the problem of multi-valued dimensions. Which is slightly different to the scenario you describe.

Thanks for your feedback.

What I don't understand is that I don't currently have a dim_widget. In fact, I don't have any dimensions that can link the two facts at this point. I only can say that WidgetA (in fact_widget) is associated with TestResult X Y and Z (in fact_TestResult). Because I don't have a dimension to link the two facts is why I think I should combine them.

Are you saying I should create a new dimension called dim_Widget? If so it would have the same number of rows that fact_widget would have. This seems weird to me.

You might not need two physical widget tables. It depends on your DWH / reporting startegy, if everything is type 1 (i.e. updates only and no history tracking) then you can create two views over one physical widget table - Fact.Widget and Dim.Widget. If type 2 (history tracking) is a requirement you will need two separate physical tables, as type 2 dimension processing requirements are different to fact table requirements.

The guiding principle for fact table design is that if a process (widget build, widget test) are separate processes i.e. can occur independently then they should be represented by diffferent fact tables.

You almost definitely need a common widget dimension. What dimensions do you have currently?

OK, I think a light bulb went off. Right now, my dimensions that relate to fact_TestResult are specific to the test itself. I never had any of the dimensions relating to the production of the widget in Fact_TestResult, because I used the bridge table to go over to the fact table to get access to those dimensions.

I believe you are saying that I could take the same dimensions that I use on fact_widget and add them into fact_TestResult. For example, one of my dimensions on Fact_Widget is Dim_Equipment. This describes what machine the widget was made on. On Fact_TestResult, I currently don't have a reference to Dim_Equipment. I believe you are saying to add a reference to Dim_Equipment on Fact_TestResult. Then, I can query both facts separately using that equipment dimension, then join the results together using the dim_Widget.

Is this accurate?

I would place the dimensions from the Fact_Widgets onto the Test Fact table. You could also put the wieght of the widget on the Test Fact table and make it a degenerate dimension.