5ESS Scheduled Processes for Analytics

This chapter contains the following:

Scheduled Processes for Analytics

Oracle Enterprise Scheduler Service (ESS) comes built into your application. ESS manages scheduled processes for the imports and updates for information used in your analytics. Some tasks are too complicated or would take way too long if you had to do them manually, especially one record at a time. There are scheduled processes already added for subject area dimensions that do the tasks for you. You can run scheduled processes as well as change when processes are run for your analytics. You can also create your own scheduled processes. If your analytic isn't showing the correct data, the issue could be that you need to run an ESS job for a refresh. The table for Scheduled ESS Processes details all the subject area and related dimensions that have existing scheduled processes for the data.

Scheduled ESS Processes for Analytic Data

Subject Area / Dimension Process Name Details

Common dimension

Time / Calendar dimension

Refresh Denormalized Time Dimension Table for BI

Run the job when the CRM Calendar (profile ZCA_COMMON_CALENDAR) is changed or updated.

Common dimension

Product dimension

Refresh Denormalized Product Dimension Table for BI

It is normally not necessary to run this job as the denormalized product dimension table is updated as the Sales Catalog changes are published. Run this job If you don't see the sales catalog changes reflected in BI.

Common dimension

Classification Hierarchy

Classification Hierarchy Generation

Classification Hierarchy ESS Jobs are not submitted automatically but most of the times, users are not required submit these ESS jobs manually. Classification hierarchies will be flattened automatically when they are created or modified and the ESS job is available for cases when there are issues with the flattened data.

FSM Task Name: Run Classification Hierarchy Generation ESS Job Name: Classification Hierarchy Generation ESS Job Description: Flatten Classification Hierarchy Rows and Columns.

Common dimension

Customer Hierarchy

Party Hierarchy Generation

Customer Hierarchy ESS Jobs are not submitted automatically but most of the time user are not required submit these ESS jobs manually. Customer Hierarchy is flattened automatically when they are created or modified and the ESS job is available for cases when there are issues with the flattened data.

FSM Task Name: Run Trading Community Party Hierarchy Generation ESS Job Name: Party Hierarchy Generation ESS Job Description: Flatten Party Hierarchy Rows and Columns.

Common dimension (Sales Geography Zone)

Sales Zone Hierarchy

Flatten HZ_GEO_HIERARCHY_CF Columns

Resource and geography jobs are submitted automatically, are not BI specific, and users are not required to submit any jobs manually.

Common dimension

Resource Hierarchy

Reporting Hierarchy Generation

Generates the resource reporting hierarchy for a given sales or marketing organization hierarchy. Updates the reporting hierarchy when changes occur in the organization hierarchy. Resource jobs are submitted automatically, are not BI specific, and users are not required to submit any jobs manually.

Sales -Forecasting

Forecast Header

Refresh Forecast

Prepares Forecast Headers with the latest Territory, Product hierarchies, and also makes a copy of Revenues for the Forecast Header.

Sales - Forecasting

Historical Forecast Facts

Generate Forecast Metrics

Generates pre-computed metrics into the Metrics table. This data is used in the Historical Forecast Subject Area.

Sales -Forecasting

Historical Forecast Facts

Compress Forecast Metrics

Updates transactional deltas in the Transactional Metrics table into the Metrics table. The combination of Metrics table and Transactional Metrics table provide real time metrics for Active Forecasts. This data is used in the Historical Forecast Subject Area to display real time metrics for Active Forecasts.

Sales - Historical Pipeline

Historical Revenue Facts

Generate Sales Historical Snapshots

Captures a daily snapshot of opportunity and revenue data.

User System Usage Sales - CRM Resource System Usage

Fact - CRM - Resource Session Activity

Refresh BI Reports Audit Data for User Adoption Reporting

Summarizes and aggregates data used for user adoption reporting. Profile option FND_TRACK_USER_ACTIVITY must be set to "ENABLED".

This process has 2 parameters:

  • Full load (Y/N)

  • Purge after (months)

Sales - CRM Object Activity

Fact - Object Activity

Refresh BI Reports Audit Data for User Adoption Reporting

Summarizes and aggregates data used for user adoption reporting.

CRM - CRM Click History

Fact - # of Clicks

Fact - Average Clicks Render Time

Fact - Average Click Total Time

Process Click History Mapping Data

Click History Data Purge Process

User Analytics Click Mapping Translation

Process Click History Mapping Data adds additional detail on clicks to the data set. Runs automatically and is not configurable.

Click History Data Purge Process aggregates the click data for reporting. This job runs daily automatically. Users can schedule as needed.

