Continue in 2 seconds

I have a modeling question: I have sales transactions (line items) that I want to put into a star schema in a data mart.

Published
  • September 04 2002, 1:00am EDT
More in

Q:

I have a modeling question: I have sales transactions (line items) that I want to put into a star schema in a data mart. Sales are dimensioned by time (day), product, store, customer, sales type and salesperson. The problem is that many times, more than one salesperson is affected by the transaction (let’s say that for a specific transaction, salesperson A is credited for 60 percent of the sale, and salesperson B is credited for 40 percent of the sale. I could dimension my sales product lines by salesperson at the loading time, but I will end up with strange quantities in my fact records (.4 qty of product for A and .6 qty of product for B). I understand that summing up those records will give me the right total, but I would like advice on that.

A:

Les Barbusinski’s Answer: I can’t see a better way than what you’ve proposed. I had a similar problem designing a mart for an insurance company where the credit for selling an annuity policy was distributed among the salesperson, their boss, their boss’s boss and so on. My solution in that case was essentially the same as yours (i.e., fractional quantities). Although it’s a little confusing at the atomic level, the quantities resolve themselves into integers when aggregated to a specific product, store or date.

The only thing that might add clarity would be to add a Transaction (i.e., line item) dimension to your fact table. That way you could aggregate to the line item level (i.e., ignoring the Salesperson dimension) and get an accurate picture of a specific transaction. Of course, this only makes sense if there’s a need to drill through to the atomic transaction. Hope this helps.

Chuck Kelley’s Answer: There are multiple ways to deal with this. With the little bit you gave us, I would have multiple defined relationships (assuming there is not an unlimited number of salespersons who can be associated with a sale). Let’s say that the maximum is 3. Create 3 relationships between salesperson and fact. Have 3 facts measures name SalesPerson1Percentage, SalesPerson2Percentage and SalesPerson3Percentage. Then store their percentage along with the full qty and total. Then calculate at run time. An alternative is the create SalesPerson1Qty, etc. as well as FullQty (all of the salesperson qtys should add up to the fullqty). Now, I would create a salesperson defined as NO ONE and give a key of 999999999 to it. For those relationships that do not exist (i.e., there is only one salesperson), then give the 2 and 3 relationships the 999999999 key.

Joe Oates’ Answer: Probably the best thing that you can do is to look at the source system and how it records the transactions that have more than one salesperson. Most of the systems that I have seen have columns/fields for two or three salespeople that can be involved in one sale and one column/field for the amount credited to each salesperson. In these systems, there has generally been only one column/field for quantity. If this is the case, and you will not ever be concerned about other systems that might record this type of transaction differently, you can probably get away with taking the original transaction, transforming the system keys to data warehouse keys and leave it at that.

However, the design that will allow you to be able to handle other systems that might come along is to create a separate fact row for each of the sales people involved. This approach will also handle systems in which the sale has more than one transaction record, e.g., one record for each salesperson with the quantities and sales amounts credited to him or her. This is the approach that I prefer. It makes the ETL slightly more complex, but I believe that this will be the best decision in the long run.

Clay Rehm’s Answer: Since you have a many-to-many relationship between Salesperson and the Sales fact table, you have no choice but to have an associative table in between Sales and Salesperson that stores the Salesperson key, the Sales key and the credit amount. The credit amount would not be stored in the fact table since you never know how many salespeople may be assigned to a specific transaction.

Since the business rules state that a sale can be made by one or more salespeople, your model has to accommodate it.

Register or login for access to this item and much more

All Information Management content is archived after seven days.

Community members receive:
  • All recent and archived articles
  • Conference offers and updates
  • A full menu of enewsletter options
  • Web seminars, white papers, ebooks

Don't have an account? Register for Free Unlimited Access