Oracle Communities

Oracle Analytics and AI

Child Item

How can I get both account-contact and orphan contact in OTBI Analysis?

Hi,

Is there a way for me to get all contacts that are associated in an account and contacts that don't have an account in OTBI analysis?

I am using subject areas below and trying to create a left join, but it is not working.

I am also trying to use 'OR' but I am only getting blank data.

Maybe I am doing it wrong or is it just not possible in how the subject areas work?

Hoping that you can give me some suggestions. Thank you!

Find more posts tagged with

Analysis

Subject Areas

OTBI (Oracle Transactional Business Intelligence)

Accepted answers

Riyaz Ali-Oracle

Hi Ivony,

It looks like you're running into duplication issues due to the way OTBI handles subject area joins. Since left joins aren't natively supported, here are some alternative approaches to refine your report:

Using Filters to Reduce Duplication:
If your report is pulling duplicate rows, check whether the filters are unintentionally causing multiple matches.
Try applying distinct filters or using aggregation functions to consolidate results.

or Try the below:

a. Open the OTBI report
b. Go to the Advance tab
c. There is an option called "Issue an explicit Distinct" Just chec this box

2. Using a Common Dimension for Linking:
If both subject areas share a common dimension (e.g., Contact ID or Person ID), ensure that:
The same dimension is included in both subject areas.
You add a measure from each subject area (OTBI requires at least one measure per subject area).

3. Exploring Performance Optimization:
Since performance issues are a concern, consider:
Reducing the number of columns in the report.
Limiting the dataset by applying filters before running the query.
Using summary-level data instead of detailed records.

Please review the below as it might be helpful:

Getting duplicate and repeated values in OTBI — Cloud Customer Connect

Account Analysis OTBI Report Retrieving Only 256 Records At One Go (Doc ID 2669709.1)

Riyaz Ali-Oracle

Hi Ivony,

If my response has answered your question or assisted you with your concern, please click"yes" below to accept the answer or comment with any additional queries. You can also read the Cloud Customer Connect Guidelines for https://community.oracle.com/customerconnect/kb/articles/1-accepted-answer

Thanks,
Riyaz Ali

All comments

Riyaz Ali-Oracle

Hi Ivony,

Looks like you're trying to retrieve both contacts associated with an account and those without an account in OTBI, but the left join isn't working as expected. This is a common challenge when working across multiple subject areas in OTBI.

Check the Below:

Understanding OTBI Subject Area Joins:

OTBI does not support traditional SQL joins like LEFT OUTER JOIN across different subject areas. Instead, it relies on predefined joins within each subject area. If the subject areas you're using do not have a direct relationship, the join may not work properly.

2. You can try the below:

A. Using a Union Query Instead of a Left Join
Since OTBI does not allow custom joins, you can try using a Union query:

Create one analysis for contacts with accounts.
Create another analysis for contacts without accounts.
Use a Union report to combine both datasets.

B. Using a Common Dimension for Linking
If your subject areas share a common dimension (e.g., Person ID or Contact ID), ensure that:

Both subject areas include the same dimension.
You add a measure from each subject area (OTBI requires at least one measure per subject area).

C. Checking Filters & Data Visibility
If you're using an OR condition, ensure that it is applied correctly.

Try running the query without filters to see if data appears.
Verify that security settings allow access to both contact types.

Ivony Ann Paula Salango

Hello @Riyaz Ali-Oracle , hope you are doing well. Thanks for your time and effort in providing these suggestions. I have tried most of it. Especially the union but in my case, I am having performance issue with the main subject area that fits for the requirement and I actually cannot do any 'left joins' given the structure of our data.

I managed to get the results using 2 subject areas, but it is duplicating. The problem is with the left join. I can't make it work.

Maybe you have other options in mind? I really appreciate your help. Thank you!

Riyaz Ali-Oracle

Hi Ivony,

Using Filters to Reduce Duplication:
If your report is pulling duplicate rows, check whether the filters are unintentionally causing multiple matches.
Try applying distinct filters or using aggregation functions to consolidate results.

or Try the below:

a. Open the OTBI report
b. Go to the Advance tab
c. There is an option called "Issue an explicit Distinct" Just chec this box

Please review the below as it might be helpful:

Getting duplicate and repeated values in OTBI — Cloud Customer Connect

Account Analysis OTBI Report Retrieving Only 256 Records At One Go (Doc ID 2669709.1)

Ivony Ann Paula Salango

Hello @Riyaz Ali-Oracle ,

Good day! Given the limitation of left join in subject area, we have come up to a workaround to use the data model instead in creating the report. But your suggestions really help us understand the structure of our data and how subject areas work.

I really appreciate your dedicated time on this issue.

Regards,

Ivony

Riyaz Ali-Oracle

Hi Ivony,

Thanks,
Riyaz Ali

Riyaz Ali-Oracle

Hi @Ivony Ann Paula Salango,

Thanks a lot.

Thanks,
Riyaz Ali