Phil Hummel, BI Practice Director at WinWire will be discussing best practices for leveraging SharePoint with SAP-BI.

Summary:

• Common scenarios for SAP-BI to MS SharePoint Integration
• How to select the right set of tools across many data sources
• Provide business users with a single interface for analytics and collaboration
• How to jumpstart integration projects with SAP

Wired2Win Speaker:

Phil Hummel is currently the Business Intelligence Practice Director for WinWire Technologies in Santa Clara. He's responsible for client relationships and delivery of consulting services exclusively focused on the Microsoft Business Intelligence platform. Prior to joining WinWire Phil spent 8 years working in Microsoft Consulting Services and the Microsoft Enterprise Sales Group as a data platform architect.

Phil Hummel (Speaker)::

The title of today's presentation is SAP BI to Sharepoint integration.

So, a quick slide on who WinWire is. WinWire technology is a super specialist consultant firm focused only on Microsoft SharePoint and business intelligence. Our headquarter is here in Silicon Valley. We have development officers in Bangalore and Hyderabad, India. Our focus in life is making information actionable and you will see that both through our, our SharePoint offerings and through our business intelligence offerings. So, here is the flow for the meeting. We are going to talk a little bit about the business backdrop for SAP environments and a slide on WinWire’s solutions approach, how we have discussions with customers around their needs. Most of the talk is going to be in these next 2 boxes; technology considerations and a client case study. I have one slide towards the end on the WinWire and then we will go to Q&A. So, very low on marketing, very high on technical content.

So, being here in Silicon Valley, we have access to a lot of customers who have SAP and a lot of high-tech manufacturing here. So, we get to have a lot of conversations with customers who are using SAP or considering SAP and some of the things that we hear frequently in SAP environments, especially from the business users is that SAP data is not available at the point of interaction and what they mean by this is the applications that they use most frequently, it’s difficult to surface SAP data into those applications. So, if they need matrix or if they need business operations information, they have to leave the environments that they typically work in and go somewhere else to get that information and that’s a hit to productivity. Another thing that we are seeing a lot in obviously non SAP shops as well as SAP shops is a focus on SharePoint as a preferred collaboration tool. I was recently in a Microsoft event and they presented results of an IBC survey from December of last year. It was 920 enterprise customers survey from US, France, UK and Germany and  SharePoint was the number one choice for enterprise-core technology in the survey. Even SAP is adding a lot of functionality to integrate with, with SharePoint as well as they are beefing up their own NetWeaver enterprise portal to have more capabilities than just SAP data. So, the trend is clear that people want a common platform for business, line business application and other types of knowledge worker applications in one place.

The other thing we hear in SAP shops as well as others is that traditional BI solutions, third party solutions that drive on top of SAP are traditionally too heavy, too long to market and by the time the solutions get implemented, business needs have changed and again you went through the same problem that the data is not at the point of interaction where you want to be.

So, this is the discussion that I would like to have with customers about business needs that’s technology agnostic. If you see on the right hand side we are talking about SAP ECC, but that could be underlying the business applications as well, but the real focus of this is to ascertain from customers where are they and where do they want to be and this is presented in a pyramid with a hierarchy. I don’t expect that all customers are going to start at the bottom and work all the way up through this hierarchy. Some people are going to start in some places and other people are going to start in other places. I do caution that if you want to jump straight to the top of the pyramid and you haven’t done any of these other things, you are going to uncover a lot of works and problems along the way and so starting at some lower basic needs and getting those filled, we will address some of the issues of data availability, data quality, those kinds of issues before you try and tackle some of the more complicated management by exception and information to improve business.

So, what do these boxes mean? Transaction process and what controls in an organization. This would be an example of where we would have a vacation request form. The backend processing is done by SAP. We want, we want end users to be able to go in through a forms-based application, make requests, have the transaction check SAP for available balance. If the available balance is there, have it routed to a manager for approval and then have the, the result of that approval recorded back in the line of business application like SAP. Reports to transact business: These will be largely non-interactive reports, lists, list of numbers, lists of customers, showing all the purchase orders that are more than 30 days back ordered; those kinds of operational reporting needs. When we hit the dashboards to manage the business, now we are talking about having the business specified goals and comparing actual values to goals, things like, key performance indicators and trend tracking.

