Oracle Analytics Cloud and Server

Home› Oracle Analytics› Oracle Analytics Forums› Oracle Analytics Cloud and Server› Oracle Analytics Cloud and Server

Categories

Welcome to the Oracle Analytics Community: Please complete your User Profile and upload your Profile Picture

Analysis measure based on external filter

Received Response
111
Views
18
Comments
Matthias_VC
Matthias_VC Rank 3 - Community Apprentice
edited August 2024 in Oracle Analytics Cloud and Server

I'm having an issue in Primavera Analytics(which uses OBIEE software as a basis)
It's a cloud implementation and I have no way of changing the Datamodel so I'd like to solve it with the Reporting Programs abilities only.

So what I'd like to have is a measure that looks something like this:

COUNT(DISTINCT "Primavera - Activity History"."General - (Activity)"."Activity Id")
     WHERE "General - (Activity)"."Activity Id" IN
          (SELECT "General - (Activity)"."Activity Id" FROM "Primavera - Activity History"

               WHERE "General - (Project)"."Project Name" = @{varProject})

In other terms:

I have a visual where I'm trying to see how many "Activity IDs" the project has in common with the project I selected in a prompt elsewhere.

Simply filtering the entire visual is no problem. But I'd like to know the amount in common AND the amount NOT in common.

- Is it possible to have subqueries in the select part of the SQL?

- If necessary I'm able to generate the list in a visual. Can I transform the results of this visual into a presentation variable and so I only have to count those that are in the presentation variable? What is the size limit of a presentation variable?

pastedImage_1.png

Kind regards,

Matthias

«1

