Alithya’s PowerShell Accelerator for Ground-to-Cloud EPM

Oracle provides a powerful toolset for interaction with the EPM Suite through a set of REST APIs and a downloadable product called EPM Automate that provides for command line access to a significant portion of the REST APIs.  At many of our customers, we implement scripts to orchestrate and schedule processes.  These processes execute EPM jobs, transfer metadata and data to the EPM Suite, and download data from the EPM Suite.

We believe in standards to facilitate common implementations across our large customer base and to manage the evolving nature of Oracle’s EPM Suite.  Standardization improves our ability to support our customers, and we have taken steps to consolidate our scripting into a single preferred service accelerator that provides high-quality, implementation-proven script utilities in a packaged delivery.

When we started this effort, we established a set of criteria for what we wanted to accomplish:

  1. Provide scripts that work in either Windows or Linux environments.
  2. Apply scripting best practices in a packaged delivery.
  3. Improve quality, delivery performance, and supportability by having a set of scripted functions that are unit tested prior to first use at a customer.
  4. Provide a standard approach to setup of jobs including signing into EPM Automate.
  5. Provide a standard logging framework.
  6. Provide exception handling including emailing.
  7. Provide a standard approach to archival of transferred files.
  8. Provide a standard approach to post procedure clean-up of temporary files.
  9. Provide ability to run scripts individually and together.
  10. Allow the calling scripts to be easily readable.

Why PowerShell?

Establishing the programming language was foundational and involved conversations about batch, Bash and PowerShell.  Although each language has advantages and weaknesses, the Product team selected PowerShell for the following reasons:

  • Robust interpreted programming language that includes standard capabilities such as variable, functions, loops, exception handling, etc.
  • Native on Windows environments with a nice development environment, PowerShell-ISE.
  • Can invoke commands and batch scripts easily.
  • Intended future direction for Microsoft with strong on-line support.
  • Open-sourced and available on Linux, testing showed that little or no modification to scripts is required for use in Linux environments.

What are we Providing?  A Working Example

We provide a packaged set of utilities called EPMAutomatePowerShellUtilities as an accelerator to development of ground-to-cloud scripts.  EPM Automate is in the name because we primarily use EPM Automate to accomplish an action, but also use the REST APIs when EPM Automate does not provide the required action.

To highlight the accelerator, lets document a working example with a customer implementing a Profitability and Cost Modeling Cloud Service (PCMCS) solution.

Customer is providing dimensional data and content data files and needs the following actions:

  • Upload Dimensional Data and integrate into PCMCS
  • Upload Content Data and Run Allocations
  • Download Post Allocated Results
  • Run all the above as a Single Script

First, the Boiler Plate

All customer scripts have the following boiler plate to provide common behavior

try

{

  • $PSScriptRoot/config/properties.ps1
  • $epmautomatepowershellutilities/Utilities.ps1

    Pre-Job-Run $Profile

    #Place your actions here!

}

catch

{

    Email-Exception

}

finally

{

    Post-Job-Run

}

What is going on?

  • try … catch … finally – allows for exception handling and script resolution in a common pattern. Standardized exception handling improves the quality of the ETL process by ensuring that support personnel are notified via email for any process execution stoppage.
  • – $PSScriptRoot/config/properties.ps1 – loads the variables required to run the scripts. For example, we load $ApplicationName which is the PCMCS application with which we are working.  The properties.ps1 is a text file that requires very little maintenance after initial setup.
  • – $epmautomatepowershellutilities/Utilities.ps1 – loads all the custom functions we provide.
  • Pre-Job-Run $profile – sets up job and makes it ready to run including signing into EPM Automate.
  • #Place your actions here! – this is where the custom actions are placed. See Scripts 1, 2, 3, and 4 below for examples of custom actions.
  • Email-Exception – when an exception occurs, then email an error message including a zip of the temporary folder that contains process log and any other files that were created by custom actions.
  • Post-Job-Run – clean up after custom actions are complete by signing out of EPM Automate and optionally removing temporary folder (configurable).

