BusinessObjects Board

Duplicate Measures - Using Distinct

building semantic layer
#1

Have had a search, but can’t seem to find anything like this.

In my Data Foundation I have a derived view that selects a limited bit of data from my original Fact table. The data in this table contains:
~an event date
~an event ID
~a user ID
~a monetary amount

The event date is simply a record per day.

  • The event ID is text:
    Count(Distinct Table.Event_ID)
    Projection Function: Sum

  • The user ID is text.
    Count(Distinct Table.User_ID)
    Projection Function: Sum

  • Money amount is numeric.
    Count(Distinct Table.Amount)

If I focus on one specific date, I would expect to see:
8 Users
13 Events
And that’s over 20 records

Basically saying 20 records were caused by 8 users recording 13 events; for clarity, an event can have multiple records.

Universe is saved and uploaded. When I create a WEBI from it, I pull all three in, the result I get is:

Date [correct]
Number of Users 13 [incorrect]
Number of Events 13 [correct]
Sum of Amount [correct]

What I can’t figure out is why the number of Users is basically the same as the number of events.

milestyle (BOB member since 2016-11-17)

#2

What does the SQL that WebI is generating look like?

joepeters :us: (BOB member since 2002-08-29)

#3

.
A projection of SUM for a COUNT DISTINCT measure is just incorrect.
Change the projection to DATABASE DELEGATED, please.

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

#4

If that’s the case, why would one be calculating accurately and the other not?

Also, I tried the Delegated projection, but that lead to #torefresh , which, when refreshed, returned blanks in the relevant cells.

milestyle (BOB member since 2016-11-17)

#5

I respectfully disagree with Andreas – I don’t think Database Delegated will resolve the issue, but something else is going on.

Again, what does the generated SQL look like?

joepeters :us: (BOB member since 2002-08-29)

#6

His “group by” date is causing the same users to be counted multiple times.

The distinct count will be correct per date, but you cant sum a distinct count without double counting. If You want a distinct count with a certain breakdown, and a summary, you need two queries.

You can also use the dense_rank() SQL function at universe level, if your db allows it.

Derf :canada: (BOB member since 2011-05-16)

#7

This might not be the only issue, but certainly is one, that must be corrected:
DISTINC COUNT must be set to DATABASE DELEGATED for projection, or any projection at local Webi report level will lead to incorrect results.
This is true for any non-fully additive measure such as averages or inventory measures as of a certain date by the way, see also here: SQL Aggregates

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