SharePoint Experts

Business Intelligence (BI) with Reporting Services in SharePoint 2010

2010-10-06T12:32:00Z

This is the sixth and last article of a series to review the following five BI vehicles in SharePoint 2010:

Business reports back in mainframe and early PC days used to be tedious to generate, ill to read, and painful to share. The administration and skills needed to organize, develop, and distribute data and reports are not trivial. I can still remember my consultant days working on JCLs and COBOL for customizing business reports in various mainframe shops. Today with some key integrations and tools, it is much easier to generate reports using web services and report generator.

In SharePoint 2010, a report server can be configured as part of a SharePoint deployment. The integration is provided through SQL Server and the Reporting Services Add-in for SharePoint Products. This integration provides benefits in storage, security, and document access. Once configured, opening a report in SharePoint will behind the scene establish a session with the associated Report Server which retrieves and processes the data followed by displaying the results in Report Viewer Web Part in SharePoint. Essentially the reporting services can now be consumed directly from SharePoint document libraries with SharePoint content management and security models. The following depicts the architecture and the steps to enable this integration:

Install SharePoint 2010.
Install SQL Server 2008 Reporting Services and specify that the report server uses SharePoint Integrated mode. See below.
Configure Reporting Services.
Install the Reporting Services Add-in and configure the report server on the SharePoint including security and content type.
Verify the installation.

In addition, SQL Reporting Services is also integrated with Report Builder 3.0 which is a feature-rich report authoring tools for end users. Sparklines and data bars, maps, and indicators are some of the new features to enhance data visualization of KPIs in a report. For those who would like to learn more, there is much information readily available for mastering Report Builder 3.0.

Business Intelligence (BI) with PerformancePoint in SharePoint 2010

2010-09-27T12:42:00Z

This is the fifth article of a series to review the following five BI vehicles in SharePoint 2010

Overview
Excel 2010 and PowerPivot
Excel Services
Visio Services
PerformancePoint Services
Reporting Services and Report Builder

was a separate product. Now included in SharePoint 2010, PerformancePoint becomes a set of services configured as a service application, and surfaces itself in a web part page with Key Performance Indicators (KPIs), Scorecards, Analytic Charts and Grids, Reports, Filters and Dashboards, etc. Each of these components interacts with a server component handling data connectivity and security. This integration with SharePoint 2010 brings opportunities to better analyze data at various levels, while SharePoint security and repository framework provides consistency, scalability, collaboration, backup and recovery, and disaster recovery capabilities. One very interesting analytics tool in PerformancePoint is the Decomposition Tree which enables a user to navigate through mass amount of data in a visual and initiative way to decompose, surface, and rank data based on selected criteria. The user experience is shown below.

PerformancePoint is installed by default in SharePoint 2010. It can be easily configured as a service application in Central Admin and deployed in a SharePoint farm as shown below. Overall, this integration makes Business Intelligence much more approachable in system integration and administration. PerformancePoint planning, administration, developers and IT pros centers, and MSDN blog are good resources to find out more information.

Business Intelligence (BI) with Visio Services in SharePoint 2010

2010-09-20T16:36:00Z

This is the fourth article of a series to review the following five BI vehicles in SharePoint 2010:

Overview
Excel 2010 and PowerPivot
Excel Services
Visio Services
PerformancePoint Services
Reporting Services and Report Builder

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<:o:p>

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<:o:p>

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:

Integrated Windows
The viewer’s identity is used for authentication.
As needed, use constrained delegation to further restrict the connection to a target SQL Server database
Secured Store Service
Service account

External Data<:o:p>

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.

Business Intelligence (BI) with Excel Services in SharePoint 2010

2010-09-13T16:11:00Z

This is the third article of a series to review the following five BI vehicles in SharePoint 2010:

Overview
Excel 2010 and PowerPivot
Excel Services
Visio Services
PerformancePoint Services
Reporting Services and Report Builder

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.

Business Intelligence (BI) with Excel 2010 and PowerPivot in SharePoint 2010

2010-09-09T16:21:00Z

This is the second article of a series to review the following five BI vehicles in SharePoint 2010:

