Introduction

This 35-minute hands-on tutorial shows you how to add measures to Quota Planning in Oracle Sales Planning. The sections build on each other and should be completed sequentially.

Background

Measures enable you to collect objective, quantifiable data or data that indicates progress toward a performance or strategy target. While high-level measures may focus on overall performance of the organization, the low-level measures may focus on performance within teams or individuals.

Quota Planning builds best practices into its content, including its forms, calculations, dashboards, drivers, and measures. Enhance the planning process in your organization by adding custom measures, task lists, or approvals.

This tutorial shows you how to add custom measures needed by your organization.

Prerequisites

Cloud EPM Hands-on Tutorials may require you to import a snapshot into your Cloud EPM Enterprise Service instance. Before you can import a tutorial snapshot, you must request another Cloud EPM Enterprise Service instance or remove your current application and business process. The tutorial snapshot will not import over your existing application or business process, nor will it automatically replace or restore the application or business process you are currently working with.

Before starting this tutorial, you must:

  • Have Service Administrator access to a Cloud EPM Enterprise Service instance. The instance should not have a business process created.
  • If you haven't already, register for a free Oracle Cloud Customer Connect account so you can access the required files.
    • Download the latest Sales Planning Sample Application snapshot from this idea page.
    • Upload and import the latest Sales Planning Sample Application snapshot into your Planning instance.

    Note:

    If you run into migration errors importing the snapshot, re-run the migration excluding the HSS-Shared Services component, as well as the Security and User Preferences artifacts in the Core component. For more information on uploading and importing snapshots, refer to the Administering Migration for Oracle Enterprise Performance Management Cloud documentation.

Setting Values for User Variables

In this section, you review the substitution variables that are set up for the application.

  1. From the home page, click Tools, then User Preferences.
    User Preferences
  2. On the left, under Preferences, click User Variables.
  3. For each variable, click its icon member selector (Select Member) to select a member as the variable's value:
    Dimension User Variable Member
    Accounts Accounts Segments All Accounts
    Currency Currency USD
    Period Period Granularity YearTotal
    Product Product Family All Product
    Currency Reporting Currency USD
    Scenario Scenario Plan
    Territory Territory NA Key Accounts - Anthony Furini
    Years Years FY24
    Accounts Account Segments All Accounts
    Scenario Forecasting Scenario Forecast
    Version Forecasting Version Working Bottom Up
    Measures Drivers Product Measures
    Product Product Family All Product
    Territory Territory NA Products - West - Mateo Lopez
    Currency Currency USD
    BaselinePromotions Current Promotion Base
    Scenario Key Account Scenario Plan
    Version Key Account Version Working Bottom Up
    Territory Overlay Territory All Overlay Territory
    Territory Sales Rep Territory Central - Products - Div I -Peter Branch
    Territory Territory NA Products - Central - Alex Smith
    Accounts Accounts All Accounts
    Product Product All Product

    Tip:

    If you selected to show aliases in Member Selector, the value listed in the Member column may vary. For example, for some dimensions, the prefix (OEP_ and OGS_) are not shown. For some territory members, instead of the numeric member name, the full alias name is displayed.
  4. Verify your selections, and click Save.
    Save
  5. In the Information message, click OK.
    Preferences Saved

Reviewing Measures in Configure: Quota Planning

The features you enabled in Quota Planning determine which out-of-the-box measures are included in your application. You can review measures for each subcomponent in Configure: Quota Planning.

  1. From the home page, click Application, then Configure.
    Click Configure

    In Configure: Quota Planning, you can manage out-of-the-box measures for Quota Planning. The subcomponents listed depend on which features were enabled in the application.

    Navigating to the Configure page
  2. Click Top Down Quota Measures.
  3. In Top Down Quota Measures, review the measures added to the component.
    Bottom Up Quota Measures
  4. For Average Quota Per Rep, click Edit Formula (Edit Formula).
    Display member formula
  5. Review the member formula, then click Cancel.
    Member formula

    The member formula calculates the Average Quota Per Rep. If Territory is at the lowest level (Level 0), the Average Quota takes the value of the Adjusted Target Quota at that level. If Territory is at a higher level, the measure value is calculated by dividing the Adjusted Target Quota by the Number of Reps.

  6. Click Actions to display and review options.
    Actions
    • To add a measure, select Add from the Actions menu, then select New Member and enter details in the new row.
    • To modify a group of measures, or to add many new ones, export the set of predefined measures. Select Export from the Actions menu, and then modify the export file in Excel. You can edit predefined measures, make a copy of a measure and modify it to create a new one, or add new measures.
    • To import the modified file, select Import from the Actions menu.
    • To export or import all measures, use Batch Export or Batch Import.
  7. Click Close (Close).
  8. Following the steps in this section, review measures for the other sub-components.
    Measures on the Configure page

