4Subject Areas

This chapter contains the following:

How Subject Areas Work

Subject areas are the building blocks of your analytics. There are subject areas that come prebuilt with your application, and over 100 analytics built from the included subject areas. Let's look a little closer at how subject areas work.

All of the electronic activity that happens each day in your company is stored and can be used to look at current and historical data, as well as predict future trends and outcomes. This information is saved and grouped and packaged as objects. The objects hold information called attributes which are pieces of information related to that object. For example, an object called Customer would hold information related to that customer, such as name, address, phone number, company and so on.

Object attributes are organized in columns which are used to provide real-time transactional reporting.

You can use the prebuilt subject areas to build your own analytics. Or you can build your own subject areas and use them for building or editing analytics. Most importantly, the focus of a subject area is to provide a way for you to gain access to key insights about your organization.

This graphic shows the subject area for Sales - CRM Pipeline with some columns added on the editor to show employee revenue per customer. The resulting analytic in bar chart format shows revenue on the y axis and customer on the x axis with employee name key by color on the right.

Subject area editing window with resulting analytic.

Choosing the Right Subject Area for Your Analytics

We provide a wide variety of subject areas to provide insight on a lot of different business activities. So how do you know which subject area is right for you? Let's say you're building your own analytic because you want to know something about your pipeline. Let's start by looking at the subject area list. All of the subject areas that come with your application are detailed in the Subject Areas for Transactional Business Intelligence guide. In addition, you will find the business questions that subject area supports. The business questions are key to helping you choose the right subject area. Here are the business questions listed for CRM Pipeline, from the subject area guide.

Image of subject area book focus on pipeline.

These questions provide a starting point for you in terms of what information this subject area provides. Do any of these questions reflect what insights you're looking for on a particular area of your business? Maybe they come close, but not exactly what you're looking for. If you click on one of the business questions you're taken to a page that details the roles that have access to the data that supports analytics around that question.

Subject area roles

Here are some more examples of subject areas and the business questions they can answer.

Subject Area Name Example Business Questions

Sales - CRM Pipeline

  • Are my sales representatives moving their opportunities fast enough.

  • How is each member on my team performing on deal size, account coverage, and win rate?

  • Is my team converting leads to opportunities fast enough?

  • What are the most likely reasons that we lose against our key competitors?

  • What are the top 10 open opportunities?

Sales - CRM Forecasting

  • What are my forecasts and closed revenues for this quarter?

  • Are revenues closed in time for their forecast figures?

  • Does the forecast versus pipeline show a healthy picture?

  • What were my forecast revenues for the same period last year?

Sales - CRM Sales Activity

  • Is there any work load balancing issues on my team?

  • I want to rebalance my team workloads. Based on upcoming activity levels what are my resource levels?

  • Are there accounts that are being heavily pursued?

  • How can I identify neglected but strategic accounts?

View Subject Area Details

We discussed that subject areas contain columns of information extracted from your transactional data sources. The next step would be to discover what columns of information are available in a particular subject area that you might be interested in using for your analytics. To do this let's go to BI and open up a subject area and see what's there.

Explore a Subject Area in BI

Here's how you view a subject area in BI:

  1. From the Home page of your application go to Navigator.

  2. Go to Tools Reports and Analytics. This brings you to the Reports and Analytics page where your sales team see analytic detail specific to their role. Let's go BI where we can access all the details and tools related to subject areas.

  3. Click Browse Catalog. Now you are in BI.

  4. Go to New and then Analysis.

  5. Choose the subject area that you are interested in. Let's pick Sales - CRM Pipeline.

  6. Expanding the dimension folders in the subject area shows you the available columns for your analytics, as well as the facts that you use to measure the values in your analytics.

This shows a blown up view of the Opportunity dimension in the Sales - CRM Pipeline subject area, as well as a view of some of the available facts in a Facts folder.

Subject area close up of dimensions.

Subject Area Context And Analytic Results