Overview
Excel 2010 and PowerPivot
Excel Services
Visio Services
PerformancePoint Services
Reporting Services and Report Builder

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:

Excel users who know how to structure, analyze, and calculate multidimensional data in workbooks and PivotTables.
SharePoint users who use team sites and document management features to store information and collaborate with colleagues.
Database professionals and IT pros who want to delegate business data development tasks to those who need the data most, but want to retain sufficient controls so that data can be secured, monitored, reproduced, and archived.

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.

Business Intelligence (BI) in SharePoint 2010

2010-09-03T14:29:00Z

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:

Excel 2010 and PowerPivot
Excel Services
Visio Services
PerformancePoint Services
Reporting Services and Report Builder

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.

Preliminary Upgrade Planning for SharePoint Foundation 2010

2010-08-14T16:09:00Z

The following table highlights the minimum requirements of Windows SharePoint Services (WSS) 3.0 and SharePoint Foundation (SPF) 2010 for preliminary upgrade planning for SPF2010. Since SPF2010 is the underlying infrastructure for SharePoint Server (SP) 2010, information presented here is applicable to upgrade planning for SP2010 as well.

Notice that the following scenarios are not supported in upgrading to SPF2010:

Upgrade from a farm running WSS 3.0 earlier than SP2
Direct upgrade from a farm running WSS 2.0/SharePoint Server (SPS) 2003. In this case, one must go from WSS 2.0/SPS2003 to WSS 3.0/MOSS2007 before going to SPF2010/SP2010.

For production deployment, please do reference the links in Official Requirements of the table to get the latest information.

Component	Minimum Requirements
Component	WSS 3.0	SharePoint Foundation 2010
Processor	2.5 GHz	64-bit, four cores
RAM	1 GB	4 GB for developer or evaluation use 8 GB for single server and multiple server farm installation for production use For large deployments, see the "Estimate memory requirements" section in Storage and SQL Server capacity planning and configuration (SharePoint Server 2010).
Hard Disk	3 GB for installation	80 GB for system drive For production use, you need additional free disk space for day-to-day operations. Maintain twice as much free space as you have RAM for production environments. For more information, see Capacity management and sizing for SharePoint Server 2010.
OS	Windows Server 2003 (Standard, Enterprise, Datacenter and Web editions) Windows Server 2008 (as of WSS 3.0 SP1)	64-bit edition of Windows Server 2008 Standard with SP2
Database	SQL 2000 SP4 SQL 2005 SP2 SQL 2008 (as of WSS 3.0 SP1)	64-bit SQL 2005 with Service Pack 3 (SP3) with Cumulative update package 3 for SQL Server 2005 Service Pack 3 64-bit SQL 2008 with Service Pack 1 (SP1) and Cumulative Update 2 installed with Cumulative update package 2 for SQL Server 2008 Service Pack 1 For large deployments, see the "Estimate memory requirements" section in Storage and SQL Server capacity planning and configuration (SharePoint Server 2010).
Official Requirements	http://technet.microsoft.com/en-us/library/cc288955(office.12).aspx	http://technet.microsoft.com/en-us/library/cc288751(office.14).aspx including software prerequisites
Download		WS2008 R2 Trial, SQL2008 R2 Trial, SPF2010, SP2010 Trial, Technical Libraries

Microsoft SharePoint Foundation 2010 and SharePoint 2010 Technical Libraries

2010-08-10T07:00:00Z

Almost everything an IT pro needs to know about SharePoint Foundation 2010 including requirements, what’s new, Getting Started, planning, deployment, and operations is in a technical library on the web and in Help (or chm) format for downloading. Similarly technical libraries of SharePoint 2010 on the web and in Help formatare also available. These technical libraries are must-have references and my recommended bedtime readings for IT pros serious about SharePoint.

...(read more)

Office Web Apps Overview

2010-08-02T15:32:30Z

