8Create 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.

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.

Review SQL Statements Used in Analyses

You can review logical and physical SQL statements using either of the following procedures.

Logical and Physical SQL

Logical SQL is non-source specific SQL that's issued to the Oracle BI Server for an analysis. Logical queries use column names from the subject areas in the Presentation Layer in the repository (RPD) metadata. Based on the logical request, the BI Server issues optimized source-specific SQL to the actual data sources in the Physical Layer of the metadata. If you have administrative privileges, you can review both logical and physical SQL for analyses.

Edit Mode

  1. Open the analysis in Edit mode and click the Advanced tab.

  2. In the SQL Issued section, review the logical SQL statement.

Administration Page

  1. On the Administration page, in the Session Management section, click the Manage Sessions link.

    Note: You must be a Business Intelligence Administrator to access the Administration and Manage Sessions page.
  2. On the Manage Sessions page, in the Action column, click the View Log link to review the SQL statement.

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

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.

Multiple Subject Area Reporting

Reporting Using Data from More than One Subject Area

You can create analyses that combine data from more than one subject area using three different methods. This section discusses these methods and how the results vary depending on which method you use.

Oracle Transactional Business Intelligence organizes reporting data elements such as dimensions and facts by business function in subject areas. Each subject area contains a collection of dimensional attributes and measures relating to the one-dimensional STAR model and grouped into individual folders. The term STAR refers to the semantic model where a single fact is joined to multiple dimensions.

You can combine results in reporting using one of the three following methods:

  • Reporting that combines queries from multiple subject areas.

    • Using common (conformed) dimensions.

    • Using local and common (confirmed) dimensions.

  • Reporting that uses set operations (Union or Union All for example) to combine more than one result set from the same or different subject areas.

  • Reporting that uses logical SQL using the Advanced tab.

A Common dimension is a dimension that exists in all subject areas that are being joined in a report. For example, the Customer dimension is the common dimension for the Sales - CRM Pipeline and Marketing - CRM Leads subject areas.

A Local dimension is a dimension that is available in one or more of the subject areas, but is not available in all of the subject areas being joined. For example, Product is a local dimension for the Sales - CRM Pipeline subject area, and Activity is a local dimension for the CRM - CRM Activity Real Time subject area.

Examples of Reporting Using Data from More than One Subject Area

The simplest and fastest way to generate a report is to use a single subject area. If the dimension attributes and fact metrics that you're interested in are all available from a single subject area, then you should use that subject area for reporting. Using single subject areas for reporting results in better performance and is much easier to maintain. But if your reporting requirements can't be met by any single subject area, the recommended option is to build a query using dimensions common across subject areas.

Combining Queries from Multiple Subject Areas

When you're reporting with multiple subject areas, keep in mind that if you use three subject areas for an analytic, your common dimensions must exist in all three subject areas. Joining on common dimensions gives you the benefit of including any metric from any of the subject areas in a single analysis.

While you can create an analysis joining any subject area to which you have access, only a cross subject area query that uses common dimensions returns data that's at the same dimension grain. This happens so that the data is cleanly merged, and the results is an analysis that returns exactly the data you want to see.

When a cross subject area analysis is generated, separate queries are executed for each subject area in the analysis and the results are merged to generate the final analysis. The data that's returned from the different subject areas is merged using the common dimensions. When you use common dimensions, the result set returned by each subject area query is at the same dimensional grain, so it can be cleanly merged and rendered in the analysis. The next topic shows an example of reporting using different subject areas with common dimensions.

Common Dimension Reporting

In this case, to use multiple subject areas for reporting, we are using common dimensions. We want to pull the number of Opportunities, number of Opportunity Revenue Lines, number of Leads, and number of Activities by Customer. The common dimension in all three subject areas used for this analysis is Customer and different fact metrics are pulled from each subject area.

We're using these subject areas:

  • "Marketing - CRM Leads"

  • "Sales - CRM Pipeline"

  • "CRM - CRM Activity Real Time"

Start by creating an analysis using the Marketing - CRM Leads Subject area. You add the second and third subject area used in the cross-subject area analysis by clicking Add/Remove Subject Areas icon in the Subject Areas section.

This is the analysis editor showing the subject areas available to add to your new analytic.

Add and remove subject areas from analysis editor.

For this example, Customer is the common dimension:

  • "Marketing - CRM Leads"."Customer"

  • "Sales - CRM Pipeline"."Customer"

  • "Sales - CRM Sales Activity"."Customer"

These are the fact metrics:

  • "Marketing - CRM Leads"."Lead Facts" ."# of Leads"

  • "Sales - CRM Pipeline"."Facts". "Pipeline Detail Facts". "# of Opportunity Revenue Lines"

  • "Sales - CRM Pipeline"."Facts"."Pipeline Facts". "# of Opportunities"

  • "CRM- CRM Activity Real Time"."Facts"."Activity Facts"."# of Activities"

