Sales Cloud Analytics: Combining Multiple Subject Areas

One of the great things about Sales Cloud is the built in Business Intelligence (OTBI) - this blog post looks at how you can fully utilise the power of OTBI with some complex reporting!

One of the great things about Oracle Sales Cloud is the built in Business Intelligence (OTBI – Oracle Transactional Business Intelligence) which can produce some very powerful analytics which can be embedded in Simplified and Mobile user interfaces (UI). Recently however, we came across a challenge when attempting to create a report which had attributes that spanned multiple subject areas. We attempted to create a report which contained Partners and Activities, but found that this would require two subject areas. This blog post will look at the solution we found for this, which proved not only to be useful in this one scenario, but opened up the ability to create some really interesting reports.

Currently the only out-of-the-box (OOTB) features on OTBI for combining two subject areas is that of the UNION function. This is great if you wish to merge data from two subject areas, for example:

Fig 1. Example of a UNION

You just need to keep in mind that the columns which you wish to merge (UNION) must have matching data types.

In some cases however, rather than using the UNION approach described above, we would like to complement data from one subject area with columns from another. In our scenario we wished to create a report on Partner attributes, complemented with Activity attributes which existed in a separate subject area. All that is needed to achieve this is a single common dimension between the two subject areas. This can even be extended to creating a report across more than two subject areas.

There are four types of joins:

• INNER JOIN
• LEFT OUTER JOIN
• RIGHT OUTER JOIN
• FULL OUTER JOIN

For this blog post we will be focusing on the LEFT OUTER JOIN (or RIGHT - they are equivalent if the order of the tables are switched), however the key principle that we are going to follow applies to all four types.

When creating a JOIN it is crucial to use a suitable JOIN attribute and ideally we should use a conformed dimension – the dimension is either identical in both subject areas, or one is a subset of the other. Primary and Foreign Keys are a great example. For example:

• PK-PK: We create a JOIN across two subject areas each with the attribute Opportunity ID
• PK-FK: We create a JOIN across two subject areas, setting Opportunity Account ID (Foreign Key) equal to Account Row ID (Primary Key)

Fig 2. Example of a LEFT OUTER JOIN

To achieve this in OTBI we need to issue our own simple logical SQL statement, which can be accessed in two ways from OTBI home:

• Create New ---> Analysis ---> Create Analysis from Simple Logical SQL
• Open an existing report ---> Advanced tab ---> SQL Issued ---> New Analysis

The code below is a template for the SQL you must apply to your report. This is done via ‘New Analysis’ under ‘SQL Issued’. ColumnA0 and ColumnB0 are our conformed JOIN attributes which belong in both subject areas and will be used to link the two.

Fig 3. The SQL issued to create a LEFT OUTER JOIN

The limitation with this approach is that we will only be able to view the columns selected, and we cannot add more columns at a later date. In addition there is no directory for the Subject Area or Attribute names when writing this SQL, so you must know the names in advance.

One of the easiest ways to address this problem is to create each half of the report in advance, and then by navigating to the ‘SQL Issued’ section you will be able to see the SELECT statement issued. Then you could quite easily copy and paste in the two inner SELECT statements. Another advantage of this is that you can apply filters before the two subject areas are joined, if required.

This surprisingly simple approach has really expanded the capabilities of OTBI - giving us the ability to build reports across seemingly distinct subject areas - and has proved to be very useful when creating complex analyses. We hope you have you enjoyed this blog post and that it has inspired you to fully utilise the power of OTBI with some complex reporting!

Find out more

Boxfusion Consulting are an Oracle Platinum Partner and recognised by Oracle as Specialised in the implementation of Oracle Sales Cloud and Oracle BI.

If you would like to hear more about how Oracle Sales Cloud and Oracle BI might be used to help your business, or where Boxfusion can help you gain more intelligence from your data, please give us a call on +44 203 283 4315 or contact us here!