topic Link one-to-many using the load-script whithout changing name of the linked column in Connectivity & Data Prep

Link one-to-many using the load-script whithout changing name of the linked column

jakobjensen — Tue, 04 Jan 2022 13:40:53 GMT

I have three tables each consisting of an "id" and a "date" column e.g for Table 1 it is:

id_1 | Date_1

------+----------

1 2018-01-01

2 2018-02-28

Then I have a fourth table consisting of dates from 2018-01-01 untill to day (a Calender). I want to link the Calender (column is named "Date") to each of "date" column in the three other tables without changing their name.

How do I, when I don't use the loading wizard, specify which tables should be linked on which columns when they aren't named the same i.e link Date (table 4) onto "Date_1" (table 1) and "Date_2" (table 2) and "Date_3" (table_3)?

Re: Link one-to-many using the load-script whithout changing name of the linked column

marcus_sommer — Tue, 04 Jan 2022 14:07:12 GMT

In many cases it's the best to concatenate (union in sql) these (fact) tables and not to associate them. Now you would have one id-field and one date-field which could be easily linked to appropriate dimension-tables. To be able to differentiate between the tables you could add an extra-field like: 'table##' as Source.

- Marcus

Re: Link one-to-many using the load-script whithout changing name of the linked column

jakobjensen — Wed, 05 Jan 2022 08:38:54 GMT

In case I want to solve the problem as stated above - how do I do that? E.g how do I specify which columns to be linked, since I cannot use the wizard to drag-and-drop (I'm using the load-script)

Re: Link one-to-many using the load-script whithout changing name of the linked column

marcus_sommer — Wed, 05 Jan 2022 10:10:19 GMT

A linking happens always between fields with the same name - regardless in which tables those fields are. If there are not very simple datamodels else more complex ones with multiple fact-tables you need to rename all relevant fields. This means those which should be linked and also those which mustn't be linked. Otherwise you may create synthetic keys and/or circular loops and/or key's which doesn't fulfil their purpose. In many cases this couldn't be done with the wizard else you need to do it manually.

Usually are multiple fact-tables not be linkable to a single dimension-table because it ends often within circular loops. To avoid it you could give each fact-table his own dimension-table but it leads mostly to disadvantages by the usability. Hot loved in such cases is the approach to create a link-table between the fact-tables and the dimension-tables are then connected to this link-table. But it's neither trivial nor the best performing solution.

Therefore I suggest again to develop the datamodel in the direction of a star-scheme like above hinted.

- Marcus