This is the Logical SQL for this example:

 SELECT
   0 s_0,
   "Marketing - CRM Leads"."Customer"."Corporate Account Name" s_1,
   "Marketing - CRM Leads"."Lead Facts"."# of Leads" s_2,
   "Sales - CRM Pipeline"."Pipeline Detail Facts"."# of Opportunity Revenue Lines" s_3,
   "Sales - CRM Pipeline"."Pipeline Facts"."# of Opportunities" s_4,
   "Sales - CRM Sales Activity"."Activity Facts"."# of Activities" s_5
FROM "Marketing - CRM Leads"
ORDER BY 1, 2 ASC NULLS LAST
FETCH FIRST 75001 ROWS ONLY

This shows the analytic results from combining common dimensions and facts from three subject areas.

Local and Common Dimension Reporting

This example reports on Customer, number of Opportunity Revenue Lines by Product, and number of Activities by Activity Type. Customer is the common dimension in both subject areas used for this query. Product is a local dimension to the Sales - CRM Pipeline subject area and Activity is a local dimension to CRM - CRM Activity Real Time. Different fact metrics are pulled from each subject area.

Note: Using local dimensions can impact the grain of the analysis resulting in repeated metrics for each of the rows.

We're using these subject areas:

  • "Sales - CRM Pipeline"

  • "CRM - CRM Activity Real Time"

Customer is the common dimensions:

  • "CRM- CRM Activity Real Time"."Customer"

  • "Sales - CRM Pipeline"."Customer"

These are the local dimensions:

  • "Sales - CRM Pipeline".Product"

  • "CRM - CRM Activity Real Time"."Activity"

These are the fact metrics:

  • "CRM- CRM Sales Activity Real Time"."Facts"."Activity Facts"."# of Activities"

  • "Sales - CRM Pipeline"."Facts"."Pipeline Detail Facts"."# of Opportunity Revenue Lines"

This is the logical SQL for this example:

SET VARIABLE ENABLE_DIMENSIONALITY=1; 
SELECT
   0 s_0,
   "Sales - CRM Pipeline"."Customer"."Corporate Customer Unique Name" s_1,
   "Sales - CRM Pipeline"."Product"."Product Name" s_2,  
    "Sales - CRM Sales Activity"."Activity"."Activity Type Code" s_3,
"Sales - CRM Pipeline"."Pipeline Detail Facts"."# of Opportunity Revenue Lines" s_4,
   "Sales - CRM Sales Activity"."Activity Facts"."# of Activities" s_5
FROM "Sales - CRM Pipeline"
ORDER BY 1, 2 ASC NULLS LAST, 3 ASC NULLS LAST, 4 ASC NULLS LAST
FETCH FIRST 75001 ROWS ONL

Here are the criteria and results of this local and common dimension query.

Local and common dimension query results

When using common and local dimensions use SET VARIABLE ENABLE_DIMENSIONALITY=1;in the Advanced SQL tab and click Apply SQL. Alternatively, select the 'Show Total value for all measures on unrelated dimensions' and click Apply SQL

Set variable ENABLE_DIMENSIONALITY=1

Guidelines for Reporting Using Cross-Subject Areas

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.

Here are some general guidelines to follow when you are combining subject areas with common and local dimensions:

  • Use a single subject area whenever possible. Single subject area analyses perform better and are easier to maintain.

  • When joining two or more subject areas, make sure at least one attribute from a common dimension is used.

  • When using common dimensions always choose attributes from the common dimension from a single subject area. For instance if you're using the Customer dimension to build a query between subject area 1 and subject area 2, then select all customer dimension attributes from either subject area 1 or from subject area 2. (Not some customer attributes from subject area 1 and some from subject area 2.) In some scenarios, the common dimension may have more attributes in one subject area than the other. In such a situation, you can only use the subset of common attributes for a cross-subject area query.

  • Always include a measure from each subject you are using. You don't have to display measures or use them, but you should include them. You can hide a measure if not needed.

  • When using common and local dimensions use SET VARIABLE ENABLE_DIMENSIONALITY=1;in the Advanced SQL tab and click Apply SQL button. Alternatively, select the 'Show Total value for all measures on unrelated dimensions' and click Apply SQL.

Using Set Operations to Combine More than One Result Set from the Same or Different Subject Areas

You can use set operations to combine more than one result from the same or different subject areas. This example creates a compound query that's a union of two result subsets from the same subject area.

We're going to combine results from these two subject areas using the following facts:

  • "# of Opportunity Revenue Lines" by "Territory" from the "Territory Management - CRM Pipeline" subject area (result 1)

  • "# of Sales Accounts" by "Territory" from the "Territory Management - CRM Sales Accounts" subject area (result 2)

Here is the logical SQL for this example:

SELECT
saw_0,
saw_1
FROM (
(
SELECT
IFNULL("Territory"."Territory", 'Unspecified')||'~~ # of Opty Revn Lines' saw_0,
"Pipeline Detail Facts"."# of Opportunity Revenue Lines" saw_1
FROM "Territory Management - CRM Pipeline"
)
UNION
(
SELECT
IFNULL("Territory"."Territory", 'Unspecified')||'~~ # of Sales Accounts' saw_0,
"Sales Account Facts"."# of Sales Accounts" saw_1
FROM "Territory Management - CRM Sales Accounts"
)
) t1 ORDER BY saw_0

