Sales, Targets, and Commission...

[cschuessler]

I am currently doing some modeling to facilitate Retail Sales and Targets, ultimately paying commission on the sales achieved. The sales information comes from a transactional table, and is aggregated into a Fact table to have the total sales by Day, by store, by Employee.

The target information, in a seperate fact table, has the target value by Store, by Day. (note the targets are set at a store level, and not by employee)

I need to pay commission to any employees who have achieved sales in a store that has met its target for the month.

I am having difficulty in understanding how best to position the data in order to do this.

Any advise would be greatly appreciated. If you require clarification on what I am attempting, please let me know.

Thanks in advance.