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

Performance Tile Filter not producing correct results

Received Response
71
Views
5
Comments
Mark Stoker
Mark Stoker Rank 2 - Community Beginner
in Oracle Transactional Business Intelligence

Summary:

I am trying to produce a measure for number of maternity cases currently in progress where the visa permit type has not been entered (is null).

It produces results but is ignoring the filter where the visa permit type is null and producing all cases in progress.

This is across two subject areas

"Workforce Management - Absence Real Time"

"Workforce Management - Person Real Time"

Content (required):

Can someone please advise where I am going wrong or how can I get the correct results?

Version (include the version you are using, if applicable):


Code Snippet (add any code snippets that support your topic, if applicable):

Tagged:

Answers

  • Nathan CCC
    Nathan CCC Rank 7 - Analytics Coach

    What does the raw data look like.

    select all 0 s_0
, "Workforce Management - Absence Real Time"."Assignment Absences"."# Of Absences" 
, "Workforce Management - Person Real Time"."Person Work Permit"."Visa Permit Type"
, "Workforce Management - Absence Real Time"."Absence Type"."Absence Type Name" 
, "Workforce Management - Absence Real Time"."Assignment Absences Details"."Absence Status"
, descriptor_idof("Workforce Management - Absence Real Time"."Assignment Absences Details"."Absence Status") as "Absence Status Code"
from "Workforce Management - Absence Real Time"
where "Workforce Management - Person Real Time"."Person Work Permit"."Visa Permit Type" IS NULL
and "Workforce Management - Absence Real Time"."Absence Type"."Absence Type Name" = 'Maternity Leave'
and descriptor_idof("Workforce Management - Absence Real Time"."Assignment Absences Details"."Absence Status") = 'INPROGRESS'

    To debug cut paste your logical sql from the advanced tab of your analyis to page "Issue Sql" 

    /analytics/saw.dll?IssueRawSQL

    then select Issue SQL then View Log. Look at the WITH clause. Does it do what you want it to do?

    Or maybe you do not even get that far. Do you get an error from the metadata repository database (RPD)?

    Maybe you need to add some columns to control what facts are used in the joins between subject areas? 

    [nQSError: 14025] No fact table exists at the requested level of detail
  • Mark Stoker
    Mark Stoker Rank 2 - Community Beginner

    wow, whole new area of OTBI I have never seen before.

    Thank you, I never knew this existed (although I am not experienced enough to know what I am looking for)!

    Although when checking the view log I picked up on this snippet:-

    Measure '# Of Absences' fixed to 'grand total' level in dimension 'Person Work Permit' because of missing logical join

    This suggests that I need to add a logical join, am I missing something simple?

    I haven't got a with clause.

    Where do i find the metadata repository database (RPD)?

  • Nathan CCC
    Nathan CCC Rank 7 - Analytics Coach

    Hi, So when you write an "Analysis" this is "logical SQL" in form "select presentationtable.presentationcolumn from subjectarea". When you the "Query Client" run your analysis from "Presentation Services" you submit your analysis logical sql to bi you are querying the metadata repository database also known as the RPD hosted by the "BI Server" on the application server layer in BI. The RPD takes your logical query then it sends one or more "physical" sql to the application transaction database server data sources to get the data it needs to give you back the answers.


    The physical SQL tends to be a WITH with multiple SELECT inside it. If you look in the log you should be able to find it. This tells you what physical tables and views and joins were actually done to get your data from your transaction system.


  • Nathan CCC
    Nathan CCC Rank 7 - Analytics Coach

    Hi, So getting a query to work as you expect it too using multiple subject areas is hard. As a "rule of thumb" you need a column from a fact along with only columns from dimensions that are shared by those facts in both subject areas. If you do not have the correct columns then it will go "pear shaped". Take some time to read the multiple various past posts on this forum where people got weird results trying to do this. Also lots of articles on this topic my oracle support too https://support.oracle.com/.

    One approach is to do this all back to front. Work out what physical sql joining which tables and views will give you the answer you need in a data model. Now try different logical sql using an analysis but keep checking issue sql/ manage sessions view log to see if the RPD is generating the physical SQL you want it too to get it to work.

    Good luck, Nathan

  • Mark Stoker
    Mark Stoker Rank 2 - Community Beginner

    So, the with statement is similar to a cte?

    I will take a look through the information you have provided, but thank you so much for your advice.