EPM Cloud - Journals and Data Management - Part 2

In the previous part I provided an overview of Journals in FCCS and went through in detail the workflow process. In this post I am going to start to look at the options for loading journals and begin with the import functionality in the simplified interface, this should provide a good insight before moving on to the different load methods available in Data Management.

To load a journal through the simplified interface only requires the scenario, year and period to be set; then import can be selected from the action menu.

In order to import a journal, you will first need to create a journal file which needs to follow a specified format. I found the documentation around the format for the journal file to be slightly confusing and to get a better understanding it helps by exporting an existing journal.

I am going to use the same journal I provided as an example in the previous post.

An export can be initiated from the action menu, the journals you want to export can be filtered from the job details page.

The journal file is generated by default with a “jlf” extension.

Opening the file provides a clearer picture to the required format.

The file is split into sections and a line starting with an exclamation point (!) indicates the beginning of a new section in the journal file.

The “VERSION” section is optional so can be ignored, I found that it only ever exported a version of 1 when apparently it should be the version of FCCS, for example 18.12

 “GROUP” defines a group. It is possible to create new groups and have multiple groups in a journal file which I will cover shortly. Including this section is optional.

 “DIMENSION_ORDER” is the dimension order of the line items in the journal.

 “POV” is the combination of scenario, year and period.

 “JOURNAL” defines the label of the journal, the group, the status and the type.

 The status can be (W)orking, (S)ubmitted, (A)pproved, (P)osted

 The status can be any of the above when exporting but for importing journals there are strict rules.

 “If the Journal Workflow is enabled, you can only import Working journals. If the Journal Workflow is disabled, you can import Working and Posted journals.”

 The type can be (R)egular or (A)uto-reversing. If this is not included in the file the default will be regular.

 “DESC” assigns a description and the remaining rows in the file are the line items of the journal in the specified dimension order, these include (C)redit and (D)ebit.

 It is possible to use either member names or aliases in the file.

 The file does not need to include the blank lines to successfully import.

 The following table highlights the different actions that occur when importing a journal:

As I mentioned it is possible to create new groups when importing a journal file though only an administrator can do this.

To create new groups, include the “GROUP” section name in the file.

If I import the file as an administrator, the groups are created.

If I try the same process with a non-administrator, the import job is submitted.

Personally, I find this an annoyance when importing journals, if there are any problems with importing the journal you will not know until you go to the jobs section.

Selecting the “Import Journal” job will display the error which confirms a non-administrator cannot create groups.

If you want to create multiple journals and assign them to different journal groups, then this can easily be done in the file.

Each journal is split by the “JOURNAL” section. Once this file has been imported it will create two new journals assigned to different groups and are against the POV specified in the file.

Now let’s try and break some of the rules for journals to see what happens. You can only import journals with a working or posted (workflow not enabled) status.

The above journal file has a status of submitted, if this file is imported and the job status checked, it shows as an error.

The import failed as expected because only working and posted journals are allowed. This is a standard error message, if workflow is enabled it will still show the same error even though posted status is not allowed.

Now to try and import a file that contains a member that the user importing the journal does not have write access to.

That validates the rule “Any user with the User role and above can import journals, but only if they have write access to all the dimension members in the journal.”

How about posting a journal to an unopened period?

The status is set to “P” for Post and no journal workflow is enabled.

An error is generated stating the journal cannot be posted because the period is not open. The journal is still created but with a working status.

Now it is highly unlikely users would want to create journals in a text editor, Excel is probably going to be the preferred choice.

You could create an Excel-based journal template which includes drop-down lists, validations and provides a much more user-friendly experience.

You can then use formulas to create correctly formatted lines for the journal file.

These rows can then be copied to a file ready to be imported.

You could get a little more sophisticated by adding some VBA code to generate the file automatically.

Once the file has been created, it can be imported in FCCS.

So what are the other options for importing journals? Well this leads me on to Data Management, and in the next part I will go through the available methods in detail.

EPM Cloud - Journals and Data Management - Part 1

I wanted to put together a post covering the different methods available in Data Management for loading journals to FCCS, but before diving in at the deep end I thought it would be worthwhile having a quick look at the functionality in FCCS. In the end I want to compare that the rules surrounding journals are the same in FCCS as they are in Data Management and hybrid FDMEE.

Please note the intention of this post is not to provide a detailed analysis of a journal process or be a step by step guide, it is really to clear up some of the questions I had when I first looked at the journal functionality.

The documentation covers a basic introduction to journals in FCCS:

“During a financial period, you may need to adjust data after it is entered or loaded into base-level entities. Journals provide an audit trail of changes made in the application and indicate which users made adjustments and which accounts, entities, and time periods are affected.

The journal tasks that you can perform depend on the role assigned to you by the administrator. If a journal task is not available due to security, or the state of the data or journal status, it is either disabled or displayed with a message that you do not have the rights to perform the action.”

If you are looking for an overview of journals in FCCS then there is a “Getting Started with Journals” video link available in the documentation.

The journal functionality is only available if it has been enabled by an administrator, this can be during application creation or through the enable features option in application configuration.

During application create there is an option to enable journal adjustments, this can be with or without the workflow process.

If you are not sure whether you want to include the journal functionality then the application can be created without it enabled, this can be then changed through configuration and the “Enable Features” option.