Script 1: UploadDimensionData.ps1 – Upload Dimensional Data and Integrate into PCMCS

We won’t repeat the boiler plate and focus on the custom actions:

Upload-DimData-And-Load $ApplicationName “$inboxFolder\Dimensional Data”

Enable-App $ApplicationName

Deploy-Cube $ApplicationName -KeepData -RunNow

Readability is a huge factor here.  We really don’t need to explain what these custom actions are doing, but let’s highlight a couple of things.  First, the called function often looks a lot like a corresponding EPM Automate command; for example, “Enable-App” corresponds to the EPM Automate command “enableApp.”  Second, we provide more complex calling functions such as “Upload-DimData-And-Load” to perform a set of common actions that run multiple commands – in this case the upload of multiple files – and then run the loadDimData command for all the uploaded files.  Behind the scenes, an archive copy with a timestamp is placed in an archive folder for each of the uploaded files.

Script 2: UploadData.ps1 – Upload Content Data and Run Allocations

Again, without boiler plate:

Clear-POV $ApplicationName “VR_Working;SC_Forecast” -InputData -AllocatedValues -POVDelimiter “;”

Copy-POV $ApplicationName “NoVersion,SC_Forecast” “VR_Working,SC_Forecast” -isManageRule

Upload-Data-And-Load -ApplicationName $ApplicationName -Path “$inboxFolder\data” -DataLoadValue “OVERWRITE_EXISTING_VALUES”

Run-Calc -ApplicationName $ApplicationName -ModelPOV “VR_Working;SC_Forecast” -ExeType “ALL_RULES” -ClearCalculated -ExecuteCalculations -RunNow -isOptimizeReporting -POVDelimiter “;”

You’ll see a mix of EPM Automate analogs and a complex function that uploads all the content data and loads them into PCMCS.  Again, the archival of uploaded files occurs during the Upload-Data-And-Load function.

Script 3 – DownloadResults.ps1 – Download Post Allocated Results

The custom actions are:

Export-Query-Results $ApplicationName “PCMCSDataExport.txt” “Post_allocated”

Download-File “profitoutbox\PCMCSDataExport.txt”

Script 4 – JustDoIt.ps1 – Perform all Three Steps

The boiler plate is built so that the Pre-Job-Run, Email-Exception, and Post-Job-Run understand when they are inside a calling script.  This allows you to create a parent script to run multiple other scripts without modification of the called scripts.

Focusing on the custom actions:

. $PSScriptRoot\UploadDimensionData.ps1

. $PSScriptRoot\UploadData.ps1

. $PSScriptRoot\DownloadResults.ps1

In this parent script, EPM Automate is logged into a single time.  Any exception results in full stop and a single email sent with an integrated process log.

Final thoughts

The accelerator provides a high-quality framework allowing Alithya to focus on customer requirements and the actions needed to integrate with Oracle’s Cloud EPM suite.  The low level, expected behaviors, such as exception reporting, logging, emailing, and file archival are available on day 1 of the engagement.  With a focus on readability, these scripts are easily transferred to the support organization for long-term sustainability.

For long-term support, the customer can update the REST API version via the properties.ps1 file, and Oracle is providing EPM Automate updates that do not break prior scripts.  If EPM Automate has a breaking change, the customer can update the utilities themselves or request an updated set from Alithya.  Feedback from our customer base is positive with specific comments about the readability of the utilities and quality of initial deployment.

Overall, the accelerator is reducing the effort and time to deploy ground-to-cloud processes while improving the quality of deployment by reducing the time spent creating and debugging scripts.

Additionally, long-term support costs are lower through standardization of implementation patterns that allow support personnel to focus on what the script is accomplishing rather than how it is accomplishing it.

For comments, questions or suggestions for future topics, please reach out to us at infosolutions@alithya.comSubscribe to receive notifications about new posts about Cloud updates and other Oracle Cloud Services such as Planning and Budgeting, Financial Consolidation, Account Reconciliation, and Enterprise Data Management.  Follow Alithya on social media for the latest information about EPM, ERP, and Analytics solutions to meet your business needs.

