Examples of Reporting Using Data from More than One Subject Area

The simplest and fastest way to generate a report is to use a single subject area. If the dimension attributes and fact metrics that you're interested in are all available from a single subject area, then you should use that subject area.

Combining Queries from Multiple Subject Areas

When you're reporting with multiple subject areas, keep in mind that if you use three subject areas for an analytic, your common dimensions must exist in all three subject areas. Joining on common dimensions gives you the benefit of including any metric from any of the subject areas in a single analysis.

While you can create an analysis joining any subject area to which you have access, only a cross subject area query that uses common dimensions returns data that's at the same dimension grain. This happens so that the data is cleanly merged, and the results is an analysis that returns exactly the data you want to see.

When a cross subject area analysis is generated, separate queries are executed for each subject area in the analysis and the results are merged to generate the final analysis. The data that's returned from the different subject areas is merged using the common dimensions. When you use common dimensions, the result set returned by each subject area query is at the same dimensional grain, so it can be cleanly merged and rendered in the analysis. The next topic shows an example of reporting using different subject areas with common dimensions.

Common Dimension Reporting

In this case, to use multiple subject areas for reporting, we are using common dimensions. We want to pull the number of Opportunities, number of Opportunity Revenue Lines, number of Leads, and number of Activities by Customer. The common dimension in all three subject areas used for this analysis is Customer and different fact metrics are pulled from each subject area.

We're using these subject areas:

• "Marketing - CRM Leads"

• "Sales - CRM Pipeline"

• "CRM - CRM Activity Real Time"

Start by creating an analysis using the Marketing - CRM Leads Subject area. You add the second and third subject area used in the cross-subject area analysis by clicking Add/Remove Subject Areas icon in the Subject Areas section.

This is the analysis editor showing the subject areas available to add to your new analytic.

For this example, Customer is the common dimension:

• "Marketing - CRM Leads"."Customer"

• "Sales - CRM Pipeline"."Customer"

• "Sales - CRM Sales Activity"."Customer"

These are the fact metrics:

• "Marketing - CRM Leads"."Lead Facts" ."# of Leads"

• "Sales - CRM Pipeline"."Facts". "Pipeline Detail Facts". "# of Opportunity Revenue Lines"

• "Sales - CRM Pipeline"."Facts"."Pipeline Facts". "# of Opportunities"

• "CRM- CRM Activity Real Time"."Facts"."Activity Facts"."# of Activities"

This is the Logical SQL for this example:

 SELECT
   0 s_0,
   "Marketing - CRM Leads"."Customer"."Corporate Account Name" s_1,
   "Marketing - CRM Leads"."Lead Facts"."# of Leads" s_2,
   "Sales - CRM Pipeline"."Pipeline Detail Facts"."# of Opportunity Revenue Lines" s_3,
   "Sales - CRM Pipeline"."Pipeline Facts"."# of Opportunities" s_4,
   "Sales - CRM Sales Activity"."Activity Facts"."# of Activities" s_5
FROM "Marketing - CRM Leads"
ORDER BY 1, 2 ASC NULLS LAST
FETCH FIRST 75001 ROWS ONLY

This shows the analytic results from combining common dimensions and facts from three subject areas.

Local and Common Dimension Reporting

This example reports on Customer, number of Opportunity Revenue Lines by Product, and number of Activities by Activity Type. Customer is the common dimension in both subject areas used for this query. Product is a local dimension to the Sales - CRM Pipeline subject area and Activity is a local dimension to CRM - CRM Activity Real Time. Different fact metrics are pulled from each subject area.

Note: Using local dimensions can impact the grain of the analysis resulting in repeated metrics for each of the rows.

We're using these subject areas:

• "Sales - CRM Pipeline"

• "CRM - CRM Activity Real Time"

Customer is the common dimensions:

• "CRM- CRM Activity Real Time"."Customer"

• "Sales - CRM Pipeline"."Customer"

These are the local dimensions:

• "Sales - CRM Pipeline".Product"

• "CRM - CRM Activity Real Time"."Activity"

These are the fact metrics:

• "CRM- CRM Sales Activity Real Time"."Facts"."Activity Facts"."# of Activities"

• "Sales - CRM Pipeline"."Facts"."Pipeline Detail Facts"."# of Opportunity Revenue Lines"

This is the logical SQL for this example:

SET VARIABLE ENABLE_DIMENSIONALITY=1; 
SELECT
   0 s_0,
   "Sales - CRM Pipeline"."Customer"."Corporate Customer Unique Name" s_1,
   "Sales - CRM Pipeline"."Product"."Product Name" s_2,  
    "Sales - CRM Sales Activity"."Activity"."Activity Type Code" s_3,
"Sales - CRM Pipeline"."Pipeline Detail Facts"."# of Opportunity Revenue Lines" s_4,
   "Sales - CRM Sales Activity"."Activity Facts"."# of Activities" s_5
FROM "Sales - CRM Pipeline"
ORDER BY 1, 2 ASC NULLS LAST, 3 ASC NULLS LAST, 4 ASC NULLS LAST
FETCH FIRST 75001 ROWS ONL

Here are the criteria and results of this local and common dimension query.

When using common and local dimensions use SET VARIABLE ENABLE_DIMENSIONALITY=1;in the Advanced SQL tab and click Apply SQL. Alternatively, select the 'Show Total value for all measures on unrelated dimensions' and click Apply SQL

Guidelines for Reporting Using Cross-Subject Areas

To create a cross-subject area analysis, include a measure from each subject area to support the join if it uses a local dimension. You can hide the measure in the results if you don't want it to appear in your analysis. Note that the subject areas have to be from the same Oracle Cloud application.

