Swiss Enterprise Blog

INTEGRATION OF SAP IN MICROSOFT BI ENVIRONMENTS OR HOW TO MERGE GIANTS...

SwissEB — Tue, 19 May 2009 13:33:00 GMT

Most companies do have environments from SAP and from Microsoft as well. This article lists and explains the different approaches for integration of applications from both environments.

About SAP NetWeaver® Business Intelligence (SAP BW)

SAP Netweaver Business Intelligence (SAP BI) is the name of the Business Intelligence, analytical, reporting and Data Warehousing (DW) solution which is one of the major enterprise software applications produced by SAP AG. It was originally named SAP BIW (Business Information Warehouse) or SAP BW, but is now known as "SAP NetWeaver BI". SAP BW (Business Warehouse) is used to integrate data for other sources in the enterprise and to transform information according to defined needs. Basically speaking it’s a typical Data Warehouse Solution.

SAP BI consists among other things of components for data management (Data Warehousing Workbench), data modeling capabilities, an analytical engine, a suite of front-end analytical tools referred to as Business Explorer (BEx), and operational tools used for importing transactional data into the system.

About Integration in SQL Server Reporting Services (SSRS):

There are multiple sources describing an integration in Microsoft components. A listing is provided below:

Integration in SQL 2005 Reporting Services - http://technet.microsoft.com/de-de/library/bb508810(en-us).aspx
Integration in SQL 2008 Reporting Services via Microsoft .NET Data Provider 1.1 for SAP NetWeaver BI uses XML for Analysis 1.1 (XML/A) protocol - http://technet.microsoft.com/en-us/library/cc974473.aspx
Whitepaper describing the integration in detail - http://download.microsoft.com/download/4/7/a/47a548b9-249e-484c-abd7-29f31282b04d/UsingRSwithSAPNetWeaver.doc

About Integration in SQL Server Integration Services (SSIS):

The Microsoft Connector for SAP BI is a set of managed components for transferring data to or from an SAP NetWeaver BI version 7.0 system. The component is designed to be used with the Enterprise and Developer editions of SQL Server 2008 Integration Services - http://www.microsoft.com/downloads/details.aspx?FamilyID=228de03f-3b5a-428a-923f-58a033d316e1&DisplayLang=en

Advantages:

Cost effective Reporting Platform with SQL Server Reporting Services
Cost effective Integration Platform for further processing and analytics of data with SQL Integration Services
Low Total Cost of Administration and lower Cost of Ownership
2 vendor strategy

About integration with Excel:

Access to SAP works via ODBO. ODBO (OLE DB for OLAP) is a Microsoft's specification and an industry standard for multi-dimensional data processing which specifically designed to access to multi-dimensional data stores. The ODBO is the standard API (Application Programming Interface) that enables the exchange of data as well as metadata between an OLAP server and a client on a Microsoft Windows platform.

Access to SAP BW via Excel is described in detail in this article from SAP - https://www.sdn.sap.com/irj/scn/go/portal/prtroot/docs/library/uuid/1031a475-a633-2a10-f4b4-ccfe615c0517

Advantages:

Excel is the most widely used analytical client. For building personal bi models access to SAP via Excel can provide advantages in the area of ad hoc model building and number comparisons
Most controllers work with Excel while SAP storing and providing information about inventory, supply chain management and other important figures

About SAP R3, 4.6c, ERP, ECC

The SAP ERP application is an integrated enterprise resource planning (ERP) software. SAP R/3 is a client/server based application, utilizing a 3-tiered model. A presentation layer, or client, interfaces with the user. The application layer houses all the business-specific logic, and the database layer records and stores all the information about the system, including transactional and configuration data.

SAP R/3 functionality is structured using its own proprietary language called ABAP (Advanced Business Application Programming). R/3 also offers a development environment where developers can either modify existing SAP code to modify existing functionality or develop their own functions, whether reports or complete transactional systems within the SAP framework.

SAP R/3 is arranged into distinct functional modules, covering the typical functions in place in an organization. The most widely used modules are Financials and Controlling (FICO), Human Resources (HR), Materials Management (MM), Sales & Distribution (SD), and Production Planning (PP).

Later R/3 Enterprise was replaced by ECC 6.0. An architectural change and integration happened between the two releases with 4.6c.

About Integration in SQL Server Reporting Services (SSRS):

