5Create and Edit Analytics

This chapter contains the following:

Data Structure for Analytics

Oracle Business Intelligence repository contains the metadata that defines which columns (or piece of data) are available for you to include in analyses, and where data for each column originates. The repository is organized into subject areas, which contain folders with the columns.

Note: You can also use the Oracle BI repository as a data source for reports.

Columns

This table describes the three types of columns.

Column Type Description Example Icon for Column Type

Fact

Provides a measure of something, meaning that the values are numbers.

Total

Yellow ruler

Attribute

Represents a piece of information about a business object, with values that are dates, IDs, or text.

Start Date

Gray paper

Hierarchy

Holds data values that are organized in a hierarchical manner.

Time, with sublevels:

  • Year

  • Quarter

  • Month

Column: Hierarchy of blue squares

Sublevel: Blue or white square

Subject Areas

When you create an analysis, you first select a subject area, which contains columns related to a specific business object or area. You then open folders within the subject area to find the columns to include in your analysis.

Folders

Each subject area has one fact folder and a number of dimension folders. Folders can have subfolders.

  • Fact folders:

    • Contain fact columns.

    • Are usually at the bottom of the list of folders and are usually named after the subject area.

  • Dimension folders:

    • Contain attribute and hierarchical columns.

    • Are joined to the fact folder within a subject area.

      For example, if your analysis has the Currency attribute from a dimension folder, you see currencies in the results. If you also add the Total fact, then your analysis includes only records with both a currency and a total amount. The more columns you add, the smaller the query set for your analysis.

    • Can be common folders or common dimensions that appear in more than one subject area.

      Note: If your analysis has columns from multiple subject areas, then you:
      • Include columns only from dimension folders that are common to all of those subject areas. At least one such column is mandatory.

      • Must include one column from the fact folder in each of those subject areas.

Analysis and Report Limits

Limits on analysis and reports prevent long-running queries.

Examples include:

  • Analytics row limits

  • Query time limits

  • Report Memory Guard limits

Note that when you are reporting on HTML content, for example, Message Body of an SR Message is a report column, you can observe that HTML tags are displayed in the report, in addition to the desired content. In order to display the content correctly without the tags, set the Column Properties > Data Format to Treat Text as HTML for the column. Select Save as the system-wide default for that particular column (not the default for this data type). Once you do this, any time a report is created that contains this column, it will automatically display HTML content correctly

This table describes the limits that constrain queries.

Application Context Limit Description Limit Setting

Answers

Analysis

Query Time Limit - Minutes

Time (in minutes) allowed for a query to return from the database.

10

Answers

Analysis

Rows Retrieved By SQL Query

Maximum rows retrieved by a logical SQL query.

75,000

Answers

Analysis

Rows Exported - CSV/Tab/XML - All Data - Export On Demand

Maximum data rows exported from the analysis into CSV, Tab Delimited and XML formats. This limit is enforced when the user exports directly from the analysis.

65,000

Answers

Analysis

Rows Exported - CSV/Tab/XML - All Data - Export Through Agent

Maximum data rows exported from the analysis into CSV, Tab Delimited and XML formats. This limit is enforced when you export using a scheduled agent.

25,000

Answers

Table

Maximum Rows Used To Populate Table

Maximum rows that can returned from an analysis query when populating the table. Anything higher than this results in failure to render the table.

75,000

Answers

Table

Rows Exported - Excel/PDF - Per View - Export On Demand

Maximum rows exported from a view to formats like PDF, Excel and Powerpoint. This limit is enforced when the user exports directly from the analysis.

25,000

Answers

Table

Rows Exported - Excel/PDF - Per View - Export Through Agent

Maximum rows exported from a view to formats like PDF, Excel and Powerpoint. This limit is enforced when you export using a scheduled agent.

25,000

Answers

Table

Cells

Maximum number of data cells in a table view.

1,000,000

Answers

Table

Sections

Maximum number of sections available for the view.

25

Answers

Table

Default Rows Per Page In View

Default rows displayed per page in the view. This can be increased in the view properties.

25

Answers

Table

Maximum Rows Per Page In View

Maximum rows that can be displayed per page in the view.

500

Answers

Table

Prompt Values

Maximum number of values allowed in Table Prompt. Exceeding this value results in 'Exceeded configured maximum number' error.

1,000

Answers

Pivot Table

Maximum Rows Used To Populate Pivot Table

