SAP BW at Microsoft

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