User Analytics Click Mapping Translation adds international content to the database. Users should run this ESS job once when first accessing the data and then whenever new languages are enabled.

Although ESS scheduled processes run in the background automatically for all roles, not all roles can manage scheduling details. For Click History scheduling, the Sales Administrator, Sales VP, and Sales Manager roles come enabled to access the Click History Purge Process and User Analytics Click Mapping Translation ESS jobs. Any custom roles need to assign the Populate Click Description Process privilege to their role. Note that for setup or when you enable a new language you need to run the User Analytics Click Mapping Translation job once.

After you submit a scheduled process, you can track its progress. Processes can finish running in seconds, or a few minutes, or even longer. If something interrupts a process while it's running, for example a server restarting, then the process automatically picks up where it left off. If you need to see all processes, not just the ones you submitted, ask your security administrator to assign you a custom role that has the ESS Monitor Role (ESSMonitor) or ESS Operator Role (ESSOperator).

Here's where you can find processes that were submitted:

  • Scheduled Processes work area

  • Other work areas with a section for scheduled processes, if available

Search Results table and Details section on the
Scheduled Processes Overview page

Here's an example of the section that you can find in some other work areas.

Section on other work areas where you can monitor
scheduled processes

Check the Status in the Scheduled Processes Work Area

Here's how you find your scheduled process and see how it's doing.

  1. Click Navigator > Tools > Scheduled Processes.

    The Search Results table shows processes that match the default saved search, Last 24 hours.

    • The table might be blank if nothing was submitted.

    • If submission notes were entered when submitting the process, you can use the Submission Notes column to help identify the process.

  2. If you don't see the process, click the Refresh icon or use the Search section.

    • For the search, enter your own criteria or select another saved search, including these:

      • Last hour

      • Last 48 hours

      • Last 72 hours

      • Cancelable Processes

    • Refresh the search results at any time to see the latest status of the process in the Status column.

  3. Do any of these tasks to check on the progress of the process:

    • For more details about the status, click the status link. For example, if a process is in the Ready status, you can click the Ready link in the Status column to see where the process is in the submission queue.

    • Check the Start Time column to see if the process has started running yet.

    • In the Search Results table, find the This Job's Items Processed / Errors / Total column. For some scheduled processes, this column shows what's getting processed and how things are going. For example, if the value is Lines 50/2/?, then this is what that means:

      • The scheduled process is processing lines, for example lines from a file that's getting imported.

      • 50 lines are successfully processed.

      • Two lines ended up in error.

      • The total number of lines to process is unknown right now. If 100 was displayed instead of the question mark, that would mean there are 100 lines in total, 52 of which are already processed, and 48 more to go.

      Even after the scheduled process reaches a final state, the first two numbers might not add up to the total, for many reasons. For example, if you cancel the process before it's done, then not all items got processed. Or, you have set a process parameter to end the process if at least 50% of the items getting processed ends in error. In which case, the scheduled process itself could still successfully end, but not everything was actually processed.

    • Click the View Log button, especially if your process has a blank This Job's Items Processed / Errors / Total column. The type of information in the log varies, depending on the process. You can open the log from here only for if the process is currently running. When the process is done, you might want to also take a look at the final log, if any, from the Details section.

View Other Details in the Scheduled Processes Work Area

To get more information about a scheduled process, select it in the Search Results table and see the Details section that appears after the table. These are some of the details you might find:

  • Completion text, which is automatically generated when the process reaches a final state. What you see depends on the process. For example, it might say that the process finished 1792 records in less than a second.

  • The log attachment, for example for details about why the process ended in error.

  • Report output that the process generated, if any.

  • Parameters for the scheduled process.

    • Open the Parameter Names with Values subsection to see the parameters from the Process Details dialog box and the values that were entered when the process was submitted.

    • Use the All Parameter Values subsection for troubleshooting purposes, if you need to see the parameters and values in their actual, technical format. This subsection might have more parameters. For example, some processes run with additional parameter values that are derived from the parameter values you entered when you submitted the process.

Monitor Scheduled Processes in Other Work Areas

Some work areas have a section where you can monitor the scheduled processes that were already submitted.

  1. Go to the section if it's available, usually on the landing page of the work area.

  2. Click the Refresh icon if you don't see any processes or need to see the latest status.

    • If you still don't see the process you're looking for, try changing the filters. If that doesn't help, use the Scheduled Processes work area instead.

    • If submission notes were entered when submitting the process, you can use the Submission Notes column to help identify the process.

  3. Do either of these things to check on the progress of the process:

    • For more details about the status, click the status link. For example, if a process is in the Ready status, you can click the Ready link in the Status column to see where the process is in the submission queue.

    • Check the Start Time column to see if the process has started running yet. If it has, you can click the link in the View Log column to get information about how the process is running.