Answers

  • Christian Berg-0racle
    Christian Berg-0racle Rank 10 - Analytics Guru

    If you can't change the model and have sth extremely specific like your request there, then your logical SQL looks like a good start already. You can always create an analysis based on a(n) LSQL and then chart the output.

    pastedImage_0.png

  • Jerry Casey
    Jerry Casey Rank 6 - Analytics Lead

    You can create  sub-query to filter on (Activities in Project A).

    pastedImage_0.png

    Then union (combine) two queries, with one filtered on Activities in the sub-query, and the other filtered on Activities NOT in the sub-query

    Include a column containing the label 'Common' in the first and 'Not Common' in the second.

  • Matthias_VC
    Matthias_VC Rank 3 - Community Apprentice

    Is this always possible?
    If I go to New-> Analysis

    I only have the availability to select from existing Models.
    If I go to New-> Dataset

    I do have the option to create a new SQL Query. but this one I can only access in reports, not in analyses, right?

  • Christian Berg-0racle
    Christian Berg-0racle Rank 10 - Analytics Guru 
    Matthias_VC wrote:Is this always possible?

    I don't have your "baked into Primavera" flavour of OBI but from the direction of your questions I suspect that for you the answer is sadly: "No it's not."

    Matthias_VC wrote:If I go to New-> DatasetI do have the option to create a new SQL Query. but this one I can only access in reports, not in analyses, right?

    Dataset? Not "Data Model"?

    pastedImage_2.png

  • Matthias_VC
    Matthias_VC Rank 3 - Community Apprentice

    Hey Jerry

    I tried your approach, as it was the most visual.

    This was a promising course, put some hours into it (which is why it took >1 day to reply), however I couldn't get it to work.

    First time I tried it I got the error:

    "The data view layout builder was unable to create the proper grid view layout. The logical column with the following ID was missing:..."

    When I tried to decompose what was causing the issue, I think it's when trying to create a union when using a COUNT or COUNT DISTINCT function.

    Any way to circumvent this problem?

  • Matthias_VC
    Matthias_VC Rank 3 - Community Apprentice

    Yes,
    You're right!

    Can it also be a case of having the correct permissions (if so I just have to file a service request).

    Otherwise I'll try to see if I can recreate the SQL Myself. Will it impose the same restrictions as the visual way I tried before?
    As in: is it ever possible to do a COUNT DISTINCT before the union?
    If not, I think I might be able to make it work by using a COUNT DISTINCT after the union...

  • Jerry Casey
    Jerry Casey Rank 6 - Analytics Lead

    In a union query, you can create calculated columns at the header level (outside the union) by using the "Add Result Column" link.

    pastedImage_0.png

    In these columns, you refer to the union columns using saw_x references (starting from the left, with saw_0):

    pastedImage_2.png

    Use these to replace the calculations within the union-ed queries.  You have to be careful to maintain the ordinal references if you move or delete any columns. 
    If you add any new columns to the base queries, you the have to go to the header line and move the new header to the left of any "saw_x" columns.

  • Matthias_VC
    Matthias_VC Rank 3 - Community Apprentice

    OK, ok thanks so much, quite clear and you've helped me a lot in understanding.


    I'm nearly there, I guess. I've had another error on the way:

    => Set operation failed due to incompatible sort order.

    But this has been resolved by using a 'Union All' instead of a normal Union.

    But now I've got this one after adding a Count distinct function as a result column after doing the Union All

    : "The SQL statement must include a GROUP BY clause"

    Can I do this without manually changing the SQL? (which would mean not doing it as an analysis and starting all over)

    Group By Clause.JPG

    Current SQL being tested (removed some of the filters from earlier)

    SELECT saw_0, saw_1, saw_2, saw_3, COUNT(DISTINCT saw_2) saw_4 FROM (

    (SELECT "General - (Project)"."Project Object Id" saw_0, "General - (Project)"."Project Name" saw_1, "General - (Activity)"."Activity Id" saw_2, 'Test' saw_3

      FROM "Primavera - Activity History" ) UNION ALL

    (SELECT "General - (Project)"."Project Object Id" saw_0, "General - (Project)"."Project Name" saw_1, "General - (Activity)"."Activity Id" saw_2, 'Test 2' saw_3

      FROM "Primavera - Activity History" )

    ) t1 ORDER BY saw_0, saw_1, saw_2, saw_3

    :

  • Jerry Casey
    Jerry Casey Rank 6 - Analytics Lead

    If there isn't a column in your query that fits the purpose, you can simply use a constant value of 1, like this:  COUNT(DISTINCT saw_2 by 1).

    This will apply the count distinct to your whole population.

  • Matthias_VC
    Matthias_VC Rank 3 - Community Apprentice

    Hmmm okay, adding a by statement solves that error.

    I now have something:

    Count distinct success!.JPG

    But this give me way too many lines to add in a graph of any sort. If I try to start creating a line chart for instance it gives me this error.

    too many rows.JPG

    So obviously I should remove one column from the table (Activity ID).

    However if I remove it in the table it gives me the following error:

    A REPORT totalling function must be in a leaf query block.

    SQL:

    SELECT saw_0, saw_1, saw_2, saw_3, COUNT(DISTINCT saw_2 by saw_1) saw_4, REPORT_AGGREGATE(COUNT(DISTINCT saw_2 by saw_1) BY saw_0, saw_1, saw_3)

    FROM (

         (SELECT "General - (Project)"."Project Object Id" saw_0, "General - (Project)"."Project Name" saw_1, "General - (Activity)"."Activity Id" saw_2, 'Test' saw_3

              FROM "Primavera - Activity History" )

         UNION ALL

         (SELECT "General - (Project)"."Project Object Id" saw_0, "General - (Project)"."Project Name" saw_1, "General - (Activity)"."Activity Id" saw_2, 'Test 2' saw_3

              FROM "Primavera - Activity History" )

    ) t1 ORDER BY saw_0, saw_1, saw_2, saw_3

    the functioning SQL was:

    SELECT saw_0, saw_1, saw_2, saw_3, COUNT(DISTINCT saw_2 by saw_1) saw_4
    FROM (

         (SELECT "General - (Project)"."Project Object Id" saw_0, "General - (Project)"."Project Name" saw_1, "General - (Activity)"."Activity Id" saw_2, 'Test' saw_3
               FROM "Primavera - Activity History")

         UNION ALL

         (SELECT "General - (Project)"."Project Object Id" saw_0, "General - (Project)"."Project Name" saw_1, "General - (Activity)"."Activity Id" saw_2, 'Test 2' saw_3

               FROM "Primavera - Activity History")

    ) t1 ORDER BY saw_0, saw_1, saw_2, saw_3

    I tried to alter the calculation by writing directly DISTINCT saw_2 by saw_0,saw_1, saw_3.

    But this still generated a "Report Aggregate" which had the same error.