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 Outer Join Subject Areas

Received Response
989
Views
12
Comments
VipinSharma
VipinSharma Rank 2 - Community Beginner
in Oracle Transactional Business Intelligence

Summary

How to connect subject areas where I need to represent null for rows that are not existing in other subject area

Content

Hello,

I am struggling with creating an analysis.

One of my subject areas is 'Product Management - Source System Item Real Time'.

Column 1: Item Name 

Column 2: Item Status

Colum 3: Formula Column with following formula

CASE WHEN TIMESTAMPDIFF(SQL_TSI_DAY,"Who Columns"."Item Last Updated Date", CURRENT_DATE)<2  THEN 'Days 00-01'
WHEN  TIMESTAMPDIFF(SQL_TSI_DAY,"Who Columns"."Item Last Updated Date", CURRENT_DATE) between 2 and 7 then 'Days 02-07' 
WHEN  TIMESTAMPDIFF(SQL_TSI_DAY,"Who Columns"."Item Last Updated Date", CURRENT_DATE) between 8 and 14 then 'Days 08-14' 
WHEN  TIMESTAMPDIFF(SQL_TSI_DAY,"Who Columns"."Item Last Updated Date", CURRENT_DATE) between 15 and 21 then 'Days 15-21' 
WHEN  TIMESTAMPDIFF(SQL_TSI_DAY,"Who Columns"."Item Last Updated Date", CURRENT_DATE) between 22 and 30 then 'Days 22-30' 
WHEN  TIMESTAMPDIFF(SQL_TSI_MONTH,"Who Columns"."Item Last Updated Date", CURRENT_DATE) = 1 then 'Months 01-02' 
WHEN  TIMESTAMPDIFF(SQL_TSI_MONTH,"Who Columns"."Item Last Updated Date", CURRENT_DATE) = 2 then 'Months 02-03' 
WHEN  TIMESTAMPDIFF(SQL_TSI_MONTH,"Who Columns"."Item Last Updated Date", CURRENT_DATE) = 3 then 'Months 03-04' 
WHEN  TIMESTAMPDIFF(SQL_TSI_MONTH,"Who Columns"."Item Last Updated Date", CURRENT_DATE) = 4 then 'Months 04-05' 
WHEN  TIMESTAMPDIFF(SQL_TSI_MONTH,"Who Columns"."Item Last Updated Date", CURRENT_DATE) = 5 then 'Months 05-06'
WHEN  TIMESTAMPDIFF(SQL_TSI_MONTH,"Who Columns"."Item Last Updated Date", CURRENT_DATE) = 6 then 'Months 06-07'  
WHEN  TIMESTAMPDIFF(SQL_TSI_MONTH,"Who Columns"."Item Last Updated Date", CURRENT_DATE) = 7 then 'Months 07-08' 
WHEN  TIMESTAMPDIFF(SQL_TSI_MONTH,"Who Columns"."Item Last Updated Date", CURRENT_DATE) = 8 then 'Months 08-09' 
WHEN  TIMESTAMPDIFF(SQL_TSI_MONTH,"Who Columns"."Item Last Updated Date", CURRENT_DATE) = 9 then 'Months 09-10' 
WHEN  TIMESTAMPDIFF(SQL_TSI_MONTH,"Who Columns"."Item Last Updated Date", CURRENT_DATE) = 10 then 'Months 10-11' 
WHEN  TIMESTAMPDIFF(SQL_TSI_MONTH,"Who Columns"."Item Last Updated Date", CURRENT_DATE) = 11 then 'Months 11-12'
ELSE  'Months 12+'  
END    
 
The above information comes correctly on the analysis, with the list of 23 items
 
Now I add another subject area
Product Management - New Item Request Approval Real Time and add the 
Column 4: New Item Request Number (from 'New Item Request Approval Details')
The list is reduced to 2.
 
Now I understand that these are 2 items for which the New Item Request is initiated.
But my expectation is to show NULL values where the item request is not initiated i.e. Total 23 items, 2 with 'New Item Request Number' populated and rest all as NULL.
 
I think I am missing to present an outer join to the system. How can I achieve this?
Thanks.
Vipin
Tagged:
«1