Maximum number of rows that can be returned from an analysis query when populating the pivot table. Anything higher than this results in failure to render the pivot table.

40,000

Answers

Pivot Table

Rows Exported - Excel/PDF - Per View - Export On Demand

Maximum rows exported from a view to formats like PDF, Excel and Powerpoint. This limit is enforced when the user exports directly from the analysis.

25,000

Answers

Pivot Table

Rows Exported - Excel/PDF - Per View - Export Through Agent

Maximum rows exported from a view to formats like PDF, Excel and Powerpoint. This limit is enforced when you export using a scheduled agent.

25,000

Answers

Pivot Table

Cells

Maximum populated cells of data in the view.

1,000,000

Answers

Pivot Table

Sections

Maximum sections available for the view.

25

Answers

Pivot Table

Default Rows Per Page In View

Default rows displayed per page in the view. This can be increased in the view properties.

25

Answers

Pivot Table

Maximum Rows Per Page In View

Maximum rows that can be displayed per page in the view.

500

Answers

Pivot Table

Prompt Values

Maximum number of values that can be returned in a Pivot prompt. Exceeding this value results in 'Exceeded configured maximum number' error.

1,000

Answers

Pivot Table

Columns In Export

Maximum exportable columns.

300

Answers

Chart

Sections

Maximum sections available for the view.

25

Answers

Chart

Slider Values

Maximum amount of values available on the slider.

150

Answers

Chart

Rows Displayed

Maximum amount of data points available on the chart.

2,000

Answers

Chart

Prompt Values

Maximum number of values listed in the view prompt. Exceeding this value results in 'Exceeded configured maximum number' error.

1,000

Answers

Simple Trellis

Maximum Rows Used To Populate Simple Trellis

Maximum rows that can returned from an analysis query when populating the simple trellis. Anything higher than this results in failure to render the simple trellis.

40,000

Answers

Simple Trellis

Cells

Maximum populated cells of data in the view.

1,000

Answers

Simple Trellis

Sections

Maximum sections available for the view.

10

Answers

Simple Trellis

Default Rows Per Page In View

Default rows displayed per page in the view. This can be increased in the view properties.

10

Answers

Simple Trellis

Maximum Rows Per Page In View

Maximum rows that can be displayed per page in the view.

100

Answers

Simple Trellis

Column In Export

Maximum exportable columns.

75

Answers

Simple Trellis

Prompt Values

Maximum number of values listed in the view prompt. Exceeding this value results in 'Exceeded configured maximum number' error.

1,000

Answers

Simple Trellis

Rows Exported - Excel/PDF - Per View - Export On Demand

Maximum rows exported from a view to formats like PDF, Excel and Powerpoint. This limit is enforced when the user exports directly from the analysis.

6,500

Answers

Simple Trellis

Rows Exported - Excel/PDF - Per View - Export Through Agent

Maximum rows exported from a view to formats like PDF, Excel and Powerpoint. This limit is enforced when you export using a scheduled agent.

100

Answers

Advanced Trellis

Maximum Rows Used To Populate Advanced Trellis

Maximum rows that can returned from an analysis query when populating the advanced trellis. Anything higher than this results in failure to render the advanced trellis.

40,000

Answers

Advanced Trellis

Cells

Maximum populated cells of data in the view.

5,000

Answers

Advanced Trellis

Sections

Maximum sections available for the view.

25

Answers

Advanced Trellis

Default Rows Per Page In View

Default rows displayed per page in the view. This can be increased in the view properties.

10

Answers

Advanced Trellis

Maximum Rows Per Page In View

Maximum rows that can be displayed per page in the view.

100

Answers

Advanced Trellis

Columns In Export

Maximum exportable columns.

150

Answers

Advanced Trellis

Prompt Values

Maximum number of values listed in the view prompt. Exceeding this value results in 'Exceeded configured maximum number' error.

1,000

Answers

Advanced Trellis

Rows Exported - Excel/PDF - Per View - Export On Demand

Maximum rows exported from a view to formats like PDF, Excel and Powerpoint. This limit is enforced when the user exports directly from the analysis.

10,000

Answers

Advanced Trellis

Rows Exported - Excel/PDF - Per View - Export Through Agent

Maximum rows exported from a view to formats like PDF, Excel and Powerpoint. This limit is enforced when you export using a scheduled agent.

100

Answers

Treemap

Prompt Values

Maximum number of values listed in the view prompt. Exceeding this value results in 'Exceeded configured maximum number' error.

