Good morning!
I'm interested in setting up BI for my workplace but before I pitch it to the bosses I'd like to make an example of a dataset of one of our clients.
Our data model in SQL is outdated and far from efficient but I've got to work with what I've got to work. I'm having the following issues:
- an account has an accountcode as PK
- an order has an ordercode as PK
- a visit has a visitcode as PK
- a distribution check has a distributioncode as PK
- an employee has an employeecode as PK
- a product has a productcode as PK
An employee can go to an account that is in his Territory and make a visit which makes him the Visitor and Owner.
But an employee can also go to an account that is NOT in his Territory and make a visit which makes him the Visitor, but not Owner.
I'd like to include an "Employee" table and a "Territory" table so that you can filter on visits where Employee X is the Owner of the account or is the Visitor.
An account can be visited multiple times of course and a visit can have multipe orders and/or multiple distribution checks.
But not all orders happen in a visit. There are some accounts that call their order in, so no Visit is registered, just the orders.
So orders are best linked to the account and so are the visits, but an ordercode sometimes have to be linked to a visitcode and sometimes not if the order was registered without a visitcode.
