Forum Discussion

skelecopter's avatar
skelecopter
Frequent Visitor
4 years ago

Chaining together dimension/fact tables

I'm working on modeling a manufacturing process. We have one main product, and I have tables for each step of the production process. Each row in each of these tables represents one work in process batch (a unique batch ID for each individual step), and attributes will be filled in as they are measured. I therefore believe each of these is a dimension table, since we aren't looking at test results as transactions. First question: is that sensible logic?

 

Second question: if I am taking each step as a dimension table that may be related 1:N or 1:1 between each other, do I still need bridging tables if none are N:N? Looking at the model/filter flow, I believe since I am bridging dimension tables, there are indirect N:N relationships being created, though I have only seen the bridging tables used in examples with N:N.

 

Thanks!

 

 

Need Help

5 Replies

  • bcdobbs's avatar
    bcdobbs
    Icon for Community Champion rankCommunity Champion
    4 years ago

    Can 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.

    • skelecopter's avatar
      skelecopter
      Frequent Visitor
      4 years ago

      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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi skelecopter ,

     

    Are all tables has same columns?
    Are the batch IDs of the same batch in different tables the same?

    For example:

    table step1:

    batch_IDs  details

    1                xxx

    2                yyy

    3                zzz

    table step2:

    batch_IDs  details

    1                aaa

    2                bbb

    3                ccc

    Please at least share the simple table structure and the expect result if I misunderstood your meaning.

     

    Best Regards,

    Jay

    • skelecopter's avatar
      skelecopter
      Frequent Visitor
      4 years ago

      Thanks for your reply. Each table has different attributes, so the goal would be to connect them in a chain along with the process to be able to drill across in a report to see the analytical results from the associated manufacturing steps, or drill down through some measures in the future.

       

      Attached is a generalized version of the process chain, starting from the top and working down. Each table will have several more attributes that I have omitted here for analytical results (5-6 each, so not too crazy) that will be entered after the record is created. All attributes in the attached screenshot are filled in at the time of record creation.