5Analytics Modification

This chapter contains the following:

Overview of Analytics Creation and Modification

You can edit and create your own analytics on your transactional data. The predefined analyses and dashboards help answer many of your business questions. But you can also create your own to meet your requirements.

Note: You can't use Direct Database Query in your analysis. Direct Database Query isn't supported in SaaS OTBI. To create a direct database SQL report, you can create a BI Publisher SQL data model and then create a report.

This table lists a few examples.

Task Example

Create an analysis

Your team needs a simple list of all your accounts, sorted by account ID. You include the account name, ID, and address in a new analysis, and sort the ID column.

Create a view

A predefined analysis has a bar graph. You save a new version of the analysis with a table view added to the graph.

Create a view selector

You later decide you want to toggle between viewing a table and a graph. You add a view selector that includes the table and graph views.

Edit a dashboard prompt

A predefined dashboard has a Start Date prompt. You make a copy of the dashboard and replace Start Date with a date range prompt.

Create a dashboard

You create a dashboard that includes an analysis and a report, which you can view together. You also add a dashboard prompt to filter both the analysis and the report.

Tip: When you create or edit analyses, you can use a wizard in the Reports and Analytics work area and panel tab.

Flexfield Attributes

Administrators can modify the business intelligence (BI) repository to determine the columns available for you to use in analyses.

Analyses

Use the wizards to quickly create and edit your analytics. You can use a wizard to create and edit most of your analytics, for example to select columns, add filters or views. You can also use advanced business intelligence features to create or edit dashboards or manage analyses and other objects in the catalog.

Create an Analysis

  1. Click Tools > Reports and Analytics in the Navigator.

  2. In the Reports and Analytics work area or the Reports and Analytics panel tab (if available), click Create and select Analysis.

  3. Select the subject area with columns you want to include.

  4. Optionally, click Add/Remove Subject Areas and, in the Add/Remove Subject Areas dialog box, select more subject areas or remove any that you no longer need, then click OK.

  5. Select the columns to include, set options for each column, and click Next.

  6. Optionally, enter a title for the analysis.

  7. Select the type of table or graph to include, specify the layout of the views, and click Next.

    Note: At any point after this step, you can click Finish to go to the last step, to save your analysis.
  8. Optionally, set more options for the table or graph, and click Next.

  9. Optionally, add sorts or filters based on any of the columns you included, and click Next.

  10. If you have a table, optionally define conditional formatting for select columns, for example to display amounts over a certain threshold in red. Click Next.

  11. Enter the name of your analysis and select a folder in the Custom folder or My Folder to save it in.

  12. Click Submit.

Edit an Analysis

  1. Click Tools > Reports and Analytics in the Navigator.

  2. Select your analysis and edit it. In the Reports and Analytics work area, click More for the analysis and select Edit. In the Reports and Analytics panel tab, click the analysis, then click Edit.

  3. Perform steps 4 through 10 from the preceding Create an Analysis task, as needed.

  4. To update an existing analysis in the Custom folder, select the same name in the same folder. To save this analysis as a new copy, either name it with a new name or save it in a new folder in the Custom folder.

  5. Click Submit.

Manage Analytics with Advanced Features

Wizards are an easy way to create or edit analyses. But you might have to use advanced features for complicated analyses or specific requirements. For example, you can create view selectors so that users can toggle between views within an analysis, or define criteria for filters using SQL statements.

You can also perform other actions on analyses, for example delete them or copy and paste them within the business intelligence catalog.

How to Create and Edit Analytics

  1. In the Reports and Analytics work area or the Reports and Analytics panel tab (if available), click the Browse Catalog button.

  2. Click the New button, select Analysis in Analysis and Interactive Reporting, and select a subject area.

    Or, select your analysis in the catalog and click Edit.

  3. This table lists the ways you can use the tabs.

    Tab Task

    Criteria

    Select and define the columns to include.

    Add filters.

    Results

    Add views and set options for results.

    Prompts

    Define prompts to filter all views in the analysis.

    Advanced

    View or update the XML code and logical SQL statement that the analysis generates.

    Set options related to query performance.

  4. Save your analysis.

