Merge two fact tables where common fields are not unique

[Murlimes]

Hi,

whats the best way to connect two fact tables without duplicating the data?

I have one table from a time tracking system, which i linked with a calender and it shows me various data about the employee on daily basis (i.e Date, dept,activity, hours,yearmonth, weekday, yearweek etc). Now i want to merge it with a table which contains employee data on a monthly basis (ie. Sales Revenue from different task , so multiple entries per employee).. Is this possible, since i assume it would duplicate al lot of data, trying to merge to tables where the common field in neither is unique? Do i need another common field, or a link table, or transform one table so it fits the other table?

Also the Sales Revenue is catogerized per Month, but timetracking table data is per day. So i somehow would need to merge the tables in a way that i still can calculated monthly hours  in the first table, but have the Sales Revenue data avaiable (one value for a whole month, in fact multiple values since there a multiple sales revenue drivers

Thank you.

Murli

[marcus_sommer · Accepted solution]

Within the most scenarios is the easiest way to merge fact-table just to concatenate (union in sql) the tables by harmonizing the field-names (whereby asynchron structure with NULL's are not a problem) and the essential data. To be able to differentiate between them an extra 'table 1' or 'table 2' as Source field could be added.

In your case it sounds that the data are identically unless the granularity of daily vs. monthly and the above mentioned method should be working well, for example by making a date from the month with something like: makedate(year, month) as date.

[Murlimes]

Thanks, that what i was thinking too. I just added a date field to the second table and put in the first of the month as value for each month. The linked calendar handles everything correct and all expressions in the pivot also work fine.

Murli