Here are some general guidelines to follow when you are combining subject areas with common and local dimensions:

• Use a single subject area whenever possible. Single subject area analyses perform better and are easier to maintain.

• When joining two or more subject areas, make sure at least one attribute from a common dimension is used.

• When using common dimensions always choose attributes from the common dimension from a single subject area. For instance if you're using the Customer dimension to build a query between subject area 1 and subject area 2, then select all customer dimension attributes from either subject area 1 or from subject area 2. (Not some customer attributes from subject area 1 and some from subject area 2.) In some scenarios, the common dimension may have more attributes in one subject area than the other. In such a situation, you can only use the subset of common attributes for a cross-subject area query.

• Always include a measure from each subject you are using. You don't have to display measures or use them, but you should include them. You can hide a measure if not needed.

• When using common and local dimensions use SET VARIABLE ENABLE_DIMENSIONALITY=1;in the Advanced SQL tab and click Apply SQL button. Alternatively, select the 'Show Total value for all measures on unrelated dimensions' and click Apply SQL.

Using Set Operations to Combine More than One Result Set from the Same or Different Subject Areas

You can use set operations to combine more than one result from the same or different subject areas. This example creates a compound query that's a union of two result subsets from the same subject area.

We're going to combine results from these two subject areas using the following facts:

• "# of Opportunity Revenue Lines" by "Territory" from the "Territory Management - CRM Pipeline" subject area (result 1)

• "# of Sales Accounts" by "Territory" from the "Territory Management - CRM Sales Accounts" subject area (result 2)

Here is the logical SQL for this example:

SELECT
saw_0,
saw_1
FROM (
(
SELECT
IFNULL("Territory"."Territory", 'Unspecified')||'~~ # of Opty Revn Lines' saw_0,
"Pipeline Detail Facts"."# of Opportunity Revenue Lines" saw_1
FROM "Territory Management - CRM Pipeline"
)
UNION
(
SELECT
IFNULL("Territory"."Territory", 'Unspecified')||'~~ # of Sales Accounts' saw_0,
"Sales Account Facts"."# of Sales Accounts" saw_1
FROM "Territory Management - CRM Sales Accounts"
)
) t1 ORDER BY saw_0

To use the set operations, click the button "Combine results based on union, intersection and difference operations" on the Selected Columns header

This shows the two combined subject areas using a union set operation.

Here is the resulting report.

Combining Logical SQL Using the Advanced Tab

If your requirement can't be met by either of the two methods already discussed, then there's another advanced technique you can try. This technique lets you join multiple logical SQL statements based on common IDs or keys, which can be written against the same or different subject areas as with normal SQL. Both . Outerand Equijoins are supported.

This example illustrates how the fact metrics on Territory assignment and Resource assignment for Opportunities can be combined to get a single report using advanced logical SQL

First, we are going to write a BI Answers query using the Sales - CRM Opportunity Territory subject area to show the # of Opportunity Territory Assignments, and Opportunity. Once the correct results are achieved, go to the Advanced tab and grab the logical SQL associated with this query.

This is the logical SQL we use for this example:

SELECT
   0 s_0,
   "Sales - CRM Opportunity Territory"."Opportunity"."Opportunity ID" s_1,
   "Sales - CRM Opportunity Territory"."Opportunity Territory Facts"."# of Opportunity Territory Assignments" s_2
FROM "Sales - CRM Opportunity Territory" 

This is the result of the query using the Advanced tab.

Second, we're going to write a second BI Answers query using the Sales - CRM Opportunity Resource subject area to show # of Opportunity Resource Roles associated to an Opportunity. Once the correct results are achieved, we go to the Advanced tab and grab the logical SQL associated with this query.

This is the logical SQL we use for this example:

SELECT
   0 s_0,
   "Sales - CRM Opportunity Resource"."Opportunity"."Opportunity ID" s_1,
   "Sales - CRM Opportunity Resource"."Opportunity Resource Facts"."# of Opportunity Resource Roles" s_2
FROM "Sales - CRM Opportunity Resource"   

Using the Advanced tab in BI Answers, click on the New Analysis button, copy and paste the following logical SQL in the new analysis window. The new SQL is an OBIEE - Equijoin of the SQL statements we showed in the two previous examples. These queries are based on Opportunity ID.

SELECT 
Opty_Terr.saw_0 OptyID, 
Opty_Terr.saw_1 OptyTerrAsgnCnt,  
Opty_Resource.saw_1 OptyRsrcRoleCnt 
FROM 
( 
SELECT 
"Opportunity"."Opportunity ID" saw_0, 
"Opportunity Territory Facts"."# of Opportunity Territory Assignments" saw_1 
FROM "Sales - CRM Opportunity Territory" ) Opty_Terr, 
( 
SELECT 
"Opportunity"."Opportunity ID" saw_0, 
"Opportunity Resource Facts"."# of Opportunity Resource Roles" saw_1 
FROM "Sales - CRM Opportunity Resource" ) Opty_Resource 
Where Opty_Terr.saw_0 = Opty_Resource.saw_0 
ORDER BY OptyID 

Note: Use any text editor to combine the logical SQL statements copied from the previous two queries. Make sure that you have extra space at the end of each line before you plug the SQL in the new analysis window.

This image shows how from your analytic editor Advanced tab, you can click New Analysis to create an analysis with SQL, or from the home page you can go to New > Analysis > and click Create Analysis from Simple Logical SQL.

This is the result of that query.

Note: If you create a new analysis using this SQL, any hierarchical columns, member selection, groups or formatting is stripped out.