Oracle OBIEE and SharePoint Integration (Transcript)

Raj Badarinath: Okay, I think it’s time to get started. Good morning everyone, welcome to the Wire to Win webinar series. Today, well, we are talking with Phil Hummel on Oracle OLAP to SharePoint integration. Phil Hummel is our director and heads up our Business Intelligence practice at WinWire and with that introduction, Phil it’s all yours.

Phil Hummel: Thanks Raj. To begin, my name is Phil Hummel, I am director of the Business Intelligence practice at WinWire technologies and I have about twenty slides to go over today, first one I would like to share with you is a little bit about who WinWire is.

WinWire technologies is the super specialist consulting firm, we are focused on only Microsoft SharePoint and Business Intelligence. As we move into the deck here a little bit, we are going to talk about what is Business Intelligence and you will understand why there is a potential for Synergy between Microsoft and Non-Microsoft products in the Business Intelligence space and that’s where we bridge that gap. Our focus is to help enterprise customers realize their business goals, making information actionable. That’s a big part of Business Intelligence, it’s a big part of what I do every day.

So, the flow for today, we are going to talk a little bit about the business back drop and that is why these topics are, are important than what users were telling us, we talk to a lot of customers about their portal needs, their information, making their information actionable needs and Business Intelligence needs. So, we have assembled some of those messages that we hear and some packaged them up and we will talk about that in the business back drop. We will talk about some technology considerations for how the Microsoft platform, the BI platform in particular the SharePoint can be a frontend for other technologies besides just Microsoft Business Intelligence. I have a couple of slides on why users like SharePoint. Couple of slides on why users like Microsoft Business Intelligence. We will wrap up with some information about what WinWire can do for you in this area and go to questions and answer. If you have any questions during the course of the presentation, you can, you can go to your Q&A tab on the live meeting and post those and we will, we will stop and take them and answer them over the, over the conference bridge.

So, what is Business Intelligence? I am assuming that most of the people on the call today are familiar with the industry definition of Business Intelligence, but it’s always good to in the context of the scope of what all Business Intelligence can cover is to put a picture like this together and what we see on the far left hand side of the screen are all the different line of business applications where transactions get recorded and used by customers and by employees in your organization. Now two that I have highlighted, three that I have highlighted, SAP, an Oracle, or others, most of my work in integration was in Microsoft platform has been on the SAP side. I see some really strong parallels between what’s happening on the Oracle OLAP front and that’s why I think this seminar today was important to start a discussion about what have we learned about integrating Microsoft Business Intelligence in SharePoint with other technologies like SAP and how could that apply to customers who are Oracle users. So, the different aspects so, we have the, the operations line of business applications then in the, in the top right hand part of the screen we, we do all the plumbing and all the hard work of pulling all the data from these different backend systems together into some kind of consolidated data warehouse or data marts and then we, we do some, some analysis on top of that and that’s what we call the decision support piece.

So, we have aggregated information from different sources, we have normalized things, so that customer A from SAP is the same definition as customer  A from an Oracle Financial system and now we are looking at comparing numbers overtime, by segments, byproducts and that’s decision support. The last piece in Business Intelligence and this is really just coming to forefront recently is the idea of Business Intelligence in a collaboration framework where I can have groups of people with common purposes, common business purpose share information that includes Business Intelligence information and other information documents, analysis that come from third party research and bring that information together in some type of a collaboration environment, so that users don't end up jumping from one application to another in order to get all the pieces of the puzzle together for, for making decisions.

