JavaScript must be enabled to correctly display this content
Introduction
This 60-minute tutorial introduces you to Strategic Modeling concepts and shows you how to integrate data between strategic models and Planning applications using Groovy scripts.
Background
Strategic Modeling, available as a module in Planning, is a feature-rich financial forecasting and modeling solution. With Strategic Modeling, you can model long-range Planning, treasury and funding options, and mergers and acquisitions, among other things.
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.
Upload and import this snapshot into your Planning instance. If you've previously uploaded the snapshot for another Groovy tutorial, you can continue using the same snapshot.
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.
Tip:
The scripts you need for this tutorial are linked as text files within each section.
Understanding Strategic Modeling
The primary artifact in Strategic Modeling is a Model. Models have the following dimensions:
Model (Entity)
Version
Measure
Business Case / Scenario
Currency
Time
Account
Custom Dimensions
Because the dimensions between your Strategic Model and your Planning application are not the same, to map data between the two you must first understand how and where the dimensions on each side of the transaction align.
Model (Entity)
The Model dimension in Strategic Modeling is hierarchical. Each Model represents a single entity— for example, a company, business unit, project, and so on—that is being modeled in the product. A Model stores a full set of data and metadata for each saved version of the model.
Version
The Version dimension is flat. This dimension enables you to specify a particula snapshot of model data that you'd like to work with. The most current version of a model's data is referred to as the Tip.
Measure
The Measure dimension is flat. It contains two members: Output and Input. The Output measure enables you to refer to calculated results in Strategic Modeling, and is what you most commonly map to and from between your Planning application. The Input measure enables you to refer to input values, but is not usually useful for mapping to Planning because the relationships that transform input values to output values (forecast methods) in Strategic Modeling can be changed by the user.
Business Case / Scenario
The Business Case / Scenario dimension is flat. Business Cases and Scenarios are closely related in Strategic Modeling. Any given mapping will work with one or the other, depending on what the user is intending to do with the data. Strategic Modeling data is stored in scenarios.
Currency
The Currency dimension is flat. Each model in Strategic Modeling works with a particular currency.
Time
The Time dimension is hierarchical. Unlike Planning, Strategic Modeling contains a single continuous Time dimension, with years stored immediately beneath the root of the Time dimension. Each year can contain a varying level of detail in Halves, Quarters, Months, and Weeks.
Account
The Account dimension is hierarchical. Each account represents an accounting concept such as Sales or Cost of Goods sold. Usually, these are line items— for example, an Output value exists for Sales in all time periods and all scenarios in the model.
You can extend the Account dimension by creating subaccounts beneath the Account structure, almost always for accounts that accept input.
Custom Dimensions
You can create an arbitrary number of hierarchical custom dimensions. Custom dimensions can represent concepts such as Products or Regions.
Understanding the Strategic Models and Planning application for this tutorial
To integrate data between Strategic Modeling and Planning, you must understand how their respective dimensions map to each other. In this tutorial, you'll be syncing data between the Operations US entities in the OEP_FS Planning cube and the strategic models in the Operations US model hierarchy.
The strategic models Operations US, Operations US East, Operations US North, Operations US South, and Operations US West, map directly to the similarly named entities in the Planning entity structure.
Note:
We'll explain the rest of the specific dimensional mappings for each exercise in this tutorial, as they differ depending on what type of integration you're trying to accomplish.
Setting up for testing
Entering test Planning data
In this section, you use a form to enter test data in the OEP_Working version in Planning. Later in this tutorial, you create Groovy scripts to push this data to Strategic Modeling.
From the home page, click Data, then expand the Strategic Modeling folder to display data entry forms. Click Enter Data Form Working to open the form. Notice that the data will be written to the OEP_Working version in Planning.
Verify that the entity selected in the POV is Operations US West. Enter the following data in the form for Dec, FY17:
Account
Value
OFS_Product Revenue
5000
OFS_Services Revenue
5000
OFS_Support Revenue
5000
OFS_Other Revenue
5000
OFS_Discounts
500
OFS_Salaries
100000
OFS_Selling Expense
3000
Enter the following data in the form for Dec, FY18:
Account
Value
OFS_Product Revenue
4000
OFS_Services Revenue
4000
OFS_Support Revenue
4000
OFS_Other Revenue
4000
OFS_Discounts
400
OFS_Salaries
120000
OFS_Selling Expense
1000
Click Save. Notice that the Year Total column is updated automatically when you save.
In the toolbar, click (Edit Members) and select Operations US East. Click Apply to change the POV.
Repeat steps 2 and 3 to enter test data into Operations US East.
Repeat this process for the entities Operations US North and Operations US South.
Click Close to return to the list of data entry forms.
Clearing Planning data
In this section, you use a form to clear data from the Target version in Planning. Later in this tutorial, you create Groovy scripts to push data from Strategic Modeling to Planning.
Click Enter Data Form Target to open the form.
Verify that the entity selected in the POV is Operations US South.
Select the first cell in the form (OFS_Product Revenue for FY17, Dec, Target) and press [Delete] to clear the cell.
Click and drag the selection handle to cover the entire grid in the form, or repeat the previous step for every cell in both Dec columns to clear all input cells.
Click Save. Notice that the calculated cells update automatically when you save.
In the toolbar, click (Edit Members) and select Operations US West. Click Apply to change the POV.
Repeat steps 3—5 for Operations US West, Operations US East, and Operations US North.
Click Close to return to the list of data entry forms, then return to the Home page.
Selecting a navigation flow in Planning
In this section, you change your navigation flow to work with specific cards in the Strategic Modeling cluster.
From the Home page, click Strategic Modeling to view the cards in the cluster by default. The default navigation flow does not display the custom set of cards you need to view or test your integration scripts. Close the cluster.
From the home page, click Tools, then click Navigation Flows.
In the Active column, click the link for the SM navigation flow to make it the active navigation flow.
Return to the home page.
From the User menu drop down, select Reload Navigation Flow.
The Strategic Modeling cluster now includes the cards you'll use to test your Groovy scripts in this tutorial: Sync Bottom Up Plan, Push Targets, Push Bottom Up Plan Multi, and Push Targets Multi Entity.
Note:
You cannot associate rules with Strategic Models to be executed on check-in. In this tutorial, all data movement from and to Strategic Models will be executed from Planning forms.
Syncing bottom-up Planning data with Strategic Forecast
In this section, you create a Groovy rule to move data from Planning to a Strategic Model. After deploying the rule, you add it to the Strategic Modeling form, then you test it in the form.
Understanding the source and target intersections
The following table shows the source intersections for Planning and the target intersections for the Strategic Model:
Planning (Source)
Strategic Modeling (Target)
Entity: Operations US
Model: Operations US
POV: Scenario: OEP_Strategic Planning Market: US Market Plan Element: OFS_Direct Input Currency: USD Product: Smart Phone 6 in
Get a DataGridDefinitionBuilder for the cube. Use the builder to construct a region from which to load the data by adding the POV members, column members and rows.
Build the DataGridDefinition object.
Define the scaling factor. We need this because in our example we are working with a Strategic Model whose units are in millions.
Push data to Strategic Modeling.
def data = []
grid.dataCellIterator().each{
data << it.data / millionsScalingFactor
println("$it.memberNames, cell data: $it.data")
}
// Create the SM model grid
operation.application.getStrategicModel('Operations US').withCloseable { smModel->
DataGridBuilder gridBuilder = smModel.dataGridBuilder()
gridBuilder.addPov('Forecast')
gridBuilder.addColumn('2017')
println("Pushing " + data[0] + "to Product Sales")
gridBuilder.addRow(['v1000:010'], [data[0]])
println("Pushing " + data[1] + "to Service Revenue")
gridBuilder.addRow(['v1000:020'], [data[1]])
println("Pushing " + data[3] + "to Discounts & Returns")
gridBuilder.addRow(['v1020'], [data[3]])
println("Pushing " + data[4] + "to Cost of Goods Sold")
gridBuilder.addRow(['v1040'], [data[4]])
double supportRevenue = data[2]
double otherRevenue = data[5]
double totalSupportOtherRevenue = supportRevenue + otherRevenue
println("Pushing " + totalSupportOtherRevenue + "to Other Revenues")
gridBuilder.addRow(['v1000:030'], [totalSupportOtherRevenue])
DataGridBuilder.Status status = new DataGridBuilder.Status()
grid = gridBuilder.build(status)
println("Total number of cells accepted: $status.numAcceptedCells")
println("Total number of cells rejected: $status.numRejectedCells")
println("First 100 rejected cells: $status.cellsRejected")
smModel.saveGrid(grid)
}
Iterate over the grid object and retrieve the cell values one cell at a time. Store the values in the data array.
Construct the Strategic Model grid similar to how it was done in Planning. Push the cell values to the Strategic Model grid object.
Save the Strategic Model grid. This checks in the model with the new values.
Adding the rule to the testing form
From the home page, open the Navigator and click Forms (under Create and Manage).
Expand the Library folder, and select Strategic Modeling.
Select the Push Bottom Up Plan One Entity form, and click (Edit).
Select the Business Rules tab. Add the SM - Sync Bottom-Up Plan with Strategic Forecast rule to the Selected Business Rules list.
Click Finish to save the changes to the form.
Testing the Groovy rule
From the home page, click Strategic Modeling, then select the Sync Bottom Up Plan card. Select the Sync Bottom Up Plan horizontal tab.
Click Actions, then select Business Rules. Click the SM - Sync Bottom-Up Plan with Strategic Forecast rule to launch it. Click OK at the confirmation message.
Select the Strategic Plan horizontal tab. In the 2017 column, verify that the data was pushed from the expected accounts (for example, OFS_Product Revenue was pushed to v1000:010 Product Sales).
Adjusting targets in a bottom-up plan
In this section, you create a Groovy rule to move data from the Model Operations US East to the Planning entity Operations US East. After deploying the rule, you add it to the Planning form, then you test it in the form.
Understanding the source and target intersections
The following table shows the source intersections for the Strategic Model and the target intersections for Planning:
Strategic Modeling (Source)
Planning (Target)
Entity: Operations US East
Model: Operations US East
POV: Scenario: Forecast
POV: Scenario: OEP_Strategic Planning Period: Dec
Market: US Market Plan Element: OFS_Direct Input Currency: USD Version: Target Product: Smart Phone 6 in
Column: Period: 2017, 2018
Column: Years: FY17, FY18
Rows: Accounts
Rows: Accounts
v1000:010 (Product Sales)
OFS_Product Revenue
v1000:020 (Service Revenue)
OFS_Services Revenue
v1000:030 (Other Revenues)
OFS_Support Revenue
v1020 (Discounts & Returns)
OFS_Adjustments to Revenue
v1040 (Cost of Goods Sold)
OFS_Other Cost of Sales
v1080:010 (Salary Expense)
OFS_Salaries
v1080:020 (Selling Expense)
OFS_Selling Expense
v1110 (Depreciation Expenses)
OFS_Depreciation
Creating the Groovy rule
Open Calculation Manager and create a rule named SM - Adjust Targets in Bottom-Up Plan in the OEP_FS cube.
In the Rule Editor, change the Designer option to Edit Script and set the Script Type to Groovy Script.
Define the year dimension mappings between Strategic Modeling and Planning.
Define the Planning account names where you're pushing the data.
Get the list of Strategic Modeling year names from the year map defined in
Get the list of Planning years from the year map.
Construct the Strategic Model object and use the withCloseable construct. This will close the model when that block of code completes execution. This ensures that the rule does not leave the model open in memory after operations on the model are completed.
Get a DataGridDefinitionBuilder for the Strategic Model. Use the builder to construct a region from which to load the data by adding the POV members, column members, and rows. Construct the grid.
Create the Planning grid and close the Strategic Model.
Cube cube = operation.application.getCube("OEP_FS")
DataGridBuilder gridBuilder = cube.dataGridBuilder("MM/DD/YYYY")
gridBuilder.addPov('Dec','OEP_Strategic Planning', 'USD', 'Target', 'Operations US East', 'OFS_Direct Input', 'Smart Phone 6 in', 'US Market')
gridBuilder.addColumn(pbcsYears as String[])
int k = 0;
int numColumns = 2;
def rowdata = []
smGrid.dataCellIterator().each {
rowdata << it.data * 0.000001
println("$it.memberNames, cell data:" + it.data * 0.000001)
println("Period Name = " + it.periodName + "Account ," + pbcsAccountNames[k] + " cell data:" + it.data * 0.000001)
if (rowdata.size() == numColumns) {
println("Row Data = " + rowdata)
gridBuilder.addRow([pbcsAccountNames[k]], rowdata)
k++
rowdata = []
}
}
DataGridBuilder.Status status = new DataGridBuilder.Status()
DataGrid planningGrid = gridBuilder.build(status)
println("Size of the grid: " + planningGrid.size())
println("Total number of cells accepted: $status.numAcceptedCells")
println("Total number of cells rejected: $status.numRejectedCells")
println("First $status.numRejectedCells rejected cells: $status.cellsRejected")
cube.saveGrid(planningGrid)
} //close SM model.
Get the OEP_FS Planning cube.
Construct an iterator that iterates over the grid one row at a time and saves the value in a data array.
Close the Strategic Modeling grid iterator.
Close the Strategic Model opened in .
Adding the rule to the testing form
From the home page, open the Navigator and click Forms (under Create and Manage).
Expand the Library folder, and select Strategic Modeling.
Select the Push Targets One Entity form, and click (Edit).
Select the Business Rules tab. Add the SM - Adjust Targets in Bottom-Up Plan rule to the Selected Business Rules list.
Click Finish to save the changes to the form.
Testing the Groovy rule
From the home page, click Strategic Modeling, then select the Push Targets card. Select the Push Targets horizontal tab.
Click Actions, then select Business Rules. Click the SM - Adjust Targets in Bottom-Up Plan rule to launch it. Click OK at the confirmation message.
Compare the resulting data to the Strategic Model horizontal tab to verify that the data was pushed from the expected accounts (for example, v1000:010 Product Sales was pushed to OFS_Product Revenue).
Pushing bottom-up Planning data to Strategic Forecast for multiple entities
In this section, you create a Groovy rule to move data from multiple Planning entities to multiple Strategic Models with the same name. After deploying the rule, you add it to the Strategic Modeling form, then you test it in the form.
Understanding the source and target intersections
The following table shows the source intersections for Planning and the target intersections for the Strategic Model:
Planning (Source)
Strategic Modeling (Target)
Entity: Operations US East
Operations US West
Operations US North
Operations US South
Model: Operations US East
Operations US West
Operations US North
Operations US South
POV: Scenario: OEP_Strategic Planning Market: US Market Plan Element: OFS_Direct Input Currency: USD Product: Smart Phone 6 in
Iterate over multiple models using the withCloseable construct when getting the model name. The block of code within braces will be executed for each model in the array.
Construct the Planning grid to retrieve values which will be pushed to the Model.
For each Strategic Model, create the DataGridBuilder which will be used to construct the Strategic Modeling grid.
Construct an iterator that iterates over all data cells containing the “OFS_Product Revenue” member in the current input grid.
Cross dim into each of the Accounts in the grid and retrieve the value. Call the addRow method on the Strategic Modeling DataGridBuilder for each Account to add a row to the Strategic Modeling grid.
Create the Strategic Modeling grid and save the grid to check in the Model with the new data values pushed from Planning.
Adding the rule to the testing form
From the home page, open the Navigator and click Forms (under Create and Manage).
Expand the Library folder, and select Strategic Modeling.
Select the Push Bottom Up Plan Multiple Entities form, and click (Edit).
Select the Business Rules tab. Add the SM - Push Bottom-Up Plans for Multiple Entities rule to the Selected Business Rules list.
Click Finish to save the changes to the form.
Testing the Groovy rule
From the home page, click Strategic Modeling, then select the Push Bottom Up Plan Multi card. Select the Push Bottom Up Plan Multi Entity horizontal tab.
Click Actions, then select Business Rules. Click the SM - Push Bottom-Up Plans for Multiple Entities rule to launch it. Click OK at the confirmation message.
Select the US Operations East horizontal tab. In the 2017 and 2018 columns, verify that the data was pushed from the expected accounts (for example, OFS_Product Revenue was pushed to v1000:010 Product Sales).
Repeat the verification for each of the remaining Strategic Model tabs: US Operations West, US Operations North, and US Operations South.
Pushing multiple Strategic Models to multiple Planning entities
In this section, you create a Groovy rule to move data from multiple Strategic Models to Planning entities with the same name. After deploying the rule, you add it to the Planning form, then you test it in the form.
Understanding the source and target intersections
The following table shows the source intersections for the Strategic Model and the target intersections for Planning:
Strategic Modeling (Source)
Planning (Target)
Model: Operations US East Operations US West Operations US North Operations US South
Entity: Operations US East Operations US West Operations US North Operations US South
POV: Scenario: Forecast
POV: Scenario: OEP_Strategic Planning Market: US Market Plan Element: OFS_Direct Input Currency: USD Version: Target Product: Smart Phone 6 in
Column: Period: 2017, 2018
Column: Years: FY17, FY18 Period: Dec
Rows: Accounts
Rows: Accounts
v1000:010 (Product Sales)
OFS_Product Revenue
v1000:020 (Service Revenue)
OFS_Services Revenue
v1000:030 (Other Revenues)
OFS_Support Revenue
v1020 (Discounts & Returns)
OFS_Adjustments to Revenue
v1040 (Cost of Goods Sold)
OFS_Other Cost of Sales
v1080:010 (Salary Expense)
OFS_Salaries
v1080:020 (Selling Expense)
OFS_Selling Expense
v1110 (Depreciation Expenses)
OFS_Depreciation
Creating the Groovy rule
Open Calculation Manager and create a rule named SM - Push Targets to Planning for Multiple Entities in the OEP_FS cube.
In the Rule Editor, change the Designer option to Edit Script and set the Script Type to Groovy Script.
Iterate over multiple models using the withCloseable construct when getting the model name. The block of code within braces will be executed for each model in the array.
Define the Scenario name.
Create the Strategic Modeling grid for each of the models.
Construct the Planning grid.
Iterate over the Strategic Modeling grid to retrieve data values and addRow on the Planning grid.
Create the Planning grid and save the grid with values pushed from Strategic Modeling.
Adding the rule to the testing form
From the home page, open the Navigator and click Forms (under Create and Manage).
Expand the Library folder, and select Strategic Modeling.
Select the Push Targets Multiple Entities form, and click (Edit).
Select the Business Rules tab. Add the SM - Push Targets to Planning for Multiple Entities rule to the Selected Business Rules list.
Click Finish to save the changes to the form.
Testing the Groovy rule
From the home page, click Strategic Modeling, then select the Push Targets Multi Entity card. Select the Push Targets Multi Entity horizontal tab.
Note:
Notice that there is already data in Operations US East, which was pushed from Strategic Modeling when you ran the SM - Adjust Targets in Bottom-Up Plan rule earlier.
Click Actions, then select Business Rules. Click the SM - Push Targets to Planning for Multiple Entities rule to launch it. Click OK at the confirmation message.
Compare the resulting data in the form to the data in the Strategic Modeling horizontal tabs (Operations West, Operations East, Operations North, and Operations South) to verify that the data was pushed from the expected accounts (for example, v1000:010 Product Sales was pushed to OFS_Product Revenue).