We’ve Got You Covered: Producing Flat-File Extracts out of Cloud Data Management

As an EPM Administrator or Implementation Specialist, we have all had that moment when someone comes to us and asks for the dreaded extract out of an Enterprise Performance Management (EPM) application.  Depending on the system combination (Hyperion Financial Management (HFM), Planning, etc.) and the file layout specifications, this can be tricky.  Layer in the concept of a Cloud application, and things have now gotten real!

In an on-premise installation of Financial Data Quality Management Enterprise Edition (FDMEE), we could use scripting within a “custom application” to build an end-to-end approach for delivering a flat-file extract for third party consumption.  With the release of version 19.06, Oracle has further enhanced this concept and brought it to Cloud Data Management (CDM). The Cloud application now provides the ability to design and produce a text file for downstream consumption in Oracle Cloud products (PCMCS, EPBCS, FCCS).  WHOA!

Wayne Paffhausen - Weve Got You Covered - 7-26-19 Image 1

The Setup

I recently busted out the functionality and this is what I have discovered:  It’s crazy simple!

  1. Create a text file with your defined headers

Wayne Paffhausen - Weve Got You Covered - 7-26-19 Image 2

  1. Create a target application and set your settings

Wayne Paffhausen - Weve Got You Covered - 7-26-19 Image 3

3.  Create an import format

Wayne Paffhausen - Weve Got You Covered - 7-26-19 Image 4

  1. Create a Location & DLR

Wayne Paffhausen - Weve Got You Covered - 7-26-19 Image 5

  1. Create the desired Maps
  2. Run the Data Load Rule

Wayne Paffhausen - Weve Got You Covered - 7-26-19 Image 6

It is that simple!  CDM produces a file that looks similar to this:

Wayne Paffhausen - Weve Got You Covered - 7-26-19 Image 7

It can PIVOT!?

As crazy as it sounds, it can even pivot the data!  I find this extremely helpful as it is a common request to have twelve months of data in column format.  CDM leverages the PIVOT command of the database for this process and creates the pivot file with ease and efficiency.

Wayne Paffhausen - Weve Got You Covered - 7-26-19 Image 8

What does it do behind the scenes?

Behind the scenes, CDM appears to run a standard import and validation of the data, but it leverages a different set of workflow instructions.  The process does not consider unmapped items which are left as blank fields in the output.

Wayne Paffhausen - Weve Got You Covered - 7-26-19 Image 9

It also does not permanently store any data in the CDM repository unless you want it to.  The documentation can be easily misinterpreted because you will see “fish,” but no data is stored (more on this later).  A quick review of the process details log shows that all the work is done in the “tDataSeg_T” table.  This is the “temporary working” table of Data Management, and it is cleared after/before each new run for optimal performance.  Since the data is never moved out of this table, it is never retained.  Even the export process that produces the output file pulls from the temporary table.

Wayne Paffhausen - Weve Got You Covered - 7-26-19 Image 10

A review of the Documentation shows that there are 3 main supported types for processing:

  • Simple (the option selected here) – Does all the work in tDataSeg_T and does not retain any data or archive maps. Although, be warned, it does retain the process details and “fish” status which can look a bit strange.

Wayne Paffhausen - Weve Got You Covered - 7-26-19 Image 11

  • Full No Archive – Data is retained in tDataSeg only after the import step. Data is deleted after the export.
  • Full – All data is retained. Full process flows are supported (check rules, drill down, etc.).

That’s great, but my file is stuck in the Cloud!

Not really…let’s think this through in a workflow process.  When using Cloud applications, we might have an automation wrapper or a larger workflow process.  If not, we are using the general user interface (GUI), and we can access the file in two ways:

  1. Data File Explorer
  2. Process Details -> Download File option

Wayne Paffhausen - Weve Got You Covered - 7-26-19 Image 12

