Relationship between two tables with duplicates IDs

[guerrez]

Hi, I have seen many posts about the topic, though I am still stuck. 

I have two tables both containing readings of IDs from two different machines, AES and OSP. Tables are called DM_AES and DM_OSP.

Both the tables contains readings of IDs that are unique codes, in both tables the colomn ID contains duplicates since these ID can be read more times on different time and date.

Prior making an operation between the two tables, I need to relate them. I understood the best way to relate the tables is using IDs since they are unique codes. Since both tables contains duplicates of IDs, I created a third table, containing the full list of all the possible IDs without duplicates (read it how on a different post):

I made then relations One to Many with drag and drop in the Model view to the third table. I did not get any warnings and it looks correct to me:

To verify it I created a table to show the counts of IDs of AES and OSP per day:

As you see above, I used the dates coming from AES. The counts of IDs from AES are correct, but the counts of OSP are not filtered by date or Citycode returning all the time the same value, 9793.

I did a second test: I used the dates coming from OSP. I get now correct values of ID counts from OSP: 

 But when I try to add the IDs of AES I get an error:

I would have expected that it does not matter which "date" I use (from AES or OSP) since the tables are now related, but that is not the case.

The tables looks not related yet to me. What am I doing wrong? Thank you for the support!

[DatawithDinesh]

In your example the tables are related with the unique ID table that you created. But they are no where related to each other. If you use date from one table then the other table won't recognize it. If both are fact table then I don't see any reason to relate them using ID. In your case you should just add a date table and relate them both with the date table, that way you can use the date from the date table and get the counts from both the table.

[guerrez]

@DatawithDinesh  just tried:

Third table is now referring to dates:

Model relations:

Now I see the counts of ID for OSP are recognized by the date (thank you!) but they are not recognizing the division per Citycode (always the same value). Even though Citycode is present in both the tables.

Should I add relations also for Citycode then? That means a fourth table?

[DatawithDinesh · Accepted solution]

Yes you can create a City Code table and relate it with the two tables. But I would suggest first you examine your requirement on what fields would be slicing/filtering the two tables and then create a dimension table having all the unique values. For example you can create a unique table having ID, Citycode, State etc. and relate the table with ID to both the tables. Now when you slice and dice use the fields that is available in the new table.

[guerrez]

Thank you for the reply. I created the extra tables to create relationships: date, citygroup and ID. It is indeed working!  (for the moment ID relationship is not needed, but I already created it)

As you have seen I created separate calculated tables. I understood I cannot group calculated tables even though it would be the most easy-to-read solution. So I think you propose to use a Composite Key with one only bridge table?

[DatawithDinesh]

Yes. It would be better if you can create a single table with a Primary Key, City, state etc and then relate this table to other two tables using the primary key. This way you will reduce your model size and also you should be able to handle the data easily.