SAP BW at Microsoft

1
SAP BW at Microsoft
Bill Faisonbfaison_at_microsoft.com
2
Agenda

• Overview of the Microsoft IT Landscape
• Why SAP BW
• What are we using in SAP BW 3.0B
• Integration with Analysis Services
• Feeding Data from BW using the SAP .NET Connector
• Upgrading SAP R/3 4.7 and SAP BW
• Future Projects

3
Terminology

• OLAP Online Analytical Processing. Dynamic
  multi-dimensional aggregate slicing/dicing
  reporting
• BW SAP Business Information Warehouse. SAPs
  OLAP product. v3.0 uses an SAP proprietary
  relational OLAP engine
• DataSources Structures in R/3 that contain the
  fields to be extracted
• Analysis Services OLAP component of Microsoft
  SQL Server
• Star Schema Relational schema with a central
  fact table and separate dimension tables.
  Partially de-normalized to aid brows ability
• ROLAP Relational OLAP. A relational star
  schema with separate storage of aggregates
  strictly within a relational database.
  Compromised performance for storage
• MOLAP Multidimensional OLAP. No use of the
  relational database for reporting. Redundant
  storage with highest analysis performance.
  Supported via Analysis Services within BW 3.0
• Office Web Components Included in Office
  license. A collection of ActiveX controls that
  enable interactive web access to pivot tables,
  spreadsheets, and databases

4
Current Landscape

• Reporting Strategy
• Extract data from R/3 one time, distribute via
  Feedstore for business-owned datamarts
• Statutory reports directly in R/3
• Web front ends for real time inquiry and document
  level reporting
• Use BW for new implementations where content fits
  Microsoft requirements

• Transactions Strategy
• All Transactions processed in SAP
• Hardcore users use SAP GUI
• Casual users use Intranet GUIs
• Partners use EDI or Internet GUIs

5
R/3 Reporting Problems

• End to end extraction and data mart builds take
  too long
• Massive extracts impose R/3 performance burden
• Redundancy among R/3 extracts and reporting
  solutions
• It is difficult to reverse engineer R/3 business
  rules and document data in a custom reporting
  solution

6
Solution Requirements

• Business
• Fast ad hoc query performance
• Role and business function focused data
• Measure policy adherence
• Identify opportunities for using preferred
  vendors
• KPI metrics reporting measuring effectiveness of
  purchasing business strategy
• Health index - benchmarking across subs
  regions, identifying best practices
• Corporate standard user interface

7
Solution Requirements(2)

• IT
• Pilot new SAP reporting technology
• Reduce custom R/3 extracts
• Integrates with SAP R/3

8
Why SAP BW

• Other options
• Custom centralized data warehouse
• 3rd party tools (Acta, Information Builders)
• Status quo
• Why BW
• Reduced ABAP requirements
• Rapid deployment for R/3-savvy team
• SAP knows R/3 data best we are betting they
  will have the best extraction solution
• Easy deployment of standard content

9
Functionality used in BW 3.0B

• Transactional Data DataSources
• ODS
• Process Chains
• DB Integration

10
DataSources

• All data from R/3 use the SAP supplied
  datasources, or the generic extractor
• As of 3.0B, we were able to retire all of our
  custom ABAP extracts
• Pull all fields from R/3, and only load what we
  need into the ODS tables
• Populate data into BW from R/3 for any critical
  data feeds on your production system well in
  advance before you go live
• 0FI_GL_4
• Used to extract data from BKPF/BSEG
• Did not have to use the BW R/3 user-exit to
  populate any fields
• Leverage the delta extract
• Cannot figure out why it sends a delta update for
  some records
• Performance has been very good
• Watch out when loading historical data from R/3
  to BW. Has some issues with parallel data
  extracts from R/3
• Be careful with any business rule processing
• Line items for a document may not be in the same
  data packet

11
DataSources

• 2LIS_02_ITM
• Source for all of our PO related data
• Leveraged the BW R/3 user-exit to populate fields
  not part of the standard business content instead
  of turning on another datasource
• Use the V3 update on R/3 for delta extractions
• Basis team does not like seeing updates piling up
  in the update queue in R/3
• Performance has not been a issue
• R/3 to BW historical data load process can only
  handle one data load at a time

12
ODS

• 5 ODS tables to hold document line item level
  data
• Load data into PSA, then into ODS so we do tie
  the data extract from R/3 to the successfully
  activation of data records into the ODS table
• Much improved since 2.0B
• Full repair request and selective delete adds
  much needed flexibility
• Start Routine in update rules allows you to apply
  very complex business rule logic to the data
• Take caution with the logic you apply and the
  size of internal tables, or you might degrade
  data load performance
• Field 0RECORDMODE in ODS indicates if the record
  is a insert, change or delete
• Lagniappe Cajun for a little something extra
• Table RSSELDONE is very helpful since it holds
  parameters of the infopackage
• Use RSSELDONE-UPDMODE to indicate if the job is a
  Full, Delta, etc.

13
Process Chains

• A process chain is a sequence of processes that
  wait in the background for an event.
• Some of these processes trigger a separate event
  that can start other processes in turn.
• Huge improvement over the event chains
• GUI interface makes it easy to set up
• Ability to transport process chains eliminates
  set up errors in other BW systems
• Some quirks in process chains do not allow you
  to do everything
• Updating one ODS table per infopackage run from
  the PSA does not work

14
DB Integration

• Load 2 fields into R/3, then pass those fields to
  BW with the 0VENDOR datasource
• Rest of the fields are loaded into BW using a
  flat file from DB
• Leveraged the standard business content
  infopackage, infocube and Bex query
• Export the data to DB using the SAP supplied
  program
• Kanji data could be corrupted when it is exported
  from the BW system