So, why SharePoint and Business Intelligence? Of all the platforms, of all the four platforms out there, all the enterprise customers that we talk to and the research that Microsoft has done and third parties, IDC, Gartner, SharePoint is the preferred collaboration tool for business today. The market share and he growth of that product has been phenomenal for Microsoft and end users get it, they like it. So, it’s really been driven at a grassroots level, the demand for bringing SharePoint collaboration into an organization is really being driven by, by the business users. Typically, Oracle BI data is not available in the central collaboration port. So, and this is, this is similar with other portal technologies, SAP portal. What we hear from business users is I have my internet portal where I can go and get my HR information and my company news and I can have my team sites, but when I want Business Intelligence information, I have to go somewhere else to another application and I don't like that. Some of the other things that we are hearing from customers is resistance to doing multiple staging. So, I will like my information in SharePoint and I know there is really great story for linking Microsoft Business Intelligence to Microsoft SharePoint portal. But I don't want to take the data and stage it again in another data warehouse and in another OLAP technology and then surface that through a portal technology like SharePoint. Data staging leads to high data latency and out of synch data and its unnecessary complexity and certainly rip-and-replace is not an option in most organizations that have either large investments in SAP or Oracle technologies.

So, our view of Business Intelligence is that there is many different levels at which organizations and even individuals want and more importantly need to incorporate Business Intelligence into their workday. The vast majority of business users are at the transactional level and they need reports on summaries of transactions or they need reports on details of a particular transaction and so that makes up the bottom of the pyramid and there are a number of technologies that can come to play there. The source for a lot of that information outline business applications, SAP and Oracle applications, project life cycle management and CRM applications and financial applications, human resource applications. Moving up to a smaller number of users, these are the people who need higher level aggregates and they need some comparison between goals and actions. So, that’s where Dashboard start to become important and the idea of KPIs. So, if you look at, if you ask the question to many organizations, are you a matrix driven organization? Many organizations believe they are, yet their ability to make the review of those matrix and the comparison of where we are today verses our matrix available to users is really quite limited. There is a lot of work, there is a lot of spreadsheet work that goes on to dump data out of systems, take it into excel, do hand manipulation on it, to get a comparison of where am I today verses my goals.

The goal of Business Intelligence is to eliminate those manual steps and automate as much of that as possible. Moving up to the very highest levels is a concept called management by exception and this is actually starting to surface, this is the concept that we have talked internally about for a long time, but it’s actually starting to surface among some of our C level sponsors,  with some of our customers are starting to ask us, what’s available? How can we do management? I don't want to have to look at everything that I know 99% of all of my matrix are probably in the range where they are acceptable. I need filters and I need drill down information on those things that are out of [???] and I would like to be alerted and notified about things that are have been on track for a while, but now are getting off track and I really want that kind of notification system as opposed to going to a portal solution in having to review everything manually. So, now we are going to jump kind of into the technology, so, that’s what business users have been telling us that they want. I am going to address more about what we can do with the Microsoft stack but now it’s time to talk about some of the technology considerations for business intelligence and because this topic, because this particular seminar is focused on Oracle OLAP, I thought it would be important for people on the call who may not be as familiar with multi dimensional sources or Online Analytical Processing to kind of go through what the different options are for how, how we connect up to back in data systems to present Business Intelligence information.

So, on one level, we have relational data store and we Oracle SQL server, MySQL, DB2, others. This data can be structured, it doesn’t have to be just because you put data into relational data store, does not automatically impose you have a high degree of structure on top of that. That depends open the particular implementation and the, and database desire. There are limited availability of tools that can be used to discover the structure of the data, most end users cannot sit down there, there are few tools that are available today that can allow end user to sit down, connect to a relational database and really understand what he business concepts are that are in the database.

What is a customer? How do I find my customers list? What is my product list and how do I find it? There are levels of abstractions that you can put on top of relational databases to make that easier but is, it’s effort and it’s a code based, it needs to be maintained and even at the end of that process, few users find relational databases easy to use for ad hoc analysis. So, the result of all that is that relational databases for Business Intelligence continue to be a challenge for end users and don't provide the best source for end user self service. At the other end of the database spectrum are multidimensional databases. Oracle OLAP, SQL server analysis services, IBM Cognos, SAP Business Warehouse or Business Intelligence. These are a common set of technologies although they don’t all have the same implementation, they all have the same purpose and that is they are very structured data sources. You have to design your OLAP data bases around the concepts of dimensions and numbers, labels and numbers, so that for, there are lot of query tools that are available that can discover the data structure from these multidimensional data sources and makes it very productive for end users to do their own self service. So, what we are talking about today in particular is Oracle OLAP Services or server and we will make some comparisons between that and Analyses Services and SAP Business Warehouse.

