Combining Columns Using Set Operations

Display data in different ways for your analytics by combining columns from different subject areas. Combining is done using set operations such as Union or Intersect.

Guidelines for Combining Columns

• The number and data types of the combined columns must be the same. The number of rows that are returned for each column can differ.
• Select columns from the same subject area or from a different subject area. Oracle recommends that to prevent conflicts in data type definitions, use the same columns if you are joining different subject areas.
• Specify one Set operation for one collection of criteria. For example, if you create criteria from the Foo subject area, you can apply only one Set operation to those columns. You cannot apply different Set operations to different columns in the collection of criteria.
• You cannot use hierarchical columns, selection steps, or groups when you combine criteria.

Combine Columns from One Subject Area

You can combine two columns from a single subject area to create comprehensive data sets. Say the Foo subject area contains the Offices folder, which contains the D1 Office and D2 Department columns. You can combine these two columns and create a column called Offices and Departments. You can include the Union All Set operation to specify that this new column shows all the values from both columns in a single column in a table.

Combine Columns Using Set Operations and Adding Columns from Related Subject Areas

There are important differences between Set operations and adding columns.

• When you combine columns using Set operations, the analysis results show a single newly combined column governed by a Set operation.
• When you add columns from related subject areas to an analysis, the results show each added column individually. For example, if you have the appropriate permissions you can create an analysis by selecting one column from a primary subject area and selecting another column from a related subject area.

Oracle recommends that you UNION on the same column as a best practice. The reason for this recommendation is that, as an example, if your UNION is done between different columns such as "Customer Row ID" and "Customer"."Level 8 Account ID", and if the underlying data type of "Customer"."Level 8 Account ID" can be changed from Numeric to VARCHAR, then the UNION would stop working. There is a risk that the underlying datatype for one of the columns might change leading to errors. Columns may have ID in their name, but they are not always necessarily numeric.

Combine Columns from One or More Subject Areas

Combining columns from multiple subject areas allows you to blend data from columns that express a combined and enhanced data insight. Here's how you do it:

1. In the Analysis editor, create an empty analysis and add a subject area.
2. In the Criteria tab, select the columns to include in the analysis.
3. In the Selected Columns pane, click the Combine results based on union, intersection, and difference operations button to display the Select Subject Area menu.
4. Select a subject area that contains the columns to combine with the columns that you have previously included. For example, click Foo-Sales.

   The Set Operations area is displayed in the Selected Columns pane. Note the boxes with dotted line borders that are displayed below the criteria. These boxes indicate the kind of column that you must combine with those that you have previously included. For example, the boxes might include "Add Column (D1 Office)" and "Add Column (1 - Revenue)". This text indicates that the columns that you include in each of those boxes are combined with the previously selected D1 Office and 1 - Revenue columns using a Set operation to form a new column. When you combine measure columns, no arithmetic operations are performed.

5. In the Subject Areas pane, select the columns to combine with the originally selected columns. For example, from the Offices folder, select D2 Department and from the Base Facts folder, select 1 - Revenue.
   Note: The boxes that previously had dotted line borders now hold the columns that you have just selected. You have now specified the columns to combine.
6. Click the Union button under the Result Columns link. Select the operation type to use for combining the columns. For example, select the Union All type. The result columns are those that are displayed in views after applying the set operation of the derived columns.
7. Click the Result Columns link. Note that the Selected Columns pane is updated toshow the newly combined columns that you have just created. You can work with these columns as you do other columns in this pane. For example, you can rename the first column (that is the single newly combined column) by following these steps:
   • Click the Options button for the D1 Office column.
   • Select Column Properties.
   • Select Column Format.
   • Ensure that Custom Headings is selected.
   • In the Column Heading box, enter Offices and Departments.
   • Click OK. Click the Results tab to view the columns in a table in the Compound Layout.

Note: Data formatting that has been saved as the system wide default for a column is not reflected in a combined column. If you want the data formatting in the combined column, then you must reapply it to the combined column.