More Actions on Analytics

  1. In the Reports and Analytics work area or the Reports and Analytics panel tab (if available), select your analysis and click Action and select More..

  2. Click More for your analysis and select the action you want, for example Delete or Copy.

Create a Change Order Approval Cycle Time by Reason Analysis

You are a product manager and you want to create an analysis to help you review change order approval cycle times. The approval times are to be analyzed by change reasons.

The following table summarizes key decisions for creating the analysis.

Decisions to Consider In This Example

What information to include?

  • Change Order Approval Cycle Time

  • Reason

What type of layout is required?

Table with a bar below it

Is this analysis available for others to use?

No. Save it to My Folders.

To create an analysis of Change Order Approval Cycle time by Reason, complete these tasks:

  1. Select columns

  2. Define the layout

  3. Save the analysis

Selecting Columns

  1. Navigate to Reports and Analytics work area.

  2. Click Create.

  3. Select Analysis.

  4. In the Select Subject Area Window, select the Product Management - Change Order Real Time subject area.

  5. On the Create Analysis: Select Columns page, expand the Product Management - Change Order Real Time folder.

  6. Expand the Change Order folder.

  7. Select Approval Cycle Time and click the Add icon button to move the column to the Selected Columns area.

  8. Expand the Change Order Reason folder.

  9. Select Change Order Reason and click the Add icon button to move the column to the Selected Columns area.

  10. Click Next.

Defining the Layout

  1. On the Create Analysis: Select Views page, enter Change Order Approval Cycle Time in the Name field.

  2. Next to the Table field, click None to open the Table menu.

  3. Select Table (recommended).

  4. Next to the Graph field, click None to open the Graph menu.

  5. Select Bar (recommended).

  6. Use the default layout value of Table above Graph.

  7. Click Next.

  8. On the Create Analysis: Edit Table page, select Change Order Reason and click the Move selected item to top of list icon.

  9. Click Next.

  10. On the Create Analysis: Edit Graph page, click Next.

  11. On the Create Analysis: Sort and Filter Page, click Next.

Saving the Analysis

  1. On the Create Analysis: Save page, enter Change Order Approval Cycle Time by Reason in the Analysis Name field, enter a description and then select My folders in the Save In region.

  2. Click Submit.

  3. Click OK.

Use UOM Columns with ToUOM Session Variable in Dashboards, Analyses

You can use predefined columns in some subject areas for Oracle Supply Chain Planning Cloud and the ToUOM session variable to author dashboards and analyses in which the primary or transaction unit of measure (UOM) for data is changed to a reporting UOM.

You can use these columns with the ToUOM session variable:

  • Reporting UOM Prompt: Use this column only to create prompts in dashboards and analyses. This column returns UOM values from INV_UNITS_OF_MEASURE_TL.UNIT_OF_MEASURE or UOM codes from INV_UNITS_OF_MEASURE_B.UOM_CODE.

  • UOM Conversion From Primary to Reporting: Use this column to get the conversion factor from the primary to the reporting UOM. Then, multiply the quantity in the primary UOM with the conversion factor to get the quantity in the reporting UOM.

  • UOM Conversion From Transaction to Reporting: Use this column to get the conversion factor from the transaction to the reporting UOM. Then, multiply the quantity in the transaction UOM with the conversion factor to get the quantity in the reporting UOM.

The predefined columns are available in these subject areas:

  • Costing - Work Order Costs Real Time

  • Inventory - Inventory Balance Real Time

  • Inventory - Inventory Transactions Real Time

  • Manufacturing - Actual Production Real Time

  • Order Management - Fulfillment Lines Real Time

  • Order Management - Order Lines Real Time

  • Shipping Real Time

You can use these columns and the ToUOM session variable in these ways:

  • Create a UOM prompt for a dashboard

  • Create a UOM prompt for an analysis

  • Set the ToUOM session variable to a UOM code in an analysis

Create a UOM Prompt for a Dashboard

