When companies integrate their financial data using SAP and BW, they must develop processes to aid in the reconciliation of data. We’ll describe two reconciling scenarios to show you how BW can simplify the daily reconciliation of multiple financial journals. The scenarios use the generic data model structure and data mining tools to identify entries that need to be adjusted. While reconciliation is required in multiple scenarios, we’ll focus on using the data mining toolset in the following two situations:

Scenario 1: A company needs to reconcile its balance sheet with its income statement, as the current year earnings line on a balance sheet is the net income for the fiscal year to date. We’ll describe a technique for identifying any variances that occur between the income statement and balance sheet. You then can correct any variances.

Scenario 2: A company needs to reconcile its non-SAP and SAP financial information when the raw data is coming from each of its source systems, with only higher-level journal entries being posted to SAP from these ancillary source systems. We’ll explain a technique for identifying and correcting these variances within BW so that financial information matches the financial book of record. The corrective technique is most appropriate for open periods when timing issues result from feeds from multiple systems.

Functionality Overview

The goal of this process is to write out an amount that either corresponds to an account variance or the calculation of the account balance. At the end of the process, these amounts are written to a separate InfoProvider that you can use for reporting. Writing the data to a separate InfoProvider provides visibility to these variances from a data-management perspective while allowing this information to remain reportable.

The generic reconciliation data model is comprised of two InfoProviders that contain the data from each source system. The first InfoProvider in this model represents the journal of record, and the second InfoProvider represents another financial journal (non-R/3 in this case). A MultiProvider is then used to join the data from these two journal sources in a query that calculates a variance.

Once the variances are identified, they are written to an ODS using Analysis Process Designer (APD). The last step is to load the data from the ODS into a reportable InfoProvider. In general, the data should be written to another InfoProvider and not directly to the InfoProvider with the variance. This provides better visibility to these entries and allows them be readjusted daily without having to process requests on the main InfoProviders. Once the data is in the reportable DataProvider, reports can be created to include this information so that financial information is consistent across the landscape.

Figure 1 provides an overview of the process. Depending on the reader’s level of skill in setting up BW data models and processes, additional help may be required from the BW development team.

Figure 1

Generic data model for financial reconciliation process

Generic Data Model Setup

To set up the scenario, you need to create the following items:

• A MultiProvider between the SAP and non-SAP source systems so that variances can be determined for these two journal sources
  
  
• A query based on the MultiProvider to calculate the variance, which then is used to create an adjusting entry
  
  
• A transactional ODS and export DataSource to store the variance information (note that the APD process can only write to transactional ODS or master data objects)
  
  
• An APD process for identifying account variances and writing these amounts to the ODS
  
  
• An InfoProvider to store the variance information
  
  
• Update rules to load the InfoProvider from the transactional ODS
  
  
• A MultiProvider to connect the non-R/3 InfoProvider and the variance InfoProvider together so that it is consistent with the R/3 financial book of record

Scenario 1: Current Year Earnings

The current year earnings scenario involves reconciling financial information between the balance sheet and the profit and loss (P&L) statement. The primary account we are examining for reconciliation is the current year earnings on the balance sheet. You can write a query that cumulates the net income from the P&L for fiscal year to date (YTD). Within this same query, the balance from the balance sheet can be reported for each fiscal year period.

A variance calculation can be created to show the variance for each fiscal year/period. This variance will be calculated for every period in the current fiscal year and stored in the InfoProvider representing the post-reconciliation corrections (InfoProvider 3 in Figure 1). Once this process is run, the current year earnings always reconcile with the P&L net income. This scenario assumes that the net income is your correct source and the balance sheet needs to reconcile to this source.

Depending on the user’s level of expertise in BW, additional help may be required for the following seven steps that are required to complete the setup:

Step 1. Create the MultiProvider. Fields used in the MultiProvider definition should contain all versions, including actual and plan with account number and fiscal year/period. The comparison is happening between P&L and balance sheet data for current year earnings (Figure 2).

Figure 2

Compare the balance sheet InfoCube and the P&L InfoCube

Step 2. Create a query. Create the query on the MultiProvider that cumulates all P&L accounts YTD for each fiscal year/period (Figure 3). Create one restricted key figure for each fiscal year/period. Calculate variances for each period with the balance from the balance sheet MultiProvider.

Figure 3

Query definition to calculate current year earnings by fiscal period

Step 3. Create a transactional ODS. The ODS must be made transactional so the APD process can write entries to this object. A transactional ODS cannot be directly used for reporting in BW. For the current year earnings scenario, the definition of the ODS (Figure 4) has an amount key figure for every fiscal period.

Figure 4

ODS definition for the retained earnings scenario

Step 4. Create an APD. You can access the APD using transaction RSANWB. The APD uses the query to aggregate data to one account and write this information to an ODS (Figure 5). As part of the query definition, a restricted key figure should be developed for each fiscal year/period and the balance.

Figure 5

APD process flow for the current year earnings scenario

You can schedule the APD process in a process chain by using program RSAN_PROCESS_EXECUTE. Query variables and variants can be used to make the APD query more flexible. For example, a user-exit variable can derive the current fiscal year versus entering this value and saving it as a variant. Do not segment the data on the query object if the query is written against a MultiProvider.

Step 5. Create a new InfoProvider. The solution was designed to write the variance information to another InfoProvider (Figure 6) so that the financial information from both source systems is whole. The variances are continually overwritten as the balance sheet looks at a snapshot in time.

