Project Report on Sap Database

Document Sample
Project Report on Sap Database Powered By Docstoc
					SAP BW at Microsoft
Bill Faison

• 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
• Upgrading SAP R/3 4.7 and SAP BW
• Future Projects
•   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
• 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

• 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
• 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
    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

• 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

•   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
     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
     • 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
       • 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

• Tradeoffs–ROLAP & MOLAP
• Why Microsoft Analysis Services
• Deployment Architecture
Tradeoffs–ROLAP & MOLAP

  – Cube data and aggregations in MetaData store
  + Best query performance
  + Near-best processing performance
  - Most additional storage
  – 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
• 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 BW 3.0B                     SQL Server
                                                            Analysis Services

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

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

Deployment Architecture
Deployment Architecture(2)

• Pros
   Fast queries
   Take advantage of new SQL Server functionality right
   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 application (outside-
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

    Call RFC with

                                 table to       External SQL Server
        SM 59
   information table

                                      SQL XML
                          XML          BULK

                       Windows 2003
     SAP BW
Upgrading SAP R/3 4.7 and SAP
• Few Issues encountered
   Have not done any performance testing yet
   No new BW tcodes or changes found so far in
• Clear out set up tables used by logistic
  cockpit before the „Prepare‟ phase of the
• 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
   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
     • Did not match how Microsoft manages SAP authorizations
Useful Sites

   Requires a OSS log on
Thank you for attending!
Please remember to complete
and return your evaluation form
following this session.

          Session Code: 211

Description: Project Report on Sap Database document sample