• Export program works, but we have found bugs that
  SAP had to fix
• DB will supply you the files in any format you
  request
• Linkage from DB a statutory perspective of the
  world does not exactly match the linkage
  Microsoft wants to see from a negotiation
  standpoint
• ExampleHP bought Compaq but it will legally take
  a long time to have every international Compaq
  site "officially" roll up under HP. Our business
  team needs to see them rolled up now - not over
  time
• If you have Japanese customers, you will have to
  provide a file for DB with kanji names and
  address

15
Integration with Analysis Services

• TradeoffsROLAP MOLAP
• Why Microsoft Analysis Services
• Deployment Architecture

16
TradeoffsROLAP MOLAP

• MOLAP
• Cube data and aggregations in MetaData store
• Best query performance
• Near-best processing performance
• Most additional storage
• ROLAP
• Cube data remains in relational store
• Aggregations in relational store
• ROLAP dimensions
• Worst query performance
• Worst processing performance
• Highest load on data warehouse server

17
Why Microsoft Analysis Services

• Complex calculated measures, aggregations and
  usage based optimization possible
• Enhanced query performance
• Able to choose best of breed web front-end
• Native Excel front end support
• Cubes can be exported for off-line reporting
• Custom ad hoc grouping
• Custom total based on OLAP MDX queries
• Adjustable scope conditional filtering
• Data mining

18
Deployment Architecture
SAP R/3
SAP BW 3.0B
SQL Server Analysis Services
MOLAP Reporting Cubes
Persistent Staging Area
Microsoft Custom SQL Views
BW ROLAP Cubes
End User
IIS Server
Fact Table Drill-Through
Non-SAP Applications
Internet Explorer Office Web Components
MSInsight Reporting Front End
XML Metadata
19
Deployment Architecture
20
Deployment Architecture(2)

• Pros
• Fast queries
• Take advantage of new SQL Server functionality
  right away
• We are able to meet Microsoft specific usability
  requirements since we own the GUI
• Access multiple data sources from one reporting
  GUI
• Reporting GUI is not tied to BW support packs
• Cons
• Not supported by SAP
• MOLAP Bridge is SAP integration of Analysis
  Services
• Need to address SAP data storage formatting
  issues
• OWC can generate poor performing queries
• Pushing data into the MOLAP cubes adds another
  potential point of failure

21
Feeding data from BW using the SAP .NET Connector

• What is the SAP .NET Connector
• Project overview

22
What is the SAP .NET Connector

• From the SAP website
• The SAP Connectors allow the integration of
  different applications and technologies with SAP
  Systems via open standards
• The SAP .NET Connector offers developers to
  expose BAPIs and remotely-enabled function
  modules to any .NET application (inside-out). You
  now also can access .NET components from within a
  mySAP.com application (outside-in)

23
What is the SAP .NET Connector

• SAP client solution, the SAP system is the server
  and the .NET application is the client that
  interacts with the RFC
• Examples of some uses for the SAP .NET Connector
  client application
• Windows form application that has a customized
  and highly interactive user experience
• Console application to access information from
  the SAP system as part of some NT batch
  processing

24
What is the SAP .NET Connector

• RFC server allows your SAP system to execute .NET
  code as if the .NET code were another SAP system
• Examples of some uses for the SAP .NET Connector
  server application
• Getting information such as maps, stock prices or
  weather from an external service to be used
  within a SAP report
• Sending emails from your SAP system

25
Design Overview

• Business case Supply data that had been
  transformed in SAP BW to a non-SAP database
• Other solutions considered
• Create a custom ABAP to create flat files to be
  consumed by the source data warehouse
• Create a DTS package to extract and pump the data
  over to the non-SAP database
• Use MS SQL replication to extract and feed the
  data to the non-SAP database
• Hub and Spoke

26
Design Overview
27
Upgrading SAP R/3 4.7 and SAP BW

• Few Issues encountered
• Have not done any performance testing yet
• No new BW tcodes or changes found so far in 4.7
• Clear out set up tables used by logistic cockpit
  before the Prepare phase of the upgrade
• Using new 4.7 functionality resulted in us
  needing to add only 2 fields to our datasource
  structures

28
Future Projects

• XStar
• SCM project based on the data in our APO system
• User will most probably use a SAP supported GUI
• We might migrate a datamart into the BW system
• Stage R/3 data to our central data store
• Leverage the SAP .NET Connector
• Real Estate
• Will be based on using the standard business
  content to extract and store data in BW
• Not sure how the users will access data yet
• Project Systems
• Will use the standard business content to extract
  and store data in BW
• Not sure how the users will access data yet

29
Future Projects

• Financial Insight Package with Enterprise Portal
  and BW
• Analytics for the following areas
• Cost center and budgeting
• Cash-Flow
• Economic profit
• Activity based costing
• GL and Divisional balance sheets
• UNICODE for BW
• Should resolve our double byte characters issues
  when we export data from BW

30
Future Projects

• Crystal Enterprise
• Investigating if this can be used for web based
  reporting for SAP BW
• Tightly integrated with SAP BW
• Reports are based BW Query, so no formatting
  issues
• Need to have an aggregate defined over the
  datatarget, or the report will not run
  efficiently.
• Investigating if the MOLAP bridge can manage the
  aggregates in BW for Crystal Reports
• Have a clear understanding on the SAP
  authorization requirements
• Did not match how Microsoft manages SAP
  authorizations

31
Useful Sites

• http//service.sap.com/CONNECTORS
• Requires a OSS log on
• http//www.microsoft-sap.com/
• http//www.sapgenie.com/interfaces/netconnector.ht
  m

32
Thank you for attending!
Please remember to complete and return your
evaluation form following this session.
Session Code 211