1 / 32

SAP BW at Microsoft

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.

arista
Download Presentation

SAP BW at Microsoft

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript


  1. SAP BW at Microsoft Bill Faisonbfaison@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. 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

  4. SAP R/3 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 • D&B 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. 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

  15. Integration with Analysis Services • Tradeoffs–ROLAP & MOLAP • Why Microsoft Analysis Services • Deployment Architecture

  16. 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

  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.htm

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

More Related