Has your team ever had the need to consolidate history, including journals, when moving to a new consolidation solution? How about detailed consolidated budgets that use journals? Or detailed reporting on journal activity? What about the need to move journals between development, quality, and production environments? All of this can be accomplished using BPC’s Restore Journals and Export Journals package that comes standard with BPC. Today’s discussion will outline some of the key concepts with using BPC Restore Journal and Export Journal Data Manager Packages. In the second installment, the discussion will continue with a couple of use cases explaining the different ways to use the functionality. As a primer, it’s important to review the standard BPC EPM documentation on how to use Journals and Data Manager Packages.
Two recent customers display a few uses for the Import Journal and Export Journal Data Manager Package. The first client, a large manufacturer of petroleum-based products, wanted to load existing Journals as part of a BPC 7.5 Microsoft to BPC 10 NW project, as opposed to entering BPC Journals manually using the BPC Journal module. In addition, they needed to load the BPC Journals in their quality and production environments. The second customer, a large telecom, had several needs including having the ability to fully consolidate a seeded forecast with journal entries, build custom BPC journal reports, and consolidate history.
So let’s take a look at how the BPC Import and Export Data Manager Packages can help accomplish some of the above scenarios. For this discussion, the Restore Journal package name has been changed to Import Journal.
All steps were performed on SAP BPC 10 NW on HANA using EPM Client SP 15 for Excel.
Initial Setup
- Add the Export Journal and Restore Journal process chains in Data Manager from Organize Package List>Add Package>Other Functions.
- Ensure that JRN is an allowed extension in the Environment parameters UJ0_IMG_02>ALLOW_EXTENSIONS=ALL.
- Create a local text file with the extension .JRN with at least 1 character in the file.
- Journals must exist in the model in order to export. If one doesn’t exist, then create and post at least one journal.
- Destination members must be flagged “Y” if the dimension has the EnableJRN property.
Exporting journals using the standard Data Manager package is similar to running a normal data package except that there isn’t a transformation or conversion file. All available fields and journals are exported from the journal tables. This includes user defined fields added from within the BPC admin consol. It’s not possible to limit the fields or journals for export. Exported journal files also serve as a source file format for the Restore Journal Data Manager package for the current model.
Steps to export journals to flat file
1) Create an export .JRN template file for upload to BPC. This file must have at least 1 character but doesn’t need journal table headers. The Export Journal process will generate the headers at run time. This .txt file is simply a source file to which the journal package writes its results.
2) Now upload the .JRN file to BPC using Data Manager or UJFS.
If you try to upload an empty file the upload will succeed but the file will NOT be saved to the web folders.
3) If journals don’t exist for a given BPC Model, then create at least one journal entry.
4) The next step is to run the Export Journal Package from Data Manager.
Browse to the uploaded .JRN file and hit Next>Finish. Open View Status to see the results.
The export log doesn’t include details like a normal import/export load.
5) Download the .JRN file from Data Manager or UJFS and view the results in a text editor or Excel.
Standard Field Headers
APPSET_ID
APPL_ID
JRN_TMPL_ID
JRN_ID
DESCRIPTION
USERC
DATEMODIFIED
USERP
DATEPOSTED
STATUS
IDJ_AUTO_REV
POSTED
IDJ_OLD
IDJ_GROUP
AUTO_REVERSED
BALANCED
REOPEN
EDITABLE
VARIABLET
<Dimension List>
ROW_NUM
DEBIT
CREDIT
REMARK
Importing Journals
Importing journals is useful for restoring journals after a Journal template has been rebuilt, for batch processing a large number of one time journals, or seeding journals for a budget or plan cycle. The standard Restore Journal package has been renamed Import Journal in this Model.As mentioned above, the importing of journal process starts with a journal export file from the current Model. This provides the correct fields and file format.
Key Concepts when Preparing an Import File
- Consider using a text editor to update JRN files. Text delimiters, such as quotes being placed around rows, may result when saving text files out of Excel.
- As always, if working in Excel while logged into BPC and member recognition is ‘on’, then IDs can be recognized and re-translated into epmolap members.
- DM packages will fail if the flat file content is incorrect in terms of delimiter, field set, and text delimiters.
- Unique Journals are not required as the system always generates a new ID at run time.
- If an unchanged journal is imported by accident, the system will accept it and issue a new Journal ID.
- Once the import file is ready upload the file via Data Manager, run the Import Journal Data Manager package selecting the import file when prompted and Run.
In the next installment we’ll look at a couple of real world examples of how to use the journal packages.
Read Part 2!
Related Articles:
Webcast: The State of EPM - Define Your Strategy with Business Intelligence (BI), Predictive Analytics, and More!
A primary responsibility for today’s CFOs is to define a company’s Enterprise Performance Management strategy. In order to do this, they will need to understand what is possible with EPM, and how to deliver bottom line value they covet with targeted investments that deliver process improvements in this area. Topics discussed will be how to leverage cloud/SaaS, in-memory databases, predictive analytics, business intelligence, in a comprehensive way to produce value for organizations in any industry and size.