So, this is a picture of the Microsoft Business Intelligence client and server stack as, as it’s referred to. So, if you are going to do a 100 percent Microsoft implementation of Business Intelligence, these would be the pieces that were available to you; Integrations Services is the ETL tool for pulling data from multiple sources, the SQL Server, Relational Database Management System for storing your data warehouse, SQL Server Analysis Services, moving up on the BI platform stack at the bottom, is the OLAP Server, the Online Analytic Process Server and the Microsoft stack.

Sitting on the top of that, is the end user tools, Excel, Report Builder part of the Reporting Services Performance Point and the delivery mechanism at the very top would be SharePoint Server where you could surface your reports, your dashboards, Excel workbooks, Analytic view scorecards, PowerPivot Workbooks in portal pages that mix and match elements of not only the Business Intelligence content, but also other content, list of contacts, documents, other related information. Hence the – it’s the opportunity and the challenge for the SharePoint designer to be able to take all of these different content sources and bring it together in ways that are intuitive to users and, and that’s the whole site design and, and portal design approach.

When we look at Oracle OLAP to SharePoint Integration, there are a couple of different options for how you can integrate with, with SharePoint. So, the, the bottom of the slide is different because it starts with either of the Oracle OLAP database or the Oracle Relational Database and then drives up through the, the platform stack and where do you end up is in the same capabilities with SharePoint depending upon which path you take up the, up the, up through the platform.

So, the first option and I am not going to cover a lot about this today, but I have the last slide in this deck is a reference slide that I have some links on to some information on Oracle’s website about using what they refer to as URL Access Methods to surface information to the SharePoint and, and Oracle has some, some whitepapers specifically to this top, to -to this topic. So, if you have an Oracle OLAP solution and you have Oracle reports that have already been, BI reports have already been developed, there are techniques for surfacing them through URL Access Methods with some availability to prioritize those reports and have those show up in, if you will, page views in, in SharePoint and then you can, then you can use the same techniques on SharePoint design to build dashboards that incorporate content from your Oracle OLAP solution as well as other sources of information.

The biggest drawback and challenge and this is not specific just to Oracle OLAP, but it’s – there are the same challenges if you look at URL Access Methods to say Business Objects for SAP, the richness of the parameterization doesn’t really allow for the ad-hoc analysis that tools that are more aware of the underlying data structures of an OLAP source are like Excel and like PerformancePoint dashboards. Those are the tools and say back to the, to the slide that we had on the comparison of relational and multidimensional data sources. It’s the knowledge of the tools to read the information about the business concepts in the OLAP source that allow end users to be productive in self service. What ends up happening in URL Access Methods is, you break that chain and you can pass parameters across and you can filter data, but the ability to discover what all of the underlying business concepts are is broken through, through, through these limited amount, the limited amount of information that’s available to be passed between the client and intelligent client on an OLAP source.

So, let’s move over to option number 2. So, this is what we – this is what I refer to as, as the, the staging approach. So, this is I, I have Oracle OLAP already in my, in my environment, but for one reason or another, I am not going to look at surfacing that directly into Microsoft Business Intelligence. I want to pull data from my Oracle back-end systems or, or any other back-end system and this is again there is direct parallels here in the SAP world. I want to go right to the ECC ERP system and pull data there, stage it into a data warehouse using Integration Services, build Analysis Services Cubes on top of that and then have SharePoint and other Business Intelligence technology connect to that. So, the downside there is additional copies of data, more storage, added complexity, multiple versions of the truth, now I have my Oracle OLAP information and I have my Microsoft OLAP information in, in Analysis Services and keeping those two in sync is always going to be a challenge and it’s never going to be a 100 percent correct. The, the advantages, the advantages are that if you, if you stack above your Business Intelligence, it’s going to be Microsoft than the integration between the BI tools, Excel, PerformancePoint, Excel Services, scorecards is best when, when the stack is, is Microsoft top to bottom.