1,000

Answers

Treemap

Cells

Maximum populated cells/tiles of data in the view.

5,000

Answers

Treemap

Sections

Maximum sections available for the view.

50

Answers

Narrative

Rows

Maximum records available in the view.

40,000

Answers

Ticker

Rows

Maximum records available in the view.

40,000

Answers

Dashboard

Rows Exported - Excel/PDF - Per View - Export On Demand

For tables, pivot tables and trellis views, if the view is set to "Fixed headers with scrolling content", rows visible on the dashboard are exported, and no more. For example, if a table is showing five rows before scrolling, five rows are exported, even if the table has more rows.

If the view is set to "Fixed headers with scrolling content", an export of the number of rows specified in the view's "Rows Per Page" parameter is attempted. This setting doesn't override other row or cell limits, so setting this to a very high row limit only guarantees the standard view's export limits are honored when exporting from a dashboard. The "Rows Per Page" setting can be higher than the actual limit of rows that can be viewed per page in Answers. Even if the higher row limit doesn't apply at runtime, it applies at export. For example, if the table limit is 500 rows per page, but you set it to 25,000, an attempt is made to export 25,000 rows.

Answers

Dashboard

Rows Exported - Excel/PDF - Per View - Export Through Agent

The same rules apply as for Export on Demand, but the exports are subject to limits enforced when exporting data through agents.

BI Publisher

Memory Guard

Maximum report data size for online reports

Maximum data size allowed for online report viewing. When a report data size exceeds the value, the report receives an 'XML Output generated exceeds specified file size limit' error.

300MB

BI Publisher

Memory Guard

Maximum report data size for offline (scheduled) reports

Maximum data size allowed for scheduled reports. When a report data size exceeds the value, the report receives an XML Output generated exceeds specified file size limit error.

500MB

BI Publisher

Memory Guard

Free memory threshold

Minimum value for free JVM space. If the report data size exceeds the threshold, then the report is paused to wait for free memory to become available. The report waits for the time specified in the property Maximum Wait Time for Free Memory to Come Back Above Threshold Value. If the free memory does not rise back above the minimum in the wait period specified, the report request is rejected.

500MB

BI Publisher

Memory Guard

Maximum report data size under the free memory threshold

Maximum single report data size allowed when free JVM memory is under the specified threshold value set in Free memory threshold. For example, assuming the default setting of 500MB, if the data generated for a single report exceeds one-tenth of the value set for Free memory threshold, or 50MB, then processing is terminated.

Free memory threshold/10

BI Publisher

Memory Guard

Maximum Wait Time for Free Memory to Come Back Above the Threshold

Maximum time in seconds that a request to run a report waits for free JVM memory to exceed the threshold value. If the free memory becomes available within the time specified, the request proceeds immediately. If free memory is still below the threshold value after the time specified, the request is rejected. For online requests, the larger this property value, the longer the browser will wait for a request to run.

30

BI Publisher

Memory Guard

Process timeout for online report formatting

For online reports, the maximum time in seconds that a formatting process is allowed to run. If an online report formatting process exceeds the limit, the report errors.

600

BI Publisher

Data Model

Maximum data size limit for data generation

Maximum XML data size that can be generated from the execution of a data model. This setting applies to both online and scheduled report requests. When the size of the file generated exceeds the value, execution of the data model is terminated and an exception is generated.

500MB

BI Publisher

Data Model

Maximum sample data size limit

Maximum file size of a sample data file that can be uploaded to the data model editor.

1MB

BI Publisher

Data Model

DB fetch size

20

BI Publisher

Data Model

SQL Query Timeout (in seconds)

600

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.

Edit Prebuilt Analytics

Out of the box you have plenty of analytics to start off with. But you will probably want to modify many of them and make them your own.

  1. Make a copy of the analytic you want to edit.

  2. Name the copy.

  3. Find the new analytic and click Edit.

  4. Select a dimension and the contents appear in the pane.

  5. From the tabs select Criteria.

  6. Drag and drop columns from the Subject Area pane to the editing palette.

Right-click the gear icon and get options for editing that column such as filtering and deleting that column from the analysis.

CX Sales Repository and Session Variables for Business Intelligence

You can use these repository and session variables 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

These variables are available for CX Sales only.

Variable Name Type Usage Offering

USER_MAX_TERR_HIER_LEVEL

Session Variable