The next layer up, information to improve the business: So, now we have a good reporting framework in place. We have identified goals. We find certain areas where we are not tracking to the goals, now how do we bring other information to the dialog related reports, comments to the artifacts from the business intelligence solution to start working out a collaborative solution, what are we going to do about this and at the very top of the pyramid, management by exception, these were for executives who don’t want to see all the scorecards with all the green lines. They are the one who know about the things that are going wrong. So, how can we provide senior management the ability to specify what things they want to be notified about and then have an application that will satisfy those needs for them. So, let’s talk about how SharePoint plays in this and I want to be careful that I don’t make it sound like I think SharePoint is the universal solution of business partners, but I do believe honestly that the flexibility of the platform allows me to address every one of the layers in that previous needs hierarchy. So, if we talk about things like transaction controls and reporting, how would you do an employee self service vacation request? You would be using business data – business connectivity services through SharePoint  2010 and the business data catalog through Sharepoint 2007 to do integration with transactions at the SAP level and in workflow on top of that, potentially pooling into active directory organizational hierarchy information to figure out who needs to do the approval and so the platform is flexible enough and has all the tools available to make this a reality, self service transactions back to SAP with value add, with controls and reporting directly in the common portal where people will be.

How about operational reporting? We have SQL reporting services and I list that, I love that in the SharePoint, because reporting services has a lot of operation where it can be fully integrated with SharePoint such that all the management of your report libraries and security and data collections are all managed through the SharePoint set of administrator and so your SharePoint administrators can work with reporting services objects just like they were any other class of document. So, between SQL server reporting services and again the business data catalog in 2007 or the business connectivity services in 2010, I can present in the reporting services, I can present read only copies of the data in the business data connectivity services and in Sharepoint 2010 I can present the list of data with read/write capability. So, all of those operational reports tied back to live data directly out of ECC in the portal where end users want to spend their time.

Unified dashboards and KPIs; Sharepoint has performed on the server as KPI lists a number of different ways to mix information that is actual data out of your ERP system or SAP ECC together with goals and targets that are specified by the business that necessarily wouldn’t live in the line of business applications. So, it’s a platform that allows you to bring those actions and goals together and have that presented up to business users.

Management by exception, certainly not an easy task and it’s not something out of the box for SharePoint, but again since SharePoint is a platform that allows me to mix out of the box controls and web parts that come with the, that come with the product and augment that through custom code, I can define forms that allow business users to specify what they want to be notified about, use the business connectivity into SAP to go find the, the actual values for those, do the comparisons and have custom dashboards for people who go and see a list of the things that are out of control or out of control and it’s based on controls they already sent. So, I firmly believe that the flexibility of the platform gives me the capability to satisfy all or any of those needs.

So, let’s drill down a little bit more on the BI technology specific. So, I have 4 scenarios across the bottom here. The first one is related to the self service. So in, in my example so far, this would be the vacation request form. I am not going to talk any more about that. We have covered that fairly extensively and this is really a BI talk. So, let’s move over to scenario 2.

So, scenario 2 is a technology option where the customer would like to have analysis services, the multidimensional or OLAP database for SharePoint and how can we make that happen? So, both vendors, mobile business vendors necessarily like you touching their relational structures directly and SAP is as adamant about that as anyone else. So, you have to be very careful about if you are going to go get data directly from ECC, from the relational structure that you do it in a supporting way. There are a number of third party intelligent ETL applications that will assist you in that. So, what you are, what you are going to implement there then would be some type of ETL process into another relational format optimized for building analysis services cubes and then build analysis services cubes on top of that. There actually is a path going through ECC to BW and directly through integration services into the relational star schema for your data mart for building cubes that would require open hub connectivity services but it’s technology that is out of the box and, and license the SQL server through integration services and you might ask the question, why on earth, since I already spent the effort to organize my information in the OLAP store in BW, why would I then turn around and stage the data one more time in a relational format and then build analysis services cubes? And the answer to that is because of the components in SharePoint that are tied strongly to analysis services cubes. So, when you look at the PerformancePoint product that prior to the 2010 release was an additional add-on to SharePoint. In 2010, all PerformancePoint capabilities have been rolled into the base product. You look at the PerformancePoint dashboards, PerformancePoint analytic views, scorecards with multi-dimensional drill-down capability; all that functionality is tied exclusively to analysis services cubes. That’s technology that came out of the ProClarity acquisition from several years ago and ProClarity was all about making analysis services information lined up in portals and in scorecards and so a lot of the capability of the PerformancePoint product is really tied very closely to having your data in analysis services cubes to start. You have to be careful and we talked about this a little bit in the slide about the business backdrop. Anytime you are going to stage data multiple times, you have to be concerned about latency and freshness in the data. So, there are downsides, complexity and latency issues are definitely downsides. Once you get the data into analysis services though, there certainly is a lot in the, in the SharePoint platform that can leverage that.