Answers

  • Syed Misbauddin-Support-Oracle
    Syed Misbauddin-Support-Oracle Rank 3 - Community Apprentice

    Vipin you can advance sql or combine analysis and see if that works for your requirement.

    Sample docs:

    Need A Report That Shows All Acitve Users (REsource Record) And If They Are An Active Participant (Doc ID 2607003.1)
    Oracle CX Sales and B2B Service Analytics: Creating Cross-Subject Area Reports Using BI Answers for CRM Subject Areas (Doc ID 1601082.1)

  • Sahil Makhiza
    Sahil Makhiza Rank 3 - Community Apprentice

    Are you using the logical SQL to build the Analysis?

    If yes then it is possible to use the outer join

  • VipinSharma
    VipinSharma Rank 2 - Community Beginner

    Sahil and Syed,
    Thanks for your replies.

    I am not using the SQL for creation of the Analysis as yet. 

    I have been working on 11i all these years. My table level (DB objects) knowledge is very limited.

    Question 2: Can you suggest, how I can find the tables related to the Product Hub. I read through few documents, not helping much. If you can direct me, it will be really helpful.

    Syed,
    I will create multiple analysis and check if that work for me.

    By the way, I have tried adding a metric column from both subject areas to the analysis (also '  checkbox is selected in the advanced tab) and that did not work for me.

    Question 3: How many dimensions should be present between the subject areas to join properly?

    Thanks for your responses in advance.

    Vipin

     

  • Syed Misbauddin-Support-Oracle
    Syed Misbauddin-Support-Oracle Rank 3 - Community Apprentice

    Question2:

    Vipin you get the underlying physical query from manage sessions and see  column in subject area to which table its hitting in back end.

    See below doc how to see session log

    Fusion Applications: How to Get nqquery Using Manage Session(Doc ID 2225909.1)

     

    For example for New Item Request Number from subject area

    this should be your query SELECT CHANGE_NOTICE from  EGO_ENGINEERING_CHANGES_B ChangeObjectBasePEO 

    WITH 
    SAWITH0 AS (select T288769.C421961877 as c1
    from 
         (SELECT V329552196.CHANGE_NOTICE AS C421961877,         V329552196.BASE_CHANGE_MGMT_TYPE_CODE AS C431314574,       
      V329552196.CHANGE_ID AS PKA_ChangeId0 FROM (SELECT ChangeObjectBasePEO.BASE_CHANGE_MGMT_TYPE_CODE,     
        ChangeObjectBasePEO.CHANGE_ID,         ChangeObjectBasePEO.CHANGE_NOTICE FROM EGO_ENGINEERING_CHANGES_B ChangeObjectBasePEO 
    WHERE (1=2)) V329552196 WHERE ( ( (V329552196.BASE_CHANGE_MGMT_TYPE_CODE = 'NEW_ITEM_REQUEST' ) ) )) T288769)
    select D1.c1 as c1, D1.c2 as c2 from ( select distinct 0 as c1,
         D1.c1 as c2
    from 
         SAWITH0 D1
    order by c2 ) D1 where rownum <= 75001

     

    Question 3: How many dimensions should be present between the subject areas to join properly?

    In the doc given earlier you need common dimension  and a fact from both subject areas to join properly.

    Oracle CX Sales and B2B Service Analytics: Creating Cross-Subject Area Reports Using BI Answers for CRM Subject Areas (Doc ID 1601082.1)

     

  • Sahil Makhiza
    Sahil Makhiza Rank 3 - Community Apprentice

    Hi Vipin,

    In order to get an insight of back end table, one option is as suggested by Syed, to create the analysis -> Copy the logical SQL from the advanced tab of the analysis and generate the NQ query log

    Alternatively, you can refer to the below link 

    https://docs.oracle.com/en/cloud/saas/supply-chain-management/20a/oedsc/product-hub.html#egibatchpuboptions-20361

    they will help you with table details and a brief description for each of them

    Hope this helps

  • Syed Misbauddin-Support-Oracle
    Syed Misbauddin-Support-Oracle Rank 3 - Community Apprentice

    Thanks Vipin for sharing table info for Product hub.

  • VipinSharma
    VipinSharma Rank 2 - Community Beginner

    Thanks all for the posts.
    I will try the options today/tomorrow and would revert with my comments.

    Cheers

    Vipin

  • VipinSharma
    VipinSharma Rank 2 - Community Beginner

    Thanks

  • VipinSharma
    VipinSharma Rank 2 - Community Beginner

    Hello,

    Thanks for your help. I am now trying to create a DataModel with sql query to get my desired output. However, I must say it is tedious.

    The table information is not very clear in Oracle documentation.

    In the meanwhile, I have been asked to use infolet to present some information. Though I could see documentation around how to create infolet.

    I don't have the Infolet selection in the sandbox. Is that something to do with my role? Do I need additional role for it?

    Thanks for answering.

    Vipin

  • Syed Misbauddin-Support-Oracle
    Syed Misbauddin-Support-Oracle Rank 3 - Community Apprentice

    Vipin see below if that helps , infolet is not related to sandbox as customization can be done using page composer or add infolet button in infolet page.

    If you are not seeing infolets dots on home page then enable using structure.

    imagehttps://www.youtube.com/watch?v=Zpm0XxjaQOY

    imagehttps://www.youtube.com/watch?v=fhmhMRHYP3Q