Distinct Counts

[jdun]

Has anyone ever had any luck creating measures at the universe level for distinct counts of data in Explorer (i.e. Number of Unique PO's, Number of unique employees, etc.)?  Data looks fine at the universe but always shows record counts in the info space.  We're on 4.1 SP02.

Thanks.

[Former Member · Accepted solution]

Hi Josh,

can you use the default Occurrences measure?

What is the current aggregation type set on your measure in the universe?

George

[jdun · Accepted solution]

Hi George,

The occurrences measure actually has the same values as my distinct count measure, which is simply the total # of records.  The aggregation type is set to "Count."   I've tried changing this to sum as well with the same results.

Are you aware of this being an actual product limitation?

Thanks!

Josh

[Former Member · Accepted solution]

I'm pretty sure the count is all records by default which is same effect as enabling "Avoid duplicate aggregation" in Webi. I will double check to see. What is your universe data source?

[jdun · Accepted solution]

I'm using a BW InfoCube as a data source.  This particular infospace does have line level detail info in it but even when I run a sample query in the universe with line level detail the "distinct" record count is what I would expect.

[Former Member · Accepted solution]

Hi Josh,
I looked in this further but cannot give any workaround. I traced the Indexing server and I see the SQL is same as in Webi  (SELECT   EMP.DEPTNO, EMP.ENAME, count( distinct EMP.JOB ) FROM ...) but internally Explorer ignores the distinct keyword. It only does the aggregation as set in  the Universe measure property, so if set to a count it will be like a Count All giving you all the line items number.
We have raised this as an Enhancement but I do believe this is potentially a big code change to change behavior.

George

[jdun · Accepted solution]

Hi George,

I appreciate you spending time looking into this for me.  Yeah, I agree with you it just feels like a limitation with the product.  From an analysis perspective this would be useful to compare unique counts of PO's or sales orders along with something like net sales per year so hopefully this will get some attention in upcoming releases.

Thanks again.

Josh