2Analytics

This chapter contains the following:

Introduction to Oracle Transactional Business Intelligence for Financials

In this example, you view an analysis called the Payables Aging Summary. This analysis was created using Oracle Transactional Business Intelligence, an ad hoc reporting tool. You can use the tool to create analyses, dashboards, and infolets using information from your Oracle Financials Cloud applications.

Viewing an Analysis

  1. Let's start from the Financial Reporting Center and search for the Payables Aging Summary.

  2. Click the Expand icon to see details like type and location.

    In this example, the type is Analysis. The location shows you where the analysis is stored.

  3. Click the title.

    Now you can see the analysis. The information here is real time and based on the latest transaction information.

    This analysis has bars and a table that show totals by aging categories like Current, Overdue 21 - 60 days, and so on. You can use different views like time series lines, bars, tables, and heat maps in your analyses. An analysis can use more than one type of view to help arrange information in a way that's useful for your audience.

  4. Click the Overdue 91-180 Days bar.

    A table shows the invoices that make up the total for this category. You can see invoice details like supplier name, invoice due date, invoice number, and unpaid amount.

  5. Click the Buckets drop-down list to change categories.

    Now you see the details that make up another aging total.

  6. Go back to the previous page.

  7. Click the Analysis icon after the title.

  8. Click the Edit link to open the layout.

    You can make changes here and save them in the same location, or you can save them in another area that you have access to. You can also create another analysis. Just click the New menu to get started.

Explore Financial Subject Areas in Oracle Transactional Business Intelligence

In this example, you examine Financial subject areas in Oracle Transactional Business Intelligence. Here's the list of Financial products with subject areas: Assets, Budgetary Control, Cash Management, Expenses, General Ledger, Intercompany, Payables, Receivables, Revenue Management, and Subledger Accounting.

  1. Start from the Financial Reporting Center.

  2. Click Tasks.

  3. Click Open Workspace for Financial Reports.

  4. Select Navigate > Applications > BI Catalog.

  5. You have to create an analysis to see the subject areas you have access to:

    1. Click the New icon.

    2. Click Analysis.

    Subject areas are organized in folders and the information they contain varies based upon reporting requirements. General Ledger subject areas cover account balances, journals, and period statuses.

  6. Select the General Ledger Transactional Balances - Real Time subject area. The term Real Time in the subject area name indicates the information is up-to-date as of the latest general ledger journal posting. You can use this subject area to create trial balances and general queries to review changes in account balances over time. Here are some of the folders that appear in the Subject Area pane.

    • Balancing Segment

    • Cost Center Segment

    • Currency

    • Time

    • Ledger

    • Ledger Set

    You can expand folders to see their components. For example, the Time folder contains accounting period and year. If you have more than one ledger, you can create an analysis that filters by ledger and ledger set. You can query a specific general ledger balance, or groups of general ledger balances.

  7. Click the Add or Remove Subject Areas icon in the Subject Areas toolbar to view more subject areas and the type of information they hold.

    For example, with the Payables subject areas you can examine information about suppliers, supplier balances, and invoices, including freight and tax charges. You can also see how invoices and payments affect the general ledger. An analysis can show which invoices were paid on time and whether checks have cleared.

    The Receivables subject areas include customer information, customer balances, and invoice details such as terms and receipts, credit memos, and revenue adjustments. Similar to Payables, you could analyze how receipts affect general ledger account balances.

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.

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.

Create and Edit a Financial Analysis Using a Wizard

You can use a wizard that guides you through creating and editing analyses. Even though the wizard doesn't give you all available features, you can still use it to make typical changes, for example, adding views and filters.

Note: The wizard isn't available for dashboards and you can't use it to delete analyses.

Creating an Analysis

  1. Navigator > Tools: Reports and Analytics.

  2. Click Create and select Analysis.

  3. Select a subject area that you want to analyze, for example, the General Ledger - Journals Real Time subject area.

  4. Select the columns to include:

    • Journal Batches: Batch Details: Journal Batch

    • Journal Headers: Header Details: Journal Header Description

    • Journal Lines: Line Details: Line

    • Journal Lines:

      • Accounted Debit

      • Accounted Credit

    • Time: Accounting Period Name

  5. Set the following options:

    • Interaction: Default

    • Hidden: Leave unchecked

  6. Click Next

  7. Enter a title of Journal Report to appear at the beginning of the analysis.

  8. Select the type of table and click Next.

    Tip: At any point after this step, you can click Submit to go to the last step, to save your analysis.
  9. Enter the name of your analysis By Cost Center and select the My Folder.

  10. Click Submit.