Adding Custom Measures in the Simplified Dimension Editor

You can add custom measures directly within the hierarchy by using the Simplified Dimension Editor.

Reviewing Measures in the Simplified Dimension Editor

  1. From the cards on the top of the page, click Overview.
    Navigating to Overview
  2. In Application, click Dimensions.
    Dimensions
  3. In Dimensions, for Cube, select OEP_QTP.
    Selecting the Quota cube
  4. Click Measures.
    Click Measures
  5. In Edit Member Properties, click Zoom in all levels (Zoom in All Levels).
    Zoom in all levels
  6. Right-click on the table header and select the following columns.
    Select columns
  7. In the grid, scroll down and locate OQP_Quota KPIs and OQP_Average Quota Per Rep.
  8. Select the OQP_Average Quota Per Rep row.
    Select Average Quota
  9. Select the Default Formula cell and clickFx(Member Formula) to view the formula.
    Default Formula

    10. The member formula for the selected measure is displayed in a dialog box.

    member Formula
  10. Review the member formula, then click Close (Close).

Adding Measures to Top Down Quota

  1. In the grid, scroll down, locate, and select OQP_Top Down KPIs.
    Top Down KPIs
  2. ClickAdd Child (Add Child).
    Add Child
  3. In Add Child, for Number of members to add, enter 2 , and click Apply.
    Add Child

    4. New rows are added under OQP_Top Down KPIs.

    Add Child

    You will be adding two measures that are dynamically calculated based on the following formulas:

    • Average LY Bookings Per Rep = LY Bookings / No. of Reps
    • Rep to Accounts Ratio = No. of Reps / Number of Accounts
  4. For each row, enter or select the following member properties:
    Member Name Default Formula Data Type Default Data Storage
    Average LY Bookings Per Rep IF(@ISLEV("Territory",0))
    "OQP_Last Year Revenue";
    ELSE
    "OQP_Last Year Revenue"/"OQP_Number of Reps"->"BegBalance";
    ENDIF;         		Dynamic Calc Currency
    Reps to Account Ratio "OQP_Number of Reps"->"BegBalance"/"OQP_Number of Accounts"->"BegBalance"; Dynamic Calc Non-Currency

    Tip:

    To add default formula, select the Default Formula cell and click Fx (Member Formula).

  5. Make sure that the Parent Member for both measures is set to OQP_Top Down KPIs.
  6. Make sure that SALESREP Consol op. for both measures is set to Not used for Cube.
    7. Measure member properties
  7. Verify your entries and click Save (Save).

    The measures (members) are added to the outline. To see your changes, you must refresh the database.

    Measure member properties

Refreshing the Database

  1. In Edit Member Properties, clickRefresh Database (Refresh Database)
  2. In Refresh Database, click Refresh Database.
    Refresh database
  3. In the warning message, click Refresh.
    Refresh database

    Cube Refresh takes a moment to complete.

  4. When the Status displays as Succeeded, click Finish.
    Refresh database
  5. Click Close (Close).
  6. In Edit Member Properties, click Cancel (Cancel).

    7. Tip:

    Alternatively, you can refresh the database from the Actions menu in the Application cluster.
  7. Return to the home page. Click Home icon (Home).

Viewing Custom Measures in Dashboards

Review the Top Down Planning dashboards for an overview of revenue and quota along with out-of-the-box measures and the custom measures you added to the hierarchy.

  1. From the home page, click Quota Planning, and then Top Down Planning.
    Navigating to Top Down Quota Planning
  2. In Top down and Adjust, for the top (My Target) and bottom (My Team Target) forms, you can review the measures you added.
    Measures on dashboard
  3. Maximize the bottom form (My Team Target). Move your mouse to the upper-right side of the form to view icons, click More icon (More) and select Maximize.
    Maximize
  4. In My Team Target, review the calculated measures for the selected point-of-view.
    My Team Target
  5. When finished, move your mouse to the upper-right side of the form to view icons, click More icon (More) and select Restore.
    Restore