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

Enterprise Data Management: Version 19.07 – Top 3 Added Features

The latest 19.07 release of Enterprise Data Management (EDM) contains a boatload, a plethora – no, make that oodles of new features (time to put Merriam-Webster away). The full release notes are documented here: EDM July 2019 Update

In this blog, I’d like to dive into 3 of my favorite features (so far) from this release.

#1: Create Request Items from Compare Results

I’m already using this feature at one of my current clients. I’ve always touted the “Compare” feature of EDM. It’s been one of EDM’s best features from the beginning and provides the ability to compare Missing Nodes, Relationship Differences, and Property Differences. The biggest gap?  Previously, when the compare results are returned, there was no way to download or utilize the results in a request to resolve the differences. While you could always fix differences manually with drag-n-drop or direct property updates, when your compare results return dozens or hundreds of differences, that is not a reasonable option.

Well fear no more!  You can now create a request load file directly from the compare results. And it works as easily as you might have hoped it would:

  1. Run a Compare between two viewpoints.
  2. When the differences are returned, click “New Request.”
  3. Notice that new icon? Click that bad boy and a request load file will be automatically generated.

Kevin Black - EDM V19.07 - 7-24-19 - Image 1

4.  Now you can see that the request file has been attached to the request and the request items are added to your “shopping cart.” Make any additional changes, submit your request, and your viewpoints are synchronized! Easy peasy.

Kevin Black - EDM V19.07 - 7-24-19 - Image 2

NOTE

What is interesting is to analyze the request file that is generated from the compare result. You will notice it contains UPDATE and PROP_UPDATE actions. Why those? Well, PROP_UPDATE is used for property differences returned by the compare. And UPDATE is used for missing node and relationship differences. The UPDATE command is quite sneaky and powerful. Not only will it update node properties, but, depending on if the node exists and if the hierarchy set allows Shared Nodes, it will also UPDATE and perform an ADD, INSERT, or MOVE, too. Pretty cool.

#2: Property Editing

This enhancement not only provides property editing capabilities not available previously, but these edits can also be performed directly on the property without going through the App Registration wizard.

From the Properties card, Inspect the property you wish to edit. You’ll notice the Edit button is now enabled. From here, you can modify property default values, make the property editable or read-only, and modify the “Allowed Values” list if applicable.

No more stepping through the App Registration wizard to apply a simple property update!

Kevin Black - EDM V19.07 - 7-24-19 - Image 3

#3: Import/Export of Allowed Values

I’m so happy this feature is now available. My fingers, and my keyboard, thank you, Oracle!

From the same property editing Inspector dialog mentioned above, you can now modify the “Allowed Values” in a pick-list property without stepping through the “App Registration” wizard. Click the “+” sign to add new values manually. Not shown, but also available in the Actions menu, is the ability to delete or reorder existing list values.

But notice there is now an Import/Export capability. This utilizes a basic Excel file for mass upload/download of list values.

This will be a lifesaver for me at another project where I have Smart List and Attribute properties to build in EDM that contain dozens of list values.

Kevin Black - EDM V19.07 - 7-24-19 - Image 4

Below is a screenshot of the Excel file used to populate this property:

Kevin Black - EDM V19.07 - 7-24-19 - Image 5

Before I close, I’m going to cheat a bit and throw a shout out to one more enhancement in version 19.07…

Honorable Mention: Inspector Dialog Sizing

While custom resizing of the inspector dialog isn’t possible yet, the larger Inspector dialog size certainly makes it easier to view your data chain objects and reduces the amount of scrolling required. This is useful, especially when you’re viewing or reordering a bunch of properties in a viewpoint or node type!

That’s it for now. Be sure to check out EDM version 19.07 if you haven’t already. It contains additional helpful enhancements beyond the few I’ve highlighted here. Stay tuned for more upcoming EDM blog posts. If you need more information or have questions about this topic, email us at infosolutions@alithya.comSubscribe to receive notifications about new posts.  Follow Alithya on social media for the latest information about EPM, ERP, and Analytics solutions to meet your business needs.

Twitter  |  Linkedin  |  Facebook  |  Youtube

EDMCS and Data Governance – Part 3

Welcome to Part 3 – the finale – of the blog series “EDMCS and Data Governance!”

Part 1 provides an introduction and primer for data governance workflows in Enterprise Data Management Cloud Service (EDMCS) which was introduced in the 19.02 release.

Part 2 discusses Workflow Stages in greater detail and dives into the brains of EDMCS workflows – the Approval Policy. Approval policies at different levels of the data chain are explained, and we conclude by building a sample workflow at the dimension level.

In Part 3, I’ll attempt to tie a bow around everything and offer some parting thoughts.

Recap