Office Web Apps are online companions to Word, Excel, PowerPoint, and OneNote giving you the freedom to work on Microsoft Office documents with browsers. This screencast gives a high-level overview of the requirements to deploy Office Web Apps in a SharePoint environment, what is a SkyDrive, how you can experience Office Web Apps today, etc. Additional information is also available from the post, Office Web Apps with SharePoint 2010 or SkyDrive Explained.

(A cross-posting from Microsoft TechNet Edge)

Office Web Apps with SharePoint 2010 or SkyDrive Explained

2010-07-25T16:58:00Z

What Are Office Web Apps

The concept of Office Web Apps is essentially your Microsoft Office in the cloud. Enterprise customer can deploy Office Web Apps in a private cloud, while for Windows Live users Microsoft makes Office Web Apps available free in the Internet.The following is a screen capture of editing a presentation with PowerPoint Web App.

Office Web Apps are online companions to Word, Excel, PowerPoint, and OneNote giving you the freedom to work on Microsoft Office documents with browsers including Internet Explorer 7 or later for Windows, Safari 4 or later for Mac, and Firefox 3.5 or later for Windows, Mac, or Linux.Office Web Apps are entirely Web-based, and there's no additional software to download or install. Office documents can be created and stored in a server supporting Office Web Apps right from the browser session without the need of a locally installed Microsoft Office client.

Using Office Web Apps a user will be able to view Office documents seamlessly in the browser with great fidelity, create new Office documents and do basic editing using the Ribbon. There are however some differences between the features of Office Web Apps and the Office 2010 programs. When making changes requiring functions beyond what are available in an Office Web App, or as preferred, one can easily open and edit the document in Office locally installed on your computer, and later save it back to the server. The ability to open Office documents directly from Office Web Apps into the desktop application is available on computers running a supported browser and with Microsoft Office 2003 or a later version of Office (for Windows PCs). This functionality will also be available on computers running a supported browser along with the forthcoming Office for Mac 2011.

What Is SkyDrive

A free, password-protected online storage available with a Windows Live ID by Microsoft, SkyDrive is. With a Windows Live ID, a user can store up to 25 gigabytes (GB) of files as of July, 2010. The upload operation accepts a file up to 50 megabytes (MB) in size. A user can arrange files with folder and subfolders, and keep private files in the personal folder while placing those to be public in a shared folder. To share a folder or individual file, a user can set permissions accordingly followed by inviting others with email. Shown below is one way to create Office documents in SkyDrive.

Although SkyDrive provides a location for storing files online, it is nevertheless not an FTP site, nor does it function with an FTP client. Further Microsoft may limit the number of files that each user can upload to SkyDrive each month. Individual seeking support on SkyDrive can participate the conversations and look for answers in SkyDrive Forum.

Office Web Apps , SharePoint, and SkyDrive

For enterprise customers with on-premise SharePoint installation, Office Web Apps require SharePoint Foundation 2010 which is free from Microsoft. On the other hand, Office Web Apps does require volume licensing. Office Web Apps can present Word, Excel, and PowerPoint files on many devices. Supported mobile viewers for Office Web Apps on SharePoint include Internet Explorer on Windows Mobile 5/6/6.1/6.5; Safari 4 on iPhone 3G and 3GS; BlackBerry 4.x and later; Nokia S60; NetFront 3.4, 3.5, and later; Opera Mobile 8.65 and later; and Openwave 6.2, 7.0 and later. To roll out the services in an enterprise environment, TechNet has documented specifics including planning and deploying Office Web Apps.

For consumers, Office Web Apps are part of the Windows Live offerings. A user with a Windows Live ID can user Office Web Apps to create, upload Office documents which are stored in SkyDrive. Supported mobile viewers for Office Web Apps on Windows Live include Safari 4 on iPhone 3G and 3GS, and Internet Explorer 7 on the upcoming Windows Phone 7. Viewing Excel files via a mobile browser is currently only available with Office Web Apps on SharePoint 2010.

Start Using Office Web Apps with SkyDrive Today

A supported browser and a Windows Live ID are all you need to create, view, edit, and share your Office documents in the cloud. Your teammates can now work with you on projects regardless if they have a locally installed copy of Microsoft Office.

(A cross-posting from http://blogs.technet.com/yungchou/)