Using columns from several subject areas, you can simply develop an analysis. If a local dimension is used in a cross-subject area analysis, you must provide a measure from each subject area to support the join. If you don’t want the metric to appear in your analysis, you can hide it in the results. It’s important to note that all of the subject areas must be from the same Oracle Cloud application.
There are three broad categories of cross-subject area analyses:
- Using common dimensions – A common dimension is a dimensional attribute that appears in all of the analyses’ topic areas. These dimensions are considered common dimensions across subject areas and can be utilized to create a report that spans multiple subject areas.
- Using common and local dimensions – In a cross-subject area inquiry, a local dimension is only available in one of the combined topic areas.
- Using set operators like union, union all, intersection, and difference, you can combine multiple result sets from diverse subject areas.
Following steps needs to be followed while using multiple subject area in OTBI report
- On the Home page, click Create and then click Analysis.
- In the Select Subject Area dialog, search for and select a subject area.
- In the Criteria tab, expand the dimensions and add a column to the analysis
- In Subject Areas, click Add/Remove Subject Areas.
- To save a column to the catalog, in the Selected Columns pane, click Options beside the column name, and click Save Column As.
- In the Subject Area region of the Criteria tab, expand the dimensions and add a column to the analysis.
- 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.
- 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.
- 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.
- Click the Results tab to see the analysis results.
- Click the Criteria tab again to return to the analysis definition.