As I continue to explore and learn about collaborative workflows in EDMCS, these are the key points that come to mind:

  • Emphasize the Fundamentals – No matter what tool you are using, People and Process are extremely important in any data governance solution along with strong executive sponsorship and robust change management.
  • Build the Foundation – get the client comfortable with the tool and content before you introduce workflows. A strong foundation (your applications, dimensions, views, and viewpoints) is needed before you start the plumbing and wiring (workflows).
  • Brush up on Security – I haven’t discussed security extensively in this blog series, but the Oracle EDMCS User Guide does a nice job describing security requirements for assigning and approving workflow requests. Note that security enhancements have been introduced along with workflows. A new “Submitter” permission is now available to go along with Owner, Data Manager, and Browser. And permissions can be assigned at the Application, Dimension, Hierarchy Set, and Node Type levels.
  • Ponder the Approval Policy – this is the most interesting one to me. As we discussed in Part 2, approval policies can be defined at 4 points in the data chain (see Figure 1). With the inheritance and inter-dependencies of approval policies across the data chain along with the actions each policy can govern, it is critical to efficiently design your approval policies up front.

o   For example:

  • Suppose your client requires a final “audit” type of approval across the board for any type of request for any dimension. Or they always a require an upfront “gatekeeper” type of approval to make sure the request is justified and complete before it continues down the approval chain. These would be good candidates for an approval policy at the Application level. And it would avoid having to define duplicative approval policies at lower levels in the data chain.
  • Will your application contain dimensions that do not need data governance workflows? Then Application level approval policies should be avoided.
  • Say you want to limit and govern the actions of a specific group so it can only work with existing nodes (insert, remove, update). An approval policy at the Hierarchy Set level is probably best.

o   Overall, I believe approval policies at the dimension level are a good place to start. Then as the workflows evolve and requirements become more clear, you can determine if there are common factors across all dimension approval policies that can be consolidated at a higher level (Application level approval policy), or if there are specific subsets of actions that need to be broken out to a lower level (Node Type or Hierarchy Set level approval policy).

o   All of which brings up another interesting point: effective approval policy design directly ties into effective viewpoint design. Think about it – you can define the set of Allowed Actions (Add, Insert, Move, etc.) at a Viewpoint level. Which means what? Special-purpose maintenance views are likely required to support certain approval policies, especially those at the Node Type or Hierarchy Set levels.

Figure 1 – Approval Policies and Data Chain

EDMCS and Data Governance – Part 3 - Image 1

How do EDMCS Workflows Compare with DRM/DRG?

I was reluctant to include this section at first because in general, I don’t like comparing Data Relationship Manager (DRM) and EDMCS. Yes, they are both master data management tools and yes, they do share some common concepts and terminology. But overall, the two products are so different in terms of philosophy, deployment design, and underlying architecture that I think comparing the products is often less than helpful.

However, with data governance and collaborative workflows, I feel there is enough commonality that it is worth highlighting a few items. So here goes:

Topic DRM/DRG EDMCS
Workflow Design
  • Based on workflow models and workflow tasks
  • Tasks linked to specific actions (Add Leaf, Add Limb, Insert, Move, etc.)
  • Based on Approval Policies
  • Approval policy level (Application, Dimension, Node Type, Hierarchy Type) determines context and scope of actions governed

 