Returns the maximum fixed hierarchy level from Territory Hierarchy for the logged-in user. This variable can be used to dynamically retrieve the maximum territory level that the logged-in user owns.

Customer Experience

USER_ORG_HIER_LEVEL

Session Variable

Holds the hierarchy level within the organization selected by the user in a report.

Customer Experience

USER_PARTY_ID

Session Variable

Stores the PARTY_ID from UserPVO.

Customer Experience

CURRENT_ENTERPRISE_PERIOD

Repository Variable

Returns the value of the Current Fiscal Period.

Customer Experience

CURRENT_ENTERPRISE_PERIOD_END_DATE

Session Variable

Returns the End date of Current Enterprise Period.

Customer Experience

CURRENT_ENTERPRISE_QUARTER

Repository Variable

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

Customer Experience

CURRENT_ENTERPRISE_QUARTER_END_DATE

Session Variable

Returns the End date of Current Enterprise Quarter.

Customer Experience

CURRENT_ENTERPRISE_YEAR

Repository Variable

Returns the value of Current Enterprise Week in the YYYY Week format.

Customer Experience

NEXT_ENTERPRISE_PERIOD

Repository Variable

Returns the value of Next Enterprise Period.

Customer Experience

NEXT_ENTERPRISE_QUARTER

Repository Variable

Returns the value of Next Enterprise Quarter in the YYYY Q n.

Customer Experience

NEXT_ENTERPRISE_YEAR

Repository Variable

Returns the value of Next Enterprise Year in the FYYYYY format.

Customer Experience

PREVIOUS_ENTERPRISE_PERIOD

Repository Variable

Returns the value of Previous Enterprise Period.

Customer Experience

PREVIOUS_ENTERPRISE_QUARTER

Repository Variable

Returns the value of Previous Enterprise Quarter in the YYYY Q n format.

Customer Experience

PREVIOUS_ENTERPRISE_YEAR

Repository Variable

Returns the value of Previous Enterprise Year in the FYYYYY format.

Customer Experience

SVC_SR_ASSIGN_INIT_DT

Session Variable

Returns a date when the POD was upgraded to latest version.

Customer Experience

Deep Linking

Register Business Intelligence to Support Deep Linking

Deep linking lets you to navigate from an analysis on the interface pages to specific attributes and objects in your work areas so that you can work with them directly. What does that mean? It means that if you are looking at an analysis on leads, for example, you can click a record on the analytic and go edit the lead record on the edit object page. To support deep links, use the Setup and Maintenance work area to register the Business Intelligence Server as a third-party application.

Register Business Intelligence Server as a Third-Party Application

