triple-counts on fact table

[Alfredomulet]

Hello everyone, I have a doubt regarding data modeling that you might be able to solve.

I need to register and then analyze the results of a sales visit. The sales rep allways shows to the customer 3 products, but each visit the order (priority) of the product shown may change. I've thought of a fact table like:

VISIT_ID, SALESREP_ID, PROD_ID, CUSTOMER_ID, PRIORITY_ID, DATE_ID
1, 11, 3604, 1, 1, 11/10/03
2, 11, 3605, 1, 2, 11/10/03
3, 11, 3606, 1, 3, 11/10/03

But when I analyze the visit information, (such as Number of visits with products 3604, 3605, and 3606 by Sales Rep 1, being Metric Number of Visits = Count(Visit_ID)), it triple-counts a single visit, I mean, although the table entries above belong to the same visit, it is counted as if they were 3 visits.

Any indeas on how to solve this issue? Thanks for your help in this matter,

Alfredo

 

[pabloj]

But you have three different visit_id!, you should have something like:
VISIT_ID, SALESREP_ID, PROD_ID, CUSTOMER_ID, PRIORITY_ID, DATE_ID
1, 11, 3604, 1, 1, 11/10/03
1, 11, 3605, 1, 2, 11/10/03
1, 11, 3606, 1, 3, 11/10/03

meaning that in the same visit one sales rep has showcased 3 products to the same customer.
The number of visits could come from a count(distinct(visit_id))



Stick to your guns