Workflow Stages
  • Use a Submit stage, a Commit stage, and optionally, one or more Enrich and/or Approve stages
  • ·Use a Submit stage and (implied) Commit stage
  • Approval policies determine approval stages (sequential vs parallel, # of approvers)
  • Requests can be re-assigned for collaboration prior to Submit
User Interface (UI)
  • Form-based design
  • No forms
  • Requesters and approvers interact directly with the viewpoints
Approval Options
  • Support Approve, Reject, and Push Back
  • Support comments, narrative, attachments
  • Support Approve, Reject, and Push Back
  • Support comments, narrative, attachments
Escalations
  • Requests can be escalated based on defined intervals
  • Requests can be escalated based on defined intervals
Separation of Duties
  • Workflows can be configured to prevent a submitter from approving their own request
  • Workflows can be configured to prevent a submitter from approving their own request
Email Notifications
  • Generates email notifications
  • Generates email notifications
Other
  • Supports conditional workflows
  • Supports splitting of requests based on pre-defined criteria
  • Not yet supported

I’m curious if Oracle will introduce a form-based UI for workflows. Part of me would very much like to see that so that you can present a clean user interface to the approvers, hide unnecessary details, and display special instructions and messages, but part of me does not. One of my favorite features of EDMCS is the visual highlighting of pending request changes and the “shopping cart” of request items that are displayed prior to submitting a request. I would hate to lose that by going with a forms-based workflow UI, but perhaps there is a solution that combines the best of both worlds. 

Conclusion

Well that’s it, an initial look at workflows and approval policies in EDMCS. I’m excited to see how this functionality evolves and expands over time. Talk to you next time!

And don’t forget to follow me on Twitter (@kblackEPM) and check out these links for more information:

EDMCS and Data Governance – Part 2

Welcome to Part 2 of the blog series “EDMCS and Data Governance!”

Part 1 provides an introduction and primer for data governance workflows in Oracle Enterprise Data Management Cloud Service (EDMCS) which was introduced in the 19.02 release. This exciting feature addresses a major gap in EDMCS as the product continues to rapidly evolve and mature.

In Part 2, we dive into the details of how to configure workflows. This process revolves around the concept of an “approval policy.” Interestingly, approval policies can be configured at different points of the EDMCS data chain and cascade or inherit to affect downstream points of the data chain.

Workflow Stages

Before we dive into approval policies, let’s discuss EDMCS workflow stages a bit more. They are similar in concept to Data Relationship Governance (DRG) workflow stages. See Figure 1 for an overview:

Figure 1 – EDMCS Workflow StagesEDMCS and Data Governance – Part 2 - Image 1
  1. Submit (or Assign) Request – A request is initially created as you do today. But wait…there’s more! You can Submit the request to immediately move the request into the Approve stage OR you can Assign the request to colleagues to collaborate on the request together. When the request is ready, it is submitted to move to the Approve stage.
  2. Approve Request – The approver(s) have 3 choices:
    • Approve – the request is approved and moves forward (thanks Captain Obvious!).
    • Push Back – like DRG, the request is pushed back to the submitter for clarification or changes, who then updates and resubmits the request.
    • Reject – like DRG, the request is denied and closed. Think of “reject” as the RAID of the data governance world – it kills requests dead.
  3. Commit Request – once fully approved, the request is auto-committed and closed. EDMCS has now been updated.

Approval Policies

Now for approval policies. Approval policies can be configured at 4 levels:

  1. Application
  2. Dimension
  3. Node Type
  4. Hierarchy Set

It is important to note that each data chain object can contain one, and only one, approval policy. However, approval policies have a cascading impact so that multiple approval policies can work in concert to govern and control exactly what you want. Yes, you heard that right:  Approval Policy Inheritance – it’s not just for properties anymore!

The types of actions governed by an approval policy depend on the data chain object it is configured with – see figure 2 below:

Figure 2 – Approval Policies and Data Chain

EDMCS and Data Governance – Part 2 - Image 2As you can see, policies defined at the Application or Dimension level govern all actions (add, delete, insert, remove, move, etc.) while policies defined at the Node Type or Hierarchy Set level govern a subset of actions. Why is this important? Because it means you need to carefully design what types of actions you want to govern and who will perform them. If I define an approval policy at the Hierarchy Set level and then submit a request that Adds 3 accounts, how many approvers are required for the request? A big ZERO! Since I requested “add” actions and only have an approval policy at the Hierarchy Set level, no applicable approval policy exists to govern the request.

Putting It All Together

Let’s walk through an example.

  1. Define Approval Policy

First, I will define an approval policy for the Account dimension. To do this, Inspect either the application or default viewpoint and access the Account dimension from the Definition tab. From there, click the Policies tab.

Here you will see the Approval policy for the Account dimension. Click on the Approval link to inspect the approval policy.

EDMCS and Data Governance – Part 2 - Image 3The General tab will display basic information about the approval policy. You can edit the approval policy name and description if necessary.

EDMCS and Data Governance – Part 2 - Image 4The Definition tab is where the magic happens. Select edit to update the following parameters:

  • Enabled – click this check box to enable the approval policy.
  • Approval Method – select Serial or Parallel.
  • One Approval Per Group – if using Serial approvals, this will automatically be set to “True.” If using Parallel approvals, you can select one approval per group or define a Total Required # of approvers.
  • Include Submitter – enable this to allow the submitter to also be an approver (the submitter’s approval will be automatically granted). If “separation of duties” is required for your company, do not enable this.
  • Reminder Notification – the # of days that will elapse before reminder emails are sent.
  • Approval Escalation – the # of times a reminder occurs before an escalation email will be sent.
  • Approval Groups – select user(s) and/or group(s) to be included in the approval process. When using Parallel approvals, the order of approval groups does not matter. When using Serial approvals, the order of approval groups does matter – you need to list the approval groups in the order that approvals should be executed.

With my example approval policy, I am using serial approvals, 2 approval groups (a Planning group and GL group), a reminder interval of 5 days, and an escalation interval of 2 reminders.

EDMCS and Data Governance – Part 2 - Image 5

  1. Submit Request

Now we’re cooking with gas. It’s time to submit a request. I will submit a request to my default Account viewpoint that includes 1 add, 1 property update, and 1 move. Here is the request in Draft status:

EDMCS and Data Governance – Part 2 - Image 6

Did you notice something new? Look at the Actions button next to Submit. This is where you can assign the request to another user and collaborate with him to finish up the request.

EDMCS and Data Governance – Part 2 - Image 7

EDMCS and Data Governance – Part 2 - Image 8

  1. Approve the Request

After the request is submitted, it is considered “in flight” because it has been submitted, but not yet approved/committed. And look! EDMCS now offers a nice Activity page on the home screen displaying the status of various workflow requests:

EDMCS and Data Governance – Part 2 - Image 9

First, the users in the Planning Approvers group will receive an email notifying them that they have been “invited to approve a request” (it’s very polite):

EDMCS and Data Governance – Part 2 - Image 10

As mentioned earlier, an approver has 3 choices: Approve, Reject, or Push Back. Reject and Push Back are available under the Actions dropdown. Here are the dialog windows that will be displayed for those actions (note the comment field is required):

EDMCS and Data Governance – Part 2 - Image 11

Otherwise, the approver will click the Approve button and see this:

EDMCS and Data Governance – Part 2 - Image 12

And then the same process will continue with the GL Approvers group since I am using Serial approvals. Once again, an approver can reject, push back, or approve. Once approved, the request is committed and closed.

Congratulations! You have now completed your very first data governance workflow request in EDMCS!

Conclusion

This blog post should be useful in providing more details and clarity on workflows, workflow stages, and approval policies. In the third and final post for this series, I’ll offer a recap and some closing thoughts. Talk to you then.

Read the next post in this EDMCS blog series:  EDMCS and Data Governance – Part 3

And don’t forget to follow me on Twitter (@kblackEPM) and check out these links for more information:

EDMCS and Data Governance – Part 1

Ahh… February. An interesting month with a variety of happenings. From the significant – Black History Month and President’s Day, to the exciting – the Super Bowl…well sometimes. From the romantic -Valentine’s Day, to the silly – that tenacious ground hog trying to find his shadow…AGAIN. Not to mention that Spring is just around the corner and brings us the glorious event known as “March Madness!”

Why am I babbling about February? <segue> Because it is also the month that introduced Data Governance and Collaborative Workflows with the release of Enterprise Data Management Cloud Service (EDMCS) v19.02. <segue>

As we continue this journey to Enterprise Performance Management (EPM) Cloud, the addition of Data Governance to EDMCS is a major step forward, especially for those of us who have worked with the classic on-premise solutions (Data Relationship Management (DRM) and Data Relationship Governance (DRG)) and who have been awaiting a similar offering in EDMCS to support our Cloud clients. From what I’ve seen so far, a major gap between DRM/DRG and EDMCS has been addressed with this release.

In this blog series, I’d like to further explore Data Governance in EDMCS. At a high level, this is how I see this series unfolding:

  • Part 1 will provide the foundation, background, and basic concepts for EDMCS and Data Governance
  • Part 2 will get more into the “techy” stuff and dive deeper into Approval Policies and Security
  • Part 3 will provide a recap and closing thoughts/lessons learned

So, with that said, onto Part 1…

Prerequisites

Before diving head first into configuring Data Governance and collaborative workflows in EDMCS, there are a few things to consider.

  • Don’t forget people and process. I’m a big believer that people and process are just as (and usually much more) important as the tool. Please refer to this blog post for a quick read on this: The Data Governance Triple Crown.

I believe the same tenets apply to EDMCS and that it’s important to start thinking about a formal data governance program that includes a charter, executive sponsorship, roles & responsibilities, metrics, and much more. Data Governance can be a challenging cultural shift for many organizations which requires strong change management to handle the inevitable resistance. This is where a formal data governance framework can help.

  • Establish the foundation. As with building a house, it’s important to lay a solid foundation before you install the wiring and plumbing. Build your EDMCS application(s) and dimensions, and populate your primary and alternate hierarchies first. Get the client comfortable with the tool and the content. Then you can start to layer in the workflows.
  • Start to identify the “who” (e.g. the people involved and the roles they will play: who will be submitting requests? Who will be approving? Who will do both?
  • Start to think about the “what.” What applications/dimensions/hierarchies will be governed? What are the use cases and typical scenarios that require data governance? Start to collaboratively mock up and storyboard some typical workflows with the client to visualize how the workflows will function. And don’t try to build a workflow for every possible scenario. Start with the big hitters and low hanging fruit first. You can always add more workflows later.

What’s Included in EDMCS Workflows?

Are you wondering what EDMCS includes as far as data governance functionality? In summary, EDMCS supports:

  • Two types of roles – submitters and approvers
  • Separation of duties – workflows can be configured to prevent submitters from approving their own requests
  • The “four eyes” principle: EDMCS data governance adheres to the principle that requests must be approved by at least two people
  • Default application views and maintenance views: workflows can work with both types of views
  • Subscriptions: workflows can be triggered by Subscription requests
  • Email-based notifications
  • Serial and Parallel approvals:
    • Serial approval means a sequential order of approvals is required. For example, Approver #2 can’t approve until Approver #1 approves, Approver #3 can’t approve until Approver #2 approves, and so on.
    • Parallel approval means the approvals can occur in any order and at the same time.
    • With either method, all approvals must occur before the request is committed.
  • Configuration of Reminder and Escalation intervals
  • Multiple Workflow Stages:
    • Submit – initiate the request and add/edit/delete line items in the request. Note that with the 19.02 release, you can also attach documents and insert comments at the line item level. These enhancements are helpful to attach policies, supporting details, and other documentation related to the workflow request.
    • Approve – similar to DRG, an approver can approve, push back, or reject a request. Pushing back will send the request back to the submitter for additional changes. Rejecting will close the request and end the workflow.
    • Commit (implied) – once the request is fully approved, it is committed, hierarchies are updated, and the request history can be viewed like any other request.
  • Approval Policies – this is really the brains of how workflows are configured in EDMCS, and the next blog post cover this in greater detail. But here is a screenshot of the Approval Policy screen showing the available options:

Kevin Black - EDMCS and Data Governance - Part 1 - 3-8-19 Image 1

Conclusion

I hope you found this blog post helpful as an introduction to EDMCS and data governance, and that you will keep reading as the rest of the series is posted. Please contact me with any questions and comments!

And don’t forget to follow me on Twitter (@kblackEPM) and check out/subscribe to my blog (along with the blogs authored by my very talented colleagues at Alithya).

Read the next post in this EDMCS blog series:  EDMCS and Data Governance – Part 2

https://ranzal.blog/author/kblackranzal/

https://ranzal.blog/

Interested in better understanding EDMCS, the RESTful API, and Cloud Data Management? Be sure to check these excellent blog posts by Tony Scalese, aka FDM Guru: https://ranzal.blog/author/ascalese/

Looking for an outstanding resource for all things master data-related and more? Look no further!  https://datarestless.com/

Automating Enterprise Planning with EPBCS: A Case Study Featuring Sims Metal Management

Enterprise Planning and Budgeting Cloud ServiceIn using Enterprise Planning & Budgeting Cloud Service (EPBCS) to support annual budgeting and forecasting processes, organizations are choosing solutions that allow them to leverage the financials, projects, capital and workforce business processes necessary to provide a driver-based solution that links expected intake to revenues and costs. In turn, they are able to more efficiently produce integrated income statements, balance sheets and cash flow statements.

Featuring Jim Clark of Sims Metal Management, Our Special Guest

 Our August 16, 2017 webinar, featuring Jim Clark, Group Manager of FP&A at Sims Metal Management, takes a detailed look at how one organization automated enterprise planning to streamline processes and produce better results.

Within a real-world scenario, this means that whether using EPBCS out of the box or as a “hybrid” of OOTB with customized extensions, companies like Sims are able to adjust sales forecasts—throughout the year and through sales cycles—to better match the actual costs and needs in areas such as raw materials and labor.

A Better Approach To Performance Management

Using this integrated approach to Performance Management, companies are, in effect, bringing actual performance numbers, on a monthly basis, into their models.

As a result, changes and adjustments can be fine-tuned and incorporated into the mix.  Forecasts can be based more on actual numbers and less on assumptions, thus leading to a balance sheet that matches projections. From a planning perspective, companies can be more nimble and, ultimately, create their models with greater accuracy.

Whether you are participating live or via a recording, this webinar will illustrate how organizations like Sims are leveraging EPBCS in ways that allow them to: 

  • Gain insight to increase efficiency and improve outcomes
  • Better understand how organizations like yours can make standardization and centralization a top priority
  • See how an integrated solution works not just in theory, but actually in practice
  • Follow the processes to results that include improved accuracy and increased efficiency across the enterprise

For More Information

No matter where your team or your organization is along your EPBCS journey, this webinar is certain to provide you with valuable insight and context that can help you to implement changes that lead to greater efficiency and a more streamlined forecasting process overall.

Register for our “Automating Enterprise Planning with EPBCS: A Case Study Featuring Sims Metal Management ” webinar:

Missed the webinar? View Recording Here.

 

A Comparison of Oracle Business Intelligence, Data Visualization, and Visual Analyzer

We recently authored The Role of Oracle Data Visualizer in the Modern Enterprise in which we had referred to both Data Visualization (DV) and Visual Analyzer (VA) as Data Visualizer.  This post addresses readers’ inquiries about the differences between DV and VA as well as a comparison to that of Oracle Business Intelligence (OBI).  The following sections provide details of the solutions for the OBI and DV/VA products as well as a matrix to compare each solution’s capabilities.  Finally, some use cases for DV/VA projects versus OBI will be outlined.

For the purposes of this post, OBI will be considered the parent solution for both on premise Oracle Business Intelligence solutions (including Enterprise Edition (OBIEE), Foundation Services (BIFS), and Standard Edition (OBSE)) as well as Business Intelligence Cloud Service (BICS). OBI is the platform thousands of Oracle customers have become familiar with to provide robust visualizations and dashboard solutions from nearly any data source.  While the on premise solutions are currently the most mature products, at some point in the future, BICS is expected to become the flagship product for Oracle at which time all features are expected to be available.

Likewise, DV/VA will be used to refer collectively to Visual Analyzer packaged with BICS (VA BICS), Visual Analyzer packaged with OBI 12c (VA 12c), Data Visualization Desktop (DVD), and Data Visualization Cloud Service (DVCS). VA was initially introduced as part of the BICS package, but has since become available as part of OBIEE 12c (the latest on premise version).  DVD was released early in 2016 as a stand-alone product that can be downloaded and installed on a local machine.  Recently, DVCS has been released as the cloud-based version of DVD.  All of these products offer similar data visualization capabilities as OBI but feature significant enhancements to the manner in which users interact with their data.  Compared to OBI, the interface is even more simplified and intuitive to use which is an accomplishment for Oracle considering how easy OBI is to use.  Reusable and business process-centric dashboards are available in DV/VA but are referred to as DV or VA Projects.  Perhaps the most powerful feature is the ability for users to mash up data from different sources (including Excel) to quickly gain insight they might have spent days or weeks manually assembling in Excel or Access.  These mashups can be used to create reusable DV/VA Projects that can be refreshed through new data loads in the source system and by uploading updated Excel spreadsheets into DV/VA.

While the six products mentioned can be grouped nicely into two categories, the following matrix outlines the differences between each product. The following sections will provide some commentary to some of the features.

Table 1

Table 1:  Product Capability Matrix

Advanced Analytics provides integrated statistical capabilities based on the R programming language and includes the following functions:

  • Trendline – This function provides a linear or exponential plot through noisy data to indicate a general pattern or direction for time series data. For instance, while there is a noisy fluctuation of revenue over these three years, a slowly increasing general trend can be detected by the Trendline plot:
Figure 1

Figure 1:  Trendline Analysis

 

  • Clusters – This function attempts to classify scattered data into related groups. Users are able to determine the number of clusters and other grouping attributes. For instance, these clusters were generated using Revenue versus Billed Quantity by Month:
Figure 2

Figure 2:  Cluster Analysis

 

  • Outliers – This function detects exceptions in the sample data. For instance, given the previous scatter plot, four outliers can be detected:
Figure 3

Figure 3:  Outlier Analysis

 

  • Regression – This function is similar to the Trendline function but correlates relationships between two measures and does not require a time series. This is often used to help create or determine forecasts. Using the previous Revenue versus Billed Quantity, the following Regression series can be detected:
Figure 4

Figure 4:  Regression Analysis

 

Insights provide users the ability to embed commentary within DV/VA projects (except for VA 12c). Users take a “snapshot” of their data at a certain intersection and make an Insight comment.  These Insights can then be associated with each other to tell a story about the data and then shared with others or assembled into a presentation.  For those readers familiar with the Hyperion Planning capabilities, Insights are analogous to Cell Comments.  OBI 12c (as well as 11g) offers the ability to write comments back to a relational table; however, this capability is not as flexible or robust as Insights and requires intervention by the BI support team to implement.

Figure 5

Figure 5:  Insights Assembled into a Story

 

Direct connections to a Relational Database Management System (RDBMS) such as an enterprise data warehouse are now possible using some of the DV/VA products. (For the purpose of this post, inserting a semantic or logical layer between the database and user is not considered a direct connection).  For the cloud-based versions (VA BICS and DVCS), only connections to other cloud databases are available while DVD allows users to connect to an on premise or cloud database.  This capability will typically be created and configured either by the IT support team or analysts familiar with the data model of the target data source as well as SQL concepts such as creating joins between relational tables.  (Direct connections using OBI are technically possible; however, they require the users to manually write the SQL to extract the data for their analysis).  Once these connections are created and the correct joins are configured between tables, users can further augment their data with data mashups.  VA 12c currently requires a Subject Area connected to a RDBMS to create projects.

Leveraging OLAP data sources such as Essbase is currently only available in OBI 12c (as well as 11g) and VA 12c. These data sources require that the OLAP cube be exposed as a Subject Area in the Presentation layer (in other words, no direct connection to OLAP data sources).  OBI is considered very mature and offers robust mechanisms for interacting with the cube, including the ability to use drillable hierarchical columns in Analysis.  VA 12c currently exposes a flattened list of hierarchical columns without a drillable hierarchical column.  As with direct connections, users are able to mashup their data with the cubes to create custom data models.

While the capabilities of the DV/VA product set are impressive, the solution currently lacks some key capabilities of OBI Analysis and Dashboards. A few of the most noticeable gaps between the capabilities of DV/VA and OBI Dashboards are the inability to:

  • Create the functional equivalent of Action Links which allows users to drill down or across from an Analysis
  • Schedule and/or deliver reports
  • Customize graphs, charts, and other data visualizations to the extent offered by OBI
  • Create Alerts which can perform conditionally-based actions such as pushing information to users
  • Use drillable hierarchical columns

At this time, OBI should continue to be used as the centerpiece for enterprise-wide analytical solutions that require complex dashboards and other capabilities. DV/VA will be more suited for analysts who need to unify discrete data sources in a repeatable and presentation-friendly format using DV/VA Projects.  As mentioned, DV/VA is even easier to use than OBI which makes it ideal for users who wish to have an analytics tool that rapidly allows them to pull together ad hoc analysis.  As was discussed in The Role of Oracle Data Visualizer in the Modern Enterprise, enterprises that are reaching for new game-changing analytic capabilities should give the DV/VA product set a thorough evaluation.  Oracle releases regular upgrades to the entire DV/VA product set, and we anticipate many of the noted gaps will be closed at some point in the future.

The Role of Oracle Data Visualizer in the Modern Enterprise

Chess as a metaphor for strategic competition is not a novel concept, and it remains one of the most respected due to the intellectual and strategic demand it places on competitors. The sheer combination of moves in a chess game (estimated to be more than the number of atoms in the universe) means that it is entirely possible that no two people have unintentionally played the same game.  Of course, many of these combinations result in a draw and many more set a player down the path of an inevitable loss after only a few moves.  It is no surprise that chess has pushed the limits of computational analytics which in turn has pushed the limits of players.  Claude Shannon, the father of information theory, was the first to state the advantages of the human and computer competitor attempting to wrest control of opposing kings from each other:

The computer is:

  1. Very fast at making calculations;
  2. Unable to make mistakes (unless the mistakes are part of the programmatic DNA);
  3. Diligent in fully analyzing a position or all possible moves;
  4. Unemotional in assessing current conditions and unencumbered by prior wins or losses.

The human, on the other hand, is:

  1. Flexible and able to deviate from a given pattern (or code);
  2. Imaginative;
  3. Able to reason;
  4. Able to learn [1].

The application of business analytics is the perfect convergence of this chess metaphor, powerful computations, and the people involved. Of course, the chess metaphor breaks down a bit since we have human and machine working together against competing partnerships of humans and machines (rather than human against machine).

Oracle Business Intelligence (along with implementation partners such as Edgewater Ranzal) has long provided enterprises with the ability to balance this convergence. Regardless of the robustness of the tool, the excellence of the implementation, the expertise of the users, and the responsiveness of the technical support team, there has been one weakness:  No organization can resolve data integration logic mistakes or incorporate new data as quickly as users request changes.  As a result, the second and third computer advantages above are hindered.  Computers making mistakes due to their programmatic DNA will continue to make these mistakes until corrective action can be implemented (which can take days, weeks, or months).  Likewise, all possible positions or moves cannot be analyzed due to missing data elements.  Exacerbating the problem, all of the human advantages stated previously can be handicapped; increasingly so depending on the variability, robustness, and depth of the missing or wrongly calculated data set.

With the introduction of Visual Analyzer (VA) and Data Visualization (DV), Oracle has made enormous strides in overcoming this weakness. Users now have the ability to perform data mashups between local data and centralized repositories of data such as data warehouses/marts and cubes.  No longer does the computer have to make data analysis without the availability of all possible data.  No longer does the user have to make educated guesses about how centralized and localized data sets correlate and how it will affect overall trends or predictions.  Used properly, users and enterprises can leverage VA/DV to iteratively refine and redefine the analytical component that contributes to their strategic goals.  Of course, all new technologies and capabilities come with their own challenges.

The first challenge is how an organization can present these new views of data and compare and contrast them with the organizational “one version of the truth”. Enterprise data repositories are a popular and useful asset because they enable organizations to slice, dice, pivot, and drill down into this centralized data while minimizing subjectivity.  Allowing users to introduce their own data creates a situation where they can increase data subjectivity.  If VA/DV is to be part of your organization’s analytics strategy, processes must be in place to validate the result of these new data models.  The level of effort that should be applied to this validation should increase according to the following factors:

  • The amount of manual manipulation the user performed on the data before performing the mashup with existing data models;
  • The reputability of the data source. Combining data from an internal ERP or CRM system is different from downloading and aligning outside data (e.g. US Census Bureau or Google results);
  • The depth and width of data. In layman’s terms, this corresponds to how many rows and columns (respectively) the data set has;
  • The expertise and experience of the individual performing the data mashup.

If you have an existing centralized data repository, you have probably already gone through data validation exercises. Reexamine and apply the data and a metadata governance processes you went through when the data repository was created (and hopefully maintained and updated).

The next challenge is integrating the data into the data repository. Fortunately, users may have already defined the process of extracting and transforming data when they assembled the VA/DV project.  Evaluating and leveraging the process the user has already defined can shorten the development cycle for enhancing existing data models and the Extract, Transform, and Load (ETL) process.  The data validation factors above can also provide a rough order of magnitude of the level of effort needed to incorporate this data.  The more difficult task may be determining how to prioritize data integration projects within an (often) overburdened IT department.  Time, scope, and cost are familiar benchmarks when determining prioritization, but it is important to take revenue into account.  Organizations that have become analytics savvy and have users demanding VA/DV data mashup capabilities have often moved beyond simple reporting and onto leveraging data to create opportunities.  Are salespeople asking to incorporate external data to gain customer insight?  Are product managers pulling in data from a system the organization never got around to integrating?  Are functional managers manipulating and re-integrating data to cut costs and boost margins?

To round out this chess metaphor, a game that seems to be nearly a draw or a loss can breathe new life by promoting a pawn to a lost queen. Many of your competitors already have a business intelligence solution; your organization can only find data differentiation through the type of data you have and how quickly it can be incorporated at an enterprise level.  Providing VA/DV to the individuals within your organization with a deep knowledge of the data they need, how to get it, and how to deploy it can be the queen that checkmates the king.

[1] Shannon, C. E. (1950). XXII. Programming a computer for playing chess. The London, Edinburgh, and Dublin Philosophical Magazine and Journal of Science, 41(314), 256-275. doi:10.1080/14786445008521796

Oracle Business Intelligence – Synchronizing Hierarchical Structures to Enable Federation

More and more Oracle customers are finding value in federating their EPM cubes with existing relational data stores such as data marts and data warehouses (for brevity, data warehouse will refer to all relational data stores). This post explains the concept of federation, explores the consequences of allowing hierarchical structures to get out of synchronization, and shares options to enable this synchronization.

In OBI, federation is the integration of distinct data sources to allow end users to perform analytical tasks without having to consider where the data is coming from. There are two types of federation to consider when using EPM and data warehouse sources:  vertical and horizontal.  Vertical federation allows users to drill down a hierarchy and switch data sources when moving from an aggregate data source to a more detailed one.  Most often, this occurs in the Time dimension whereby the EPM cube stores data for year, quarter, and month, and the relational data sources have details on daily transactions.  Horizontal federation allows users to combine different measures from the distinct data sources naturally in an OBI analysis, rather than extracting the data and building a unified report in another tool.

Federation makes it imperative that the common hierarchical structures are kept in sync. To demonstrate issues that can occur during vertical federation when the data sources are not synchronized, take the following hierarchies in an EMP application and a data warehouse:

Figure 1: Unsynchronized Hierarchies

Jason Hodson Blog Figure 1.jpg

Notice that Colorado falls under the Western region in the EPM application, but under the Southwestern region in the data warehouse. Also notice that the data warehouse contains an additional level (or granularity) in the form of cities for each region.  Assume that both data sources contain revenue data.  An OBI analysis such as this would route the query to the EPM cube and return these results:

Figure 2: EPM Analysis – Vertical Federation

Jason Hodson Blog Figure 2

However, if the user were to expand the state of Washington to see the results for each city, OBI would route the query to the data warehouse. When the results return, the user would be confronted with different revenue figures for the Southwest and West regions:

Figure 3: Data Warehouse – Vertical Federation

Jason Hodson Blog Figure 3

When the hierarchical structures are not aligned between the two data sources, irreconcilable differences can occur when switching between the sources. Many times, end users are not aware that they are switching between EPM and a data warehouse, and will simply experience a confusing reorganization in their analysis.

To demonstrate issues that occur in horizontal federation, assume the same hierarchies as in Figure 1 above, but the EPM application contains data on budget revenue while the data warehouse contains details on actual revenue. An analysis such as this could be created to query each source simultaneously and combine the budget and actual data along the common dimension:

Figure 4: Horizontal Federation

Jason Hodson Blog Figure 4

However, drilling into the West and Southwest regions will result in Colorado becoming an erroneously “shared” member:

Figure 5: Colorado as a “Shared” Member

Jason Hodson Blog Figure 5

In actuality, the mocked up analysis above would more than likely result in an error since OBI would not be able to match the hierarchical structures during query generation.

There are a number of options to enable the synchronization of hierarchical structures across EPM applications and data warehouses. Many organizations are manually maintaining their hierarchical structures in spreadsheets and text files, often located on an individual’s desktop.  It is possible to continue this manual maintenance; however, these dispersed files should be centralized, a governance processes defined, and the EPM metadata management and data warehouse ETL process redesigned to pick up these centralized files.  This method is still subject to errors and is inherently difficult to properly govern and audit.  For organizations that are already using Enterprise Performance Management Architect (EPMA), a scripting process can be implemented that extracts the hierarchical structures in flat files.  A follow on ETL process to move these hierarchies into the data warehouse will also have to be implemented.

The best practices solution is to use Hyperion Data Relationship Management (DRM) to manage these hierarchical structures. DRM boasts robust metadata management capabilities coupled with a system-agnostic approach to exporting this metadata.  DRM’s most valuable export method allows pushing directly to a relational database.  If a data warehouse is built in tandem with an EPM application, DRM can push directly to a dimensional table that can then be accessed by OBI.  If there is a data warehouse already in place, existing ETL processes may have to be modified or a dimensional table devoted to the dimension hierarchy created.  Ranzal has a DRM accelerator package to enable the synchronization of hierarchical structures between EPM and data warehouses that is designed to work with our existing EPM application DRM implementation accelerators.  Using these accelerators, Ranzal can perform an implementation in as little as six weeks that provides metadata management for the EPM application, establishes a process for maintaining hierarchical structure synchronization between EPM and the data warehouse, and federation of the data source.

While the federation of EPM and data warehouse sources has been the primary focus, it is worth noting that two EPM cubes or two data warehouses could be federated in OBI. For many of the reasons discussed previously, data synchronization processes will have to be in place to enable this federation.  The previous solutions for maintaining metadata synchronization may be able to be adapted to enable this federation.

The federation of EPM and data warehouse sources allows an enterprise to create a more tightly integrated analytical solution. This tight integration allows users to transverse the organization’s data, gain insight, and answer business essential questions at the speed of thought.  As demonstrated, mismanaging hierarchical structures can result in an analytical solution that produces unexpected results that can harm user confidence.  Enterprise solutions often need enterprise approaches to governance; therefore, it is often imperative to understand and address shortcomings in hierarchical structure management.  Ranzal has a deep knowledge of EPM, DRM, and OBIEE, and how these systems can be implemented to tightly work together to address an organization’s analytical and reporting needs.

Using Data Visualization and Usability to enhance end user reporting – Part 4: Tying it all together

Now that the foundations have been set in my last three posts, in this final post I’ll share how we can create reports, leveraging:

• Standard definitions and metrics
• The understanding of how users  will consume data and interact with the system

To effectively create reports, make sure to follow these key best practices:

1. Reduce the data presented by focusing on the important information. For example, rather than showing two lines for revenue actuals and revenue budget, try showing one for the difference. Users can identify trends much more quickly when there are fewer objects to focus on.

2. Concentrate on important data and consolidate it into chunks. If you have two charts, use the same color for revenue on both of them. This makes it easier to interpret and see trends between them

3. Remove non-data items, especially the images, unnecessary lines and graphics. This helps the user focus on the actual data, so they can see trends and information rather than clutter.

Here is an example of two reports with the same data. The first provides a table with various colors, bold fonts and line. The second report highlights the important areas/regions. Your eyes are immediately drawn to those areas needing attention. Table two allows the user to draw accurate conclusions more effectively and in a much shorter timeframe.

These are some general practices which can be applied in most cases and will give users a much more positive experience with your reporting system. If you need help making sense of your reporting requirements, creating a coherent reporting strategy or implementing enterprise reporting, please contact us at info@ranzal.com.