To register the BI Server:

  1. Use the Manage Third Party Applications page.

  2. Click Create.

  3. Enter AnalyticsApp as the application name.

  4. Enter the full URL for the BI Server (for example: http://host:port/analytics/saw.dll).

  5. Enter Analytics Server as the Partner Name.

  6. Click Save and Close.

Create Deep Links

Create deep links in your analytics that let you navigate from an analytic to specific attributes and objects in your transactional pages to work with them directly.

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

  2. Create or edit an analysis. Use a subject area that includes the attribute you want to link to in the transactional pages, and ensure that you include the object ID.

  3. On the Criteria tab, click the Options icon for the attribute column on which you need a deep link and select Column Properties.

  4. Open the Interaction tab in the Column Properties dialog box.

  5. In the Primary Interaction list in the Value section, select Action Links to display the Action Links table.

  6. Select the Do not display in a pop-up if only one action link is available at runtime option.

  7. Click Add Action Link.

  8. In the New Action Link dialog box, enter the link text for the navigation.

  9. Click the Create New Action icon and select Navigate to a Web Page.

  10. In the Create New Action dialog box, enter the URL to navigate to, which references parameters you define in the prompt. For shared analyses://<hostname>/fscmUI/faces/deeplink?objType=@{1}&objKey=<object ID>=@{2}&action=@{3}&returnApp=AnalyticsApp&returnAppParams=PortalGo%26path=</shared/Custom/<folder>/@{6}

    For personal analyses stored in My Folders: //<hostname>/fscmUI/faces/deeplink?objType=@{1}&objKey=<object ID>=@{2}&action=@{3}&returnApp=AnalyticsApp&returnAppParams=PortalGo%26pather=/users/@{5}/@{6}

  11. Click Define Parameters to add corresponding parameters for the URL.

  12. In the Define Parameters section, click Add Parameter to add the following mandatory parameters:

    Name Prompt Type of Value Value Fixed Hidden Notes

    1

    Object Type

    Value

    <Object Type>

    Selected

    Selected

    Used for shared and personal analyses.

    2

    <Prompt Name>

    Column Value

    Column Name in "<table.name>"."<column.name>" format

    Selected

    Selected

    Used for shared and personal analyses.

    3

    Action

    Value

    edit

    Selected

    Selected

    Used for shared and personal analyses.

    4

    returnApp

    Value

    AnalyticsApp

    Selected

    Selected

    Used for shared and personal analyses.

    5

    Logged in User

    Session Variable

    USER

    Selected

    Selected

    Session variable parameter used for personal analysis permissions.

    6

    Report Name

    Value

    Analysis Name

    Selected

    Selected

    Used for shared and personal analyses. The name of the analysis to return to after working in the transactional page.

  13. Click Options to set how the page opens.

  14. In the Action Options dialog box, select Open in New Window to open the page in a new window.

  15. Save your work.

  16. If you have provided the Return Application name, a Return icon is available in the transactional page to return to the analysis.

Report on System User Activity

Interested in user activity on your application? With a few setup steps you can get critical information on how users are working in your application. We call it user adoption. User adoption gives you analytic data to evaluate user activity in terms of logging in and out of the application, as well as what they are doing when they are logged in.

Here are some of the business benefits of user adoption analytics:

  • See the relationship between being signed into the application an interaction with objects.

  • Leverage usage patterns and behaviors from successful users as best practice guidelines for other users.

  • Gain insight on usage and trends for potential future investment opportunities.

  • Identify improvements that can increase adoption and usage.

These are the main steps for setting it up:

  • Enable system tracking in Setup and Maintenance

  • Get the role of FBI_USER_SYSTEM_USAGE_TRANSACTION_ANALYSIS_DUTY

New Subject Areas for User Adoption

There are three new subject areas to use when you build tracking analytics. You can build analytics with any or all of these subject areas, depending on what data you want to see. For example, you can compare user log in against user interaction with objects, or you can track user adoption and object activity across teams within the organization.

  • User System Usage: This subject area provides the key user adoption metric at the application user level.

  • Sales - CRM Resource: This subject area provides the mapping between the user and the specific resource object.

  • Sales - CRM Object Activity: This subject area supports reporting on object activity metrics such as number of records created and number of records updated.

About Resource and Usage Subject Areas

Resource and Usage subject areas make it possible for you to analyze many different aspects of user adoption such as:

  • Adoption and usage from different channels over a period of time.

  • User Log in by channels, such as web, mobile, mail.

Here are some of the details on usage tracking values:

  • The primary metric used in user adoption subject areas is # of Active Days. This metric provides information about an activity such as how long a signed in user has stayed on the device.

  • The # of Active Days value is not a sign-in count.

  • The day value is based on the server time zone.

  • Sessions can be tracked for usage channels for web and mobile.

  • Tracking differentiates between user activities made directly by the user, or through a proxy user.

  • Two additional Metrics are used in these subject areas, they are # of Active Users/Resource and # of Users/Resource.

About the Object Activity Subject Area

The primary metrics used for object activity subject area is Insert Action Count and Update Action Count. These metrics provide information on the number of records added, and the number of records updated.

Here is the information you can analyze using the object activity subject area:

  • Track the type of object that was updated.

  • Name of the employee who did the update.

  • Manager of the employee that did the update.

  • Number of records that were added.

  • Number of records that were updated.

  • Date the record update was made.

  • Month the update report is produced.

Remember These Usage Reporting Musts

Setting up the time parameters for user adoption reporting can be tricky. For best results follow these guidelines:

  • To be sure the analytics you created in R12 work after you upgrade to 18A+, you should apply the # of Active Days > 0 filter.

  • Add a filter for a specific year. The User System Usage subject area shows usage for all Time Periods and Users/Resources. This reporting method tracks when the user is actively using the application and the months where there were no activities/system usage. For this reason you should define specific time filters. Without time filters, the report shows results for the complete 100 years Time dimension.

  • Avoid reporting by date. Instead use Month or Quarter.

  • Filter by group of users, instead of individual users.

  • Apply a filter on the # of Active Days fact > than 0 to ensure the analytic shows positive reporting values.

Enable User Adoption Tracking

User adoption should be set up on your application before you do anything else.

  1. Go to Setup and Maintenance.

  2. Search for Manage Administrator Profile Values.

  3. Search for profile option code FND_TRACK_USER_ACTIVITY and ensure that it is enabled.

    This figure shows the profile value as "ENABLED" for FND_TRACK_USER_ACTIVITY profile.

    Profile enabled for BI User System Usage Duty role

Get the User System Usage Duty Role

The User System Usage subject area holds all the activity information for users of the application. This subject area is secured and can be accessed by users who have the role FBI_USER_SYSTEM_USAGE_TRANSACTION_ANALYSIS_DUTY or by users who have the BI Administrator role.

  1. Sign in as a Security Manager. For example, IT_SECURITY_MANAGER.

  2. Click Tools > Security Console.

  3. Select the Roles tab, if the tab is not already selected by default.

  4. Click Create Role.

  5. Fill in the required information.

  6. Click Next until you come to the Role Hierarchy page.

  7. Click the role name and then click Add Role Membership.

  8. Search for the BI Duty Role by name or code: FBI_USER_SYSTEM_USAGE_TRANSACTION_ANALYSIS_DUTY .

    This is the Add Role Membership page with the new duty role content.

    Add role membership
  9. From the search result select BI Administrator Role and click Add Role Membership.

  10. Click Next until you get to the Users page.

  11. Click Add User and search for the users to assign this role. Click Add user to Role.

  12. Click Next.

  13. Click Next to go to the Summary and Impact Report. Click Save and Close.

Add Additional Users for System Usage

Once the new role is created, you can add more users to a role if need be.

  1. Sign in as a Security Manager. For example, IT_SECURITY_MANAGER.

  2. Click Tools > Security Console

  3. Select the Users tab.

  4. Search for the user to be assigned the role.

  5. Select the user and choose Edit.

  6. Click Add Role.

  7. Search for role you want to assign, in this case the BI System Usage SA Custom Role.

  8. Select the Role and click Add Role Membership.

    This shows the search roles, and the Add Role Membership.

    Add role membership.
  9. Click Done.

  10. Click Save and Close.

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 an Activity Analysis for Your Sales Team

An activity analysis can help you make sure your resources are being used to your greatest advantage.

Analytics to Answer Business Questions

To build custom analytics you start by defining your objectives. What problem do you want to solve? What opportunities might you be missing? What insights have potential to drive your sales forward? Here are a few examples:

  • Is there any workload balancing issues in my team?

  • Which team members are available to focus on a new product?

  • What are my urgent overdue tasks, and what's my workload for this week?

  • How is my team performing?

  • Which accounts are my sales representative focusing on and does this align with business priorities?

  • Which accounts can I focus on more to meet our business objectives?

Create Your Own Activity Analysis

You Sales Manager wants to see how much their team is interacting with potential opportunities. How many times has a potential customer been contacted? And how have they been contacted? Let's try creating an activity analysis to help our Sales Manager figure things out.

  1. In BI, Click New analysis.

  2. Select CRM - CRM Activity Real Time.

    • From the dimension Customer, add Account Name.

    • From the dimension Employee, add Last Name.

    • From the Facts folder, add # of Activities.

  3. You can filter Customer to see a specific account, select the properties on Account Name.

  4. You select the filter to show only A.C. Networks, for example, and click OK.

  5. Click Results. The Results tab shows your analysis at this point. Move back and forth from Criteria to Results at any time to see your analysis.

  6. Name and save your analysis.

Now you can check out everything that has been going on with with A.C. Networks. Is it time to reach out to them again and tell them about some new stuff you're offering?

Create an Opportunity Analysis

You can keep an eye on your revenue pipeline using Opportunity analytics. Opportunities can be looked at over different time periods, or the current time period. You can examine each stage of an opportunity and the associated revenue for that stage.

How to Create an Opportunity Analysis

The simple analysis you build in this example shows Total Revenue, Open Pipeline, and Won Revenue for sales representatives for the current quarter. Keep in mind you can add any columns you want from the CRM Pipeline subject area. You can even add columns from other subject areas. But for this exercise, let's keep it simple.

This is an opportunity analysis. Let's create one now.

Create opportunity analysis.

To Create an Opportunity analysis:

  1. Navigate to BI.

  2. Select the New drop-down list.

  3. Select Analysis.

  4. Go to the editing palette and on the Subject Area panel expand the Sales - CRM Pipeline subject area.

    This subject area Sales - CRM Pipeline includes the following columns and facts to use for this example.

    Columns and Facts Definition

    Sales Stage

    Contains data on the stage the opportunity on in the pipeline.

    Amount

    Contains the amount of the opportunity revenue at a stage in the pipeline.

    Open Pipeline

    Contains the amount of the revenue not closed in the pipeline.

    Won Revenue

    Contains the amount of revenue closed in this quarter.

    Total Revenue

    Contains the amount of all opportunity revenue in the pipeline for this quarter.

    Enterprise Quarter

    Contains real time opportunity custom data

    Pipeline Facts: Number of Opportunities

    Contains real time revenue data

  5. In the Subject Areas Panel, in the Sales - CRM Opportunities subject area, expand Historical Sales Stage. Add the columns: Sales Stage, Sales Stage Name, and Sales Stage Row ID.

  6. Expand Pipeline Detail Facts: Add Amount, Open Pipeline, , Won Revenue, % of Total Amount.

  7. Expand Pipeline Facts:# of Opportunities.

  8. Expand Employee. Add Employee Row ID.

  9. Expand Time. Add Enterprise Quarter.

  10. Expand Employee. Add Employee Login. On the editing palette, on Employee Login, hover over the gear image and from the drop-down list menu and select Filter. Under Add More Options select Session Variable. In the Session Variable add: USER_PARTY_ID. Select OK. From the drop-down list again, select Delete. You only added the column to add the variable. Adding this variable tells the application to show data for the signed in user specific to her opportunities only.

  11. That's it. Now let's see what it looks like. Select the Results tab and there it is. Don't forget to name and save your analytics.

Create and Filter an Historical Trending Analysis

Looking at a broad view of your pipeline helps you get a sense of what things might look like in the future. How does the revenue in your pipeline look this quarter, compared to previous quarters? Maybe there are potential problems down the road you need get a jump on now. Let's build a historical trending analysis and see how things are shaping up.

How to Create an Historical Trending Analysis

Historical Trending analytics use the subject area Sales - CRM Opportunity Sales Stage Snapshot. This subject area is specifically designed for reporting on opportunities and revenues against their daily, weekly or monthly (depending on the enterprise calendar period setup), quarterly and yearly trends or to compare opportunity and revenue data against specific points in time.

To Create an Historical Trending Analysis:

  1. Navigate to BI.

  2. Choose New.

  3. Select Analysis.

  4. Select the Sales - CRM Historical Pipeline subject area. You may need to scroll down.

  5. In the regional area, expand Opportunity.

  6. Double-click Opportunity Name to add it to the Selected Columns section. You may need to scroll down to locate Opportunity Name.

  7. Add Owner First Name.

  8. Add Owner Last Name.

  9. Expand Customer and add Customer Name.

  10. Collapse Customer and expand Industry.

  11. Add Industry Name and collapse Industry.

  12. Expand Product and add Product Name.

  13. Collapse Product and expand Revenue.

  14. Add Revenue ID and collapse Revenue.

  15. Expand Pipeline Snapshot Date.

    Notice that you can choose a pipeline date, period (week or month), quarter, or year.

  16. Add Pipeline Snapshot Date and collapse Pipeline Snapshot Date.

  17. Expand Historical Pipeline Detail Facts.

    Notice that you can add facts on revenue lines, open or closed opportunities, or both.

  18. Add Opportunity Line Revenue.

  19. Verify your columns.

    After you create a historical trending report, you should create filters for your report. The next section shows you how.

Filtering Historical Analytics

You can filter your analytics to show open or closed opportunites, and more.
  1. Click Create a filter for the current Subject Area, in the local area, under Filters.

  2. In the drop-down list, click More Columns.

  3. In the dialog box, expand Opportunity.

  4. Select Opportunity Status Category.

  5. Click OK.

  6. Verify that Operator = is equal to / is in.

  7. Select Value = OPEN.

  8. Click OK to close the New Filter dialog box.

    After creating your filters for your historical trending report, name and save your analysis.

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.

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.

Best Practices for Working with Analytics

For best results when creating and editing analytics, Oracle recommends you refer to the "BI Reports and Dashboard - Best Practices" document located on the Oracle Support site at: https://support.oracle.com/epmos/faces/DocumentDisplay?id=2331484.1. You can also click the link in Related Topics to download the PDF document directly.

FAQs for Analytics 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.

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.