Other than processing records, some scheduled processes also give you output. When you submit the process, you can select the output layout and format, and set other output options. In the Scheduled Processes work area, you can view the output and even republish it in a different format without resubmitting the process. You might be able to see the output from other work areas too.

When You're In the Scheduled Processes Work Area

Here's what you do:

  1. Click Navigator > Tools > Scheduled Process.

  2. Find your scheduled process and, if you need to, refresh the search results to see the latest status.

  3. Select the scheduled process.

  4. Go to the Output subsection in the Details section.

  5. Click the link in the Output Name column to view or download the output, which you can then print.

  6. To view or export the output in a different format:

    1. Click the Republish button.

    2. Click the Actions icon.

    3. Select Export, and then select a format.

      Tip: If you want the Data format, you can just click the XML Data icon instead.

When You're In Other Work Areas

Some other work areas have a section, usually on the landing page of the work area, where you can monitor the scheduled processes that were already submitted. In the table there, find your process and click the Output icon in the View Output column.

Life is full of second chances! After a schedule process is submitted, you can still cancel it or make other changes in the Scheduled Processes work area. What you can do to the process depends on its status. Also, depending on what you have access to, you can even work on processes that someone else submitted. If you need to cancel processes that someone else submitted, ask your security administrator to assign you a custom role that has the ESS Administrator Role (ESSAdmin).

Make Changes to Processes

Select the scheduled process in the Search Results table.

  • If the process hasn't started running yet, you can still use the Actions menu to change the schedule or output.

    • Edit Schedule: Change the submission schedule, for example to submit it biweekly instead of weekly.

    • Edit Output: If the process generates output, you can change output options, for example from HTML format to PDF.

  • If the process is running, you can click Put On Hold to pause it, or Release Process so that a process that's on hold continues to run.

Cancel a Process

Select the scheduled process in the Search Results table, and click Cancel Process.

If you submitted a process to run on a schedule, for example once a day, you can cancel the scheduled runs even if some of the runs already happened. Find the original submission, the row with the process ID you got when you submitted the process. The status should be Wait. When you cancel this original submission, you cancel any current and future runs based on the schedule you had set.

Sometimes it takes a while for a process to finish canceling. So, you can use the Actions menu to end it. The option you get depends on the process.

  • Hard Cancel: To end the process shortly after you canceled it, without waiting for the cancellation to finish by itself.

  • Force Cancel: To end a process that has been canceling for over 30 minutes but isn't done yet.

Some processes run on a remote server. When you click Force Cancel, even though the status is changed to Canceled, the process might still be running on the remote server. With the scheduled process still selected in the Search Results table, you check the status of the remote process in the Details section that appears after the table. The Remote Status field might display any of these statuses for the remote process (not for your scheduled process):

  • Completed Successfully: The remote process was successfully canceled within the 30 minute grace period.

  • Running/Unknown: Your scheduled process is in a Canceled state, but the remote process is still running.

  • Terminated: Your scheduled process is in a Canceled state, and the remote process is successfully canceled after the 30 minute grace period.

Cancel Processes in Bulk

You can cancel up to 100 processes at once, as long as the processes haven't reached a final state.

  1. Click Navigator > Tools > Scheduled Processes.

  2. In the Search section, select Cancelable Processes from the Saved Search list.

  3. Make sure that what you get in the Search Results table meets these requirements:

    • No more than 100 processes

    • Only processes with a cancelable status:

      • Wait

      • Ready

      • Running

      • Completed

      • Blocked

      • Hold

      • Paused

      • Pending Validation

      • Schedule Ended

      • Error Auto-Retry

  4. Use the Search section to change your search results, if you need to.

  5. Select Cancel Processes in Bulk from the Actions menu.

Use the Scheduled Processes work area to run all the scheduled processes that you have access to, including process sets. You can also submit many processes from other work areas. Some of the processes that give you output are also reports that you can view from the Reports and Analytics work area or panel tab. You can find that panel tab in some work areas

It's quick to submit a scheduled process with the bare minimum steps. But there are many additional options you can set, for example, if you want to run the process on a schedule.

Select the Scheduled Process and Define Parameters

