OTBI Outer Join Subject Areas

VipinSharma

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

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

Are you using the logical SQL to build the Analysis?

If yes then it is possible to use the outer join

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 ' Show Total value for all measures on unrelated dimensions' 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

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

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

Thanks Vipin for sharing table info for Product hub.

VipinSharma

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

Cheers

Vipin

VipinSharma

Thanks

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

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.

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

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