1 / 55

Required Slide

Required Slide. Required Slide. SESSION CODE: BICO4-INT. Microsoft BI and SAP from a functional and technical perspective. Arun Justus BI Technology Advisor, Western Europe Microsoft Corporation. Agenda.

javier
Download Presentation

Required Slide

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. Required Slide

  2. Required Slide SESSION CODE: BICO4-INT Microsoft BI and SAPfrom a functional and technical perspective Arun Justus BI Technology Advisor, Western Europe Microsoft Corporation

  3. Agenda This session throws light on how customerscanmake the best of theirinvestmentswith Microsoft and SAP from a BI perspective. Itcoversfunctional areas likeoperationalreporting, analytics on SAP data and alsofocuses on technicalintegration aspects with SAP systems. MS BI integration with SAP • Overview • SAP Customer Pain Points • Microsoft BI and SAP : Solution Scenarios • SAP Access Licensing Aspects • Security, Master data topics • Third Party connectivity options • Customer References • Resources

  4. Microsoft Business Intelligence Business Intelligence with the world’s most popular business productivity suite and most widely deployed information platform Empower Your People Improve Organizational Effectiveness Increase IT Efficiency Self-service access & insight Collaborative across your value chain Trusted, scalable & secure Familiar interface & tools you own today Alignment, accountability & ownership IT management & developer productivity

  5. Microsoft Business IntelligenceGet more out of products you already own Familiar User Experience Self-service Access and Insight Data Exploration and Analysis Predictive Analysis Data Visualization Contextual Visualization Business User Experience Integrated Content and Collaboration Thin Clients Dashboards BI Search Content Management Compositions Business Collaboration Platform Information Platform Analysis Services Reporting Services Integration Services Master Data Data Mining Services Data Warehousing Information Platform

  6. Office 2010+SQL 2008 R2+Sharepoint2010 • A major release cycle for Microsoft BI • PerformancePoint Services in Sharepoint 2010 • Introducing PowerPivot • Self-Service BI • Introducing Master Data Services • Introducing Parallel Data Warehouse

  7. SAP BPC and Microsoft BI Source : SAP

  8. .... we compete and co-exist • A SAP customer is likely to deploy SAP BI • You already own it • You consider it part of your investment (ERP) • Your execs have been sold the value • Microsoft objectives • Enable personal, team and organizational BI • Leverage investment in Office System and SQL • Unify or standardize user experience, and information sharing, across SAP and non-SAP data

  9. SAP BI Choice • Choose a Microsoft solution • Pros • Leverage SAP BW business content and R/3 integration • Flexible free-form reporting on SAP BW • Enables integration of non-SAP data • Deployment using market leading OLAP server • Greater support from third party client tools • Greater flexibility, through deployment options • Cons • Some limitations in different scenarios

  10. Some SAP Customer Pain Points • Master and Meta Data reusability • Data Governance • Flexible Data Integration Scenarios • Integrate SAP Dimensions/hierarchies into local Reporting solutions • Integrate SAP facts/transactions into local Reporting solutions • Integrate non-SAP data and SAP data to form a Unified Dimensional Model • Integrate external data sources • TightintegrationwithSharepoint and Office

  11. SAP BI Limitations • Limited Support for non-SAP data • Some OLAP sources supported, else data must be extracted and loaded into BW • BI Client tools do not meet all requirements • BEx Analyzer is an OLAP client • Report Designer is a v1.0 product (only for BI 7.0) • Projects are lengthy and expensive • BW ranked lowest on benefits index (OLAP Report) • BI Accelerator is not a panacea • One BI Accelerator per instance, hard-limit on scaling, not commodity pricing • Roadmap with Business Objects is not at all clear

  12. SAP NetWeaver BI • Part of NetWeaver • - Comprises • Data Warehousing • BI Platform • BExSuite • Approximately 1/3 of R/3 customers deployed BW • Previous Release: BW 3.5 • - Current Release: BI 7.0 Source : SAP

  13. Microsoft BI for SAP Microsoft .Net Provider for SAP NetWeaver Business Intelligence Sharepoint 2010 with BI tools PowerPivot & Excel Services SAP R/3 / SAP mySAP SAP BW / NW BI SQL 2005 / 2008 Reporting Services XML/A Open Hub MS Connector 1.1 SQL 2005/08 Data Mart SQL 2005/08 Analysis Services RFC ADO.Net SQL 2005/08 Integration Services Microsoft .Net Provider for SAP mySAP Business Suite

  14. SAP BW 3.5 or 7 SQL 2005/2008 Reporting Services XML/A Solution Scenario 1 Embrace BW • Overview: • Reporting Services as a client tool for SAP BI, using Microsoft .Net Provider for SAP NetWeaver Business Intelligence • Issues addressed: • SAP BW reporting • Uses SAP security (no additional layer) • Report Builder 2.0 & 3.0 as front end tools to create reports on SAP BI • Reporting Services feeds SAP data to PowerPivot in Excel 2010 thereby enabling Self Service BI

  15. .NET Data Provider 1.0 certification • Both SQL Server Reporting Services 2005 and 2008 support query on SAP BW/BI “Query” objects • We do not do table level access • We access through the SAP XMLA interface to any InfoProvider exposed. This can be a • “Query” object, an InfoCube (of different types) or a Multi-Provider

  16. SQL Server 2008 Reporting Services • New features in Reporting Services 2008 include: • New SAP connection string editor. • Support for single sign on. • Improved metadata load times. • Enhanced variable support. • Display Technical Names option: shows the SAP technical name of elements beside their friendly name. Toggled on/off by a button on the toolbar. • Collections of cubes, dimensions and hierarchies are displayed sorted alphabetically for easy navigation. • Added support for cubes containing two SAP structures: the contents of each structure (key figures and characteristics) can be accessed transparently. • Simplified SAP variable support: each report is now automatically parameterized according to any SAP variable available in the cube. • Prerequisites The provider has been developed for and tested against SAP BW 3.0, 3.5 & SAP BI 7. • Support Package 20 for SAP BW 3.5 • For Windows Integrated Authentication the provider was developed for and tested against the following systems: • SAP Portals 6.40 Support Package 20 • SAP Portals 7.0 Support Package 11 • SAP Duet 1.0

  17. Solution Scenario 2 SAP BW 3.5 SQL 2005 Reporting Services OHS SQL 2005 Data Mart SQL 2005 Analysis Services • Overview: • Use SAP Open Hub Service (OHS) to extract data into SQL Server 2005/08, from where Analysis Services and Reporting Services are used for analysis and reporting, use of Sharepoint BI tools on SQL Server Analysis Services • Issues addressed: • SAP BW reporting (re-use of business content) • Greater flexibility to incorporate non-SAP data sources • Greater support by Microsoft and third party client BI tools • Known issues: • SAP security applied only at time of export; need to maintain security in Analysis Services • Customer needs to have SAP OHS license Use BW but feed data into other DM SQL 2005 Integration Services

  18. MS Connector 1.1 Certification Download April 2009 SQL Server Feature Pack

  19. SQL Server 2008 Integration Services • Extract or load data into SAP BI 7.0 using Microsoft Connector 1.1 for SAP BI which consist of: • SAP BI Source—The SAP BI source is a data flow source component that lets you extract data from an SAP NetWeaver BI version 7 system. • SAP BI Destination—The SAP BI destination is a data flow destination component that lets you load data into an SAP NetWeaver BI version 7 system. • SAP BI Connection Manager—The SAP BI connection manager connects either an SAP BI source or SAP BI destination to an SAP NetWeaver BI version 7 system. • Note: MS Connector for SAP BI uses Open Hub Services

  20. Solution Scenario 3 SAP R/3 4.7 (running IDES) SQL 2005 Reporting Services ETL data from transaction DB (SAP) SQL 2005 Data Mart SQL 2005 Analysis Services • Overview: • Extraction of data directly from SAP R/3 using Integration Services and the Microsoft .Net Provider for SAP mySAP Business Suite into a SQL Server 2005 data mart, where Analysis Services and Reporting Services are used to deliver an analysis and reporting solution • Issues addressed: • Alternative solution to SAP BW (no SAP BW license required) • Greater flexibility to incorporate non-SAP data sources • Greater support by Microsoft and third party client BI tools • More productive ETL / Cube development environment • Known issues: • No access to SAP BW business content • SAP security applied only at time of export; need to maintain security in Analysis Services RFC ADO.Net SQL 2005 Integration Services

  21. BizTalk Adapter 3.0 Certification

  22. Connectivity - BizTalk Adapter Pack • Language: English only • Includesadaptersfor:• SAP• Siebel• Oracle Database • Includes ADO.NET providersfor:• SAP• Siebel • Available for Integration Scenarios: • Microsoft Office SharePoint Server 2007 • SQL Server Integration Services 2005, 2008 • BizTalk Server 2006 R2 • Stand Alone SKU available http://www.microsoft.com/biztalk/evaluation/adapter/default.mspx

  23. SQL Server Data Mart • SAP SD (Sales and Distribution) • Key Dimensions: • Material / Product Hierarchy • Customer / Customer Group • Sales Org / Sales Office / Sales Group / Division • Distribution Channel • Plant / Currency / Unit of Measure • Key Facts: • Sales Order / Order Line • Schedule Line • Billing Document / Item • Delivery Document / Item

  24. Data Mart Data Model Solution notes: Classic star-schema. All Type1 SCDs. Initial load only. Some complexityremoved.

  25. SAP R/3 Data Extraction • Examples*: • MARA Material Master DimMaterial • KNA1 Customer Master DimCustomer • T001W Plants / Branches DimPlant • TVKBT Org unit: sales office DimSalesOffice • TSPAT Org unit: division DimDivision • TVKO Org unit: sales org DimSalesOrg • TVTWT Org unit: distr.channelDimDistributionChannel • VBAK, VBAP Sales Order/Item FactSalesOrderLine • VBEP Schedule Line FactScheduleLine • VBRK, VBRK Billing Doc/Item FactBillingLine • LIKP, LIPS Delivery Doc/Item FactDeliveryLine *List above notes only example primary extract tables, many other text tables are used in each mapping to resolve IDs, lookup texts and construct meaningful hierarchies

  26. Scenario : Native Excel 2007 BW access – 1 of 3

  27. Native Excel 2007 BW access – 2 of 3OLE DB Driver shipped by SAP

  28. Native Excel 2007 BW access – 3 of 3

  29. New Scenario : SQL Server 2008 R2 Master Data Services

  30. Analytic And Operational MDM • Add 15 attributes to product xrb019 • Information workers can’t edit dimensions ERP - USA Warehouse or analytic app • Which is the real XYZ Corp? • Resolve XYZ Corp’s address • Does product xrb019 = fft019? Operational MDM – Difficulty coordinating multiple systems Analytic MDM – Difficulty managing dimensions • SQL Server MDS – solving for both ERP - Europe

  31. Capabilities Master Data Hub Master Data Platform Stewardship Portal • Stewardship Process API support for all operations Workflow Hierarchy and attribute management Master data maintenance Stewardship Portal Master DataHub Any domain Modeling Notifications Versioning Security Transaction logging Business rules Model documentation Stewardship Process Master DataPlatform Model deployment Import and export Validation Data quality and cleansing

  32. Inboundfrom SAP to MDS

  33. Outboundfrom MDS to SAP

  34. New Scenario : Self Service BI on SAP data withPowerPivot 2010 • SQL Server Reporting Services feeds SAP data to PowerPivot thereby enabling Self Service BI • Use of Open Hub • Excel as a source for PowerPivot • Ease of use for the business user to manipulate SAP data • Metadata, columnnamesetcalready setup in report; business user cansimply consume and analyse

  35. PowerPivot & Self Service analytics Connect to Sources, Design and author Browser Viewand Interact High quality web rendering Zero-footprint Interactive: Set parameters, sort, filter, explore Connect to data, Analyze & Report Save yourWorkbook Export/Snapshotinto Excel Open in Excel for rich exploration and analysis Open snapshots Publish: Share & Collaborate: Microsoft Office SharePoint Server Excel Excel Spreadsheet calculation and rendering External data retrieval and caching 100% calculation fidelity 100% IT monitor applications Programmatic Access Set values, perform calculations, get updated values via web services Use any Reporting & Analytic solution against the “Cube”

  36. SAP Access - licensing • Right now the official statements about licensing regarding data extracton the SAP Service Marketplace are not 100% clear • We know from partners and customers that SAP asks for some kind oflicensing - doesn‘t matter how one gets access to SAP data • For BW we know about three different kinds of licensing : - userswho do reporting ( e.g. SSRS on top of BW ) have to have a valid SAP user license - extracting data into Excel sheets and sending them around seems to require a so-called Information Broadcast license - extractingdatafrom BW into an external datamartrequiresthe Open Hub license - Using a third party tool like ERPLink to access or extract data from SAP does not require the Open Hub License. - Best to enquire from SAP about licensing matters.

  37. Security, Master Data topics

  38. SAP BW roles

  39. SAP BW roles

  40. BW hierarchiescanbe time dependent

  41. Security mapping • Especially in SAP BW - roles related to time dependent hierarchies might become a very complicated subject • Most customers who extract data from SAP to a SQL Data Mart are manually mapping BW roles which means to recreate permissions / authorizations in SQL Server • Except for third party connectors and tools like ERPLink which provide for mapping SAP BW roles to SQL Server (SSAS) • ERPLink uses a AMO interface that auto populates the SSAS Roles and data authorizations for the reflected BW InfoCube schema, accordingly. • Microsoft Reporting Tools use SAP security

  42. Master datamanagement • A lot of customers talk about central master data management but only a few implemented something ( e.g. using SAP MDM ) • It seems that for many customers the ROI isn‘t big enough • So most SAP customers consider their SAP ERP or BW system as the master copy of master data • SQL Server 2008 R2 Master Data Services could be an option here

  43. Master Data Services

  44. 3rd-party products MS-SAP integrationList of some vendors – no specific order • http://www.theobald-software.com/cms/en/haupt/produkte-l-sungen.html • http://www.everyangle.nl • http://www.erplink.com/index.asp • http://www.sitrion.com/ • http://www.simba.com/sql-to-mdx.htm • http://www.winshuttle.com/index.html • http://www.simplement.us/public/default.aspx • http://www.syskoplan.com/syskonet/index.php?StoryID=137 • ….

  45. Third party connector – theobald software Integrate your SAP BW system with SQL server. With a few clicks of the mouse, you can extract and load mass data, extraction of hierarchies, BW cubes, tables, BAPI, reports…. Source : Theobald Software

  46. ERP-Link – iNET.BI ROLAP connector A key advantage of ERP-Link’s iNET.BI for SAP connector, is that it facilitates the loading and processing of an Analysis Services cube directly from SAP R3, BW ODS and InfoCube structures, without having to persist data in intermediate relational repositories SAP BW – PS CPM. Phase 1 rollout to Commercial Marine for Capital Projects, solution feeds BW PS data to MS Performance Point Server and accessed by users via SharePoint Portal Server, eventually to be rolled out across multiple business divisions to 6,000+ users. Source : ERP-Link

  47. Large Anglo Dutch Consumer Co • Customer in production • Currently 1500 users deployed; target 5000 SQL 2005RS Local BEx SAP BW 3.5 PPS 2007 SAP R/3 4.7c ETL SQL IS SQL Data Mart Sales Cube x6 MSO BW instances 10-15TB each Country clusters (geo) SAP BW 3.5 HP DL585 Win 2K3 Svr SQL 2005 Mart: 2TB endstate Cube:<1TB endstate 3TB current 3-5TB endstate

  48. Customer Reference • Large AngloDutch Consumer Co. • Project: Sales Analysis • Requirement: • Flexible, functional OLAP solution without SAP upgrade • High degree of integration with MS Office & Sharepoint • Scenario: • Implement SAP BW as ODS • Extract from BW into SSAS using Open Hub • SQL Server Reporting Services and PerformancePoint Services • SAP Details: • SAP R/3 4.7 • SAP BW 3.5 600 GB SSAS Data Model from 6 SAP sources of 100 GB

More Related