Twitter  |  Linkedin  |  Facebook  |  YouTube

Demystify the Balance Dimension in Profitability and Cost Management

Management Ledger models, whether Hyperion Profitability and Cost Management (HPCM) or Profitability and Cost Management Cloud Service (PCMCS), have been around for a few years, but I still receive emails asking for help with figuring out where the results are coming from. This request is often related to a lack of understanding of the Balance dimension. Here are some key pieces of information regarding this system dimension, how it works, how it should be used when defining allocations and integration jobs, and how to leverage it to troubleshoot your allocations.

Before we have a look at each member within this dimension, let’s go over some basic rules that govern the creation of an HPCM or PCMCS Management Ledger (ML) application:

  1. All HPCM or PCMCS ML applications must contain just one dimension named Balance
  2. Members and their properties cannot be edited or removed.
  3. You don’t need to import a file in order to load/setup the Balance dimension; members are created automatically when deploying an application for the first time.
  4. You can choose to rename the Balance dimension (translate it into another language, for example) when you first set up the application in PCMCS.

For the most part, the Balance dimension members are quite easy to follow and understand, but familiarity with usage guidelines helps to avoid issues during development and supports troubleshooting.

Demystifying the Balance Dimension in PCM - Image 1

  • Input — Used to store data input/pre-allocated data sets, whether these are pool or driver data sets. Data is generally loaded against this member in combination with the NoRule member. Input can be populated through custom calculations, but it is generally advised to keep it dedicated to valid data loads/input rather than for storing calculated or allocated results.
  • Adjustment In —Adjustment In can be used for manual adjustments to the Input data prior to running allocations. In this case, the Adjustment In data will be loaded against the NoRule member. Any manually submitted data on the Adjustment member against a Rule ID member may be eliminated during the subsequent data loads and calculations. Adjustment In can also be used during custom calculations to store intermediary values or calculated driver data.
  • Adjustment Out —Same usage as for Adjustment In, but with a negative data value.
  • Allocation In — This member will be populated against the Destination or Target intersection for the allocation rule.
  • Allocation Out —This member will be populated against the Source intersection of the allocation rule and the corresponding Rule ID member, or against a predefined “Offset” intersection that is custom defined for a given rule.
  • Allocation Offset Amount — Displays an amount that further reduces an Allocation In member, if one was used in addition to the Allocation Out. I have provided an example of how this member is populated and used in a lower section of this post.
  • Net Change — represents the total change for a given intersection, regardless of alternate offset actions.
  • Net Balance – sum of Input (initial data loaded) and any Net Changes made to the same intersection.
  • Remainder — Displays the difference between Allocation In and Allocation Out plus Allocation Offset Amount, if any.
  • Balance — The amount resulting when adjustments, allocations, and offsets are considered.

Rules assign funds to destinations based on the way you have defined the allocation logic (member selections, sequencing, concurrency, etc.). “Allocations in” and “allocations out” are being generated upon executing the calculations of the Profitability model. Each pair of adjustments and allocations (the “in” and the “out”) should result in a zero sum in order to balance the transaction. The Input member is affected by each adjustment and allocation. The difference between what was taken from Input and what remains at the end of an allocation will be accounted for in the Remainder.

The Remainder member is the source of your allocations, not the Net Balance member, as most would think.  Remainder takes into consideration alternate offsets and ensures we do not perform a double booking or a double allocation of the same data source, regardless of where the offset was applied.

To further explain the Balance dimension usage, I have used an example from the Bikes default application BksML30, which can be deployed into PCMCS through a few clicks.

The original application had only one adjustment Rule populating the Adjustment In member. I have copied that rule and reused it to demonstrate the same usage for the Adjustment Out member. Remember the adjustment out aggregation operator is still +, so if you want to offset data sets, you must use the appropriate signage for your data; in other words, negate the result either via a multiplication with -1 or by simply adding a – to the formula.

The new ruleset contents will look like this:

Demystifying the Balance Dimension in PCM - Image 2

Our initial data set is loaded on the Input/No Rule combination for the two accounts – Rent and Utilities – on the intersection with Corporate Entity.

