BPC MS 10.0 Work Status and Its SQL Tables

Purpose

The purpose of this article is to introduce configuration steps involved in setting up Work Status in BPC for Microsoft 10.0 and various SQL Server tables that underpin the Work Status configuration and its activity.

Overview

In order to be able to use Work Status in BPC 10.0, various configurations are required. The data related to these configuration settings correspond to a number of tables in SQL Server database. Knowledge of the relationship between the settings and their corresponding tables in SQL Server will help us to understand the Work Status process and troubleshoot Work Status related issues.

Please note, this Wiki is not intending to cover every single detail of Work Status but the main aspects. Whenever possible, it lists a source for obtaining more detailed information.

Work Status Set Up

The steps we are going to discuss below are not strictly sequential. Swapping different steps would still achieve the same outcome.

Defining the Work Status Code for an Environment

Work State describes the physical state of data along the budgeting cycle. For instance:

“Open” status means data can be modified;
“Submitted” means data is locked for review, and can only be modified by a manager or an owner of the data;
“Approved” would mean that data is final and no further modification can be done.

For detailed information on defining Work Status and Work Status rules, please refer to “Work Status Setup” section in BPC MS 10.0 application help on SAP Help Portal.

Work States are set up in BPC Administration Console. It is Environment Specific but not Model specific. An example of a Work States set up can be seen below:

The Work Status Codes shown above are stored in a table called dbo.tblStatusCode in SQL Server. This table can be found in the individual Environment database. An example of dbo.tblStatusCode is provided below:

Data can be modified in various ways in BPC, e.g. via

a Data Manager package
a Journal entry
manual input
comments
uploading a document

Therefore, status codes are set against these different modification methods. The values in the columns ‘DM’, ‘JRN’, ‘ManualInput’, ‘Comment’,’SupportDoc’ in the tblStatusCode table reflect the settings in the Work Status screen in the Administration Console.

Work Status Settings for a Model

Work Status dimensions allow users to determine data regions that work status will be applied to. For instance, if users wish to be able to lock data by version, time and entity, then the corresponding dimensions will be defined as Work Status dimensions. The combination of the dimensions which are defined as Work Status dimensions can be termed the ‘data region’ because the combination covers a region of data.

In order to control Work Status, i.e. being able to set and change Work Status, a control dimension needs to be set up. This dimension contains an “Owner Property”, which defines who can make changes to the data.

For validation purposes, users also need to define a validation member for non-Work Status dimensions (i.e. dimensions not set to ‘Yes’ in the Work State column). The validation member of each non-Work Status dimension together with the work status dimension data region specifies the intersection of data that the BPC system will need to check prior to submitting data. If this intersection has a value of “0” this indicates that data can be changed and work status can be set to “Submitted” for that intersection. If a data region is set to a status of “Submitted”, subsequent submissions to that exact intersection are not permitted.

When a work status dimension has multiple hierarchies, the Base Hierarchy parameter allows the user to determine the hierarchy that work status will be applied to (for more details, please refer to BPC MS 10.0 Application Help).

Below is an example of a Work Status Settings screen for a Planning model from the default EnvironmentShell environment.

Information defined in the Work Status Settings screen is stored in a table called dbo.tblLockDimension in SQL Server. This table contains information across all the models belonging to the same Environment. An example of such a table based on above information is shown here:

“0” in the WorkStatusDimension column indicates it is not a work status dimension. “1” indicates a work status dimension; and “2” means it is a control dimension. We can see that the validation member is translated into a LockMember in the table. The PrimaryFlag column specifies the dimension that is the control dimension. The ConcurrentLock column records “1” for all the work status dimensions and “0” for non-work status dimensions, regardless of the applications that the dimensions belong to.

Using Work Status

This section depicts the corresponding changes to SQL Server tables when Work Status is changed from the BPC front end.

Work Status can be changed using the EPM Add-in from this menu option: Save Data -> Change Work Status.

In the example below, Work Status for the data region ‘Germany’, ‘Actual’, ‘2013 Oct’ is changed from Unlocked to Submitted.

Once the New Work Status action is changed, the corresponding change is updated in a SQL server table called dbo.tbl$modelname$Lock table. In this example, the work status is modified in the Planning model of the EnvironmentShell environment. Therefore, the latest Work Status for the selected data region can be found in dbo.tblPlanningLock table as shown below.

StatusCode information can be found from dbo.tblStatusCode table. A Status Code of “2” represents the “Submitted” status.

After more Work Status activities, another example of dbo.tblPlanningLock table could look like the screenshot below:

Users will not be able to modify data against any data region with a Status Code of 2 or 3 in the dbo.tblPlanningLock table.

IGNORE_STATUS Function

On some occasions users might wish to post data back to the locked data region via Script Logic or Data Manager Packages. One way of achieving this is to include the “IGNORE_STATUS” function in the default logic or/and the logic scripts that can be run as part of Data Manager Package. There is also an option called “Do not check Work Status settings” when running the package that will achieve the same outcome.

Conclusion

Work Status related issues can be difficult to verify. Knowledge of the underlying tables and an appreciation of the relationships between the data will provide a good starting point for investigation.

Page tree