Microsoft SQL Server includes support for accessing SAP data by using the Microsoft .NET Data Provider for mySAP Business Suite. This provider lets you create a package that can connect to a mySAP Business Suite solution and then execute commands on the server. You can also create Reporting Services reports against a SAP server. The Microsoft .NET Data Provider for mySAP Business Suite is tested on SAP R/3 versions 4.6C and higher. Earlier versions of SAP R/3 are not supported.

Microsoft .NET Data Provider for mySAP Business Suite is part of the Microsoft BizTalk Adapter Pack 1.0 is certified by SAP as “Certified for SAP NetWeaver”. Microsoft .NET provider for SAP supports two types of commands: Execute RFCs/BAPIs and SELECT queries against SAP tables. This provider enables Table and BAPI access/execution via RFC (remote function calls) to the SAP Application Server tier.

Documentation of integration in SQL Server Reporting Services and Integration Services 2008 (SSRS & SSIS) can be found here - http://technet.microsoft.com/en-us/library/ms141761.aspx
Documentation of integration in SQL Integration Services 2005 (SSIS) via ADO.NET and through the BizTalk Adapter Pack can be found in this presentation - http://download.microsoft.com/download/3/f/a/3faddceb-a338-497a-888e-f390aab91bc6/SAP_03_SQL2k5_SSIS.ppt

About 3rd party integration tools:

As there are many SAP installations there is a market for 3rd party vendors of integration tools. Some of them are listed below:

Integration of SAP BW Release 3 and higher - Cubeware Cockpit Pro - Cubeware Connectivity for SAP® Solutions directly accesses SAP tables, BAPIs, SAP Business Warehouse - http://www.cubeware-international.com/
Integration of SAP R/3 and BI/BW Data – Access R/3 Query Objects, R/3 Report Objects, R/3 BAPIs/RFCs, R/3 Table Views, BW ODS Queries, SAP BW InfoCubes, BW Cube Queries – http://www.erp-link.com

Roland Lenz

What’s hot on SQL Server 2008 R2?

SwissEB — Thu, 14 May 2009 15:54:00 GMT

As written in the last article from my colleague Bernd (SQL Server 2008 R2 Master Data Services Master Data Management announced), SQL Server 2008 R2 is not a rewrite or rework of the SQL Server 2008 but rather provides new features and capabilities. In this article, we will take a look at some of the new features and how they translate in value. In general, SQL Server 2008 R2 delivers new capabilities to enable self-service business intelligence, multi-server management and master data services:

Master Data Services, which will ship with SQL Server 2008 R2, provides customers with a single authoritative data source to ensure the integrity of the data they use to make critical business decisions.
Microsoft’s innovative new low latency complex event processing platform allows customers to gain insights in near real time from event streams.

Other features included in SQL Server 2008 R2:

Self-Service analysis (Project ‘Gemini’) empowers users to intuitively create their own BI solutions, enabling them to easily share and collaborate on personal BI results.
Self-service reporting tools enable end users to access, aggregate, slice, dice, and report on data with minimal dependence on IT, thereby enabling them to gain information and insights that can help make faster, smarter decisions – without having to learn new and specialized BI skills.

About MDM…

The typical definition of Master Data Management comprises a set of processes and tools that consistently defines and manages the non-transactional data entities of an organization. Those data entities if often called reference data as well. MDM has the objective of providing processes for collecting, aggregating, matching, consolidating, quality-assuring, persisting and distributing such data throughout an organization to ensure consistency and control in the ongoing maintenance and application use of this information.

The purpose of using MDM in an organization is mainly not using multiple versions of the same master data through multiple systems and different parts of the operation. Other problem statements include consistency of the data used in the systems and in the organization and data classification as well.

A good article to gain understanding of MDM has been published in the MSDN from Roger Wolter and Kirk Haselden and can be found here: http://msdn.microsoft.com/en-us/library/bb190163.aspx

About MDS in SQL Server 2008 R2…

Master Data Services (MDS) shall help organizations standardize and streamline the business data customers use across their organization to make critical business decisions. SQL Server MDS is a Master Data Management (MDM) application built from platform components which may be deployed as an application or extended by use of the platform components to consistently define and manage the critical data entities of an organization. MDS is an any-domain hub that supports but is not limited to domains such as product, customer, location, cost center, equipment, employee, and vendor. Using MDS, you are able to manage critical data assets by enabling proactive stewardship, enforcing data quality rules, defining workflows around data changes, notifying impacted parties, managing hierarchies, and sharing the authoritative source with all impacted systems.