So, let’s look at scenario 3 where you potentially want to talk directly to SAP BW and you don’t want to state your data a second or third time and this is the focus - scenario 3 is the focus of the case study that I am going to present in just a couple of slides. The complexity here is around the availability of data connectors for SharePoint technologies to talk directly to BW. So, in the 2007, in the SharePoint 2007 world, SAP produced what’s called an OLE DB for OLAP data and ODBO driver that allowed Excel services and reporting services to talk directly to BW. The downside was that that was a 32 bit only connector. So, if you, if you had 64 bit SharePoint deployments, you couldn’t use the 32 bit driver. The complicating factor is in 2010. SharePoint 2010 is 64 bit only and so far SAP has not rode the ODBO driver to a 64 bit version. So, as of today we don’t have a supported driver from SAP for talking to a multi-dimensional source from things like PerformancePoint and Excel through Excel services. What we do have is an XMLA interface that SAP provides that we can talk to through reporting services. So, reporting services in both the, in both the SQL 2008 and 2008 R2 releases, ships with a connector for the XMLA interface on the BW side that allows you to do reporting services directly against SAP BW cubes. The design experience for the most part is identical to talking to analysis services. So, all the nice drag and drop features of dimensions and key figures, if you are on the SAP side or measures and dimensions if you are on the analysis services side; the design experience is just as nice against BW is in analysis services. The other thing that reporting services in the 2008 R2 release provides is the ability to publish data from a SQL server reporting services report as a form of XML feed called an Atoms feed and these Atom feeds can then be picked up by other applications, most notably the new PowerPivot technology that’s offered through Excel’s 2010 client and SharePoint 2010 server to be able to do rich analytics and much more ad-hoc analysis that you can through reporting services with data that is fed through an XML feed from the SSRS side and again that’s something we did in the case study that I am going to talk about here in just a minute. So, for right now the best options that we have especially on the 2010 platform, if you are on 2007 32 bit platform, you can still have Excel services, you can have XMLA, if you are on either 64 bit 2007 platform or 2010, which is always 64 bit, the best options there are reporting services and PowerPivot.

The fourth tab is coming up through the business objects and crystal and other business objects technologies into web parts that live in your SharePoint infrastructure and SAP produces a number of web parts and SharePoint integration kits for getting business objects reports directly into your SharePoint environment. So, you need to talk to a number of customers that already have a big investment in business objects, but they want their content, they want their reporting content to be able to be deployed into dashboards and into web design integrated with the rest of the nice features that you get in SharePoint presence if you are using unified communication technology team sites, more collaborative features and so that’s another interesting option for integrating SAP BW or ECC data directly into your SharePoint environment.

So, this is what a 6-week proof of concept would be. This is based upon a recent project that we have with our customer giving idea of how long it might take for a business to be able to get something up and hosted and get the business users to evaluate what the capabilities of the SharePoint and Microsoft BI stack on top of SAP data looks like to get a feel for whether it’s a rich enough environment, whether it’s, it’s something that end users and business users would be interested in. So, we started out with a week of requirements gathering. We talked to both IT and we talked to both the SAP experts and we talked with the business experts to find a good common ground between what was technically achievable and available and what was of interest to the business and we focused on this particular proof of concept around the sales and distribution cube technology in BW.

The next step is we went to a visualization stage where, without working directly in the SharePoint infrastructure, just doing Excel markups, because we are doing, because we knew we are going to potentially end up in PowerPivot, we did some PowerPivot markups and we did some just pencil and paper markups and visual markups for the reporting services pieces just to get an idea from the end users of how they want the information presented. What do you want on the rows? What do you want on the columns? You want pie charts? What’s the legend going to look like? What, what numbers do you want on the body of the report and at the same time that allows us to start doing query development that would satisfy those, so we can address issues like data quality, data completeness and feasibility. We had one example of where the customer came and said, well, I just want a list of all my customers on what they bought in the last 3 years. It sounds like a fairly simple request, but when you have 10,000 or 20,000 customers and 20,000 skews, that’s not the usual report. So, now we have to kind of look at the cardinality of these dimensions in BW and have to be able to come back to the business and say, okay that intersection is really huge, we have to start filtering this and then, you know, you can see subsets of that data and about this much of full screen or 2 screens or 3 screens [???] and so we have to start that negotiation, that design process.

Then in the third phase in the design and feedback, we actually start creating the SharePoint objects, the reporting services reports and the PowerPivot objects and we iterate on those. We take an initial cut to the design, feed it back to the users, get their comments, change the filters, move filters around, change the drill-down, change the navigation and then we move into a weaker business exceptions where what’s dominating to the business was real data and real reports and then we have a project wrapped up knowledge transfer and we talk to some of the customers people, the ability to create objects on their own, how to do the PowerPivot and we and [???]. So, the customer who worked with in, this case study was  Omnicell a medical device manufacturer. You can read some statistics about in there. I think some of the words that cover up a little bit by the graphics and you get the idea of where they are. So, the technology scope that we looked at, they had both CRM – SAP CRM as well as SAP ECC and they have BW. The BW implementation is fairly [???], it’s mostly in the sand box, it’s mostly under development at this point. So, part of the, part of the proof of concept was actually taking the existing sales and distribution cube they have and enhancing it for some further key matrix and some further key figures and dimensions. We ended up not trying to merge CRM data and ECC for this 6 week project. So, we used directions directly from ECC and BW around the sales and distribution cube. We knew that reporting services would be a big piece so that it gave us the ability to offer prioritized reports and easily accessible SharePoint environment. We could mix and match facts and dimensions from various parts of the, of the sales and distribution cube in a dashboard like view through SharePoint, by having multiple report view or controls on one page that brought together charts and tables from different matrix together in one place where, where users could compare and see the correlations between different variables. So, even without  full blown PerformancePoint dashboards, we were able to – just using SharePoint page designer and reporting services, we are able to give the look and feel of dashboards that, that the users ended up liking a lot.

