OTBI Outer Join Subject Areas
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
Answers
-
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)0 -
Are you using the logical SQL to build the Analysis?
If yes then it is possible to use the outer join0 -
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
0 -
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 <= 75001Question 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)
0 -
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
they will help you with table details and a brief description for each of them
Hope this helps
0 -
Thanks Vipin for sharing table info for Product hub.
0 -
Thanks all for the posts.
I will try the options today/tomorrow and would revert with my comments.Cheers
Vipin
0 -
Thanks
0 -
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
0 -
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.
0 -
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
0 -
Vipin yeah will be better to start new thread and attach a screenshot of what you see.
0