Combine Subject Areas in Analyses

You can combine multiple subject areas in an analysis.

Topics:

About Cross-Subject Area Joins

You can create analyses that combine data from more than one subject area.

Review guidelines for creating these joins in My Oracle Cloud Support (KM ID KB89403).

Note:

Use joins sparingly with cross subject areas as they can affect report performance.

Cross-subject area analyses can be classified into three broad categories:

  • Using common dimensions - A common dimension is a dimensional attribute that exists in all subject areas in the analysis. These dimensions are considered common dimensions between subject areas and can be used to build a cross-subject area report.
  • Using common and local dimensions - A local dimension is available only in one of the combined subject areas in a cross-subject area query.
  • Combining more than one result set from different subject areas - You do this using set operators such as union, union all, intersection and difference.

Create a Cross-Subject Area Analysis

You can easily create an analysis using columns from multiple subject areas.

When you create a cross-subject area analysis, you must 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.

  1. On the Home page, click Create and then click Analysis.
  2. In the Select Subject Area dialog, search for and select a subject area.
  3. In the Criteria tab, expand the dimensions and add a column to the analysis
  4. In Subject Areas, click Add/Remove Subject Areas.
  5. To save a column to the catalog, in the Selected Columns pane, click Options beside the column name, and click Save Column As.
  6. In the Subject Area region of the Criteria tab, expand the dimensions and add a column to the analysis.
  7. If the column is a local dimension, add a measure from the subject area.

    In any join query, you must add at least one measure from all subject areas involved, otherwise unexpected results or errors might occur.

  8. If you want to hide the measure in your analysis, select its Column Properties, and in the Column Format tab of the Column Properties dialog, select Hide and click OK.
  9. If you're using a local dimension, in the Advanced tab, navigate to Advanced SQL Clauses, and select Show Total value for all measures on unrelated dimensions, then click Apply SQL.
  10. Click the Results tab to see the analysis results.
  11. Click the Criteria tab again to return to the analysis definition.