To create a UOM prompt for a dashboard, follow these steps:

  1. Create an analysis for a subject area that has the predefined columns.

    To provide the user with the UOM conversion factor, include the UOM Conversion From Primary to Reporting or UOM Conversion From Transaction to Reporting column.

    1. Save the analysis.

  2. Create a column-based dashboard prompt for the subject area.

    1. Under Definition, click New, and select Column Prompt.

      The Select Column dialog box opens.

    2. Select the Reporting UOM Prompt column, and click OK.

      The New Prompt dialog box opens.

    3. Under Options, select Request Variable in the Set a variable list.

      A field appears under the list.

    4. Enter ToUOM in the field.

    5. Save the dashboard prompt.

  3. Create a dashboard.

    1. Add the dashboard prompt and analysis to the dashboard.

    2. Save the dashboard.

When you change the value in the Reporting UOM Prompt list of the dashboard, the UOM Conversion From Primary to Reporting or UOM Conversion From Transaction to Reporting column shows the conversion factor from the primary or transaction UOM to the reporting UOM.

Create a UOM Prompt for an Analysis

The procedure described in this section requires you to know the corresponding codes for UOM values.

To create a UOM prompt for an analysis, follow these steps:

  1. Create an analysis for a subject area that has the predefined columns.

    To provide the user with the UOM conversion factor, include the UOM Conversion From Primary to Reporting or UOM Conversion From Transaction to Reporting column.

  2. On the Prompts tab, click New, and select Variable Prompt.

    The New Prompt dialog box opens.

  3. In the Prompt for list, select Request Variable.

  4. In the field next to the Prompt for list, enter ToUOM.

  5. In the User Input list, select Choice List.

    The Choice List Values list appears.

  6. In the list, select SQL Results.

    A field for entering SQL code appears.

  7. Replace the SQL statement with another in the following format: SELECT DESCRIPTOR_IDOF("<subject area>"."Reporting UOM Prompt") FROM "<subject area>" FETCH FIRST 65001 ROWS ONLY

    Substitute values for the text within the angle brackets.

    For example, a complete SQL statement is as follows: SELECT DESCRIPTOR_IDOF("Inventory - Inventory Balance Real Time"."- Main"."Reporting UOM Prompt") FROM "Inventory - Inventory Balance Real Time" FETCH FIRST 65001 ROWS ONLY

  8. Click OK.

  9. Save the analysis.

When you run the analysis, you're first prompted to select a UOM code in a list. When you click OK, the analysis results are displayed, and the UOM Conversion From Primary to Reporting or UOM Conversion From Transaction to Reporting column shows the conversion factor from the primary or transaction UOM to the reporting UOM.

Set the ToUOM Session Variable to a UOM Code in an Analysis

The procedure described in this section requires you to know the corresponding codes for UOM values.

To set the ToUOM session variable to a UOM code in an analysis, follow these steps:

  1. Create an analysis for a subject area that has the predefined columns.

    To provide the user with the UOM conversion factor, include the UOM Conversion From Primary to Reporting or UOM Conversion From Transaction to Reporting column.

  2. On the Advanced tab, under Advanced SQL Clauses, in Prefix, enter an SQL statement in the following format: SET VARIABLE ToUOM='<UOM Code>';

    Substitute a UOM code for the text within the angle brackets.

    For example, a complete SQL statement is as follows: SET VARIABLE ToUOM='B050';

  3. Click Apply SQL.

  4. Save the analysis.

When you run the analysis, the UOM Conversion From Primary to Reporting or UOM Conversion From Transaction to Reporting column shows the conversion factor from the primary or transaction UOM to the reporting UOM. Moreover, you can't select any other code for the reporting UOM.

How can I provide access to a custom analysis or report to multiple users?

By default, shared analyses and reports are owned by the duty role of the user who created them. All users with that duty role are able to view, modify, and save the objects.

Cross-Subject Area Joins

Overview of Cross-Subject Area Joins

You can create analyses that combine data from more than one subject area, or cross-subject area analyses. Review guidelines for creating these joins in MyOracle Support (Doc ID 1567672.1). Cross-subject area analyses can be classified into three broad categories:

  • Using common dimensions.

  • Using common and local dimensions.

  • Combining more than one result set from different subject areas using set operators such as union, union all, intersection and difference.

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.

Common and Local Dimensions

A local dimension is available only in one of the combined subject areas in a cross-subject area query.