Editing an Analysis

  1. Navigator > Tools: Reports and Analytics.

  2. Select your analysis By Cost Center in the pane and click Edit.

  3. Add Posting Status: Posting Status Meaning.

  4. Click Submit: Save your analysis with the same name in the same folder.

    Tip: You can create a copy of the analysis, whether you edited it or not, just by saving it either with a new name or in a new folder.

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.

Add an Analysis to the Project Performance Dashboard

This example shows how to add an analysis to the Project Performance Dashboard. This dashboard is a page in the application, not a dashboard in the business intelligence (BI) catalog.

You can add predefined or user-defined analyses to any desktop page that you can personalize or edit.

The following table summarizes key decisions for this scenario.

Decisions to Consider In This Example

Which analysis do you want to add to the dashboard?

Project Income Statement

Caution: Ensure that the analysis doesn't query a large number of records. If it is, then the Project Performance Dashboard page can take a long time to open after you add the analysis.

Do you have to change the layout of the page?

Yes, to a two-column layout that is wider on the right side.

Note: The one- or two-column layout gives enough space to properly display analyses.

Do you want all or only one view of the analysis?

All views.

Are these changes for you only, or for all users of the Project Performance Dashboard?

You only.

Adding an Analysis

  1. Open the Project Performance Dashboard.

  2. Click your name in the global header and from the Administration submenu select Edit Pages....

  3. You need to be in an active sandbox session to add an analysis. Activate a sandbox if you're not already in a sandbox session. Click Activate Sandbox.

    If you are already in an active sandbox session, steps 3 to 7 are not required.

  4. On the Manage Sandboxes window, select a sandbox and click Set as Active.

  5. After you activate the sandbox, the application redirects you to the home page. On the Warning window, click Yes to continue.

  6. Open the Project Performance Dashboard.

  7. Click your name in the global header and from the Administration submenu select Edit Pages....

  8. Click Change Layout and select the Two columns, narrow left option.

  9. Click Add Content for the wider column.

    The Reports and Analytics folder in the Add Content dialog box contains what's in the BI catalog.

  10. Click through the folders in the catalog until you see the Project Financials Sample Reports folder. Click the name of the analysis in this folder, and click Add to include all views of the analysis.

    If you click Open or Project Income Statement, you can select a specific view to add.

  11. Click Close after you see the analysis added to the top of the wider column on the dashboard.

  12. Click Close to close the composer view and view the dashboard.

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.

Copy the Payables Invoice Audit Listing Dashboard

This example shows how to copy a predefined dashboard so that you can edit the copy and not the original. If you have appropriate roles, then you can (only if necessary) create dashboards or edit predefined ones directly. If not, then you must copy predefined dashboards and edit the copies.

The following table summarizes key decisions for this scenario.

Decisions to Consider In This Example

Which predefined dashboard are you copying?

Payables Invoice Audit Listing

Is the copied version for yourself only or for multiple users?

Multiple users

To copy the dashboard and get it working:

  • Make a copy of the Payables Invoice Audit Listing dashboard.

  • Copy the components of the dashboard, the analysis and prompt.

  • Edit the copied dashboard so that it contains the copied analysis and prompt instead of the predefined ones.

Save your copies under Shared Folders > Custom in the business intelligence (BI) catalog. You must create folders within Custom so that the copies have a folder path similar to the originals.

Copying the Dashboard

  1. Open the Reports and Analytics work area.

  2. In the Contents pane, select Shared Folders > Financials > Payables > Invoices > Payables Invoice Audit Listing > Invoice Audit Listing, and click the More link.

  3. With the Payables Invoice Audit Listing dashboard selected in the Folders pane, click the Copy button on the toolbar.

  4. In the Folders pane, select Shared Folders > Custom > Financials.

  5. On the toolbar, click the New button and select Folder.

  6. Enter Payables in the Name field and click OK.

  7. Create an Invoices subfolder within the new Payables folder.

  8. Click Paste on the toolbar to copy the dashboard into the Invoices folder.

