Multi-key & multi fact datamodel (with link table)

DawidDaw — Thu, 23 Dec 2021 13:18:51 GMT

Hello,

I'm working on a datamodel that has 4 dimensions and 3 fact tables. I tried using a link table to connect everything together but it seems that my model has some complications I'm unable to manage. Would any one of you be able to advize?

Draw.io of the datamodel below & attached:

Basically I have two issues I'm unable to code correctly in my script:

- How do I manage the two key fields on project (project_id & project_code)? I can't link project to my link table twice, and I can't link project directly to a fact as that would create circular references.

- How do I manage the composite keys? I need to be able to select on ref_date (actually, it's an "always one selected"), should I just add it to my link_key?

Any idea how I could fix this mess?

Thanks a bunch!

Re: Multi-key & multi fact datamodel (with link table)

marcus_sommer — Wed, 11 Nov 2020 09:38:09 GMT

Just concatenate (is union in sql) your fact-tables into a single-table by adding an extra [Source] field so that you could differ between the different sources. That they don't have an identically data-structure is mostly not a problem (and you may also add/adjust some of the missing parts maybe with a mapping).

- Marcus

topic Multi-key & multi fact datamodel (with link table) in Connectivity & Data Prep

Multi-key & multi fact datamodel (with link table)

Re: Multi-key & multi fact datamodel (with link table)