_________________________________
_________________________________
_________________________________
_________________________________
Tips and Tricks to _________________________________
Automate SAP BW/BI _________________________________
Reconciliation to SAP _________________________________
Financials Data
_________________________________
_________________________________
Chip Brenner
Breakwater Consulting _________________________________
© 2007 Wellesley Information Services. All rights reserved.
What We’ll Cover …
_________________________________
• Reconciling BI financials extraction: overview _________________________________
• Building a remote InfoCube as the basis for _________________________________
reconciliation
_________________________________
• Building MultiProviders and reconciliation reports
_________________________________
• Automating financial data validation
_________________________________
• Wrap-up
_________________________________
_________________________________
_________________________________
_________________________________
1
Reconciling Extracted Financial Data in BI: Overview
_________________________________
• Why reconcile? _________________________________
Incorrect data can lead to a lack of confidence in BI reports or
worse, decisions being made on incorrect information
_________________________________
Financial data extracts need to be reconciled to ensure _________________________________
accurate reporting/decision making
_________________________________
• Automating this process makes it feasible and timely
_________________________________
Will allow support teams to catch and correct data load errors
before they are viewed by the end user _________________________________
The reconciliation process can also be used during _________________________________
integration testing to validate the load process
_________________________________
_________________________________
2
Common Problems During Data Extraction
_________________________________
• Technical issues: _________________________________
IDocs fail or communication connection is lost
_________________________________
Loads “hang” during processing
Short dumps cause the load to cancel _________________________________
Lack of system resources causes the loads to be delayed, _________________________________
or fail
_________________________________
Most technical issues can be identified and caught when the
process chain loads _________________________________
Technical errors usually create notifications for the groups _________________________________
responsible for the load, and are then corrected overnight
_________________________________
_________________________________
3
Common Problems During Data Extraction (cont.)
_________________________________
• Data issues (i.e., no technical errors are issued): _________________________________
Duplicate records are loaded in delta loads _________________________________
Overlapping packages are not deleted in full loads
Some records may not be loaded, or are missed
_________________________________
Packages may not be set to be reportable _________________________________
Data issues are more “Functional” in nature and may not _________________________________
create notifications, as the load was deemed “successful”
Data issues are often caught by the user community before _________________________________
the support teams are aware of it _________________________________
_________________________________
GOTCHA! _________________________________
4
In This Session ...
_________________________________
• Advantages of using Remote cubes, and how to use _________________________________
them to reconcile financial data
_________________________________
• Configuration of a Remote G/L InfoCube to support
validation of financial data including: _________________________________
General Ledger _________________________________
Consolidation Staging _________________________________
Controlling
_________________________________
Accounts Receivable or Accounts Payable
CO-PA _________________________________
• How to automate reconciliation using the _________________________________
BEx Broadcaster _________________________________
5
What We’ll Cover …
_________________________________
• Reconciling BI financials extraction: overview _________________________________
• Building a remote InfoCube as the basis for _________________________________
reconciliation
_________________________________
• Building MultiProviders and reconciliation reports
_________________________________
• Automating financial data validation
• Wrap-up _________________________________
_________________________________
_________________________________
_________________________________
_________________________________
6
Building a Remote InfoCube as the Basis for Reconciliation
_________________________________
• Why use G/L data? _________________________________
All of financial reporting is based on the G/L and should tie to _________________________________
the G/L
Reconciling other cubes with the G/L not only ensures _________________________________
accuracy of those cubes, it also solidifies user confidence in _________________________________
ECC data in other modules
_________________________________
• Why use remote cubes?
Because the remote cube reads data real-time, it can be used _________________________________
to reconcile loads that may finish at different times _________________________________
_________________________________
_________________________________
7
Remote InfoCubes vs. Standard InfoCubes
_________________________________
• Standard InfoCubes contain “copies” of ERP data _________________________________
Loaded in via SAP NetWeaver® BI’s ETL process
_________________________________
• Primary benefit: performance
_________________________________
Data can be optimized for performance
Aggregates, Caching, BI Accelerator _________________________________
Calculations and transformation _________________________________
can be handled in the extract
and stored in BI _________________________________
Extracts are most often run _________________________________
nightly so that users will report
on data that can be “stale” in _________________________________
some cases _________________________________
8
Remote InfoCubes vs. Standard InfoCubes (cont.)
_________________________________
• Remote InfoCubes pull data from the source “on the fly” _________________________________
and store in memory
_________________________________
• Primary benefits: convenience and storage
_________________________________
Reads from ECC at runtime, so performance is not as good as
reports on standard InfoCubes _________________________________
Easy to setup and manage (no ETL process to set up/monitor) _________________________________
Data displayed in the report is
the most current data thus _________________________________
eliminating “stale” datasets _________________________________
_________________________________
_________________________________
9
Prerequisites for Building Remote InfoCubes
_________________________________
• Remote InfoCubes require both the InfoCube and the _________________________________
DataSource to allow (be set up for) remote access _________________________________
Be careful when creating a remote cube, as there is
additional hidden logic _________________________________
DataSource must have Direct Access other than “D” in order _________________________________
to retrieve data remotely (see below)
_________________________________
_________________________________
_________________________________
_________________________________
_________________________________
10
Prerequisites for Building Remote InfoCubes (cont.)
_________________________________
• A Single cube cannot be used for both remote and static _________________________________
data (use a MultiCube for this)
_________________________________
MultiProviders combine data in multiple InfoCubes
for reporting _________________________________
A MultiProvider is not the same thing as a “Join” _________________________________
SAP recommends assigning no more than 10 InfoCubes in a
single MultiProvider _________________________________
_________________________________
_________________________________
_________________________________
_________________________________
11
How to Build Remote Cubes
_________________________________
1. Build the remote cube _________________________________
2. Create a transformation for the cube _________________________________
3. Assign field movements to the transformation _________________________________
4. Create a Data Transfer Process (DTP) to read the data
_________________________________
5. Validate the data
_________________________________
_________________________________
_________________________________
_________________________________
_________________________________
12
1 — Create a New Remote Cube
_________________________________
• Build the remote cube _________________________________
Start with Business Content, which already allows Remote _________________________________
Access (0PCA_C01 in this example)
This ensures the remote logic will be included _________________________________
Copy the cube and make sure that it is set up correctly for _________________________________
remote access
_________________________________
After the cube is copied, you can then change the fields in
the InfoCube as required _________________________________
_________________________________
_________________________________
_________________________________
13
1 — Create a New Remote Cube (cont.)
_________________________________
_________________________________
_________________________________
_________________________________
_________________________________
_________________________________
_________________________________
_________________________________
_________________________________
_________________________________
14
2 — Create a Transformation for the Cube
_________________________________
• Create a transformation by assigning a remote _________________________________
DataSource (0sem_BCS_10 in this example)
_________________________________
Search “Remote” in the Business Content for other examples
_________________________________
_________________________________
_________________________________
_________________________________
_________________________________
_________________________________
_________________________________
15
3 — Assign Field Movements to the Transformation
_________________________________
• … as you would a standard InfoCube _________________________________
_________________________________
_________________________________
_________________________________
_________________________________
_________________________________
_________________________________
_________________________________
_________________________________
16
4 — Create a Data Transfer Process (DTP) to Read the Data
_________________________________
• Pre-SAP NetWeaver 7.0: create a remote InfoPackage _________________________________
_________________________________
_________________________________
_________________________________
_________________________________
_________________________________
_________________________________
_________________________________
_________________________________
17
5 — Validate the Data
_________________________________
• Create a query on the cube to ensure it returns data _________________________________
List cube does not always work on remote cubes
_________________________________
• Validate the returned data to reports in ECC
_________________________________
As this is being used to validate other reports, ensure the
results are correct _________________________________
Also make sure to validate totals by drill-down characteristics _________________________________
_________________________________
_________________________________
_________________________________
_________________________________
18
Example: Query on G/L Remote Cube by G/L Account
_________________________________
_________________________________
_________________________________
_________________________________
_________________________________
_________________________________
_________________________________
_________________________________
_________________________________
_________________________________
19
Example: ECC G/L Report – TCode S_PL0_86000030
_________________________________
_________________________________
_________________________________
Compare Balances to _________________________________
ECC Report
_________________________________
_________________________________
_________________________________
_________________________________
_________________________________
_________________________________
20
Tips – Using Remote Cubes for Financials Data
_________________________________
• Remote cubes are great when used in low-volume _________________________________
scenarios
_________________________________
E.g., month-end close processes
Validate Financial Entries prior to the nightly loads _________________________________
Review pre-consolidated numbers prior to consolidation _________________________________
Ad hoc reviews and snapshots could allow the month-end _________________________________
close group more flexibility in G/L Analysis
The performance hit means you should only release to limited _________________________________
audiences _________________________________
_________________________________
_________________________________
21
What We’ll Cover …
_________________________________
• Reconciling BI financials extraction: overview _________________________________
• Building a remote InfoCube as the basis for _________________________________
reconciliation
_________________________________
• Building MultiProviders and reconciliation reports
_________________________________
• Automating financial data validation
_________________________________
• Wrap-up
_________________________________
_________________________________
_________________________________
_________________________________
22
Building MultiProviders to Reconcile Data
_________________________________
• Using a G/L remote cube will enable you to reconcile _________________________________
your financial postings from:
_________________________________
General Ledger
Consolidations (BCS or EC-CS) _________________________________
Controlling _________________________________
Accounts Receivable/Accounts Payable
_________________________________
Profitability Analysis (CO-PA)
_________________________________
• Building a MultiProvider from the remote cube and the
respective financials cubes lets you perform a _________________________________
side-by-side comparison in a single query _________________________________
_________________________________
23
Building MultiProviders to Reconcile G/L Data
_________________________________
• The structures for the Standard G/L InfoCube and _________________________________
Remote G/L InfoCube are identical in this case
_________________________________
• In the query:
_________________________________
Create a column where you specify the remote cube in one
column and the G/L Cube in the other _________________________________
Add a difference column _________________________________
Cumulative Values are probably best for validation to ensure
that year-to-date amounts are correct _________________________________
_________________________________
_________________________________
_________________________________
24
G/L Reconciliation Example
_________________________________
_________________________________
_________________________________
_________________________________
_________________________________
_________________________________
_________________________________
Amounts from Amounts from Difference _________________________________
Remote Cube Standard G/L
Cube
_________________________________
_________________________________
25
Building MultiProviders to Reconcile BCS Data
_________________________________
• Add a Consolidation Chart of Accounts and FS Item to _________________________________
the remote cube in order to more easily match BCS data
On the transformation, convert G/L Account to FS Item using _________________________________
a mapping from Group account or other logic _________________________________
• In the query for BCS, only pull Posting Level = 00
_________________________________
• Using the extractor in this example (0sem_BCS_10) will
also allow you to pull Movement Type _________________________________
Movement Type is part of the standard PCA cube, which was _________________________________
copied in this example
Creating the remote cube with extractor 0sem_BCS_10 will _________________________________
also allow you to create a remote data collection task in BCS to
collect pre-consolidated trial balances on the fly _________________________________
_________________________________
26
BCS Reconciliation Example
_________________________________
_________________________________
_________________________________
_________________________________
_________________________________
_________________________________
_________________________________
_________________________________
_________________________________
Amounts Amounts from Difference _________________________________
from BCS Remote Cube
27
Building MultiProviders to Reconcile CO Data
_________________________________
• Add Cost Element field to the remote cube _________________________________
• If using the new G/L, you can use transaction _________________________________
FAGLBW03 to create a standard DataSource for the
_________________________________
new G/L
The new G/L extractor is remote-enabled _________________________________
This will also include any custom fields added to the new G/L _________________________________
In the new G/L, Cost Element is included in table FAGFLEXT
_________________________________
This is a great new feature as you can now drill down by
secondary cost element on FICO Reconciliation postings _________________________________
in BI Reports on the new G/L _________________________________
_________________________________
28
CO Reconciliation Example – With New G/L
_________________________________
_________________________________
_________________________________
_________________________________
_________________________________
_________________________________
_________________________________
_________________________________
_________________________________
_________________________________
29
Building MultiProviders to Reconcile CO Data
_________________________________
• If not using new G/L extractor, you will need to tweak _________________________________
the cube and query
_________________________________
Create a field for Cost Element in the cube
Create custom logic to map to the primary cost elements _________________________________
In FI, make sure there is a consistent hierarchy which includes _________________________________
the FICO Recon Accounts in the same nodes as the respective
secondary cost element (or just do the comparison for total
_________________________________
expenses) _________________________________
As detail by account may be different, default the
_________________________________
reconciliation by company code to ensure total expenses are
reconciled _________________________________
_________________________________
30
CO Reconciliation Example – If Not Using New G/L
_________________________________
_________________________________
_________________________________
_________________________________
_________________________________
_________________________________
_________________________________
_________________________________
_________________________________
Amounts Amounts from
from Remote Standard Cost
Difference
_________________________________
Cube Cube
31
Building MultiProviders to Reconcile A/R Data
_________________________________
• Reconciliation approach depends on how your _________________________________
customers have been defined
_________________________________
• Amounts should be matched by Reconciliation Account
(i.e., G/L account) _________________________________
• The query will need to have additional logic included on _________________________________
the A/R side: _________________________________
Include both open and closed items at the key date of
_________________________________
the query
When executing, use the last day of the month for the key _________________________________
date, as the G/L is stored by period _________________________________
• Similar logic can be used for A/P Reconciliation
_________________________________
32
A/R Reconciliation Example
_________________________________
_________________________________
_________________________________
_________________________________
_________________________________
_________________________________
InfoProvider
_________________________________
Restriction
_________________________________
_________________________________
_________________________________
Key Date
Used for A/R
and A/P
33
A/R Reconciliation Example (cont.)
_________________________________
_________________________________
_________________________________
_________________________________
_________________________________
InfoProvider
_________________________________
Restriction
_________________________________
_________________________________
Fiscal Year/ _________________________________
Period Used
for G/L
_________________________________
34
A/R Reconciliation Example (cont.)
_________________________________
_________________________________
_________________________________
_________________________________
_________________________________
_________________________________
_________________________________
Amounts Amounts from Difference
_________________________________
from A/R Remote Cube
Cube _________________________________
_________________________________
35
Building MultiProviders to Reconcile CO-PA Data
_________________________________
• Account-based CO-PA _________________________________
If using account-based CO-PA, be sure to include Cost
_________________________________
Element in the CO-PA Extractor (transaction KEB0 in
SAP ERP) _________________________________
_________________________________
_________________________________
_________________________________
_________________________________
_________________________________
_________________________________
36
Building MultiProviders to Reconcile CO-PA Data (cont.)
_________________________________
• Cost-based CO-PA _________________________________
Cost-based CO-PA is more difficult _________________________________
Work with the CO-PA functional consultant to build
reconciliation logic in the query _________________________________
This will also help the functional team tie out CO-PA to the G/L _________________________________
_________________________________
_________________________________
_________________________________
_________________________________
_________________________________
37
What We’ll Cover …
_________________________________
• Reconciling BI financials extraction: overview _________________________________
• Building a remote InfoCube as the basis for _________________________________
reconciliation
_________________________________
• Building MultiProviders and reconciliation reports
_________________________________
• Automating financial data validation
• Wrap-up _________________________________
_________________________________
_________________________________
_________________________________
_________________________________
38
Automating the Validation of Financial Data
_________________________________
• Once reports are created, BI Broadcaster can be used to _________________________________
send the results to the support group
_________________________________
Create Broadcasting settings to email the validation reports
upon successful completion of the loads _________________________________
Broadcaster can send a complete report as a zip file for each _________________________________
report or a link to the report (complete report is better)
Individuals responsible for functional validation can then use
_________________________________
the report to check for discrepancies _________________________________
If any discrepancies are found, they can execute the report
_________________________________
to analyze the differences
_________________________________
_________________________________
39
Broadcaster Settings – Step 1 (Using the Wizard)
_________________________________
_________________________________
_________________________________
_________________________________
MHTML
_________________________________
delivers
entire report _________________________________
results in a
zip file
_________________________________
_________________________________
_________________________________
_________________________________
40
Broadcaster Settings – Step 2
_________________________________
_________________________________
_________________________________
_________________________________
_________________________________
_________________________________
_________________________________
_________________________________
_________________________________
_________________________________
41
Broadcaster Settings – Step 3
_________________________________
_________________________________
_________________________________
_________________________________
_________________________________
_________________________________
_________________________________
_________________________________
_________________________________
_________________________________
42
Broadcaster Settings – Step 4
_________________________________
_________________________________
_________________________________
_________________________________
_________________________________
_________________________________
_________________________________
_________________________________
_________________________________
_________________________________
43
What We’ll Cover …
_________________________________
• Reconciling BI financials extraction: overview _________________________________
• Building a remote InfoCube as the basis for _________________________________
reconciliation
_________________________________
• Building MultiProviders and reconciliation reports
_________________________________
• Automating financial data validation
• Wrap-up _________________________________
_________________________________
_________________________________
_________________________________
_________________________________
44
Resources
_________________________________
• How-To Guides in SAP’s Service Marketplace* _________________________________
How to Reconcile Data Between SAP Source Systems and
_________________________________
SAP NetWeaver BI (June 2005)
How to Implement a Virtual Provider with Services _________________________________
(March 2005) _________________________________
Developers Guide – SAP NetWeaver 7.0 SP12 (May 2007)
_________________________________
How To Validate InfoCube Data by Comparing It with PSA Data
(December 2004) _________________________________
How To Create Efficient MultiProvider Queries (July 2006)
_________________________________
_________________________________
Where
to _________________________________
FIND it
* Requires login credentials to the SAP Service Marketplace
45
Resources (cont.)
_________________________________
• SAP Notes: _________________________________
Note 852971 – SEM-BCS: Integration with the new general _________________________________
ledger
Note 983605 – Query on Remote Provider: No data or _________________________________
termination _________________________________
Note 1043008 – Key date selection ineffective for char with
direct access _________________________________
_________________________________
_________________________________
_________________________________
Where
to _________________________________
FIND it
46
7 Key Points to Take Home
_________________________________
• Data loads may have errors that are not caught during _________________________________
BI process chain processing
_________________________________
• Remote cubes provide the ability to access ECC data
on the fly, so that reconciliation can occur immediately _________________________________
after each data load _________________________________
• MultiProviders let you combine multiple cubes in a _________________________________
single query (max 10)
_________________________________
• Using the Remote G/L Cube as a reference, you can
_________________________________
reconcile the extracts of financial data and catch errors
before users do! _________________________________
_________________________________
47
7 Key Points to Take Home (cont.)
_________________________________
• To ease reconciliation, make sure to add fields to the _________________________________
remote cube (FS Item, Cost Element, etc.) that are part
_________________________________
of the cubes you want to reconcile
_________________________________
• Setting up the reports in the Broadcaster is easy, and
reports will be executed as soon as data load is _________________________________
complete (important when using remote validation) _________________________________
• The validation will also give comfort to users that the _________________________________
different modules in ERP are reconciled in addition to
the BI Reports (e.g., CO-PA matches G/L) _________________________________
_________________________________
_________________________________
48
Your Turn!
_________________________________
_________________________________
_________________________________
_________________________________
_________________________________
_________________________________
_________________________________
_________________________________
How to contact me: _________________________________
Chip Brenner _________________________________
c.brenner@breakwater-consulting.com
49
Notes:
______________________________________________________________________________
______________________________________________________________________________
______________________________________________________________________________
______________________________________________________________________________
______________________________________________________________________________
______________________________________________________________________________
______________________________________________________________________________
______________________________________________________________________________
______________________________________________________________________________
______________________________________________________________________________
______________________________________________________________________________
______________________________________________________________________________
______________________________________________________________________________
______________________________________________________________________________
______________________________________________________________________________
______________________________________________________________________________
______________________________________________________________________________
______________________________________________________________________________
______________________________________________________________________________
______________________________________________________________________________
______________________________________________________________________________
______________________________________________________________________________
______________________________________________________________________________
______________________________________________________________________________
______________________________________________________________________________
______________________________________________________________________________
______________________________________________________________________________
______________________________________________________________________________
______________________________________________________________________________
______________________________________________________________________________
Notes:
______________________________________________________________________________
______________________________________________________________________________
______________________________________________________________________________
______________________________________________________________________________
______________________________________________________________________________
______________________________________________________________________________
______________________________________________________________________________
______________________________________________________________________________
______________________________________________________________________________
______________________________________________________________________________
______________________________________________________________________________
______________________________________________________________________________
______________________________________________________________________________
______________________________________________________________________________
______________________________________________________________________________
______________________________________________________________________________
______________________________________________________________________________
______________________________________________________________________________
______________________________________________________________________________
______________________________________________________________________________
______________________________________________________________________________
______________________________________________________________________________
______________________________________________________________________________
______________________________________________________________________________
______________________________________________________________________________
______________________________________________________________________________
______________________________________________________________________________
______________________________________________________________________________
______________________________________________________________________________
______________________________________________________________________________
Wellesley Information Services, 990 Washington Street, Suite 308, Dedham, MA 02026
Copyright © 2007 Wellesley Information Services. All rights reserved.