The data adjustments are stored against Adjustment In and Adjustment Out.

Demystifying the Balance Dimension in PCM - Image 3

In order to further illustrate how to correctly follow the allocation process, I split the original Reassignment rule into 2 rules, each dedicated to its own account. I also updated the metadata by adding two new Account siblings to Rent and Utilities as offsets for each account.

Alternate offsets are simply intersections of members where you would like to store the offset data point, if it should differ from the source of the allocation.

The Remainder member demonstration is connected to the usage of alternate offsets, and before we go into the details of the numerical example, I would like to list out a few rules for setting up alternate offsets:

  • Alternate offsets are available for selection only in standard allocation rules. For Custom calculations, your Offset custom calc would have to be pointed to the appropriate “alternate” target.
  • All dimensions, including the ones predefined in the rule context, are repeated in the Offset screen as soon as you select “Alternate Offset Location.” You must select a single base level member for at least one dimension.
  • There is no “Same As Source” (SAS) option for offsets. The dimensions that must be offset on the Source intersections can be left blank in the Offset screen selections.
  • If each source member selection has its own offset, you will have to split the rule up into as many granular rules as needed in order to cover the individual offset selection. For example, if you have 6 accounts, each with its own offset account equivalent, you will have to create 6 standard allocation rules to create the individual offset selection for each account.

Going back to the numerical example and the usage of the Offset tab, in the update rule I have selected the below member intersections:

Demystifying the Balance Dimension in PCM - Image 4

The Source account was Rent, target is “Same as Source” (SAS), and the alternate offset account is FACOffset_Rent.

After the rules are executed, we will see the results below; focus on the Allocation Offset Amount member and the Allocation Out Member.

Even though the offset was applied to an alternate account for both Rent and Utilities, the allocation engine correctly identifies the Remainder of these two accounts as being 0.

  1. The first step behind the scenes is for the allocation to correctly distribute the data to the target intersections.
  2. The second step is to perform the offset on the intersection specified by the user, if different from the source intersection.
  3. The third step is to copy the Allocation Out value onto the Source Intersection members, on allocation Offset Amount member. This final step is performed via a custom calculation embedded in the PCMCS generated scripts which ensures there will be no double counting of pool data.

So even though we “moved” data from the Rent account, Corporate Entity, to other Entities, on the same target Account, the offset was performed on an alternate member. This allows us to create a report with Rent (Input), Rent (Allocation In) and FACOffset_Rent (Allocation out).

This is not a typical example of how alternate offsets are used from a functional standpoint, but it helps explain the mechanics behind the scenes. This alternate offset option is mostly used in cases where a Bill Out account and a Chargeback account will differ and allows users to trace which portion of a chargeback account is coming from different source accounts.

The final goal of an allocation is to generate a Remainder member with a value of 0. This ensures the total allocation of a pool data set, whether this was loaded or received from prior allocation steps. If the Remainder member has a positive value, then it is indicating that you have not fully utilized your pool data. If the Remainder member has a negative value, then you have overutilized your pool data which may be, in some cases, intentional.

Demystifying the Balance Dimension in PCM - Image 5

In situations where you will not give access to the PCMCS ML application to users who need to understand the various components of a data point flowing through the allocation steps, due to licensing costs or other considerations, the usage of alternate offsets throughout your allocation flow might be helpful.

When talking about reporting out of PCMCS ML, our clients always emphasize simplicity, and we often get requests to remove the Rule and Balance dimensions from final reporting solutions, to cancel the noise and give finance users solely the core information. In such situations, the usage of alternate offsets has proved beneficial as these finance users can still follow the flow and components of a cost without having to deal with the rule by rule detail. If further investigation is necessary, this can be pursued within the PCMCS ML model itself rather than in the external reporting solution.

If you need further help with figuring out the purpose and usage of the Balance dimension within PCMCS, email us at infosolutions@alithya.com. Our PCM Center of Excellence team is ready to share leading practices and industry-specific solutions that accelerate your ROI and expand the capabilities of your chosen profitability software.

