Docstoc

Project Report on Sap Database

Document Sample
Project Report on Sap Database Powered By Docstoc
					SAP BW at Microsoft
Bill Faison
bfaison@microsoft.com
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
Terminology
•   OLAP: Online Analytical Processing. Dynamic multi-dimensional
    aggregate slicing/dicing reporting
•   BW: SAP Business Information Warehouse. SAP’s 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
  Current Landscape




                          SAP R/3




Transactions Strategy:                Reporting Strategy:
• All Transactions processed in SAP   • Extract data from R/3 one time, distribute via
                                         Feedstore for business-owned datamarts
• Hardcore users use SAP GUI
                                      • Statutory reports directly in R/3
• Casual users use Intranet GUIs
                                      • Web front ends for real time inquiry and
• Partners use EDI or Internet GUIs      document level reporting
                                      • Use BW for new implementations where content
                                         fits Microsoft requirements
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
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
Solution Requirements(2)

• IT
  Pilot new SAP reporting technology
  Reduce custom R/3 extracts
  Integrates with SAP R/3
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
Functionality used in BW 3.0B

•   Transactional Data DataSources
•   ODS
•   Process Chains
•   D&B Integration
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
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
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.
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
D&B 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 D&B
    Leveraged the standard business content infopackage, infocube and
     Bex query
• Export the data to D&B 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
• D&B will supply you the files in any format you request
    Linkage from D&B a statutory perspective of the world does not
     exactly match the linkage Microsoft wants to see from a negotiation
     standpoint
       • Example:HP 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
     D&B with kanji names and address
Integration with Analysis
Services

• Tradeoffs–ROLAP & MOLAP
• Why Microsoft Analysis Services
• Deployment Architecture
Tradeoffs–ROLAP & 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
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
Deployment Architecture

SAP R/3


                              SAP BW 3.0B                     SQL Server
                                                            Analysis Services

                                             Microsoft
                Persistent     BW                              MOLAP
                                            Custom SQL
               Staging Area   ROLAP                           Reporting             End User
                                              Views
                              Cubes                            Cubes



                                             Fact Table        IIS Server
                                            Drill-Through                       Internet Explorer
 Non-SAP                                                      MSInsight         Office Web Components
Applications                                                  Reporting
                                                              Front End

                                                                    XML
                                                                  Metadata
Deployment Architecture
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
Feeding data from BW using the
SAP .NET Connector

• What is the SAP .NET Connector
• Project overview
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)
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
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
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
Design Overview


                             Windows Service

     SAP ABAP
      Program
    Call RFC with
     destination



                                 Convert
                                 Income
                                 table to       External SQL Server
                                   XML
        SM 59
      Destination
   information table

                                      SQL XML
                          XML          BULK
                        Schema
                                      INSERT



                       Windows 2003
     SAP BW
                          Server
      Layer
                           Layer
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
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
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
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
Useful Sites

• http://service.sap.com/CONNECTORS
   Requires a OSS log on
• http://www.microsoft-sap.com/
• http://www.sapgenie.com/interfaces/netc
  onnector.htm
Thank you for attending!
Please remember to complete
and return your evaluation form
following this session.


          Session Code: 211

				
DOCUMENT INFO
Description: Project Report on Sap Database document sample