Figure 6

Current-year earning InfoProvider definition that will be used for reporting

Step 6. Create update rules. Create rules to load these variances from the transactional ODS to a reportable InfoCube (Figure 7). Make sure you map all amount variances to 0BALANCE by copying the key figure and specifying different fiscal years/periods for each of these key figure values.

Figure 7

Update rules to convert the APD ODS information to a reportable InfoCube

Step 7. Create a reporting MultiProvider. Include the new InfoCube within the MultiProvider for reporting (Figure 8). This allows queries to be written against the MultiProvider reporting on the current year retained earnings information.

Figure 8

Process flow for the MultiProvider, which includes the new InfoCube

Scenario 2: Account Variances

This variance reconciliation scenario involves reconciling financial information between the book of record, SAP financials, and the G/L for one of the company’s operating groups (non-SAP). Although the non-SAP financial information flows to the SAP system for the preparation of the company’s financial statements, each system maintains different levels of information.

For consistency, the operating group that uses the non-SAP system would like to see its financial data as it is maintained in its source system. However, it also wants to have this data tie to the book of record at a profit center and account level. This is often important when operating groups want to have P&L-type information that is consistent throughout the landscape.

The following seven steps in this scenario are similar to the steps outlined in the previous scenario:

Step 1. Create a MultiProvider. A MultiProvider is used to tie the information from multiple financial journals so that a variance calculation can be calculated (Figure 9). Fields used in the MultiProvider definition should be at the appropriate level of detail to calculate the variance (e.g., it could contain the profit center, account, fiscal period, version, and amount).

Figure 9

Compare the financial journal InfoProviders to create a variance calculation

Step 2. Create a query. Create a simple variance query based on the MultiProvider (Figure 10) to calculate the difference between the two financial journals. You can do this by creating restricted key figures on an amount and restricting it to each InfoProvider being compared. Create a calculated key figure that resolves to one if there is a variance and zero if there is no variance.

Figure 10

Define the query to calculate the difference between the two financial journals

Step 3. Create a transactional ODS. Create an ODS and make it transactional so the APD process can write entries to this object (Figure 11). After it is created, an export InfoSource also must be created so that it can be used for loading. A transactional ODS cannot be directly used for reporting in BW.

Figure 11

ODS definition for restating variances

Step 4. Create an APD. Access APD via transaction RSANWB. APD uses a simple variance query to drive the process (Figure 12). As part of the query definition, a key figure should be developed to resolve to a one or a zero to indicate that a variance exists. If a variance exists, the filter object filters out these variances and writes them to the ODS.

Figure 12

APD process flow for restating financial variances

The APD process can be scheduled in a process chain by using program RSAN_PROCESS_EXECUTE. Query variables and variants can be used to make the APD query more flexible. For example, a user-exit variable can derive the current fiscal year versus entering this value and saving it as a variant.

Step 5. Create a new InfoProvider. The solution was designed to write the variance information to another InfoProvider so that the financial information from both source systems is whole (Figure 13). Since this process can be run daily and adjustments can be made to the variance InfoProvider, the system corrects itself if there are timing issues. As timing issues are resolved, the variances are readjusted. Therefore, it is important to include the variance InfoCube in the variance calculation so that the same variances are not continually written out in the InfoCube.

Figure 13

Financial variance InfoProvider definition that will be used for reporting

Step 6. Create update rules. Since a transactional ODS is not reportable, this information needs to be exported to a reportable InfoProvider. Here you can create an export DataSource from the ODS, where you can then create update rules to the reporting InfoCube (Figure 14).

Figure 14

Update rules to convert the APD ODS information to a reportable InfoCube

Step 7. Create a reporting MultiProvider. Once the MultiProvider is created with the new InfoProvider, you can use the new data for reporting (Figure 15).

Figure 15

Process flow for the MultiProvider, which includes the new InfoCube

Derek Johnson

Derek Johnson is a program manager on the Delivery Operations Team with SAP America, Inc. He is responsible for the design and development of analytical and reporting applications to support SAP America’s consulting organization. Formerly a platinum consultant with the National Competency Center (NCC), he has more than 10 years experience working with SAP NetWeaver BW. Besides traditional data modeling and reporting, he also has extensive experience with Yahoo! Widgets, SAP NetWeaver Visual Composer, Web reporting, BW data mining, Xcelsius, ABAP, and JavaScript. He holds a BS degree in engineering from the University of Michigan-Ann Arbor, an MS degree in engineering from Wayne State University-Detroit, and an MS degree in management information systems from Case Western Reserve University-Cleveland.

You may contact the author at derek.l.johnson@sap.com.

If you have comments about this article or publication, or would like to submit an article idea, please contact the editor.

Prakash Darji

Prakash Darji is an experienced professional with more than 10 years of end-to-end experience in enterprise software. He has a broad depth of experience including corporate strategy, sales, product management, architecture, and development. He has experience in product launch activities, including positioning, packaging, and pricing. He has delivered numerous product releases in a variety of capacities through his career. He thrives on building high-performing, scalable teams to achieve strategic deliverables, whether they close strategic sales deals, roll in product features, or roll out new releases. He is a recurring author for several publications and a speaker at SAP conferences around the world. Prakash is on LinkedIn at https://www.linkedin.com/in/prakashdarji.

You may contact the author at editor@BIexpertOnline.com.

If you have comments about this article or publication, or would like to submit an article idea, please contact the editor.