Security configuration in TaskFlows (EPMA 11.1.2) – Cookbook style

TaskFlows in Enterprise Performance Management Architect (EPMA) can be used to sequence any number of EPMA operations such as dimension updates, application deployments, and data synchronisations.  They are also able to execute batch jobs and send emails, all of which makes them potentially useful for automation and integration of EPMA applications.

TaskFlows can be scheduled by a built-in scheduler in TaskFlow Management, but there is also the option to run task flows ‘interactively’ (i.e. on-demand). For one particular client application, this was an attractive approach to delivering a capability to kick-off processes in-day on an ad-hoc basis, but we needed to ensure that some kind of security could be applied to the different task flows so that ‘run’ permissions could be assigned to the correct people for the correct TaskFlow.

Our client system was based on 11.1.2, and the procedure below is based on 11.1.2.1.  I could not find much documentation around doing this on 11.1.2, and it appears that the controls around TaskFlow security have changed since 11.1.1.3.  Therefore I decided to set up a simple PoC as described below.

The summary approach is as follows:

  • Set up several simple TaskFlows
  • Set up security roles (Create the aggregated HSS roles)
  • Modify the Access Control to the TaskFlows
  • Set up several users & provision them to have access to different roles
  • Demonstrate the effect of these different levels of access

Set up some simple TaskFlows

TaskFlows can be created to run many different EPM processes such as cube deployment, data import/export, but for our example we are just going to get the TaskFlows to execute a simple batch file, which will write to an output file, which indicates that our TaskFlow has successfully run.

The administrator should have, as a minimum, the following roles provisioned in Shared Services, to be able to create TaskFlows:

TaskFlow administration is then accessed in EPMA, via ‘Navigate’ > ‘Application Library’, and then choose menu ‘Administration’  > ‘Manage Task Flows’, which will bring up the following screen:

From here, new TaskFlows can be created, and existing ones can be edited, deleted, scheduled and executed.

Below I have set up TaskFlow TF_1 to execute a batch job in Stage1.

This is done by selecting the processing tab & choosing ‘Hub’ from the ‘Application’ drop-down, ‘Execute’ from the ‘Action’ drop-down, and specifying the name of the batch file to execute.

(by default batch files are located in %HYPERION_HOME%\Common\Utilities, and output is routed to Oracle\Middleware\user_projects\domains\EPMSystem folder).

(Note, the example has Stage2 in it – TaskFlows can contain any number of Stages – this example has 2 stages purely as a result of experimentation – each stage simply executes a basic batch file to create an output file)

For the purposes of  testing access control, I created the following 4 TaskFlows (by using the ‘Save As’ control) :

Now that we have set up several TaskFlows, we will set up some users to demonstrate access control.  The first step is to create ‘aggregated roles’ in Shared Services (HSS) to allow each TaskFlow to have a different level of access, by associating it with different roles.

Set up security roles (Create the aggregated HSS roles)

The pre-defined HSS Administrator roles of ‘Manage TaskFlows’ & ‘Run TaskFlows’ will give default access to a user to run manage / run a new TaskFlow, until the access control of that TaskFlow is edited.

To satisfy our requirement to have differing levels of access we create different  ‘aggregated’ roles in HSS, with different access levels.  There are 2 access levels for TaskFlows

  • ‘Manage’ access allows the user to create, delete, schedule & run TaskFlows
  • ‘Run’ access allows the user to only run TaskFlows.

(Having no access, simply means that a user will only be able to ‘View’ TaskFlow status.)

For our example, we want to achieve the following access to TaskFlows TF_1 -> TF_4:

This requires us to create the following aggregated roles, which will eventually be associated with the specific TaskFlows :

Creation of aggregated  roles is simple.

1.   Log into HSS as admin, expand the ‘User Directories’, ‘Native Directories’ tree in the explorer and right-mouse-click on ‘Roles, and choose ‘New’:

2.   This brings up the ‘Create Role’ dialog – we enter a Role name, and choose ‘HUB-11.1.2’ as the product group (each product group has its own list of relevant ‘Available Roles’, but TaskFlow related roles exist in the ‘HUB…’ group):

