Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.
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.
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