Master Data Services provides MDM capabilities that helps manage the data organizations rely on to create a ‘single version of the truth.’ In large organizations, data is generated by multiple systems and parties across organizational and geographic boundaries. If that data isn’t reconciled in a central location, decisions may be made based on data that is inaccurate, incomplete or stale.

There are a couple of scenarios for customers to leverage MDS:

Financial Application – Providing a unified and consistent view of cost center information, chart of accounts, product and customer hierarchies.
ERP Systems – Product management of 100’s of attributes, classifications and hierarchies and consistency across multiple systems.

Master Data Services includes a Master Data Hub, a set of Services, and an Interface that enables organizations to manage important data assets for both line of business and analytic applications. More specifically MDS is a SQL Server Database, Windows Communication Foundation (WCF) Services, and an ASPX application that includes:

Master Data Hub – Central Storage, Authoritative Source, Versioning, Rules, Transactions
Stewardship Portal – Model Management, Documentation, Workflow, Integration

At this point in time, it seems that MDS is going to be purely a capability built in SQL Server 2008 R2 where SharePoint will provide a foundation for meta data management. Master data describes core business entities such as customers, locations, products, and so on. Metadata is "data about other data." It’s structured data which describes the characteristics of a resource. Meta-data answers the who, what, when, where, why, and how about every facet of the data that is documented. Therefore, MDS shipping as part of SQL Server 2008 R2 will not include a dependency on Microsoft Office SharePoint Server.

Kirk Haselden, the product unit manager for MDS described the big deal about MDS in his blog: Master Data Services – What’s the big deal

The existing external roadmap for Master Data Services is available at http://www.microsoft.com/sqlserver/2008/en/us/mds.aspx

About complex event processing…

An event processing platform captures data from system-level, application-level, and external events and correlates them into patterns. CEP automates the capture, analysis, and response to activity patterns in the database. The patterns detected by a CEP system can be analyzed and used to make business decisions. The benefits vary by industry and scenario, but all are a result of the ability to process large volumes of time varying events/data with very low latency and to take action as a result. For instance, a financial institution – which streams millions of events per millisecond – can use low latency complex event processing to support algorithmic trading, monitor for data compliance, detect fraud in trading, and manage risk.

While OLTP databases have matured and are capable of processing thousands of transactions per second, they are not optimized for processing continuous high volume, low latency streams of data. A low latency CEP platform allows customers to use their own proprietary algorithms to build custom applications, ISVs to build industry specific solutions and embedded system developers to offer low latency processing.

Roland Lenz

SQL Server 2008 R2 Master Data Services Master Data Management announced

SwissEB — Tue, 12 May 2009 10:59:40 GMT

SQL2008 R2 (former Kilimanjaro) AND SQL Server 2008 R2 Master Data Services (Master Data Management) was announced on TechEd yesterday.

In June 2007, Microsoft announced the acquisition of Stratature, a privately held company based in Alpharetta, Georgia, and a leading provider of master data management (MDM) software. This was the first step in Microsoft establishing a presence in the master data management space, and will accelerate Microsoft’s delivery of technology in the MDM market. The Microsoft MDM product codename is “Bulldog”. Bulldog includes Microsoft process and standards applied to the Stratature code base as well as several important new capabilities. Microsoft customers want a solution that leverages their technology investment, integrates with their software assets, is easy to install and maintain, and provides a low total cost of ownership. Bulldog will meet these requirements while building on a long‐term vision for master data management at Microsoft.

On initial scoping, it was determined that 'Bulldog' would ship as part of Microsoft Office SharePoint in the O14 wave. At TechEd 2009, we announced a change in packaging for the new MDM capabilities. Project 'Bulldog' will now ship as part of the next release of SQL Server codenamed ‘Kilimanjaro’ as 'SQL Server Master Data Services.'

This means that in addition to new capabilities such as Self Service BI and multi-server management, SQL Server ‘Kilimanjaro’ will also provide customers with a rich platform for MDM through SQL Server Master Data Services. Customers who have purchased Software Assurance (SA) should view this as net new value and innovation that they will have access to as a result of their investments in SA.

more on MDM http://www.microsoft.com/sqlserver/2008/en/us/mds.aspx and the MDM blog http://sqlblog.com/blogs/knightreign/

in addition you can find more information on SQL Server 2008 R2 (Codename: Kilimanjaro) on http://www.microsoft.com/sqlserver/2008/en/us/r2.aspx

Bernd