If your analytic doesn't look right, or columns of information aren't showing up, it could be an issue with context. The context defines what column details the analysis displays. Adding an employee column doesn't mean all employees show up in the analysis. It depends on the context you're using to create the analysis. If you build an analysis, and it doesn't show what you're expecting, be sure that you're adding your columns and facts in context.

The subject area dimension folders contain the columns and the facts folders define the relationship of the columns. If you add the Employee column to your analysis, and then add the Fact, Number of Activities to the same analysis, then only the employees that have one or more activities show on this analysis in this context. There might be hundreds of employees that have some sort of relationship with A.C. Networks, but no associated activities, so they don't show up on your activity analysis.

The following is an example that might help explain this further.

In this exercise you will build an activity analysis, and add an additional subject area, then explore some different scenarios.

  1. Build an activity analysis as directed in "Create an Activity Analysis".

  2. With your activity analysis in edit mode, add the standard subject area Sales - CRM Quota Management.

  3. Both subject areas appear under Subject Areas. Expand Sales - CRM Sales Activity. Expand Customer. Expand Sales Account Extension. Drag Level 1 Account Name onto the palette.

  4. Still in Sales Activity, expand Employee. Drag First Name and Last Name onto the palette.

  5. Expand Facts, then Activity Facts. Drag # of Activities onto the palette. This fact is key to this analysis because the relationship of Employee to this subject area is dependent on the employee having one or more activities for one or more accounts. If your employee has never entered activities for any given account, they don't t show up on this report, even if they have another type of relationship with an account. Since the context of this subject area has to do with sales activities, only employees with activities are included.

  6. Now select the "Results" tab. You see four employees in the resulting analysis. Each of these employees has one or more activities.

  7. Now select the "Criteria" tab. Under Subject Areas expand Sales - CRM Quota Management. Expand Facts. Expand Pipeline Facts and drag Opportunity Revenue to the palette.

  8. Go to the "Results" tab. Notice that now there are more employees. This result is because you have added employees that also have relationships to Quota Management. In this case, employees are added that have generated revenue.

  9. Go back to "Criteria". Remove # of Activities. The results show only the three employees that have revenue. Helena has both revenue and activities so she shows up in both scenarios.

Finally, note that if you remove both # of Activities and Opportunity Revenue and look at the results, you again have only the four employees that have a relationship with only the Sales Activity dimension.

Subject Area Employee graphic

Multiple Subject Areas in a Single Analytic

Subject areas can be tricky to master, but once you get familiar with them you can easily mix and match subject area components for your analytics. The reason you would want to do this is just for flexibility. You might want to look at your business from different perspectives. Maybe you want to look at leads against pipeline against activity. Are your sales people doing a good job following up on leads?

You can create analytics that combine attributes and metrics from custom and standard subject areas that share a common dimension. Or, if that isn't enough, you can create analytics that combine data from a custom subject area like opportunity, with any related subject area such as Sales CRM Pipeline. You can create combined analytics with an array of standard objects, including accounts, contacts, households, opportunities, partners, sales accounts, territories, and resources.

To start with, let's learn how to add a subject area to your analytic editing palette. Whether you're creating a new analysis, or using an existing analysis to add objects from your custom subject area, the steps for adding multiple custom or standard subject areas to your palette are the same.

To add subject areas to editing palettes:

  1. Create your analysis using a single subject area.

  2. In the Subject Area section, click the Add/Remove icon.

    This shows the Add or Remove subject areas icon.

    Add or remove subject areas icon
  3. Select or remove one or more standard or custom subject areas from this analysis by selecting or deselecting subject area. If you have created custom subject areas, they also appear in this list under the name you assigned to them.

Cross Subject Area Queries

Each subject area contains a collection of 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 create an analysis that combines data from more than one subject area. This type of analysis is referred to as a cross-subject area query. Cross-subject area queries are classified into three broad categories:

  • Combining queries from multiple subject areas.

    • Using common (conformed) dimensions.

    • Using local and common (confirmed) dimensions.

  • Using a "set" operation (Union or Union All for example) to combine more than one result set from different subject areas.

  • Combining Logical SQL using the Advanced tab.

