1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Count_Distinct in a report with multiple business areas fails (EUL design)

Discussion in 'Oracle Discoverer' started by InJenuity, Feb 11, 2015.

  1. InJenuity

    InJenuity Active Member

    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    55
    Hi all,

    I am in a hole and I am just digging deeper, so I am hoping someone can just provide some guidance on my design issue.

    I am using Oracle Discoverer Administrator 10.2.1 to design an EUL for our business.

    I want to be able to use COUNT_DISTINCT(field) and then use this in reports that may use more than one business area.

    Business Area 1: "Jobs attended per Crime Scene Investigator CSI" -> Grain = Job

    A client wishes to know:
    a) How many jobs attended
    b) How many distinct crimes was this in relation to. (one crime may have many crime scenes)
    Dimensions: "Crime Type", and "CSI"

    This works great using a COUNT_DISTINCT.

    My problem when the business wants to add additional data to this report from another business area:

    Business Area 2: "Exhibits Recovered from Jobs attended per Crime Scene Investigator" -> Grain = Exhibit per Job
    c) How many fingerprint lifts were recovered?
    Dimensions "Crime Type" and "CSI"

    The two business areas join nicely as they link on the two dimensions, but I get the error:

    "Join Configuration could not be resolved - distinct function cannot be reaggregated". [not one mention when I google????]

    How do I get around this? / What am I miss-understanding?

    What I have tried so far:

    a) Created a new folder and / or new business areas specifically for the COUNT_DISTINCT.
    - This then allows me to do the join but I get aggregation issues on the other values. When I look at the SQL it is not putting the COUNT_DISTINCT in a fan, and hence it re-aggregates all the other values.
    b) Trolled the internet....Stuck/ Nothing on internet / Serious stumbling block on project / serious stress / seriously need help

    Any guidance / design or fix would be great. My users are not technical, and use the Discoverer Desktop, but do have Plus available.

    I have done similar things on both BOXI and MS BI Stack okay..

    Kind Regards

    Matt.
     
  2. DTSIGuy

    DTSIGuy Forum Advisor

    Messages:
    402
    Likes Received:
    58
    Trophy Points:
    410
    Location:
    Texas
    Maybe an inline SQL clause for the extra column?
     
    InJenuity likes this.
  3. InJenuity

    InJenuity Active Member

    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    55
    DTSIGuy,

    Thanks for the life line. I will create custom folder with the distinct count. I have not used these folders but think it will work. I will let you know how it goes. Thanks for just a hint of hope.

    All the best

    Matt
     
  4. InJenuity

    InJenuity Active Member

    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    55
    I have now resolved my issue. Thanks. The Inline SQL didn't work purely because you cannot have a count distinct without a group by clause, and we won't know what that group by is until run-time.

    However....I have to admit....I made a bit of a school boy error during some early testing, and got a join the wrong way around.

    The solution in the end was to create a new fact table which was basically

    SELECT DISTINCT CountMeasureId, DimensionId1, DimensionId2, ....etc FROM BaseFactTable

    And then just do a standard count.

    Life is good again.