Oracle Transactional Business Intelligence

Home› Oracle Analytics› Oracle Analytics Forums› More Solutions› Oracle Transactional Business Intelligence

Categories

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

OTBI Simple Aggregate Question - Count Does Not Match Details

Accepted answer
25
Views
2
Comments
Bart S
Bart S Rank 1 - Community Starter
in Oracle Transactional Business Intelligence

I have a very simple OTBI Analysis using the Worker Assignment Event Real Time where I show the number of hires using the following columns:

  1. EXTRACT (MONTH FROM "Assignment Event Details"."Effective Start Date")
  2. EXTRACT(YEAR FROM "Assignment Event Details"."Effective Start Date")
  3. COUNT("Worker"."Person Number")

There is a filter on the Action Code = HIRE.

This query returns a count of 36.

HOWEVER, If I remove the COUNT function on the Person Number column, 33 Person Numbers return.

When I write a SQL query against the data base using the same constraints in place for the OTBI query, I get 33 as the aggregate count. 33 is the correct number. 33 Person Numbers will return from the SQL if I remove the aggregates and simply list Person Numbers and those 33 person numbers are the same ones from the OTBI analysis when I remove the aggregate count.

If I redo the OTBI query and simply use the # of Hires measure from Assignment Event facts, I get 36. Anytime I use an aggregate on OTBI, the number is off from SQL or OTBI when I simply list the row data with no aggregate.

Any ideas why this is?

OTBI COUNT HIRE1.GIF
OTBI COUNT HIRE2.GIF
OTBI COUNT HIRE3.GIF
SQL COUNT HIRE.GIF

Best Answers

  • Raghavendra Raghunath-Oracle
    Raghavendra Raghunath-Oracle Mod
    Answer ✓

    Hi @Bart S in your OTBI query, can you add the following columns along with the 3 that you have mentioned above

    • Assignment Event Details.Assignment Number
    • Assignment Event Details.Assignment Type
    • HR Action.Action Type Code

    You may notice that for 3 of your workers there are 2 assignments, Pending Worker and Employee

    Can you please share the report output?

    Raghu

  • Bart S
    Bart S Rank 1 - Community Starter
    edited Sep 17, 2024 6:38PM Answer ✓

    Hi @Raghavendra Raghunath-Oracle,

    Thanks. The Assignment Type was the issue. I should have known that as I did account for it in my SQL but totally missed it in OTBI. I use SQL mostly and just am getting acquainted with OTBI. And we don't typically use the Pending Worker step here so I wasn't even thinking about that, surprised we had a few. We used to routinely run through the Pending Worker setup, but for a variety of reasons we just don't now.

    Really, thanks again. I appreciate the responsiveness.

    Bart