Oracle Transactional Business Intelligence

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

Categories

OTBI Outer Join Subject Areas

Received Response
919
Views
12
Comments
VipinSharma
VipinSharma ✭✭
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:

Answers

  • Syed Misbauddin-Support-Oracle
    Syed Misbauddin-Support-Oracle ✭✭✭

    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 ✭✭✭

    Are you using the logical SQL to build the Analysis?

    If yes then it is possible to use the outer join

  • VipinSharma
    VipinSharma ✭✭

    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 ✭✭✭

    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 ✭✭✭

    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 ✭✭✭

    Thanks Vipin for sharing table info for Product hub.

  • VipinSharma
    VipinSharma ✭✭

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

    Cheers

    Vipin

  • VipinSharma
    VipinSharma ✭✭

    Thanks

  • VipinSharma
    VipinSharma ✭✭

    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 ✭✭✭

    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

  • VipinSharma
    VipinSharma ✭✭

    The Create Infolet option is still missing. I have tried reading many documents (wrong ones obviously) but not able to find how to show the 'create infolet' option.
    We are still in process of implementing SAAS (just Product Hub as of now). I believe there will be lot of priviledges not provided to my user. Can you share how to enable myself to create an infolet.

    Hello Syed,
    The Create Infolet option is still missing. I have tried reading many documents (wrong ones obviously) but not able to find how to show the 'create infolet' option.
    We are still in process of implementing SAAS (just Product Hub as of now). I believe there will be lot of priviledges not provided to my user. Can you share how to enable myself to create an infolet.
    I had even gone into the sandbox, the compose page option is also disabled in there for me.

    Hello Sahil,
    Thanks. I was able to use the logical SQL for creating an analysis.
    Should I close this thread and open a new question about infolet? because the answer about the outer join (for which I raised the topic is answered)

    Thanks.

    Vipin

  • Syed Misbauddin-Support-Oracle
    Syed Misbauddin-Support-Oracle ✭✭✭

    Vipin yeah will be better to start new thread and attach a screenshot of what you see.