Cross-Subject Area Report Authoring Tips

isn’t limited to one area of information. It allows you to combine data from more than one subject area, opening up a world of possibilities for analysis. Such queries, referred to as cross-subject area analysis, are a testament to the comprehensive nature of the platform. The following section discusses different types of cross-subject area analyses and best practices for building cross-subject area analysis, giving you the confidence to explore the full potential of the platform.

There are three broad categories for building cross-subject area analyses:

Conformed, or common, dimensions have the same meaning and value across different fact tables or subject areas, meaning, they are common dimensions across all dimensions. For example, Ledger is a conforming dimensions across all the Fusion ERP Analytics subject areas.

Non-conformed, or non-common, dimensions are dimensions that aren’t attached to all the fact tables or subject areas. For example, the Financials - AP Holds subject area has hold folders that contain information which is only specific to the Financials - AP Holds subject area and isn’t relevant to other Financials - Accounts Payable subject areas.

Cross-Subject Area Analysis Using Conforming Dimensions

You can create a visualization from multiple subject areas using facts and confirming dimensions from all the subject areas. There are clear advantages to building a visualization that only uses conforming dimensions from across subject areas. You can use any metric from any subject area in your report and join on conforming dimensions. This allows you to include metrics from multiple subject areas in a single visualization.

Always follow the best practices mentioned in Common Authoring Tips.

General Guidelines

If all the required metrics and attributes for the report are available in a single subject area and fact, use that single subject area only and don’t create a cross-subject area query.
When you want to bring the data from more than one subject area, you must choose metrics from all the subject areas in the analysis.
Start with the necessary filters before you start building visualizations to ensure you use the best performing queries when you add the necessary metrics required in the visualization.
Always start by selecting all the columns in one subject area, including the facts and dimensions, and then add the facts from the second subject area.
Always start by adding the Accounting Calendar and Time Dimensions filters first. Restrict the data for one period, and then build on to the report by adding facts and columns one-by-one from one or more subject areas.
When joining two subject areas in a report, use at least one attribute from a common dimension. Refer to bus matrix for common (conforming) dimensions.

See Bus Matrix for the list of conforming dimensions for Fusion ERP Analytics.

Cross-Subject Area Analysis Using Conforming Dimensions and Non Conforming Dimensions

You need to study the subject areas you’re using when you create cross-subject area analyses using common (conforming) and non-common (non-conforming) dimensions in a single report. Each subject area has a fact and each fact has a transactional grain, so you need to review and understand the transactional grain of each subject area you use. See Subject Areas.

After reviewing the transactional grain of the subject areas, follow these guidelines to create your report.

First, analyze the structure of the subject areas and the type of report that you are planning to create.
Start by creating separate reports for the subject areas that you want to combine by adding the necessary metrics and the dimensions in the necessary reports.
Add more filters to reduce the data scope to understand and analyze the transaction grain of both, or all, the reports.
After analyzing, you can start by choosing one report and start adding non-conforming dimensions one by one from the other reports.
Review the logical and physical queries at each step. Understand how to construct a logical query and how to join two logical queries on common dimension attributes. See Expression Editor Reference.

Challenges with Conforming and Non-Conforming Dimensions

Creating reports with conforming and non conforming dimension can cause two types of issues:

Report errors
Unexpected results

To work around these issues, perform these steps:

Add expression filters in the report to force a specific join path. Oracle Analytics supports many types of filters to focus on the most interesting data in visualizations, canvases, and workbooks. Expression filters allow you to create complex filters using SQL expressions. For example, you can create an expression filter in to join a non-confirming attribute from one subject area to the non-conforming attribute of another subject area. See Filter Types.
Use action links. You need to break up the report with conforming and non-conforming dimensions into two separate reports. Add the reports to separate canvases in the same workbook or create separate workbooks and use a data action to link them together. A data action link passes context values as parameters to other workbooks or visualizations. You can use the data action to drill from one subject area to another. This creates an interactive way to review the content of the reports without having to join them together. Data actions are often required to move from one report to another, especially when you can’t join both reports. See Use Data Actions.

Combining Subject Areas Using Union Operators

You can create an analysis by combining data from one or more subject areas using union operators. To combine the data from one or more subject areas using union operators, you create datasets from local subject areas stored in your Oracle Analytics instance. See Create a Dataset from a Local Subject Area.

To create the datasets from local subject areas, you can drag and drop the subject areas and select the columns. Or you can copy the logical SQL from an existing report and create local subject areas based on the local SQL. Use this option to create the logical SQL queries using union operators.

General Guidelines

Start by creating separate reports for the subject areas that you want to combine by adding the necessary metrics and the dimensions in the necessary reports.
Analyze the local SQL statements in the logs of individual reports and use them to create a dataset.
Always have the necessary filters before you finalize the logical SQLs statements to ensure the queries are optimized when you build the dataset and add the metrics required in the visualization.
To simplify troubleshooting, add an additional field to identify which part of the local SQL the data is coming from.
For optimized performance, limit the amount of the data that is brought in to the dataset.

Bus Matrix

This Bus Matrix shows the conforming dimension for Fusion ERP Analytics subject areas. Review the spreadsheet before creating a cross-subject areas analysis.