What Should be the proper desing for this case???? Please help.
I have a problem in designing fact tables and dimension. I design a fact table to have salesmanid and timeid and a meaure of sales amount.
The problem is the sales can be transferred to any unit oftenly. When I create a salesman table that contains salesman information and include department there. The summary of the sales amount by department can be wrong if the sales amount the salesman did when he belongs to deparment A, will be summarized to current department at this moment.
Time 1 Saleman A, Deparment D1, sales amount of 100
later he moves to Department D2
Time 2 Saleman A, Deparment D2, sales amount of 150
When summarized in cube, the sales of 100 will be added to Dept D2 which is wrong
I have a problem in designing fact tables and dimension. I design a fact table to have salesmanid and timeid and a meaure of sales amount.
The problem is the sales can be transferred to any unit oftenly. When I create a salesman table that contains salesman information and include department there. The summary of the sales amount by department can be wrong if the sales amount the salesman did when he belongs to deparment A, will be summarized to current department at this moment.
Time 1 Saleman A, Deparment D1, sales amount of 100
later he moves to Department D2
Time 2 Saleman A, Deparment D2, sales amount of 150
When summarized in cube, the sales of 100 will be added to Dept D2 which is wrong