question

callen avatar image
callen asked

Multiple Salespeople in a Star Schema

I have a situation where there can be multiple salespeople on an order. The fact table is a list of orders.

I'm not sure of the best way to handle this. I could create several columns but that design, I believe, is bad. The other option is to have a single salesperson column in the fact table. However, that means I'll have repeating data. But I don't see a better option.

schema
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Kev Riley avatar image
Kev Riley answered

How are you wanting the data to be analysed?

Lets assume an order (o1) of value £100, with 3 sales people (p1, p2, p3) associated with it. You can't say each salesperson generated an order of £100, as that would then aggregate up to £300, so are you wanting to

  • split it evenly (value/ count of salespeople)
  • or weighted somehow p1:50%, p2:30%, p3:20%
  • or not split at all (the team of p1, p2, p3) generated £100

for the first 2 you could create virtual fact rows that make up the full order, and split out the values accordingly, for the third create a virtual salesperson of a team - and then manage the team dimension as a slowly changing dimension

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

callen avatar image
callen answered

Thank you for your quick answer. Not splitting at all is the answer. All salespeople get equal credit.

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.