Oracle Transactional Business Intelligence

Products Banner

Issues in HC & FTE measures

Received Response

Summary: Creating an analysis with several subject areas (six) with an incorrect output

Content (required):

Hello, I am creating a new analysis based on different subject areas (e.g. Person real time, assignment real time, assignment event, work relationship, payroll...).

From a UI perspective all the information about FTE and HC are correct but from the report in some cases I can see 5 HC and 5 FTE for the same employee. Do you know why it could happen and what is the logic behind?

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

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



  • Creating cross-SA analysis is a double-edged sword. Especially when you are using so many of them.

    Unlike BI Publisher report where you have absolute control over the SQL you are writing, here you have to depend on the logic written in the RPD which generated Physical SQL, and many a time when using so many subject areas together the joins can get pretty messy. This is when you see incorrect data in the analysis.

    It is quite time-consuming to debug such issues.

    The optimal approach here is to review your use case/requirement and see if it can be somewhat fulfilled by using a single or at most 2 subject areas. If that is not possible, it is better to create a BI publisher report by writing custom SQL.

    But if you still want to explore OTBI Cross-SA analysis route, please review this document :

    Fusion BI : What Are the Key Steps Required to Perform Cross Subject Area Analysis ? (Doc ID 2013245.1)

    Once you have done that, follow this diagnostic approach :

    1) Figure out the 1 subject area which provides you most of the columns you need, and start building a new analysis using that SA, this will ensure that SA is the primary SA

    2) Primary SA is the one on which you should place filters, ideally it is not a good idea to use filters and prompts on columns pulled from secondary subject areas, So put all the filters you need on the SA in this step

    3) Now add the second subject area and start adding columns 1 by 1, at each step validate if the data you are seeing is correct or not, if everything is correct, add the 3rd subject area, repeat this process again for the 3rd subject area and so on. Keep repeating till all the data you want is available in the analysis.

    Hope this helps.

  • Bhaskar Konar
    Bhaskar Konar ✭✭✭✭✭

    Hi Virginia M. Calvi,

    Would it be possible for you to share the Report XML or Catalog so that it can be looked into more details.