If journal adjustments have not been enabled there will be the option to enable them and whether to include workflow.

After journal adjustments have been enabled they cannot be disabled, the workflow can be enabled at a later stage.

Once workflow has been enabled it doesn’t look like it can be disabled so be sure on your requirements before enabling.

I am going to go down the route of having workflow enabled, this is because I found the documentation slightly misleading and I am going to provide my experience on how the functionality operates. As this is EPM Cloud everything is subject to change, so the way it functions today may not be the same in the future.

The basis of the workflow process is to create a journal, submit, approve and then post.

There are several rules that apply to journals and to the workflow process, these are a selection of the main points:

• They must be balanced, it is possible to create unbalanced journals but not post them.
• If workflow is enabled, unbalanced journals cannot be submitted for approval.
• They are not written to the database (Essbase) until they are posted.
• A period must be open before you can post a journal. 
• If there are approved journals you cannot close the period. If there are journals that have been created or submitted a warning will be displayed, but the period can still be closed.
• Users with the “Viewer” role can only view posted journals which will also depend on access permissions.
• Users with the “User” role or higher can view all journals depending on access permissions.
• Users who have write access to the POV can create a journal. They can also edit and delete journals and perform various actions depending on the stage in the workflow.

I will shortly try and address some of the above points and cover off other rules around the workflow process.

Journals are created against the consolidation dimension member “Entity Input”, “View” member “Periodic” and currency member “Entity Currency”, they are managed by a combination of scenario, year and period.

Journals are also created against the seeded “Data Source” dimension member “FCCS_Journal Input”.

Let us start out with a journal that has already been created, once a journal has been created it has the status of “Working”.

If journals are enabled without workflow then after creating a journal you can either “Post” or “Unpost”.

After creating a journal, the following options are available as part of the workflow process.

If we access the manage journals page with a “View” user against the same POV and the user has read access permissions to the “Actual” member.

No journals are displayed, this is because “View” users can only view posted journals.

If the journal had a status of “Posted” then it would be displayed.

If the user does not have read access to any of the members that have security enabled, they will not be able to open the journal.

Now let us go through the journal workflow process with a user that has the “User” role assigned. The user has write access to the security enabled dimensions except for read on an entity member.

Please note that you would usually apply access permissions at group level rather than user, this is just for demo purposes.

If the user tries to create a new journal:

Then selects the entity member they have read access to they will receive an error message.

The security was then updated so the user has write access to the member and the journal created.

The journal has been created unbalanced to show what happens when it is submitted for approval. It is possible to scan the journal from the actions menu to verify it is valid.

Once created, the status of the journal will be “Working”.

This is really where the workflow process kicks in. The documentation seems to indicate that email notification can be enabled as part of the approvals process. I think it is a bit misleading because from my testing I found that the approvals process is entirely separate, and the journal email notifications are enabled by default and cannot be disabled. This is just my experience, and this is something that may change in future releases.

The user then submits the journal for approval.

As the journal is not balanced an error is generated and cannot be submitted.

The journal was updated to be balanced and submitted.

The status of the journal is now “Submitted” and can be viewed but not edited.

An email notification will be sent to any users that have write access to the parent entities in the journal, by default this includes administrators.

To verify that the approver must have write access to the parent entity, I set the approver with read access.

Before trying to approve the journal, I set the period to be closed.

The approver then tried to approve the journal.

As expected an error message is displayed informing the period is not open and the journal could not be approved.

At this point the journal could also be rejected which would set it back to “Working” status.

The period was then opened.

There is another journal option which is controlled by administrators and can restrict who can approve journals.

The option was disabled and the approver who has the “User” role tried to approve the journal. The following error message was displayed, and the journal could not be approved.

The option was enabled again, the user tried to approve the journal, but the same error was displayed.

This goes back to the approver only having read access to the parent entity member.

Write access was granted and this time the journal was accepted for approval.

Email notification is sent to the user that submitted the journal.

At this point, only an administrator or the user that submitted the journal can post it.

If any other user with the correct access permissions tries to post the journal they will receive an error message.

The user who submitted the journal then posts the journal and the status is updated. The data is pushed to the Essbase database.

Once a journal has been posted, it can only be unposted by either an administrator or the user that posted it.

If any other user tries to “Unpost” they will receive the following error message.

If a journal is unposted it returns to “Approved” status.

In terms of email notifications this is where I found it to be confusing, hopefully I have understood correctly because it is not currently documented. If a journal is unposted, the user who unposted the journal and any users who have write access to the parent entities in the journal including administrators will receive an email. Though the same rule applies that only the administrator or the user that submitted the journal can post it again.

Anyway, another thing to note is that if workflow is enabled you must go through each stage in the process. For example, if a journal has a submitted status, the next step would be for it to be approved. If you try and miss the approval step and go straight to post, you will receive an error message.

If you try and delete members where they are referenced in a journal you will receive a deletion error message.

Going back to the earlier statement that it is not until the journal is posted that it is written to the Essbase database.

If I perform a retrieve against the journal POV before it is posted then you will see that no data exists in Essbase.

This does not change through the workflow process until the journal is posted.

If the journal is unposted then the data Is set to zero.

I can only assume that the data is set to zero and not to missing to stop any issues with calculations being incorrect.

So that completes this first part and in the next part I will look at the different options for loading journals which includes Data Management.