BI is a concept encompassing many areas of IT and like many other IT terms, it means different things to different people. One simple definition of BI is “Using of analytic and visualization tools to better understand and interpret data.” Recently there have been active discussions on BI as a priority in CIO’s list. Interestingly, the more we talk about BI, it seems the bigger the scope BI has. Indeed, PowerPivot, Excel Services, PerformancePoint, etc. these tools and features can sound confusing and overwhelming. To better understand BI, I find a great review discussing How SharePoint 2010 brings BI to the next level and a nicely done poster, Getting started with business intelligence in SharePoint Server 2010, are both very interesting and informative.
Notice there are three areas of BI, i.e. at individual, community, and organizational levels. SharePoint 2010 addresses these areas as a whole with various vehicles including: Excel and PowerPivot Add-in, Excel Services, PerformancePoint Services, Visio Services, and Reporting Services and Report Builder as depicted below. And it is important to keep the context in mind of a BI scenario that is being assessed such that the best vehicle, namely right tools and the best-fit features, will become evident.
(Source: Getting started with business intelligence in SharePoint Server 2010)
This is an overview of a series of articles to review the following five BI vehicles in SharePoint 2010:
Also I highly recommend reviewing a great series of Office and SharePoint relevant content publsihed by Dan Stolts, one of my fellow Evangelist based in Boston area.
(A cross-posting from Microsoft SharePoint Experts Blog)
This is the second article of a series to review the following five BI vehicles in SharePoint 2010:
Excel 2010 and PowerPivot introduces a fascinating integration. PowerPivot, a data analysis tool and free add-in to Excel 2010, gives users the power to create compelling BI solutions right at from an individual's desktop. With this add-in to Excel, a user can transform mass quantities of data with significant speed into meaningful information to facilitate decision making. Excel with PowerPivot is, in essence, a user-driven, self-service solution model with minimal infrastructure dependency. For those who need a quick and user-driven BI solution with minimal infrastructure dependency, Excel 2010 with PowerPivot is a great candidate.
PowerPivot, natively supports various data stores, SQL Analysis Services, and data feeds as shown above. Once in place, PowerPivot has an in-memory engine capable of processing millions of rows of data with impressive performance. At minimum, 1 GB RAM is expected to run PowerPivot. The actual RAM needed will obviously depend on the amount and business logic that PowerPivot carries. The computing power is delivered directly within Excel with a consistent user experience. At an operational level, an Excel user will be able to employ PowerPivot very easily with just routine Excel end-user operations, i.e. mouse-clicks, cut-n-paste, etc. It's a very cost-effective way to analyze large amount of data for achieving business insight and shortening decision cycles. There is also an integration of PowerPivot with SharePoint 2010 (see below) that scales this self-service BI model to an enterprise level.
PowerPivot for SharePoint adds services and infrastructure for loading and unloading PowerPivot data. After creating a PowerPivot workbook, one can save/publish it to a SharePoint server or farm that has PowerPivot for SharePoint and Excel Services installed. This adds collaboration and document management support for a published PowerPivot workbook. In SharePoint, while the PowerPivot services processing the data, Excel Services renders it in a browser session. The SharePoint integration enables users to share data models and analysis. By configuring refresh cycles, the data can stay current automatically. Further, a published workbook may become the basis for Reporting Servicesreports created by other authorized SharePoint users, repurposed in other PowerPivot workbooks, or linked to from different sites, possibly in different farms. There are many interesting business scenarios and possibilities with PowerPivot. To learn more, Http://powerpivot.com/ is a great resource.
Is There A Need for Processing Millions of Rows of Data
A key delivery of PowerPivot is the ability to process millions of rows of data. Here the amazing capacity of sorting more than 100 million rows on a desktop delivered by Excel 2010 and PowerPivot is shown below.
In some of my TechNet events, a few IT professionals nevertheless told me they would never need to process millions of rows of data in Excel. I was not surprised with this response and, in a way that was very true.... till the introduction of PowerPivot. In my view, the capacity and performance limitations then existed in hardware and software making it not practical to process extremely massive amount of data in a desktop environment. That was not because there had had no need for processing very large amount of data. Companies would need to spend a lot of time and money; contracting it out or having a developer team to develop, produce, and maintain reports for making business decisions. I can vividly remember in mid 90's while working as a consultant, many of my engagements were to fix business logic and improving the performance of COBOL reports based on large amount of data. When it comes to statistical models, demographics, trend analysis, optimization, information portal, etc. data will never be too much and the demands have been always there. The difference is reports used to take hours of a team of specialists and operators to implement and much CPU time to generate, now available in seconds at the finger tip of an information worker running Windows 7 desktop and Excel 2010 with PowerPivot.
And just like many of us once argued 1024x768 resolution would be more than enough for word processing and email routines, while today few works with a low resolution screen anymore. Not too long ago, I thought Instant Messaging was counterproductive, while today it is a necessity for me to be productive and take care of business every day. So, does everyone need the ability to process millions rows of data? Maybe not, not yet. I do however believe as PowerPivot is becoming a standard add-in for Excel 2010, businesses will soon expect the ability and performance to process extremely large amount of data from multiple data stores are and will be readily available with a PC desktop. The question is and will not be if data can be analyzed, but what to analyze and how good the analytical model is. Above all, PowerPivot is for:
The empowerment through a self-service model to derive business intelligence right on the desktop and the immense capacity offered by PowerPivot as a free add-on, together makes this solution a must-have for conducting data analysis.
This is the third article of a series to review the following five BI vehicles in SharePoint 2010:
First introduced in Microsoft Office SharePoint Server 2007, Excel Services provides server-side calculations and browser-based rendering of Excel workbooks. On the right is the architectural concept of Excel Services. In the core is Excel Calculation Service (ECS) which is the calculation engine. Excel Web Access (EWA) is a web part which displays and interacts with a workbook. The access to methods and objects is through APIs provided by Excel Web Services (EWS) hosted in SharePoint Services.
Excel Services allows a user to publish a workbook or selected spreadsheet cells as a webpage. Because the content is published without exposing the underlying business logic, intellectual properties are protected and as well applied in a standardized/consistent fashion. The motivation is to publish "one version of the truth" such that users always view a consistent set of values if published as read only, and results derived on business logic that is consistently defined. In a large organization, consistency and synchronicity are key productivity enablers which are many SharePoint features are about. Both Excel 2010 and Excel 2007 have the ability to publish an Excel workbook to a SharePoint site.
By naming selected cells in an Excel workbook, an author can then indirectly letting a user change the cell values and apply them as parameters of an analytical model. For example, as shown on the left, a user provides interest rate, loan period, and loan values in the Parameters Task Pane to calculate a monthly mortgage payment. While any of the three parameters varies its value, the derived monthly mortgage payment changes accordingly. Since the business logic, i.e. formulas, embedded in these cells are not exposed; Excel Services can display the results with the business logic implemented in a consistent and protected way. In this example, the mortgage calculation happens to be a well-known formula and the protection may appear trivial. However in a production application, this may be a work order estimate or a marketing program discount rate calculator. In this case, Excel Services not only can protect the underlying business logic perhaps based on proprietary knowledge, but as well ensure the logic is applied in a consistent and predictable fashion.
In other words, in addition to publishing one version of the truth as read-only data like KPIs, charts, and tables, Excel Services can also allow a user to enter values as parameters to a protected analytical model and carry out what-if analysis.
This is the fourth article of a series to review the following five BI vehicles in SharePoint 2010:
A picture is worth a thousand words. This cannot be more applicable to what Visio Services can deliver. A feature of SharePoint 2010, Visio Services enables data-bound Visio drawings to be viewed in a web browser. This feature is for sharing Visio drawings and letting authorized users view Visio diagrams in a SharePoint library without having Visio or the Visio Viewer installed on their local computers. Visio Services can also refresh data and recalculate the visuals of a data-connected Visio drawing hosted on a SharePoint 2010 site. So a user will always see the latest and up to date information in a visual form. For instance, a complex manufacturing supply chain can be presented with clarity and simplicity, and up to date status with Visio Services as shown below. A Visio Services overview is a good starting point to better understand this feature. And the installation and administration of Visio Services are very easy to follow.
Visio Services can display Visio drawings using a Web Part without having a locally installed Microsoft Visio 2010 on the client computer. However Visio Services is not for creating or editing Visio diagrams. To create, edit, and publish diagrams to Visio Services, an author must have a locally installed Microsoft Visio Professional 2010 or Microsoft Visio Premium 2010.
Licensing
Available only with SharePoint Server 2010 Enterprise Client Access License (ECAL), Visio Services must be deployed, provisioned, and enabled before first use. In addition, one must have Microsoft Visio Professional 2010 or Microsoft Visio Premium 2010 in order to save diagrams to SharePoint as Web drawings.
Access Control
To view a Visio drawing based on a SharePoint list or an Excel workbook connected to an Excel Services, a user must be authenticated and authorized by the SharePoint 2010 hosting the content. And three authentication methods are supported:
External Data
While developing enterprise service architecture, planning for services that access external data sources is something not to overlook. For a service application as one the following using a delegated Windows identity to access an external source, the external data source must reside within the same domain with the SharePoint 2010 farm where the service application is located or the service application must be configured to use the Secure Store Service.
Namely Delegation of a Windows identity, Windows domain, and Secure Store Service are a few things to keep in mind if a service application to access a data store beyond the SharePoint farm where the service application is running. In other words, do the right thing to plan your Visio Services deployment.
September, 2010 - Yung Chou on Hybrid Cloud - Site Home - TechNet Blogs