Here's what you do:

  1. Click Navigator > Tools > Scheduled Processes.

  2. On the Scheduled Processes Overview page, click Schedule New Process.

  3. Leave the type as Job, select the process that you want to submit, and click OK.

  4. In the Process Details dialog box, enter at least the required parameters, if any.

    • Some processes have no parameters at all.

    • Some parameters depend on other parameters. For example, date range parameters might appear only after you select By Date for another parameter.

  5. Click the Advanced button if you want to define the schedule, notifications, or output. Continue to the next steps. Or, just skip to the steps for finishing the submission, to run the process once as soon as possible with the default output.

Define the Schedule

Set up a schedule to run the process on a recurring basis or at a specific time. Use the Process Details dialog box in Advanced mode.

  1. Open the Schedule tab and select Using a schedule for the Run option.

  2. Select a frequency.

    • Select User-Defined if you want to enter the exact dates and times to run the process.

    • You can select Use a Saved Schedule to use an existing schedule, if there are any.

  3. Depending on the frequency, define when the process should run.

Some processes can't be run more often than a certain frequency, for example more than every 10 minutes. But there are some situations where that validation doesn't apply, for example, when different users run that same process less than 10 minutes apart. Or, if you use a saved schedule that has an individual run in addition to the regular frequency, for example a schedule that runs every 10 minutes and once at five minutes after the first run.

Define the Output

You can choose the layout, format, and destination for your output. Use the Process Details dialog box in Advanced mode.

  1. Open the Output tab, if available.

  2. Click Add Output Document.

  3. Change the name if you want. The name identifies this output document when you go to view output later.

  4. Select a layout if you have more than one to choose from.

  5. Select a format, for example one of these:

    • PDF: Is the best option if you want to print the output.

    • Excel: Supports embedded images, such as charts and logos, but can be opened only in Excel 2003 or higher.

    • Data: Gives you report data in an XML file, which is used mainly for editing the report layout.

  6. Click Add Destination to send the output somewhere, for example to a printer or email address. You can add many destinations and send the output to all of them at the same time.

    Tip: You can also print the output as part of the steps for finishing the submission, instead of adding a printer destination here.
  7. To add more output documents, repeat steps 2 to 6.

Set Up Notifications

You can have notifications sent out depending on how the process ends. Use the Process Details dialog box in Advanced mode to set that up.

  1. Open the Notification tab.

  2. Click Create Notification.

  3. In the Recipient field, enter the user ID of the person you want to send the notification to.

  4. In the Condition list, select when to send the notification, for example when the process ends in error.

  5. Click OK.

  6. To send notifications to more people, repeat steps 2 to 5.

Finish the Submission

Follow these steps in the Process Details dialog box:

  1. Click the Process Options button if you want to define settings that affect the data to be processed.

  2. If the process gives you output and you want to print it, select the Print output check box and a printer.

  3. Select the Notify me when this process ends check box if you want to get an email notification.

  4. Enter submission notes to capture any information you want to associate with this submission. You can use your notes to find this submission later.

  5. Click Submit.

  6. Click OK to confirm.

    Tip: Note down the process ID for your submission so you can easily find it later. For example, let's say you set the process to run once a week. If you later want to cancel all runs on this schedule, you need to cancel this original submission with the process ID you see.

Submit Process Sets

To submit a process set from the Scheduled Processes Overview page:

  1. Proceed with the steps that you would follow to submit any scheduled process, but select Job Set for the Type option.

  2. In the Process details dialog box, set parameters for individual processes in the set. A process set itself doesn't have parameters.

    1. Select a process on the Processes tab.

    2. Enter parameters for that process, if any.

    3. Repeat for other processes in the set.

  3. Define the schedule, output, and notifications for the process set, as you would do for any scheduled process.

  4. Set any other options and click Submit.

Every job definition has a job type, such as Java or Oracle Business Intelligence (BI) Publisher reports. You can only create or duplicate job definitions that are based on BI Publisher reports. When there's a job definition for a BI Publisher report, users can run the report as a scheduled process in the Scheduled Processes work area. Otherwise, they can open reports (which are set up to be run online) elsewhere, for example in the Reports and Analytics work area.

Create Job Definitions

