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 analyses. 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.

  1. Start the wizard from the Reports and Analytics work area or the Reports and Analytics panel tab (if available).

    • To create a new analysis click Create and select Analysis. Select a subject area for your analysis and click theContinue button.

    • To edit an existing analysis, in the Reports and Analytics work area, select it in a folder or the favorites list, click its ellipsis icon and select Edit. In the Reports and Analytics panel tab, click the analysis, then click Edit.

  2. In the Select Columns page, 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, and click OK. You can't remove the original subject area selected for the analysis. To remove any other subject area, first remove its columns from the analysis.

  3. From here on, make selections in a series of analysis-definition pages, selecting Next or Back to navigate among them.

    • In the Select Columns page, expand your subject area and folders within it to choose the columns to include in your analysis. Also set options for those columns.

    • In the Select Views page, determine whether your analysis is to include a table, a graph, or both. For either, select among several types. If you include both, select the order in which they appear.

    • In Edit Table and Edit Graph pages, select options that apply to your table and graph layouts. Each of these pages is active only if you selected the item it applies to in the Select Views page.

    • In the Sort and Filter page, optionally apply filters to columns to refine the selection of records in your analysis, and apply sorts to them to order your results.

    • In the Highlight page, optionally add color highlights based on numeric thresholds you set.

    • In the Save page, enter a name for the analysis, select a catalog folder to save it in, and click Submit.

In general, these pages are designed so that procedures for using them are readily apparent. Even so, here are some things you will want to know.

Select Columns Page

For each column in your analysis, you select an Interaction option.

  • Two of the options, Default and Drill, do the same thing: If you click on a column header, the analysis adds a column displaying values at the next hierarchical level. (For example, if you click on Control Name, the analysis adds a Control ID column.) If you click on a column value, the analysis adds the subordinate column, but also filters to display only records containing the value you clicked.

  • Navigate to Transaction: If you click on a value from a column for which this option is set, the analysis presents a link to the record of an object the value applies to.

    For such links to work, however, further configuration is required: You need to define paths to the records that are to be opened. If you want to use this option, you're probably better off creating the analysis in the BI Catalog. However, you can create it in the wizard, then edit it in the BI Catalog. See the topic titled Link Analyses to Application Pages.

  • None: This option in effect turns the Default option off and turns nothing on. Nothing happens if you click on a value in a column for which this option is set.

For each column, you can also select a Hidden option. This prevents the analysis from displaying the column, but leaves its values available for use behind the scenes, for example in filters.

Select Views Page

You can add a graph to your analysis only if it includes at least one column from a fact folder. (You can include a table in your analysis no matter what columns you select for it.) A fact column contains numeric values, such as counts of incidents returned by advanced controls. Other columns contain attributes of objects, such as names of advanced controls. Without numeric values, there's nothing to base a graph on.

You can create a title for the analysis in this page, but doing so is optional. You also create a name for the analysis in the Save page, and that one is required. If you create both, the analysis displays both; they don't have to be the same.

You can use a Preview option to ensure the analysis returns data you expect. Turn it on or off in this page or in subsequent wizard pages. Once it's on, the preview remains on in other wizard pages you navigate to, unless you turn it off.

Edit Table Page

Here are the layout options you can select for tables:

  • Columns: This is the default. Each column you assign this value to appears as a column in the table.

  • Prompt For: In a prompt field, you select among values from the column you're configuring. For example, you would select a date if the column were Calendar Month Start Date. The table would then display only rows containing the value you selected.

  • Section By: The table is divided into sections. Each value of the column you're configuring becomes a header, and the section beneath each header includes rows containing that value.

  • Excluded: The column you select is no longer available to the view you're configuring. The column is hidden, and its values are unavailable for behind-the-scenes tasks such as filtering. However, the column remains available to other views in your analysis.

Edit Graph Page

You can't apply layout options to fact columns. For other columns, graph layout options include Prompt For, Section By, and Excluded, which have the same effect as they do in tables. You can also apply these options in graphs:

  • Vary Color By: Each value in the column you're configuring is represented by a distinct color in the graph.

  • Group By: Values in the columns you assign this option to are combined in the graph. For example, if you select this option for the State and Calendar Month Start Date columns in a bar graph, each bar represents a particular status on a particular date.

Highlight Page

You can use this feature only if your analysis includes at least one fact column, and you can apply it only to fact columns.

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 Analysis to View Resource Qualifications from HCM

Create a resource qualification analysis in the Reports and Analytics work area to view real-time qualifications for project resources from the Human Capital Management (HCM) application.

When you create a resource qualification analysis, you select the Resource Name and Resource Qualification columns to display the qualifications associated to the resource. Resource Name and Resource Qualification dimensions are valid for the various facts associated with the Project Resource Management - Resource Management Real Time subject area. When you use Oracle Transactional Business Intelligence (OTBI) for a cross-dimension query, OTBI selects the fact column from the implicit fact column defined for a subject area to display the result. For the Project Resource Management - Resource Management Real Time subject area, the implicit fact column defined is # of Requests. Hence, OTBI displays the qualifications from the requests to which the resource is associated and not from the HCM application. For the analysis to display the qualifications as defined in their HCM Talent Profile, you must add the # of Qualifications column that is the relevant measure.

Creating a Resource Qualification Analysis

  1. Navigate to the Reports and Analytics work area.

  2. Click Browse Catalog.

  3. From the New menu, click Analysis.

  4. From Select Subject Area window, click Project Resource Management - Resource Management Real Time.

  5. In the Subject Area pane, expand:

    1. Resource folder and double-click Resource Name.

    2. Qualification folder and double-click Qualification Name.

    3. Project Resource Supply folder and double-click # of Qualifications.

  6. In the Selected Columns section, from the Resource Name column criteria, click Filter.

  7. In the New Filter window, select a resource from the Value list, and click OK.

  8. In the Selected Columns section, from the # of Qualifications column criteria, click Column Properties.

  9. On the Column Format tab, select the Hide check box and click OK to hide the # of Qualifications column in the analysis.

  10. Click Save Analysis.

  11. By default, the application selects the My Folders folder. Enter a name for your analysis, such as Resource Qualification Analysis, in the Name field.

  12. Click Catalog menu.

  13. Expand My Folders and open the Resource Qualification Analysis. The analysis contains the following details for your selected resources:

    • Resource Name

    • Qualification Name

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. Note that the subject areas have to be from the same Oracle Cloud application.

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.

  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. Bring 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.

    • Move content around.

    • Change the layout of a page.

FAQs

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's 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're 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.

When project application administrators create new project or task codes, the codes don't appear in the OTBI subject areas. The administrator must first clear the internal business intelligence (BI) server cache for the codes to display in the OTBI subject areas. Running a SQL command clears the BI server cache.

Follow these steps to clear the cache.

  1. Navigate to the Reports and Analytics work area.

  2. Click Browse Catalog.

  3. On the Oracle BI Catalog page, in the global header, click the Administration link.

  4. On the Administration page, click the Issue SQL link.

  5. In the Issue SQL command window, enter the below command:

    call SAPurgeInternalCache('1');

  6. Click Issue SQL.

  7. Close all your browser sessions and log in again. You can now see the new project and task codes in the OTBI subject areas.