Forum Discussion
Chaining together dimension/fact tables
Community ChampionCan you share some example data from each table?
From your description they sound more like Fact tables. Eg each row describes an "event". You'd then have things like batch as a dimension along with a calendar table to link the fact tables.
I unfortunately can't share a lot of details about the specifics of the data.
It is an agricultural process, so one of my tables I was calling a dimension is for the fresh harvested plants with some attributes like harvest weight that are added to each record in the table when it is created. However, it also has attributes that are entered from laboratory data after the fact. This is where my confusion comes in: each WIP batch is kind of an "event" as you suggested as they are created through the process but is incomplete.
I had thought about having a generic facts table with just numerical results that are potentially related to each step of the process, but this would violate referential integrity (empty foreign keys) and would have hundreds of empty values for irrelevant tests. Another approach I suppose would to have fact tables for each WIP dimension but am not sure that is a good practice.