Many-to-many relation or distinct data selection

Hey,

In addition to @devin.tiemens :
First Union the tables by dragging the first table (let's say master table, the best table if you will) to the DSA (transformation) data area. Then smart synchronize (drag the second table to the table you've just created, with the right mouse button) the two tables. Now you;ve got all the data from both tables in this table. Make sure there is no primary key selected (I mean, you could and that would also de-duplicate the table), but this is not a very ‘nice’ way to do it. After both tables are unioned, you can aggregate the way Devin is sugestion here above.

Although this is next option is not my preferred option:
You could write a view unioning the two tables in a CTE and then in the next SELECT use the SELECT DISTINCT and join the second table to the first. Then maybe use COALESCE to get rid of the empty values

Hope this helps

= Daniel

that made sense .. I’ve tried that (and it was pretty easy) and I can make the relationship from both tables to the aggreated table .. now a new question comes in mind .. will this Aggregated table automatically be updated with values, when new data is added to the “parent table” (I don’t know what the correct term is) ?

I’m not sure I got this process .. If i do what you suggest, will I not just make a relation between the 3 tables then ?

If you schedule the reloads, it will automaticly update the tables in the correct order. TimeXtender knows the data lineaga and by means of that, it is able to load the tables in the right order.

Perfect .. thanks so far!

No problem!

Hi,

apart from the technical side of how to achieve a certain goal in TimeXtender, don't forget to think about the business process side of things. This will lead to the cardinalities between entities and guide you into what you want and can or cannot do.

In your example, I am not sure what problem you are facing: there are no duplicates in either of your example tables. I would expect an Invoice to be able to cover multiple items, and I would expect an Item to be linked to many invoices as a general concept. The question is what the business need is behind the insight you are trying to deliver and subsequently whether your data system can actually deliver that insight. Given a certain business need to can define a 'grain’ or level of detail and use that to guide aggregations required. I.e. an invoice-centric view may start from transactional data related to invoices whereas a purchasing-centric view will likely start from a different transactional table. Knowing the model your source uses allows you to discover what needs to be linked to what, and more importantly: what cannot be directly linked. If you take the “I have this data, so I can do this” approach, you will likely face a couple of relation reversals or circular references in your future. 

In your specific examples (apart from what you are trying to achieve in general) I would want to know why the VendorNoandExternalNo combination seems to link to a different ExternalNo than the ExternalNo for a given record for instance. That should clue you into what the actual business keys are. If the Invoice table is the only source for an Item description (strange in my opinion), you may need create a lookup from an aggregate of the Invoice table into the Item table. You do need to be sure that that is what is required though. Is this Dynamics 365? In that case you will likely need to load certain tables from the ODX to DWH multiple times to achieve different enrichments to avoid a lot of custom code which is hard to maintain.