Analytics > Designing an Analysis > Step 1: Defining Criteria in Analytics > Combining Multiple Subject Areas in a Single Analysis
Combining Multiple Subject Areas in a Single Analysis
The simplest and fastest way to create and generate an analysis is to use a single subject area. If the dimension columns and metrics that you are interested in are all available from a single subject area, then you should use that subject area to build the analysis.
If your analysis requirements cannot be met by any single subject area, then you can create an analysis that combines information from two or more subject areas. You can create a combined analysis using the Set operations, which were supported in releases earlier than Release 40 and continue to be supported. For more information about using the Set Operations, see Combining Analyses Using Set Operations. Starting with Release 40, you can also create a combined analysis using common dimensions. A common dimension is a dimension that is available in all of the subject areas that are to be combined. A local dimension is a dimension that is available in one or more of the subject areas, but is not available in all of them. You can include columns from local dimensions as well as columns from common dimensions in a combined analysis.
If you want to use metrics from multiple subject areas, then there is an advantage to using columns from common dimensions only, if your requirements can be met in this way. When an analysis that combines multiple subject areas 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 is returned from the different subject areas is merged using the common dimensions. If you use columns from common dimensions only, then the result set returned by each subject area query is at the same granular level, and it can be cleanly merged and rendered in the analysis.
When you include columns from local dimensions in your analysis, some of the results might be at different granular levels.
When combining two or more subject areas in a single analysis, note the following points:
- The subject area that you select when you first create an analysis is referred to as the primary subject area. Any subject areas that you later add to the analysis are referred to as the related subject areas.
- You can combine a real-time subject area with other real-time subject areas, and you can combine an historical subject area with other historical subject areas. You cannot combine a real-time subject area with an historical subject area.
- You must include a metric from the primary subject area and a metric from each of the related subject areas in the combined analysis. You do not have to display the metrics or use them, but you must include them. You can hide a metric if you do not need it in the analysis.
- To make sure that the results for all of the subject areas are at the same granular level, include at least one column from at least one common dimension in your combined analysis.
- When you select columns from a common dimension, select only columns that are available in the same dimension in all of the subject areas.
In some cases, a dimension that is common to two or more subject areas might not contain the same set of columns in each of the subject areas. In such cases, it is recommended that you select only columns that are available in the common dimension in all of the subject areas.
- When selecting columns from a common dimension, select all of the columns from a single subject area.
- If you select columns from one or more local dimensions as well as columns from one or more common dimensions, then you must enable the dimensionality variable for the query, so that the query will return the total value for the selected measures. If you select columns from common dimensions only, then you do not need to enable the dimensionality variable. The procedure later in this topic includes information on how to enable the dimensionality variable for a query.
For an example of combining multiple subject areas in a single analysis, see Example of Combining Multiple Subject Areas in a Single Analysis.
The following procedure describes how to combine multiple subject areas in a single analysis.
Before you begin. To perform this procedure, your user role must have the Cross-Subject Areas Analytics privilege.
To combine multiple subject areas in a single analysis
- In the Criteria tab or the Results tab of the analysis editor, in the Subject Areas pane, click the Add/Remove Subject Areas icon.
- Select the check box for each of the related subject areas that you want to add to the analysis.
You can now select the columns and metrics that you want from the primary subject area and the related subject areas. Remember that you must include a metric from each of the subject areas.
- If you selected columns from both local dimensions as well as columns from common dimensions, then when you finish adding the columns and metrics to the analysis, do the following:
- Click the Advanced tab.
The Advanced tab shows the SQL code that will be submitted to the Oracle BI server when the analysis is executed.
- In the Advanced tab, select the Dimensionality check box and then click Apply SQL.
The SQL code is automatically updated to enable the dimensionality variable, so that the query will return the total value for the selected measures in the dimensions.
NOTE: If you do not enable the dimensionality value and apply the SQL when using local dimensions, or if you do not add metrics from all the subject areas involved in the analysis, then you might experience ODBC errors or get incorrect results. You might also get incorrect results when you combine subject areas for which there are no relationships defined at the subject area level. In such cases, create a combined analysis using SET operators instead to get the results that you need.
If you want to remove a related subject area from an analysis, perform the steps in the following procedure.
To remove a related subject area from an analysis
- In the Criteria tab or the Results tab of the analysis editor, delete from the analysis all of the columns and metrics that you selected from the related subject area.
NOTE: If any of the columns or metrics that you selected from the related subject area remain in the analysis, then you will not be able to remove the related subject area from the analysis.
- In the Subject Areas pane, click the Add/Remove Subject Areas icon, and deselect the check box for the related subject area.
|