The report builder for Self-Service: this is an interesting scenario. So, it sounds kind of, it sounds a little bit roundabout and it is, but we actually showed the business at the end of our proof of concept. We built a power pivot model off SAP data being fed through reporting services and XML feeds through reporting service, publish a PowerPivot model up to SharePoint and then one other things that you can do is you can use report builder to connect to a PowerPivot model in SharePoint and have users take the concepts out of that data feed and arrange it in tables and charts in unique ways that the original designer has notified. So, we are streaming data from SAP BW into an XML feed, into a PowerPivot model on the server and then having end users connect to that with report builder and do ad-hoc reporting. The other thing we looked at was we were, we were hoping that we were going to get a preview of the data driver, the multidimensional data driver for SAP BW, but apparently it just it’s, it’s not available to anybody right now. So, there has been a couple of commitments to, to having a driver for PerformancePoint after 2000, after performance, after SharePoint 2010 launches. There are some third parties out there if you, if you look at different software and there are some others that are developing. There are people who are developing multi-dimensional connectors between BW and PerformancePoint and Excel services, but our particular customer was anxious to not add third party technology to the mix if they didn’t have to. So, there are in kind of an evaluation model to see what can we do today with, with technology that we get directly from Microsoft and SAP and then what’s going to come in the future – future decisions about how to expand this at a later time and of course SharePoint was a, was a key part of our technology choice – access to dashboards, access to reports. We ended up not using Excel services, but we use the PowerPivot services of SharePoint 2010 extensively.  

So, here is an example of a PowerPivot display. This is a screenshot actually from a web browser, connecting up to a PowerPivot model of the SharePoint site and what we have in the, in the, in the gray area here is the body of the report. So, we have our numbers and, and, and key figures. We have some drill-down here on a, on a particular dimension and then across the top and down the left hand side are filters where our customers, business users call filters, but in, in Excel 2010, in Excel 2010 lingo are called slicers and these have the ability to slice and dice this information in an ad-hoc way so that the numbers in the report body filter reflect the selections that you make through these, through these various combinations of slicers. One of the really nice things about these slicers is if you look at sales office for, for example, if I select 1 or 2 categories of the sales office, the relevant populated cells for the rest of the dimensions that are representing the slicers will bubble to the top of those display, so I can immediately see where data is actually populated. So I may not have all of these customer groups represent the re-sales office, so as soon as I select one of the filters it will automatically gray out the options that are not relevant for those selections. The business users love this technology. It’s very intuitive to them. It supports shift click and control click for multi select options. It gives a lot of flexibility about the layout of the slicers. If you see the, the table format for the customer group and the list format for the sales office and a lot of flexibility about how you build these slides or display and because it’s Excel, very familiar to the users, we are really excited about the, the, the take up of this user interface by business users who aren’t necessarily BI tool savvy.

Here is an example of a standard kind of SQL server reporting services report, three dimensional bar charts, the standard reporting services, viewer control that gives you the capability to do paging, scrolling and export to different formats. The thing that users like about this, people who don’t want to do slicing and dicing, ad-hoc slicing and dicing, there is a large segment of the user population out there who could tell you the numbers they want to see and they want, they can tell you within 2 or 3 clicks how they were to drill-down into this information and that’s, that’s the way they want to see it all the time. It’s part of their job and that’s what they do and that’s how they think about the day and that display, that development is so high value and so high productivity that we can really present a lot of information in a short amount of time if we get the right requirements gathered and processed upfront to understand what the navigation is and what the top level numbers are, the customers want to see and how they are going to drill down into it and I can say, I can then build dashboard effect pages by combining multiple reporting services reports in a single page. So, this is – oh, I just looked at my Q&A and alright, someone started to ask a question and, and bad luck, alright. So, this is by one marketing slide. I can add one slide upfront told you we are in and we are and this is, this is one, one marketing slide about why WinWire is different than perhaps other consultant companies you have worked with.

We would like to focus on the fact that we are smart enough to listen and big enough to deliver and we are really focused on Microsoft technologies especially around SharePoint and business intelligence.

For slides, please email marketing@winwire.com.