3.   Select ‘Next’ and from the left hand list, select ‘Manage TaskFlows, and move it to the right hand list, and ‘Save’ :

4.   For the Roles which will have Run access, we simply choose the ‘Run TaskFlows’ role from the left-hand list instead.

5.   When we have finished creating all the new aggregated  roles we should have a list in HSS like this:

The next task is to edit our TaskFlows to utilise these new roles.

Modify the Access Control to the TaskFlows

As admin, log into EPMA & navigate to the ‘Manage TaskFlows’ screen.

Select the TaskFlow TF_1 & choose ‘Access Control’:

Each TaskFlow has the option to set the role allowed for ‘Manage’ access and the role allowed for ‘Run’ access:

The drop-down for the ‘Manage Permission Role’ displays all roles (pre-defined and aggregated) that have ‘Manage TaskFlow’ level access:

The drop-down for the ‘Execute Permission Role’ displays all roles (predefined and aggregated) that have ‘Run TaskFlow’ level access:

You can see the Aggregated roles that we created earlier are available.  The ‘Administrator…….Taskflow’ roles correspond to the predefined ‘Manage..’ and ‘Run..’ roles directly under the Administrator node in HSS:

So we can now associate our task flows with the different aggregated roles available, for both the ‘Manage’ & the ‘Run’ access level.

So for TaskFlow TF_1, we set the roles as follows:

The TaskFlows TF_2, 3 & 4 are configured as per the following table:

Now we need to provision our users to give then the correct level of access.

Set up several users & provision them to have access to different roles

Set up the example users (User1 -> User5) in HSS & provision them in the usual way as follows :

I have found that a user must be provisioned with ‘Create Integrations’ role as well as the ‘Manage’ or “Run’ roles, in order to get access to the ‘Manage TaskFlows’ screen.

(These are the roles required for each user in order to achieve the required access to the TaskFlows – it will be necessary to assign users to additional roles to achieve access rights to other parts of the product !)

Running a ‘Provisioning Report’ from HSS on the 5 users for Shared Services applications, we can see what their provisioning is:

Now we can login to EPMA for each of the 5 users and we can see the combined effect of the role creation, user provisioning & ‘Access  Control’ configuration:

User1:

User2:

User3:

User4:

User5:

Additional Information

The pre-defined Shared Services roles ‘Manage TaskFlows’ and ‘Run TaskFlows’ apply by default to any new TaskFlows that are created, because new TaskFlows have the access control roles of ‘Administrator Manage Taskflow’ and ‘Administrator Execute Taskflow’ set by default.  Any new TaskFlows with these default access control settings will only be available to users provisioned with the pre-defined Shared Services roles ‘Manage TaskFlows’ and ‘Run TaskFlows’.  Conversely, any users provisioned only with these pre-defined Shared Services roles, would have access only to TaskFlows with these default access control settings

The ‘Create Integrations’ role that is required to give any user access to the ‘Manage Taskflows’ screen can be embedded into the aggregated roles, rather than provision the role to the user separately:

I found that If a user has roles with ‘Manage TaskFlow’ access to TaskFlow A, and ‘Run TaskFlow’ to TaskFlow B, when the user logs in they appear to have ‘Manage TaskFlow’ access to both TaskFlow A & B. This means that that user can delete TaskFlows that they were not intended to have ‘Manage TaskFlow’ rights to , so it would be best to avoid this scenario.

Finally I found that logging out of one user session and logging back in as another user, without closing down the browser session,  had the effect that the second user’s privileges appeared to be the same as the first, even if their access was lower.  I worked around this by exiting all browser sessions & clearing the browser cache in between logins.  This could have been a local environment issue.

References

The Oracle library has the following guidance on TaskFlows and security relating to them:

http://docs.oracle.com/cd/E17236_01/epm.1112/hss_admin_1112200.pdf chapter 6 – Managing Roles, chapter 8 -Managing TaskFlows

http://docs.oracle.com/cd/E17236_01/epm.1112/epma_admin.pdf part IV, Using Task Automation