If we are using a more automated approach, we just need to include additional steps to:

  1. Monitor the data load rule for completion
  2. Verify the status of that completion (do not proceed forward if it failed; do something different)
  3. Confirm that the file was created
  4. Download the file that was created
  5. Continue the automated routine

In Summary…

It is simple to produce a file using Data Management in the EPM Cloud products.  This is a welcomed change that further enhances the product lines by delivering on client needs.  This allows us to build a simplified Cloud solution that was previously only on-premise.

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

Alithya Leverages the Power of Oracle Hyperion FDMEE

One of the biggest challenges of every organization nowadays is to provide reliable data for a clear business outlook. This essential activity is more critical than ever now that the solutions for hosting data are increasingly varied, with multiple scenarios involving on-site hosting, Cloud, and hybrid solutions. However, there are solutions that allow companies to efficiently and seamlessly navigate amongst the different hosting solutions. Alithya Group (NASDAQ: ALYA, TSX: ALYA) (“Alithya”) is well positioned to advise its clients on this topic.

Efficient management of data requires solid know-how.

As companies attempt to develop long-term guidance in this area, Alithya ensures that its clients’ data hosted in different environments continue to be used effectively. Alithya’s Data Governance and Integration practice includes specialists in Data Integration to help free up resources leveraging FDMEE for data validation and to maximize FDMEE with its offering for financial data application review.

Alithya’s Tony Scalese published a book providing deeper understanding of FDMEE.

Banking on the numerous mandates Alithya Group has been entrusted by its clients as a market-leading provider of Oracle Enterprise Performance Management Platform solutions, the company leverages the power of Oracle Hyperion Financial Data Quality Management, Enterprise Edition (FDMEE) to help organizations enhance the quality of internal controls and reporting processes. The extensive Alithya team specializing in these FDMEE solutions has among its ranks a widely recognized expert in the market, Tony Scalese, VP of Technology at Alithya and Oracle ACE, who published The Definitive Guide to Oracle FDMEE [Second Edition], in May 2019.

Connecting current on-premise and future Cloud solutions.

“As thought leaders, we are committed to providing essential resources to help clients enhance the quality of internal controls and reporting processes,” stated Chris Churchill, Senior Vice President at Alithya. “Our Data Governance and Integration practice aligns offerings with best practices and includes a team of dedicated experts as well as some of the most comprehensive resources in the industry.”

Sharing real-world FDMEE deployment strategies.

It is the great interest of Tony Scalese for the integration of data and the sharing of his great knowledge with a maximum of interested parties that led him to publish books on Oracle FDMEE. After a first edition that was very successful in 2016, he just launched the second edition of The Definitive Guide to Oracle FDMEE. Since many organizations are now considering or have begun migrating to the Cloud, the book provides a deeper understanding of FDMEE by informing readers about such topics as batch automation, Cloud & hybrid integration, and data synchronization between EPM products.

“FDMEE can integrate not only with on-premise applications, but also Oracle EPM Software as a Service (SaaS) Cloud Service offering,” says Tony. “It provides the foundation for Cloud Data Management and Integrations which are embedded in each of the EPM Cloud Services.  A deep understanding of FDMEE ensures that integrations built on-premise or in the Cloud function well and stand the test of time.”

When FDM Isn’t an Option…Using Essbase to Map Data

lots-of-arrowsThere are times when you do not have an option of using FDM to do large data mapping exercises prior to loading data into Essbase. There are many techniques for handling large amounts of data mappings in Essbase, I have used the technique oultined here several times for large mappings and it continues to exceed my expectations from a performance and repeatability perspective.

Typically, without FDM or some other ETL process, we would simply use an Essbase load rule to do a “mapping” or a replace. However, there are those times when you need to do a mapping based on multiple members. For example, if account = x and cost center = y then change account to z.

Let’s first start with the dimensionality that is in play based on the example below: Time, Scenario, Type, NOU, Account, Total Hospital, and Charge Code