A Common dimension is a dimension that exists in all subject areas that are being joined in an analysis. 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 exists only in one subject area. For example, Opportunity and Revenue are local dimensions for the "Sales - CRM Pipeline" subject area.

The following are some general guidelines to follow when working with multiple subject areas:

  • If all the metrics and attributes needed for the analysis are available in a single subject area and fact metrics, use that subject area only and don't create a cross subject area query. Such an analysis performs better and is easier to maintain.

  • When joining two subject areas in an analysis, make sure at least one attribute from a common dimension is used in the analysis.

  • 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 area that's being used in your analysis. You don't have to display measures or use them, but you should include them. You can hide a measure if not needed in the analysis.

  • When using common and local dimensions use SET VARIABLE ENABLE_DIMENSIONALITY=1; in the Advanced SQL tab.

Queries from Multiple Subject Areas

The simplest and fastest way to generate an analysis 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 to build the analysis. Such an analysis results in better performance and is much easier to maintain.

If your analysis requirements can't be met by any single subject area because you need metrics from more than one subject area, you can build a cross-subject area query using common dimensions. There's a clear advantage to building a cross-subject area query using only common dimensions, which is recommended.

Keep in mind that if you use three subject areas for an analysis, 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 result is an analysis that returns exactly the data you want to see.

Knowing how cross-subject area queries are executed in BI helps you understand the importance of using common dimensions when building such an analysis. 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.

Common Dimensions

Here are some examples of combining common dimensions. In this case, we combine the number of Opportunities, number of Opportunity Revenue Lines, number of Leads, and number of Interactions 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.

The following subject areas are used for this example analysis:

  • Subject area 1: "Marketing - CRM Leads"

  • Subject area 2: "Sales - CRM Pipeline"

  • Subject area 3: "Sales - CRM Sales Activity"

Customer is the common dimension used for this example analysis:

  • "Marketing - CRM Leads"."Customer" -- Customer

  • "Sales - CRM Pipeline"."Customer" -- Customer

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

The following are the metric measures for this example analysis:

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

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

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

  • "Sales - CRM Sales Activity"."Interaction Facts" -- "# of Interactions"

Local and Common Dimensions

This example pulls Opportunity Line Revenue by Product and number of Interactions by Customer. Customer is a common dimension in both subject areas used for this query. Product is a local dimension to the Sales - CRM Pipeline subject area and Interaction is a local dimension to Sales - CRM Sales Activity. Different fact metrics are pulled from each subject area. Note that use of local dimension may impact the grain of the analysis. In such cases the metrics may get repeated for each of these rows.

The following are the subject areas used for this example analysis:

  • Subject area 1: "Sales - CRM Pipeline"

  • Subject area 2: "Sales - CRM Sales Activity"

Sales is the common dimension for this example analysis:

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

The following are the local dimensions used for this example analysis:

  • "Sales - CRM Pipeline"."Product" - Product

  • "Sales - CRM Sales Activity"."Interaction" -- Interaction

The following are the metrics (measures) used for this analysis:

  • "Sales - CRM Sales Activity"."Interaction Facts" -- # of Interactions

  • "Sales - CRM Pipeline"."Pipeline Detail Facts" -- Opportunity Line Revenue

The following is the logical SQL used for this analysis;

SET VARIABLE ENABLE_DIMENSIONALITY=1; SELECT 
0 s_0, "Sales - CRM Pipeline"."Customer"."Customer Name" s_1, 
0 s_0, "Sales - CRM Pipeline"."Customer"."Customer Name" s_1, 
"Sales - CRM Sales Activity"."Interaction"."Interaction Type Name" s_3, 
"Sales - CRM Pipeline"."Pipeline Detail Facts"."Opportunity Line Revenue" s_4, 
"Sales - CRM Sales Activity"."Interaction Facts"."# of Interactions" s_5 
FROM "Sales - RM Pipeline" ORDER BY 1, 6 DESC NULLS FIRST, 2 ASC NULLS LAST, 3 ASC NULLS 
LAST, 4 ASC NULLS LAST FETCH FIRST 65001 ROWS ONLY 

