SAP BPC systems receive data from a range of external sources. The planning and configuration necessary to insert this data into the system is an important aspect of an implementation. The integrity of data is vital, and invalid data can affect processes within SAP BPC, such as calculations and reporting. Safeguards should be in place to prevent the entry of invalid data.
The main methods employed to enter external data into a SAP BPC system are:
- Interfaces to source systems such as an ERP or SAP BW system.
- Flat file uploads.
- Input schedules for manual data entry.
The first two sources – interfaces and flat files – are tested, configured, and operated by experienced administrators and consultants who understand the data structures of source systems and SAP BPC, so these usually do not create problems. The greatest risk is with data entered through input schedules by users who, whilst knowledgeable about the commercial functions of their organisations, are not experts in the data structures of financial information systems like SAP BPC. The techniques available to ensure the integrity of data entered via input schedules are the focus of this article.
The entry of incorrect data into SAP BPC can have a wide range of consequences including:
- Data that is required for calculation scripts, for example in driver-based forecasting, needs to be posted to the correct dimension intersections to be included in the scope of calculations. Incorrect data entry can cause calculations to not perform as designed.
- Invalid data in the system may not be immediately visible and can cause unexpected results when consolidated upward into reporting.
- Users who enter data to incorrect dimension members can find themselves unable to locate it later and report that their data has been “lost”. This causes administrator effort and can result in loss of confidence in the system by users.
- Invalid data takes space in the database and can affect performance.
There are a range of techniques and functionalities within the BPC Excel Add-in and Administrator that can be used to prevent the entry of invalid data and maximise the usability of input schedules.
Input Schedule Structure
Input schedules can be structured to ensure that data can only be posted to the correct locations. Commonly used techniques include:
- Fixing Dimension Members - Dimension members that do not need to change should be fixed (without users having the option to make selections) in the page, row, and column axes. This may include dimensions in the page axis applying to the entire schedule, for example the Datasource and Measures dimensions. Dimensions with multiple members for entry, such as the Account-type dimension, may also be fixed in the row axis. This prevents the possibility of postings to incorrect members of these dimensions.
- Restricting Dimension Selections - Dimensions that are selectable by users should only be those that need to be changed. The use of EPMContextMember and EPMSelectMember formulas in cells within an input schedule are a good technique to highlight to users that these are the dimensions that need to be selected. These formulas can have filter criteria using dimension properties to limit potential selections for users. This can be a useful technique to prevent entry to incorrect dimension members. An example that could apply to a forecast input schedule would be to create two properties in the Category-type dimension to identify forecast members and active members. A Category selection formula could filter on both of these properties.
- Automating Dimension Member Selections – Some dimension selections that are variable can be automated using dimension properties in a manually selected dimension. An example is in a model with Cost Centre and Legal Entity dimension. If a Cost Centre member is within a single Legal Entity member, a property in the Cost Centre dimension member may be used to specify the relevant Legal Entity dimension member. This property can be used to automate the Legal Entity member based on a manually selected Cost Centre member. This both ensures the integrity of the Cost Centre/Legal Entity combination and improves usability by having one less user selection.
- Formatting – Dynamic formatting functionality can be used for both usability and restricting data entry. Imputable cells are commonly formatted with a white background. Cells that should not have data input can be formatted in a way that indicates this such as a grey pattern, bold font and/or borders. Locking of cells that should not have data input can also be applied dynamically.
SAP BPC Administration and Structure
The Administration and Structure of the SAP BPC system can be used to direct and control data entry. The creation of reports and input schedules in the Excel Add-In is usually towards the end of an implementation project. Requirements for the Excel Add-In should be included in the planning and design early in the project.
This section covers some features and areas of structure that can be used in conjunction with the Excel Add-In for input schedules:
- Dimension Structure – Dimensions can be structured to automate the layout of an input schedule and to restrict data that can be entered into it. Hierarchies can be used for expansions in the row and column axes. Properties also have applications such as axis expansions, dimension selections (as described above), and dynamic formatting.
- Business Process Flows – BPFs are a key feature of SAP BPC that can be used to control and monitor business processes. An example of their use is to direct users to enter and complete input schedules. It is possible to open the schedule via BPC with dimension members selected automatically.
- Dimension Security – Member access profiles determine the members of secured dimensions that users have read only or read/write access to. A common technique is to restrict users’ read/write access to members they have reason to post data to. The Entity-type dimension is typically secured.
- Work Status – This functionality allows data to be tracked, approved, and locked. Each status can be configured as required including determining which users can modify data at each level.
Conclusion
The integrity of data in an SAP BPC system is very important, and the consequences of invalid data entering and accumulating within models can be substantial. The manual entry of data via input schedules is perhaps the highest risk data source.
A well-structured and administered SAP BPC system can be built to make it difficult or impossible for users to post data to incorrect locations. The first step towards doing so in an SAP BPC implementation is to consider the use of the Excel Add-In in the system configuration including, but not limited to, the parts that the Excel Add-In relies on. The greatest contribution to this is made by designing input schedules in the Excel Add-In carefully so that they have good usability and only enable data to be posted to the correct location.
SAP BPC Consultants and Administrators who use their expertise to design a system well, enable users who are not information system experts to focus on their commercial expertise in forecasting and other processes that the system is used for.
Learn more from Column5's EPM experts with our EPM Summit Annual Pass!
Related Blogs