The third approach is to go with a third party bridge. So, what I have listed here is the Simba MDX provider and Simba Technologies is a third party company, it’s not owned by Oracle, it’s not owned by Microsoft, but these guys specialize in writing bridges or they provide us, if you will, that span different back-end systems with different front-end technologies especially on the Microsoft platform. So, if you – the Parallels on the SAP side, SAP provides a multidimensional provider and MDX provider for the SAP business warehouse, that provider was also written by Simba Technologies. SAP chooses to distribute that to licensed BW users and it’s a technology that allows you to do very similar things to what we can do on the Oracle side with the Simba Provider. On the Oracle side, Oracle does not distribute that provider. It’s available only through Simba and it’s an additional fee.

So, let’s drill down a little bit more. I really want to focus more on the, on the Microsoft BI stack, the SharePoint stack, why, why users like that and then I am going to talk to you more about the, the Simba Technologies.

So, the Simba MDX provider for Oracle OLAP and, and this text comes primarily from the, from the Simba side, I have paraphrased a little bit, that directly connects Excel to your OLAP 11g, Oracle OLAP 11g data sources for ad-hoc query and analysis. So, this is a rich data provider that supports the full multidimensional query language, so that clients tools like Excel can not only show you the data, but they can also show you metadata about what business concepts are there. It allows you to gain meaningful insight from your Oracle data in the same way if you are connecting directly to Analysis Services or SAP NetWeaver BW, that’s a really important comparison and, and point to make is that, most OLAP providers provide similar concepts to, to business users. So, if you are familiar with one OLAP technology, moving from one OLAP source to another is usually a fairly easy transition to make, both for developers and for end users. It’s a low cost solution. There is, there is cost associated with it. Simba characterizes a little cost solution. You have to decide all that for yourself. The MDX Oracle OLAP provider is sold directly by Simba and there is a link to the website.

So, why do, why do business users like SharePoint so well? It’s because it really provides a single point of contact or entry for a lot of different business needs and processes, so transactional summary reporting, line of business application, self service, so vacation requests, expense reimbursements, a lot of, a lot of those transactional interactions with line of business applications can be incorporated directly into the portal, so it cuts down on the number of applications that an end user needs to log into and, and learn user interfaces for. On the, on the OLAP side, as a, as a source for, for Business Intelligence data, Reporting Services, Dashboards, KPIs, PerformancePoint, Excel Web Access to allow Excel power users to create interesting content and distribute that through the same portal technology. So, SharePoint really can be that single, mission critical enterprise platform for lots of different application integration and information sources.

Why do customers like Microsoft Business Intelligence?

So, this is an example of a PerformancePoint scorecard and related views with, with OLAP data. Once you have structured your data into an OLAP data source whether that be BW Analysis Services or Oracle OLAP, if you have the right data provider that can link these tools up, the design for these scorecards and dashboards can be done by non professional developers. That ability to structure KPIs in the data source and have those surface through report authoring tools at, at design time and then mix and match into, into dashboards with – so we have 3 panels here, we have our scorecard panel, we have some KPI details and we have reporting services reports and trend lines. This is as simple as composing the data sources for these 3 web parts and constructing a webpage with 3 different zones of putting the, putting the web parts on the, on, on the page. With Analytic views and OLAP data, this gives the end users a nearly unlimited flexibility in self service navigations through data to take pass of drilling into the data that weren’t anticipated at design time. These were built in at design time. All we did was point these data objects, these, these analytic views at an OLAP source and the functionality for being able to break information down either across one dimensional product hierarchy or time, so we have power usage by day of the week. Now, if I want to take one of those bars and I want to then drill into by customer, so I am looking at power usage by day of the week and now I will switch to a drill by a customer hierarchy, that navigation is built into the user interface, so there was no custom code written, written and there was, there was no need for the business user to do any setup or configuration for that to happen. It’s a combination of the power of the OLAP data source and the very structured nature of an OLAP data source and the intelligence of the client to be able to discover what business concepts are there and how to present those menus, so that end users can, can navigate in ways that are important to them, but couldn’t be anticipated by the, by the designer.

