BusinessObjects Board

Universe

building semantic layer
#1

Hi,

I have a View in the Universe that has two dimensions Customer ID, Customer Address, and payment measure field. When I run a query in webi and bring these objects in my query I should ge getting the total on the basis of Customer ID which range from 1 to 10. The issue is some customer ID have more than one registered Customer address and the payment are unique count on Customer ID, so if I have customer id and
payment I should get

cust ID payment(unique customer count)
101 1
102 1
103 1
104 1
105 1
106 1
107 1
108 1
109 1
110 1

so the total payment should be 10 but now when i bring in the customer address i get

cust id cust add payment
101 35 james st 1
102 45 Mark st 1
103 55 Goven st 1
103 60 John st 1
104 65 mathew LN 1
105 103 Corner Bl 1
105 202 Sky Ave 1
106 505 Arther Ave 1
107 Perry st 1
108 James town 1
109 Michigan Ave 1
110 Quilt Ave 1

and the payment become 12 instead of 10. My query does a group by on Cust add. How can I get the payment equal to 10 and have both cust id and cust add in my query. Any hint would be helpful

Thanks,

081791 (BOB member since 2014-02-07)

#2

Welcome to B:bob:B!

What you are experiencing is called a fan trap. Here is the Designer’s FAQ that describes the problem and how to solve it:

Marek Chladny :slovakia: (BOB member since 2003-11-27)

#3

Just use a COUNT DISTINCT when defining your measure in Universe Designer/IDT and set projection to database delegated.

Andreas :de: (BOB member since 2002-06-20)