User Experience Level: Intermediate/Advanced
There are several methods used to export data out of a Planning and Budgeting Cloud Service (PBCS) application. These include the “DATAEXPORT” calculation command, the “Export Data to File” functionality, and the “Write-Back to Flat File” process through Data Management. We’ll be discussing this later method in the context of a PBCS application, “PLNtstc1”.
Watch the recorded demo!
Read the full blog post!
To begin working with data write-back, first access Data Management. Within “Setup”, configure “System Settings”, “Application Settings”, and “User Settings” however you’d like, or feel free to leave the defaults as they are. These “Configure” settings will not have an impact on the data write-back process.
Within “Register”, the section “Source System” can be skipped, as the PBCS/Planning app is the source used for pushing the data to the target. In the case of data write-back, the target is more formally referred to as a “Custom Target Application”, which allows for the data to be exported to the flat file (see references below for more details).
Now, let us look at the “Target Application” section. Here, we will begin the initial preparations necessary for running a data write-back process. Under “Target Application”, “Add” the source PBCS Planning application if it is not already present. Note that PBCS only allows for one Planning app per environment. Ensure the dimensions are defined for the plan type that has the data you want to transfer. For instance, in this case consider the source “PLNtstc1”:
In this example, you can see most dimensions are defined under “Data Table Column Name” except “Employee” (as that dimension is part of another plan type that has data we do not want to transfer), as well as “Period”, “Scenario”, and “Year” (as those dimensions are defined later on in the process).
Next, we need to create the “Custom Target Application” that will be used to process the Planning application data and allow for export to a flat file. Click “Add” under “Target Application Summary”, and select “Type” to be “Custom Application”. Then, click “OK”.
Define the “Dimension Details” in a similar way that was done for the source Planning application, but be sure to only add the dimensions that require mapping from the Source Planning application and are not defined through “Period” and “Category” Mapping. For example, consider “Test_CustomApp3”:
In “Application Options”, define parameters for the export file, for example:
Now that the Custom Target Application has been established, we can move on to the “Integration Setup” sections, beginning with the “Import Format”. Click “Add” to add a new Import Format that has “Source Type” as EPM (Planning application) and “Target Type” as EPM (Custom Target Application). Then, map the dimensionality as needed. For example:
With the Import Format built, we can move on to the “Location” section and define a Location for grouping our Import Format, Source Application, and Target Application. Click “Add” to add a new Location and populate the fields as needed, referencing the “Import Format” created in the previous step. For example:
Next, we’ll move on to defining the time frame for the data that needs to be exported. Go to “Period Mapping” and then “Global Mapping”. In our example, “Global Mapping” has already been populated with multiple time frames that can be utilized in “Application Mapping” and “Source Mapping”. If these mappings are not already defined in your environment, you’ll have to “Add” them first before they can be used in “Application” and/or “Source Mapping”.
Consider this example, where we take advantage of “Source Mapping” (allowing for EPM à EPM mapping) in this testing, isolating the time frame “Nov-15”:
In addition to defining the time frame(s), we also have to define the Scenario for the data we want to export. This is done through “Category Mapping”. In this section, “Global Mapping” contains Scenarios that have been mapped and can be used in “Application Mapping”. As is the case with “Period Mapping”, if these mappings are not already defined in your environment, you’ll have to “Add” them before they can be used in “Application Mapping”.
Here, we take advantage of “Application Mapping” to isolate mapping for “Actual”:
At this point, we’re all done with the sections used in “Setup”! We can now move on to “Workflow”.
In “Workflow”, first access “Data Load Mapping” (under “Data Load”). Establish value/member mappings between your source (PBCS/Planning) and target (Custom) applications.
*Note that the “Target Value” column defines the values that will be printed on the export file. That said, it is possibly to manipulate how you want the output to look to some extent. For instance, if you are using a prefix of “D” for the Department dimension members in your Planning application, and you wanted to omit that “D” for member values on the exported flat file, you could do so in “Like” mapping for Department (Source Value = “D*”, Target Value = “*”)
In this example, we use “Like” mapping for each dimension (basically “one-to-one” mapping, to export members as they exist in the Planning app):
Once the dimension mappings are defined, we move on to the “Data Load Rule” section. Build a Data Load Rule for the export (for your designated Location). Under “Source Parameters” at the bottom – set “Extract Dynamic Calculated Data” to “Yes” to export data for parent Account members.
In “Source Filters” (in the “Source Options” tab) – you must isolate a slice of the database for export by defining filter conditions (similar case with the Data Sync process). Note that the write-back process will not work unless you input member(s) for each of the source dimensions that contain data which you want to export. Note also that you can use member functions (e.g., “@Descendants”, etc.)
For example, in our case:
You’re all set! Click “Execute” to run the rule at any time.
Check “Process Details” to monitor the process steps and retrieve the output (data export file) under the “Output File” column on the far-right.
Click “Download” to open the export file, in our case:
Data write-back to a flat file is an efficient process used for exporting data out of a PBCS Planning application. The setup process done in Data Management is similar to that done for loading data. As such, the Data Load Rule associated with data write-back can be automated (via EPMAutomate) to run over a specific frequency. Once the file is processed, it can be opened on a local machine or server, archived, or processed for import into another system.
Blog post by Noah Neal of Key Performance Ideas
Custom Target Application – Detail : “You can create the file-based application using the Custom Target Application feature…A custom target application enables you to load data from Planning, and then extract the data into a flat file instead of loading it to a Planning application”