Create a Cross-Subject Area Analysis

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

How to Create a Cross-Subject Area Analysis

  1. In the Reports and Analytic work area, click Browse Catalog.

  2. Click New and select Analysis

  3. Select a subject area.

  4. In the Criteria tab, expand the dimensions and add a column to the analysis.

  5. In the Subject Areas region, click Add/Remove Subject Areas.

  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. If it's preferable to hide the measure in your analysis, select its Column Properties, and in the Column Format tab of the Column Properties dialog box, select the Hide check box and click OK.

  8. If you're using a local dimension, in the Advanced tab, navigate to the Advanced SQL Clauses section, select Show Total value for all measures on unrelated dimensions, then click Apply SQL.

  9. Click the Results tab to see the analysis results.

  10. Click the Criteria tab again to return to the analysis definition.

Dashboards

Create and Edit Dashboards

You can create and edit dashboards and define their content and layout to organize your analytics and other objects to create meaningful and navigable palettes of information. In addition to objects in the business intelligence (BI) catalog, such as analyses, reports, and prompts, you can add text, sections, and more to a dashboard.

Create a Dashboard

  1. Open the Reports and Analytics work area, or the Reports and Analytics panel tab (if available).

  2. Click Browse Catalog.

  3. Click New and select Dashboard under Analysis and Interactive Reporting.

  4. Enter the dashboard's name and description, and select a folder to save in.

  5. With the Add content now option selected, click OK.

  6. Optionally, add more pages, or tabs, within the dashboard.

  7. Drag and drop items from the Dashboard Objects or Catalog pane to add content to a page.

  8. Click Save.

Note: The first dashboard page is saved with the page 1 name by default. To rename this page:
  1. Click the Catalog link.

  2. In the Folders pane, select your dashboard.

  3. For page 1, click More and select Rename.

  4. Enter the new name and click OK.

Edit a Dashboard

  1. In the Reports and Analytics work area or the Reports and Analytics panel tab (if available) select your dashboard in the pane and click More.

  2. Select your dashboard in the pane and click More.

  3. Click Edit.

  4. Perform steps 6 and 7 from the preceding Creating Dashboards task, and make other changes as needed, for example:

    • Remove content from the dashboard.

    • Drag and drop within a page to move content around.

    • Change the layout of a page.

FAQs for Analyses and Dashboards

What are subject areas, dimensions, attributes, facts, and metrics?

Information for your analytics is grouped into related functional areas called subject areas that contain fact and dimension folders with metrics and columns you can add to your analyses.

Dimension folders include the grouping of dimensional attributes for the subject area. Columns (such as date of birth or name) that are grouped for a dimension are known as attributes. Fact folders contain formulas for getting calculated numeric values, such as counts, sums, and percentages.

What's the relationship between dimensions and fact in a subject area?

A subject area is based around a single fact. The dimensions are all related to each other through the fact only. The fact is automatically included in any query that is created, even if none of the measures in the fact appear in the analysis.

What's a common dimension?

A common dimension is shared across multiple subject areas. For example, Time, Department, and Location are common dimensions. When constructing a cross-subject area analysis, only common dimensions can be used.

How can I determine which dimensions are shared across two subject areas?

If the dimensions exist in both subject areas, they are common dimensions, and are often among the first folders in a subject area. You can join any subject areas you have access to in Answers, but analyses are subject to the normalized data structure. Unless the underlying tables are joined by design, joining subject areas in Answers results in errors.

How can I identify subject areas to create analyses?

All OTBI subject area names end with the words "Real Time".

Can I change the columns in subject areas?

You can use only the available subject areas and their dimensions and facts. You can use other The data elements that are provided out of the box are the only ones that the customers can make use of. You can potentially use BI-enabled flexfields to analyze any column in the transactional tables.

Do analyses query transactional tables to display data?

Analyses run real-time queries of transactional tables through View Objects. Oracle Fusion data security, flexfields, user interface hints, lists of values, and other metadata are delivered through the View Objects.

What's a dashboard?

A dashboard is a container page to display analyses, reports, and other objects. Administrators can create shared dashboards for groups of users with common responsibilities or job functions. Personalized views can be created based on a user's permissions.