This is an example of Excel Services with OLAP data. So, this is an Excel workbook that would have been created by an Excel Power user and then published into a SharePoint site and hosted into a webpage, a SharePoint page, so that anyone in the organization who had access to that webpage through the browser could see the content in full fidelity rendered exactly as, as it would have been in, in Excel. It doesn’t mean you have all the functionality of Excel in the browser, but you have all the viewing capability and you have ruled out the capability and limited interaction directly through the browser. If you have rights to download this, you can also bring this workbook and its related data sources back to the desktop in Excel, do further analysis and if you choose to publish that out for, to share with other people, you can either overwrite the workbook that’s on the SharePoint site now or you could create a new version.

This is an example of an Excel 2010 either excel services, so we have here the new slicer technology and a body of report with, with fax and the ability for end users to be able to make selections through slicers or filters that were determined at, at design time and filter this information in combinations and permutations again. If you try to do this through parameter as reports, it would be very difficult to anticipate all the different collections or groups of filters that, that any group of end users might want. So, again the power of OLAP in very structured data and intelligent clients.

I am going to move a little bit more quickly Reporting Services 2008 either OLAP or relational data, rich graphics, these are, these are some of the reasons why Microsoft business intelligence, especially as presented in dashboards through, through SharePoint is becoming so popular because of the richness of the visualizations and the richness of the analytic tools that are available Excel Analytic views, parameterized reports with Reporting Services, gauges maps, lots of different chart types, trend lines, data bars to show relative contribution of data. Another aspect of reporting services is the ability to take either relational data or OLAP data in a traditional, the old traditional tabular report and then add to that elements of a pivot or a matrix report and mix and match tabular reports and matrix reports in one view that the result ends up looking something very much like what customers and Business Intelligence end users typically do in Excel. So, with a offering with a publication and subscriber model, we can now build reports that have trend lines, trend indicators, data highlighting, all the kinds of things that you would typically see in Excel workbook, a mixture of tabular report and matrix reports in one element and either published through Reporting Services or published up through SharePoint. So, the richness and the options for the kinds of reporting you can do both on from professional developer side from an IT side and also from end user authoring is really quite extensive.

Report builder gives the ability for a thick client experience to click once deploy an authoring tool down to end users pointed at a data source and where this works best is with an OLAP data source, again because of the richness of the structure of the data in OLAP and the intelligence of the, of the client tools to discover that and make it easy for customers to see what are the labels that I can put on my, on my, on my rows and columns [???] report, what are the numbers I can put in the middle and without a lot of support from IT, be able to build reports themselves.

So, we kind of understand why SharePoint is popular. We understand why Microsoft Business Intelligence is popular. We have talked about three approaches to how to marry Oracle OLAP with, with SharePoint and Microsoft Business Intelligence and now I just want to spend a few minutes and talk about WinWire. So, we really focus on SharePoint as a platform. So, for us Business Intelligence is only one dimension of what SharePoint can do. We, we work on lot of business applications, collaboration, internet, extranet, public facing websites. So, we are really a very deep SharePoint experts Business Intelligence is one of those dimensions that we, that we do a lot of work in. Microsoft Heritage, we are Microsoft managed gold partner. Many of the principles of the company are ex-Microsoft, has a history with the products and the people of Microsoft for 5, 10, 15 years and so that keeps us, that keeps us connected not only with, with what’s happening currently in the market, but also allows us to have a really good relationship and a, and a forward looking approach to how to support Microsoft products and how to support our customers using Microsoft technologies. Our execution focus, we have great customer references. We have superior client management. We use Microsoft technologies including SharePoint and our Win project management portal solution to manage our own projects with our own customers and we are the right size at the right price. We like to say that we are small enough to listen and big enough to care. So, what are we looking to get out of, of this particular workshop in relationship with, with the people who are attending today or listening to the recording? We are just going down this path of integration of SharePoint and Oracle OLAP. As you noted the, the Simba Provider is only supported on the 11g release, so it’s fairly new technology. Again, I mean, my, my background and my experience in integration with, with SharePoint and Business Intelligence is much deeper on the SAP side, but there is a lot of parallels that and a lot of the expertise that we have gained and a lot of the knowledge that we have about backend systems and especially OLAP technologies is directly applicable. So, I think, here is a proposal for a, a short six weeks to success proof of concept, how could you, how could you demonstrate to your business users and your business decision makers that this is a viable approach and something that would work well for your organization. We have done this a number of times on the SAP side and we are looking for opportunities to work with Oracle customers out there to do the same kinds of, had the same kinds of success in marrying Oracle OLAP with SharePoint integration. Our process starts with, requirements documentation. We can’t, we can’t boil the ocean and provide all of your Business Intelligence needs in a six week proof of concept or poof of technology. So, requirements definition and requirements agreement coming up with requirements that are big enough that the business users and the business decision makers can see the potential for the, for the concept that yet, not getting the scope so large that we can’t complete it in a reasonable amount of time.