Set Operations to Combine Result Sets from a Subject Area

This example creates a compound analysis query that's a union of two result subsets from same subject area, combining results from:

  • #Leads by Campaign from the Marketing - CRM Campaign Performance subject area (result 1)

  • #Interactions by Interaction Type from the Marketing - CRM Interactions Real Time subject area (result 2)

SELECT saw_0, saw_1 FROM ((SELECT 'Campaign ~ ' || "Marketing Source"."Campaign Name" saw_0, "Response Facts"."# Responses" saw_1 FROM "Marketing - CRM Campaign Performance")
UNION
(SELECT 'Interaction ~ ' || "Interaction"."Interaction Type Code" saw_0, "Interactions Facts"."# of Interactions" saw_1 FROM "Marketing - CRM Interactions Real Time")) t1 ORDER BY saw

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, just as used in normal SQL. Both Outer and Equijoin are supported. The following example demonstrates this technique.

Currently Oracle Transactional BI doesn't support Lead as a dimension for Revenue. However, this example illustrates how you can combine pipeline and lead data in an analysis by combining the logical SQL found on the Advanced tab.

Step 1: Write a BI Answers query using the "Sales - CRM Opportunities and Products Real Time" subject area to show the Revenue line amount and Opportunity. Once the correct results are achieved, go to the Advanced tab and grab the logical SQL associated with this query.

Logical SQL for Query 1:

SELECT
"Sales - CRM Opportunities and Products Real Time"."Contact"."Full Name" s_1, 
"Sales - CRM Opportunities and Products Real Time"."Opportunity"."Opportunity ID" OPTY_ID, 
"Sales - CRM Opportunities and Products Real Time"."Pipeline Detail Facts"."Opportunity Line
Revenue" s_12 
FROM "Sales - CRM Opportunities and Products Real Time"  

Step 2: Write a second BI Answers query using the "Marketing - CRM Leads and Opportunities Real Time" subject area to show Leads associated with the Opportunity. Once the correct results are achieved, go to the Advanced tab and grab the logical SQL associated with this query.

Logical SQL for Query 2:

SELECT
"Marketing - CRM Leads and Opportunities Real Time"."Lead"."Lead Id" s_1,
"Marketing - CRM Leads and Opportunities Real Time"."Lead"."Lead Name" s_2,
"Marketing - CRM Leads and Opportunities Real Time"."Opportunity"."Opportunity ID" s_3,
"Marketing - CRM Leads and Opportunities Real Time"."Opportunity"."Opportunity Name" s_4 
FROM "Marketing - CRM Leads and Opportunities Real Time"    

Step 3: Go to the Advanced tab in BI Answers and copy/paste the following logical SQL which is an OBIEE - Equijoin of the two previous SQL statements based on Opportunity ID. Use any text editor to combine the logical SQL statements copied from Steps 1 and 2.

SELECT
Lead_Opty.s_1, Lead_Opty.s_2, Lead_Opty.s_3, Lead_Opty.s_4, 
Opty_Prod.s_1, Opty_Prod.s_2 
FROM
(
SELECT
"Marketing - CRM Leads and Opportunities Real Time"."Lead"."Lead Id" s_1, 
"Marketing - CRM Leads and Opportunities Real Time"."Lead"."Lead Name" s_2, 
"Marketing - CRM Leads and Opportunities Real Time"."Opportunity"."Opportunity ID" s_3, 
"Marketing - CRM Leads and Opportunities Real Time"."Opportunity"."Opportunity Name" s_4
FROM "Marketing - CRM Leads and Opportunities Real Time"
) Lead_Opty, 
(
SELECT
"Sales - CRM Opportunities and Products Real Time"."Contact"."Full Name" s_1,
"Sales - CRM Opportunities and Products Real Time"."Opportunity"."Opportunity ID" OPTY_ID,
"Sales - CRM Opportunities and Products Real Time"."Pipeline Detail Facts"."Opportunity Line
Revenue" s_2 
FROM "Sales - CRM Opportunities and Products Real Time" 
) Opty_Prod 
 WHERE Lead_Opty.s_3 = Opty_Prod.OPTY_ID  