Here's how you create a job definition for a BI Publisher report:

  1. In the Setup and Maintenance work area, go to the Manage Enterprise Scheduler Job Definitions and Job Sets task in the Application Extensions functional area. Or, depending on your offering, you might use a different functional area.

    Note: If you don't see the task, make sure that the Enterprise Scheduler Job Definitions and Job Sets feature is enabled at the offering level.
  2. On the Manage Enterprise Scheduler Job Definitions and Job Sets page, click the Manage Job Definitions tab.

  3. Click the Create icon.

  4. Fill in the Job Definition section.

    Field or Check Box What You Do

    Display Name

    Enter the name that users see and select to submit the scheduled process.

    Name

    Enter a name with only alphanumeric characters, for example, AtkEssPrograms1. It can't have spaces or any special characters.

    Job Application Name

    Select the application to associate the job definition with.

    Enable submission from Enterprise Manager

    Make sure you don't select this check box.

    Job Type

    Select BIPJobType.

    Default Output Format

    Select the output format users get by default when they submit the scheduled process.

    Report ID

    Enter the path to the report in the BI catalog, starting with the folder within Shared Folders, for example: Custom/<Family Name>/<Product Name>/<Report File Name>.xdo.

    Make sure to include the .xdo file extension for the report definition.

    Enable submission from Scheduled Processes

    Leave the check box selected.

  5. In the Parameters subtab, you can define parameters that are available to users when they submit the scheduled process based on your job definition.

  6. In the User Properties subtab, don't create or edit a user property unless you have the accurate information that's required to create or edit one. The EXT_PortletContainerWebModule user property is automatically created.

  7. Click Save and Close.

When you create a job definition, a privilege with the same name as the job definition is automatically created. For example, for a job definition named AtkEssPrograms, the privilege is named RUN_ATKESSPROGRAMS. Use the Security Console to assign this privilege to roles, as needed.

Duplicate Job Definitions

You can also create job definitions by duplicating existing job definitions, including parameters. If you're duplicating a predefined job definition, make sure you test it carefully because it might not work exactly like the original.

  1. On the Manage Job Definitions tab, select a job definition with the BIPJobType job type.

  2. Click Duplicate to get another row in the table with the duplicate job definition.

  3. Select the duplicate job definition and click Edit.

  4. Enter the name and path.

  5. Update the parameters if you need to.

  6. Click Save and Close.

When you duplicate a job definition, you automatically create a view object with the same name in the list of values sources. Validations for the attributes in the parameters view object aren't copied over.

Define Parameters for Job Definitions

A parameter controls which records are included or how they are affected when a job runs. Job definitions can have one or more parameters or none at all. You define parameters while creating or editing job definitions using the Manage Enterprise Scheduler Job Definitions and Job Sets page. In the Setup and Maintenance work area, use the following:

  • Functional Area: Application Extensions or a product-specific functional area

  • Task: Manage Enterprise Scheduler Job Definitions and Job Sets

When users run the scheduled process, the values they enter for the parameters determine the data to be included in the report. Also, the values are passed to the data model that the report is using.

The parameters that you define must be in the same order as parameters in the data model. For example, the data model has parameters in this order:

  • P_START_DATE

  • P_END_DATE

  • P_CURRENCY

You create parameters as follows:

  • Start Date

  • End Date

  • Currency

Defining Parameters: Job Definitions

To define parameters while creating or editing job definitions:

  1. On the Manage Job Definitions page, open the Parameters sub tab.

  2. Click Create.

  3. Enter the parameter prompt that users see when they submit the scheduled process.

  4. Select a data type and configure how the parameter and the data entered are displayed, as described in this table.

    Data Type Fields

    Boolean

    Select this if you want the parameter to be a check box.

    Select True or False to determine if the check box is selected or not.

    Date or time

    Select Date and time or Date only option.

    Select a value from the Default Date Format.

    Number

    Select a Number Format.

    Select Left or Right for data alignment.

    String

    Select a Page Element.

    Select Text box if you want the user to provide a text.

    Select Choice list if you want a list with limited options (maximum 10).

    Select List of values if you want a list with unlimited options with a search facility.

  5. Select the Read Only check box if you don't want to enable users to set this parameter. When a parameter is set as read only, the user is required to provide a default value to be passed to the job definition.

  6. If you select list of values or choice list page element, select a List of Values Source and an Attribute. Use the list of values sources from the Manage List of Values Sources tab. Don't define lists of values in the data model that the report is using.

  7. From the list of available attributes, select the attributes you want to appear in the list and move them to the selected attributes section. These attributes determine the values that the user can see.

  8. Define a Default Value for the parameter.

  9. In the Tooltip Text field, provide additional information for the user to follow.

  10. Select the Required check box if users must set this parameter to submit the scheduled process.

  11. Select the Do not Display check box if users should not see this parameter while submitting the process.

  12. Click Save and Create Another or Save and Close.

Dependent Parameters

The attributes of some parameters depend on the attributes or values of certain other parameters. The attributes of a parameter would change if the value of its dependent parameter changes.

For example, you have three parameters, namely Country, State and, City. In this case, the value of the Country parameter would determine the values available in the State parameter. The values in the State parameter would determine the values available in the City parameter.