Need to Use two subject areas together; Procurement and Payables

Question

Hello All,

I hope all is fine with you.

I have an analysis using the subject area "Procurement - Procure To Pay Real Time". Business wants a field to be added "Invoice Received Date" and for this I have to add subject area "Payables Invoices - Transactions Real Time".

I have added a common column of Invoice Number from both subject area and also tried with adding Invoice ID from both the areas as well, but as soon as I add the columns from Payables subject area, the data stops coming in the analysis.

I have checked both subject areas independently and they work fine for me.

It would be really helpful if I can get some guidance to work with both subject areas together.

Thanks,

Abhijeet Singh

Bhaskar Konar · Answer

Hi @Abhi_19,

If you are not able to achieve your requirement using multiple SAs, you can try using Logical SQL.

Here's a quick example or high level steps that will help you to understand more:

Consider your existing Report (should be having the a Primary Key like INVOICE_ID) As DataSet1 and then create another report using 'Payables Invoices - Transactions Real Time' where you have 'INVOICE_ID', 'Invoice Received Date' etc. which is DataSet2

Now join DataSet1 & DataSet2 on 'INVOICE_ID' and select the required fields for your report.

You should be able to achieve your requirement in this way.

Hope this help.

Cheers,

Bhaskar Konar · Answer

Hi @Abhi_19,

Please find below the Best practices that need to be considered while developing a report using more than one subject areas.

* You should have common dimension in the subject areas used in the report.
* Add at least one Measure/Fact/Metric from each subject area on the report.
* Click on the Advance tab of the Analysis report --> Scroll down till you see Advance SQL clause --> Check the Dimensionality check box --> click on Apply SQL button. Go to the Results tab and test the results.

Additional things that need to be considered:

* You also need to consider the lowest grain at which the Subject Areas built.
* More them one subject area reports are complex reports, apply proper filters on the report.
* How to debug the issue:* create a simple report with one subject area, remember to add as many dimension columns from first SA and add at least one measure. Apply filters.
* Add second subject area add required dimension columns and don't forget to add measure.

These Guidelines Apply to current FA/OTBI Custom Analysis requirements.

Please follow cross subject area guidelines as documented in : Creating and Administering Analytics and Reports > How Data Is Structured for Analytics

https://docs.oracle.com/en/cloud/saas/applications-common/22c/faacc/how-data-is-structured-for-analytics.html#s20046164

Hope this help.

Cheers,

gclampitt · Answer

hi i can't comment on that specific use case, but i would say normally when combine Subject Areas,  its to bring in Measures, not Dimensions. The Invoice Recieved Date dimension probably doesnt conform with the other ones from AP, hence why not getting the data.

I think answer might be to have to create/amend the logicial sql Or if this still doesn't work, create a custom BIP report with SQL that can return what is required