Dimension Type Members in
Dimension
Members
Stored
Time Dense 395 380
Scenario Dense 13 6
Type Sparse 4 4
NOU Sparse 25 18
Account Sparse 888 454
Total Hospital Sparse 5523 2103
Charge Code Sparse 39385 39385

You then need to be able to identify the logic of where the mapping takes place.  I will want to keep the mapping data segregated from all other data so I will load this to a Mapping scenario (Act_Map).  I load a value of ‘1’ to the appropriate intersection, always level0.  Since the mapping applies to all Period detail I will load to a BegBalance member.  The client will then update this mapping file from a go forward basis based on new mapping combinations.

Here is a sample of what the mapping file looks like that gets loaded into Essbase:
NOU STATUS Revised DEPT ACCT # CDM Data
SLJ   IP            2CC      2         0012013         1
SLJ   IP            2CC      2         0012021         1
SLJ   IP            2CC      2         0012062         1

Here is what it looks like when you do a retrieve.  So for 4410CC->2600427->IP->67->SVM there is a value of 1 and for 4410CC->2600435->IP->67->SVM as well.

Essbase Mapping

The next step in the process is to load the actual data that ultimately needs to be mapped. I will load this data based on the detail and dimensionality I have, again at level0.  In my experience, the data is missing a level of detail (GL account for project based planning, Unit/Stat for charge master detail, etc.). So this data gets loaded to specific “No_Dimension” member via a load rule or a generic member. Again, I load this data to a separate scenario as well (Act_Load).

In the example below you will see I am loading Account detail (67 & 68 in the above screenshot) to the Stat_Load member. The data comes across missing the account detail.

essbase mapping

The final step is to calculate the Actuals scenario based on the two scenarios above. You will see that after we run the calculation, Current Yr Actuals is calculated correctly in that the data resides where it should reside.

essbase mapping

Keeping all the data segregated in different scenarios allows you to easily clear data should anything be wrong with one of the loads, thereby keeping the other datasets intact. This process runs on the entire year in less than 2 minutes and not only performs the calculation but also does an aggregation for the Current Yr Actuals.

Hyperion Financial Management: Zero View and Default View Settings

One of the most common areas of confusion in Hyperion Financial Management (HFM) has been the Scenario application setting for the Default View and Zero View.  Peering back into the history of Hyperion Solutions, you will find this setting has existed in all the Consolidation products, such as Hyperion Enterprise’s “missing values” setting under Categories.  They are not new features and are a fundamental to HFM.

ZeroViewForAdj and ZeroViewForNonadj

There are two ZeroView settings in HFM.  The ZeroViewForNonadj is a setting which applies to the <Entity Currency> Value dimension.   The second is the ZeroViewForAdj which applies a setting to the <Entity Curr Adjs> Value dimension member.

The derived data that results from the ZeroView settings commonly appears in HFM as a slightly grey numeric value, as opposed to a black display found with standard data.

Before we explain what it does, let’s talk about why we need it.

Part of Hyperion Financial Management’s financial intelligence is its Account Types.  These types help to manage data flowing through the chart of accounts and support variance reporting.

  • Revenue
  • Expense
  • Asset
  • Liability
  • Balance
  • Flow
  • GroupLabel
  • Currency
  • BalanceRecurring

The Revenue, Expense and Flow Account types are referred to as “flow” accounts and tend to support Profit and Loss reporting.  As such, they are supported by the View dimension.  The View dimension allows data to be viewed, keyed or loaded as YTD or Periodic amounts.  Data entered as periodic sales of $1000 to all months would allow users to view December as Periodic $1000 or YTD $12,000.

Example 1:

The ZeroView setting informs the HFM Scenario how to handle data for a flow account when no data is loaded to a Period.  No data is not a zero; it is a blank, non-existent value.

Example 2:

If data was loaded from your source system for Jan as $1000, how should HFM calculate Feb? There are two options using the ZeroView settings which are YTD or Periodic.  The first option is the YTD setting for the ZeroViewForNonadjs attribute.

