topic Re: Several references to same dimension table, avoiding circular references in App Development

Several references to same dimension table, avoiding circular references

FernandaNava — Sat, 16 Nov 2024 01:23:33 GMT

Say I have a fact table with several fields, among which there are columns that represent actions done by employees at different stages of the process.

Transaction	Date	Type	ReceivedBy	ProcessedBy	DeliveredBy
1	15/07/2020	A	58	78	63
2	16/07/2020	A	63	24	22
3	17/07/2020	C	58	27	21
4	14/08/2020	B	21	26	58
5	12/07/2020	D	24	21	79
6	18/09/2020	B	78	63	41
7	19/09/2020	D	63	78	26

These are all the same employees contained in a table:

Employees	Name	LastName
21	Juan	Diaz
22	Luis	Smith
24	John	Rodriguez
26	Robert	Lopez
27	Sam	Black
41	Peter	White
58	Tony	Rivera
63	Anna	Johnson
78	Mary	Diaz
79	Lucy	Carter

How to reference the table? I tried doing:

Employees	Name	LastName	ReceiverID	ProcesserID
21	Juan	Diaz	21	21
22	Luis	Smith	22	22
24	John	Rodriguez	24	24

Transaction	ReceiverID
1	58
2	63
3	58

Transaction	ProcesserID
1	78
2	24
3	27

But this created circular referencing issues. Any suggestions would be very appreciated.

Re: Several references to same dimension table, avoiding circular references

Taoufiq_Zarra — Thu, 24 Sep 2020 14:38:20 GMT

@FernandaNava if I understood correctly, you need to add employee information to Fact table ?

Mayebe like this :

MappingName: mapping load Employees, Name&'_'&LastName as Nametmp INLINE [ Employees, Name, LastName 21, Juan, Diaz 22, Luis, Smith 24, John, Rodriguez 26, Robert, Lopez 27, Sam, Black 41, Peter, White 58, Tony, Rivera 63, Anna, Johnson 78, Mary, Diaz 79, Lucy, Carter ]; Fact: LOAD Transaction, Date, Type, ReceivedBy, subfield(applymap('MappingName',ProcessedBy),'_',1) as NameProcessEmployee , subfield(applymap('MappingName',ProcessedBy),'_',2) as LastNameProcessEmployee , subfield(applymap('MappingName',DeliveredBy),'_',1) as NameDelivereEmployee , subfield(applymap('MappingName',DeliveredBy),'_',2) as LastNameDelivereEmployee INLINE [ Transaction, Date, Type, ReceivedBy, ProcessedBy, DeliveredBy 1, 15/07/2020, A, 58, 78, 63 2, 16/07/2020, A, 63, 24, 22 3, 17/07/2020, C, 58, 27, 21 4, 14/08/2020, B, 21, 26, 58 5, 12/07/2020, D, 24, 21, 79 6, 18/09/2020, B, 78, 63, 41 7, 19/09/2020, D, 63, 78, 26 ];

output:

Re: Several references to same dimension table, avoiding circular references

FernandaNava — Thu, 24 Sep 2020 15:03:18 GMT

Yes I think this would work! Just a quick question: do I have to load an Employees table before the "Mapping Name" one, or would this be the first time I'm loading this data? What if the Employees table had more columns such as "type" and "username", can I just load them "normally"?

Also, I assume you did the subfield part to keep Name and Last Name separated, but I'm guessing I could just have one field with the full name and skip this part?

Thank you!

Re: Several references to same dimension table, avoiding circular references

Taoufiq_Zarra — Thu, 24 Sep 2020 15:14:36 GMT

@FernandaNava No the first time you load EMployee table is in the mapping section (One time in Mapping section)

to add other information for me i prefer using mapping and subfield like for example if you want add type and username, you can do :

MappingName: mapping load Employees, Name&' '&LastName&'|'&type&'|'&username as EmplyeeName INLINE [ Employees, Name, LastName,type,username 21, Juan, Diaz,a,z 22, Luis, Smith,z,e 24, John, Rodriguez,r,r 26, Robert, Lopez,t,e 27, Sam, Black,t,c 41, Peter, White,y,u 58, Tony, Rivera,d,d 63, Anna, Johnson,g,h 78, Mary, Diaz,az,rfef 79, Lucy, Carter,lkj,rr ]; Fact: LOAD Transaction, Date, Type, ReceivedBy, subfield(applymap('MappingName',ProcessedBy),'|',1) as NameProcessEmployee , subfield(applymap('MappingName',DeliveredBy),'|',1) as NameDeliveredByEmployee , subfield(applymap('MappingName',ProcessedBy),'|',2) as typeProcessEmployee, subfield(applymap('MappingName',ProcessedBy),'|',3) as usernameProcessedByEmployee, subfield(applymap('MappingName',DeliveredBy),'|',2) as typeDeliveredByEmployee, subfield(applymap('MappingName',DeliveredBy),'|',3) as usernameDeliveredByEmployee INLINE [ Transaction, Date, Type, ReceivedBy, ProcessedBy, DeliveredBy 1, 15/07/2020, A, 58, 78, 63 2, 16/07/2020, A, 63, 24, 22 3, 17/07/2020, C, 58, 27, 21 4, 14/08/2020, B, 21, 26, 58 5, 12/07/2020, D, 24, 21, 79 6, 18/09/2020, B, 78, 63, 41 7, 19/09/2020, D, 63, 78, 26 ];

output :

Re: Several references to same dimension table, avoiding circular references

FernandaNava — Thu, 24 Sep 2020 15:24:29 GMT

Ok, I understand! It's like a more agile way of doing a join.

Thank you!

Re: Several references to same dimension table, avoiding circular references

Taoufiq_Zarra — Thu, 24 Sep 2020 15:29:15 GMT

yes@FernandaNava "don't join-use Applymap instead" as HIC said