The other thing that we are really good at is the visualization piece. So, typically what ends up happening is client tools and reporting tools often times get overwhelmed by the amount of information that is available in a, in a OLAP database. So, having experience about how to discover the information that’s available and this goes all the way down to, how large is the customer dimension? How many customers at each level and the customer hierarchy? How does that number reduce down as you look at, as you look at sales regions and if you look at geographic regions, so being able to have an idea of what’s called the cardinality of the data so that as we look at visualizations, there is no sense in proposing that when we do a pie chart of revenue by customer, if you have 10,000 customers in your, in your database and that may seem like a trivial example, but we often times in the requirements phase, have customers who say just that, I want to see revenue by customer. So, how do we help them come up with visualizations that start at the right level, give them graphic techniques that we are using and allow them to drill into either additional graphics or additional tabular or matrix displays that don’t overwhelm the user with too much information, either overwhelm the screen or overwhelm the user and how do we help people develop those guided data explorations given your data sources, your cardinality and the tools that are available to us. That’s a really important piece that only comes with extensive experience.

So we move, after we, after we discuss visualizations regarding your particular data needs, then we will move into design and feedbacks of, now we will actually be taking the, the mockups of what could be or actually be building those and go into an [???] process with business users to validate that our assumptions that we, that we develop during the mockup phase are actually working for, for particular business users. This is the point, now that we are actually surfacing real data, this is the place where potential data quality issues, data completeness, data models fit and business user said, well I also need this other attribute. Well, it didn’t, it didn’t make into the data model the first time around. So, what we end up doing is, we end up helping the administrators of the OLAP system understand data quality, data completeness and perhaps coming up with requirements for next versions of the, of the OLAP source to provide more richness and complete data depending upon the business needs.

The next step we move into business acceptance. So, we [???] through with some, with some power users or some, some delegates of the business who have a good vision of where, where the, how the information needs to be structured and now we  will rolled it out and we are going to do a demo to larger group and see whether this approach resonates well. Once the people see their information surface through interesting visualizations and interesting tables and matrixes in a SharePoint portal environment, especially if we can design some dashboard pages that bring content from other sources on the page together with Business Intelligence content, that’s when you start to hear, that’s when you start to hear users say, I get it, I got to have this, I need more of this.

And the last thing then would be to wrap up the project, transfer all the artifacts that we have developed, do knowledge transfer and help your developers and IT professionals understand how to move forward. So with that, I think I am ready to start the question and answer period.

Raj Badarinath: Perfect. Thank you Phil. We will now be going to the Q&A session. If there are any questions, please feel free to use the toolbar on top and click on the Q&A link. <removed>

So, I think with that, I think we would like to conclude the presentation. Thank you for your time and we look forward to having you again here with the next Wire2Win webinar session. Please watch out for details on that on our website and on your e-mail. Thank you very much and have a great morning.

Phil Hummel: Thanks a lot.