Choosing the ZeroView setting as YTD, HFM will fill the period following with a derived value as zero YTD.

Example 3 ZeroView as YTD:

Recall that Flow accounts can be viewed as Periodic or YTD, what does Feb look like as Periodic?  If there is $1000 YTD in Jan and zero YTD in Feb, there must have been a change in periodic activity.  Therefore, the Feb Periodic value would be $-1000 to arrive at YTD zero.

Example 4 ZeroView as YTD:

The second option is to set the ZeroView settings as Periodic.  This setting addresses the same properties of flow type accounts.  In this instance, the following period will have a zero applied as a Periodic zero, which is the activity.  This setting is common on the Budget and Forecast Scenarios.  The data result is the entry flows through all the periods because the YTD value is derived from the Periodic activity.

Example 5 ZeroView as Periodic:

Example 6 ZeroView as Periodic:

How does one determine which setting is correct for their application?  Typically, we analyze how the data load file is constructed.  For example, for flow accounts that may be adjusted to zero, are they included in the file?  Does a periodic version of the file supply zero items as the year-to-date negative offset? The most common setting is to set the ZeroView as YTD for Actual data loads.

A good example why we would set the ZeroView to YTD in Actual would be if data was re-classed month-to-month or a new account is used.  In the example below, a different account is used in Feb compared to the data supplied in Jan.  The ZeroView setting as YTD will automatically assume the Feb amount is to be YTD zero.

Example 7 ZeroView as YTD:

If the Scenario was set to Periodic for the ZeroView, the application would have incorrect results for Feb as 2000.  The data would flow through all the Periods, and in this case, the Sales account should be zero for Feb.  The original account would require a -1000 Periodic or “0” YTD entries to clear the value in Feb on account Sales.

Example 8 ZeroView as Periodic:

The ZeroViewForAdjs setting applies to how HFM Journal entries will function for missing data and how they affect future periods.

For Journal adjustments we see the same impact on the data within the <Entity Curr Adjs> Value member.  The YTD setting will essentially reverse the Journal in the following period by applying the YTD offset to the Journal.

Example 9 ZeroView as YTD:

In cases were a prior period is being adjusted, the YTD setting will keep the YTD values of future Period, such as Feb, constant.  As an example, if the original value were 1000 in Jan and 1000 in Feb, the Feb YTD amount would be 2000.  A journal posted to Jan would impact the Jan results, but the Feb YTD amount of 2000 would not change.  In the example above, what is adjusted is the Periodic amount from 1000 to 500.  Therefore, to see the impact on all periods, the Journal would need to be created and posted in all periods going forward.

Journal adjustments with the ZeroView setting for Journals as Periodic will allow the impact of the Journal to affect the YTD results in the future periods.  Because of this, to reverse the affect in a future period a reversing Journal entry is required.

Example 10 ZeroView as Periodic:

Therefore, when viewing data within HFM as a YTD or Periodic view, the grey offset values are the result of your Scenario settings for ZeroViewForNonadjs or ZeroViewForAdjs.  These amounts impact your YTD and Periodic results in Local and Translated amounts.

Default View

The Default view determines what View will be applied to the “<Scenario View>”.  When opening a Grid, form or report, this is the default member that will display.

Because of this, it is important when writing reports or comparing data, a specific view be chosen.  The “<Scenario View>” results will vary by the design of each Scenario.

The default view also is a concern for Rules in HFM.  This setting sets the basis of calculations.

As an example, if there was a Rule that simply called for “estimated bonus” to be a percent of “sales” after we attained our goal of $1000.00, the calculation would vary by Default View on Scenarios.

In the example, the YTD Scenario will calculate a result because it is calculating Rules off the YTD value of $1500. The Periodic Scenario does not calculate the Rule because it is driven off the Periodic value of $500.

Careful attention to Rules development and the utilization of the Dynamic Sub Routine in rules can help to minimize issues caused by differences in the Default View settings.