Copying the Prompt and Analysis

  1. With the Invoices folder still open, click New on the toolbar and select Folder.

  2. Enter Prompts in the Name field and click OK.

  3. Create another folder with the name Report Components. (In this context, report refers to the dashboard.)

  4. In the Folders pane, select Shared Folders > Financials > Payables > Invoices > Prompts.

  5. For the Payables Invoice Audit Listing dashboard prompt, click the More link and select Copy.

  6. In the Folders pane, select Shared Folders > Custom > Financials > Payables > Invoices > Prompts, and click Paste on the toolbar.

  7. In the Folders pane, select Shared Folders > Financials > Payables > Invoices > Report Components.

  8. For the Payables Invoice Audit Listing analysis, click the More link and select Copy.

  9. In the Folders pane, select Shared Folders > Custom > Financials > Payables > Invoices > Report Components, and click Paste on the toolbar.

Editing the Copied Dashboard

  1. In the Folders pane, select Shared Folders > Custom > Financials > Payables > Invoices > Payables Invoice Audit Listing.

  2. Click the Edit link for the Invoice Audit Listing dashboard.

  3. Click the Delete button for the Payables Invoice Audit Listing dashboard prompt within the Search region.

  4. In the Catalog pane, select Shared Folders > Custom > Financials > Payables > Invoices > Prompts > Payables Invoice Audit Listing and move it into the Search region.

  5. Delete the Payables Invoice Audit Listing compound view within the Section 1 region.

  6. In the Catalog pane, select Shared Folders > Custom > Financials > Payables > Invoices > Report Components > Payables Invoice Audit Listing and move it into the Section 1 region.

  7. Click Save.

Repository and Session Variables for Business Intelligence

This table shows repository and session variables available for use in your analyses.

Variable Name Type Usage Offering

CURRENT_DAY

Session Variable

Returns the value of Current Date.

All

CURRENT_MONTH

Repository Variable

Returns the value of Current Month in the YYYY/MM format.

All

CURRENT_WEEK

Repository Variable

Returns the value of the current week in YYYY Weeknnn format.

All

CURRENT_QUARTER

Repository Variable

Returns the value of Current Quarter in YYYY Q n format.

All

CURRENT_YEAR

Repository Variable

Returns the value of Current Year in the YYYY format.

All

NEXT_MONTH

Repository Variable

Returns the value of Next Month in the YYYY / MM format.

All

NEXT_WEEK

Repository Variable

Returns the value of Next Week in the YYYY Week nn format.

All

NEXT_QUARTER

Repository Variable

Returns the value of Next Calendar Quarter in the YYYY Q nn format.

All

NEXT_YEAR

Repository Variable

Returns the value of Next Year in the YYYY format.

All

PREVIOUS_MONTH

Repository Variable

Returns the value of Previous Month in the YYYY/MM format.

All

PREVIOUS_WEEK

Repository Variable

Returns the value of Previous Week in the YYYY Week nn format.

All

PREVIOUS_QUARTER

Repository Variable

Returns the value of Previous Quarter in the YYYY Q nn format.

All

FAQs

What happens to modified analytics and reports when a release update is applied?

Updates don't affect the Custom folder or My Folder in the catalog. Anything saved there is preserved, including the analytics and reports you edited or created. But anything saved outside those folders, in the predefined catalog, is preserved only if the update doesn't include a new version of those BI objects.

If an update includes a new version of a predefined object that you edited outside the Custom folder, the changes you made are saved as a new object. The new version from the update overwrites the existing predefined object. And a copy of the existing object (with your edits) is automatically created in the same folder, with a new name that indicates it's a new version.

If the update includes a new version of both the predefined object and a folder in its file path, the new folder name, along with the new version of the object, overwrites the existing predefined folder and object. And a copy of the existing folder (along with your edited object) is automatically created. The folder is renamed to indicate that it's a new version, but your edited object isn't renamed.

Note: Future updates won't affect renamed objects or anything within a renamed folder.