To use the set operations, click the button "Combine results based on union, intersection and difference operations" on the Selected Columns header

Set operations button

This shows the two combined subject areas using a union set operation.

Subject area set union join subject areas shown

Here is the resulting report.

Subject area union join results

Combining Logical SQL Using the Advanced Tab

If your requirement can't be met by either of the two methods already discussed, then there's another advanced technique you can try. This technique lets you join multiple logical SQL statements based on common IDs or keys, which can be written against the same or different subject areas as with normal SQL. Both . Outerand Equijoins are supported.

This example illustrates how the fact metrics on Territory assignment and Resource assignment for Opportunities can be combined to get a single report using advanced logical SQL

First, we are going to write a BI Answers query using the Sales - CRM Opportunity Territory subject area to show the # of Opportunity Territory Assignments, and Opportunity. Once the correct results are achieved, go to the Advanced tab and grab the logical SQL associated with this query.

This is the logical SQL we use for this example:

SELECT
   0 s_0,
   "Sales - CRM Opportunity Territory"."Opportunity"."Opportunity ID" s_1,
   "Sales - CRM Opportunity Territory"."Opportunity Territory Facts"."# of Opportunity Territory Assignments" s_2
FROM "Sales - CRM Opportunity Territory" 
SQL for logical cross subject area combine

This is the result of the query using the Advanced tab.

Results for logical SQL combine

Second, we're going to write a second BI Answers query using the Sales - CRM Opportunity Resource subject area to show # of Opportunity Resource Roles associated to an Opportunity. Once the correct results are achieved, we go to the Advanced tab and grab the logical SQL associated with this query.

This is the logical SQL we use for this example:

SELECT
   0 s_0,
   "Sales - CRM Opportunity Resource"."Opportunity"."Opportunity ID" s_1,
   "Sales - CRM Opportunity Resource"."Opportunity Resource Facts"."# of Opportunity Resource Roles" s_2
FROM "Sales - CRM Opportunity Resource"   

Using the Advanced tab in BI Answers, click on the New Analysis button, copy and paste the following logical SQL in the new analysis window. The new SQL is an OBIEE - Equijoin of the SQL statements we showed in the two previous examples. These queries are based on Opportunity ID.

SELECT 
Opty_Terr.saw_0 OptyID, 
Opty_Terr.saw_1 OptyTerrAsgnCnt,  
Opty_Resource.saw_1 OptyRsrcRoleCnt 
FROM 
( 
SELECT 
"Opportunity"."Opportunity ID" saw_0, 
"Opportunity Territory Facts"."# of Opportunity Territory Assignments" saw_1 
FROM "Sales - CRM Opportunity Territory" ) Opty_Terr, 
( 
SELECT 
"Opportunity"."Opportunity ID" saw_0, 
"Opportunity Resource Facts"."# of Opportunity Resource Roles" saw_1 
FROM "Sales - CRM Opportunity Resource" ) Opty_Resource 
Where Opty_Terr.saw_0 = Opty_Resource.saw_0 
ORDER BY OptyID 
Note: Use any text editor to combine the logical SQL statements copied from the previous two queries. Make sure that you have extra space at the end of each line before you plug the SQL in the new analysis window.

This image shows how from your analytic editor Advanced tab, you can click New Analysis to create an analysis with SQL, or from the home page you can go to New > Analysis > and click Create Analysis from Simple Logical SQL.

Create new analysis to combine SQL for subject
are combined query

This is the result of that query.

subject area results
Note: If you create a new analysis using this SQL, any hierarchical columns, member selection, groups or formatting is stripped out.

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

Localize Catalog Captions

As an administrator, you can localize the names of reporting objects, or captions, that users create in the catalog. For example, you might localize the names of dashboards and analyses into Spanish and French.

To localize object names in the catalog, export the captions from the catalog, localize them, and upload back to the catalog.

  1. Export the default captions to an XML file.

  2. Localize the downloaded XML file.

  3. Upload the localized XML file.

After translated caption XML is uploaded, the new strings take effect once the cache refreshes. Translated strings are protected during an upgrade.

Export Captions from the Catalog

  1. Click Administration.

  2. Click Export Captions.

  3. Click Browse and select the folder you want to localize in the catalog, then click OK.

  4. Save the XML file.

Localize Your Captions

Translate the XML file into one or more languages.

Upload Localized Captions to the Catalog

After you have localized captions, upload the translated XML files, one for each language.

  1. Click Administration.

  2. Click Import Captions.

  3. Click Browse and select the localized XML file, then click OK.

  4. Use the Select Language option to select the language to which you have localized, then click OK.

  5. Save the XML file.

To download, review, or delete imported captions files, use the Manage Captions option.

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

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.