Modeling asof dimension

[igorgois_]

I am struggling in a model, maybe someone here can help me

Imagine that I have a sales fact table:

Date | ID_employee | ID_store | Sales

And I have a employee dimension:

ID_employee | ID_store | Name | Role

There are two possible roles: salesperson and supervisor

A salesperson has only one store.

The supervisor can have N stores. But his id_store is acctualy the id_store from the back office. There arent rows in the fact table with id_employee or id_store from a supervisor. I also have a table with the id_employee from the supervisors and the real stores they are responsible for.

The main goal here is get a table with:

ID_employee | sum(sales)

Thanks in advance

[sunny_talwar]

Would you be able to share some raw data with the expected output?

[igorgois_]

Fact:

Date | ID_employee | ID_store | Sales

05/04/2018 | 1 | a | 10

05/04/2018 | 2 | a | 10

05/04/2018 | 3 | a | 10

05/04/2018 | 4 | a | 10

05/04/2018 | 5 | b | 10

05/04/2018 | 6 | b | 10

05/04/2018 | 7 | b | 10

05/04/2018 | 8 | b | 10

05/04/2018 | 9 | c | 10

05/04/2018 | 10 | c | 10

05/04/2018 | 11 | c | 10

05/04/2018 | 12 | c | 10

Employee dimension:

ID_employee | ID_store | Name | Role

1 | a | John1 | saleperson

2 | a | John2 | saleperson

3 | a | John3 | saleperson

4 | a | John4 | saleperson

5 | b | John5 | saleperson

6 | b | John6 | saleperson

7 | b | John7 | saleperson

8 | b | John8 | saleperson

9 | c | John9 | saleperson

10 | c | John10 | saleperson

11 | c | John11 | saleperson

12 | c | John12 | saleperson

13 | z | Supervisor1 | supervisor

14 | z |  Supervisor2 | supervisor

a, b, c are stores

z is back office

Supervisor to stores:

ID_employee | ID_store

13 | a

13 | b

14 | c

Output table:

ID_employee | sum(sales)

1 | 10

2 | 10

3 | 10

4 | 10

5 | 10

6 | 10

7 | 10

8 | 10

9 | 10

10 | 10

11 | 10

12 | 10

13 | 80 (store a + store b)

14 | 40 (store c)