Note: If you create a new analysis using this SQL, any hierarchical columns, member selection, groups or formatting is stripped out.

Custom and Standard Subject Areas Joins

Analyses can be build using combinations of standard, as well as both custom and standard subject areas. The add subject area option appears once you have created an analysis from a single subject area. You can delete subject areas using these same steps. When you create your analysis, you select a single subject area during the creation steps. Once the analysis is created, you add additional standard or custom subject areas.

For example, if a custom object is used to record past sales value, a join analysis can be created to show the pipeline together with the sales history using a join between the standard subject area CRM Customer Overview, and a custom subject area that includes past sales from the custom object. Only certain objects can be used when you're combining your own custom subject area with a standard subject area.

The following objects support joining custom with standard subject areas:

  • Customer

    • Organization (Account)

    • Sales Account

    • Household (Group)

  • Resource

    • Resource

    • Partner Resource

    • Employee

    • Sales Resource

  • Contact (Person)

  • Opportunity

  • Partner

  • Territory

Listing of Prebuilt Subject Areas

There are a variety of standard subject areas that you can use right out of the box to build your analytics. This list includes subject areas for other applications as well. See the Related Topics for a link to the subject area listing.

Subject Area Updates

We add and update subject areas as we build new features for you. Here are the new CX Sales and B2B Service subject areas for 20B

New Subject Area Description

Sales - CRM Resource System Usage

This subject area provides data for reporting on CRM resource activity (a resource is a person or user).

Use this subject area to report on resource activity by channel, resource activity in time periods, and time periods with no resource activity.

Sales - CRM Object Activity

This subject area provides data for reporting on object activity, specifically object creation and updates.

Use this subject area to report on object activity by channel, object activity performed by users, object activity by channel, and time periods with no object activity.

Service - CRM Service Request Lifecycle

SRs go through a life cycle, from the point they are created up until they are resolved, and finally closed. Service personnel are interested in keeping this life cycle short while attempting a timely, quality fix to issues. This subject area helps building analyses that helps keep a close watch on the SRs, finding outliers and taking corrective steps pro-actively to avoid customer escalations and potential SLA violations. Useful measures such as actual time spent by an agent on an SR, customer wait times, SR duration with specific queues and assignees and number of queue transfers provide the much needed insight into potential issues before they reach crisis proportions. The ability to analyze these measures in relation to key information of an SR together with surrounding entities such as Agent, Account, Channel and Product make the analyses even more powerful.

Service - CRM Survey Requests Real Time

Use this subject area to obtain insight into survey request activity in the context of a Service Request. Build summary reports for a comparison of survey requests sent and SRs resolved over a chosen periodicity such as monthly or quarterly. Analyze survey response percentages against requests sent in relation to service categories, accounts, account regions, products, teams and other key business contexts. Examine request/response performance for survey templates used, to determine if any template changes are necessary.

About Creating Your Own Subject Areas

With CX Sales applications, you get prebuilt analytics that answer typical business questions you might have. But if your questions aren't answered, then you can create your own analytics. Create your own analytics using either prebuilt subject areas or custom subject areas. Custom subject areas are helpful if you find that the prebuilt subject areas don't cover what you need. Custom subject areas are especially useful as you work through the process of configuring your applications. For example, if you create custom objects and want to report on them, then you will need to create custom subject areas.

You create custom subject areas using a step-by-step train process in Application Composer. For more information, see the Oracle Applications Cloud Configuring Applications Using Application Composer guide.