Forum Discussion
Having trouble with data model relationships
Riny_van_Eekelen thanks for your answer.
the SIOP data contains many fields - it’s a future forecast of demand by part number by plant with a total for each month. The same part number may appear 15 times. There is only one part number per manufacturer.
The EDI is a plant demand by part number file - it has monthly demand by part number by plant, but no manufacturer data. just like the SIOP file, the same part number can appear 15 times.
both of these files are generated by external teams - and my team needs to do monthly analysis and comparison of demand and forecast by part number, tech density, and supplier.
the reason I don’t have manufacturer in the SIOP file is I don’t generate it. I generated the master key as I’m new to learning data models and excel and figured out quickly you need a clean master key with no reoccurrences to align data sources.
would you mind explaining what you mean by can’t filter upstream? I had thought if i had a clean correlation by part number between tables any field could be correlated and that is my misunderstanding of how the data model works I guess! Thanks so much.
answering on my phone so I’ll try to add data examples in a bit.
JonS79 OK, I'll wait for your examples.
You wrote: "There is only one part number per manufacturer."
My question was "are there multiple manufacturers for a part number?" One manufacturer may of course supply multiple part numbers. That's no problem.
Riny_van_Eekelen Thank you for taking your time to answer my questions. OK, I'm on my computer now, let me try and clarify:
To answer the first question: there is only one manufacturer per part number, thankfully.
The first table, SIOP (24 month rolling customer demand forecast) looks like this:
It's basically a finance generated report of part numbers, descriptions, and monthly demand volumes, on a rolling 24 month forecast. This report has all of the detailed information - demand by plant, by project, by customer program. The part number (main identifier) can appear many times.
The second table, EDI (24 month plant order demand) looks like this:
This report is much simpler - it only includes part numbers and plants - it does NOT delineate by project, does not include manufacturer name, etc. I will note - plant codes are the SAME between the two files.
The end goal of this is to build a dashboard that will let me easily compare the two data sources, and report volume by part number, sliced by plant, manufacturer, and tech density.
It's very easy to add manufacturer to the memory master key file - and all the things I'm trying to do I can do very easily if I'm looking at ONE data source - either SIOP or EDI. The minute I try to slice or filter EDI by manufacturer (doesn't exist in SIOP), or even try and slice by SIOP by EDI plant code - it fails.
This doesn't make any sense to me - because it defeats the point of multiple tables so far, to me. If I need to create a master that contains everything, I guess I'm missing the point of the data model, or perhaps I'm just doing it wrong 🙂 Thanks for your help.
JonS79 If you only have one manufacturer for any product you don't need to worry about adding that info the the 'key' (dimension) table. You can use the TREATAS function to create what you could call a 'virtual' relationship between the other two (fact) tables. A bit difficult to explain without your own data and tables to work with. Can you share a file (Onedrive, Dropbox or similar) with some anonymized data. Then I or someone else can have a look at it.