This chapter will look into how Business Intelligence is integrated into the SharePoint 2010 platform, starting with an overview of the basics of Business Intelligence, introducing the new additions made available to Business Intelligence in SharePoint 2010, exploring how to optimize your Business Intelligence Resources to get the most out of them, followed by Web Parts and the enhancements that are readily available for your use. This chapter will also review the new features in Excel Services, talk about planning PerformancePoint Services, and culminate with assessing which Reporting Services type of mode is suitable for your needs.
If you are already familiar with the basics of Business Intelligence, feel free to move directly into the topic of your choice or interest.
The term Business Intelligence has been a buzz word for some time. Buzz words lose their true meaning with the passing of time and thus cause confusion, so let’s start by defining the meaning of Business Intelligence.
Business Intelligence is understood as a variety of methodologies and techniques used in identifying, gathering, extracting, analyzing and providing data to empower users in the decision making process.
Why has Business Intelligence become such an important focus for businesses? It has steadily attracted the attention of management executives. To cite a few reasons, it can
When the amount of time required for decision making has been reduced, the possibility of impacting the bottom line increases as the company’s efficiency and processing times areboosted by enhancing and providing a competitive advantage against their competitors. As a result, businesses have realized that it is no longer a luxury but a worthwhile and essential investment.
Business Intelligence within SharePoint has grown since the previous version. In fact, there has been a considerable investment and expansion to what is known as the self-service Business Intelligence arena.
The self-service Business Intelligence area has consolidated a previously known product named ProClarity with PerformancePoint; the result is called PerformancePoint Services within SharePoint 2010. Likewise, Excel Services has grown more robust since its previous implementation in Microsoft Office SharePoint 2007; it is no longer confined to data within Excel thanks to the integration of PowerPivot with Excel. Excel Services can now gather data from multiple sources and perform calculations on large amounts of data very quickly. Moreover, it includes a new and improved Excel Web Access Web Part that Reporting Services has made available; as a result, elegant and visually appealing charts that were not available before in SQL Server 2008 are now possible.
The Business Intelligence Center has been revamped and includes dashboards and template options; you’re no longer confined to a predetermined template. A new Chart Web Part allows charts to be generated from data in SharePoint Lists, plus new and improved Reporting Services Web Parts are AJAX-enabled.
There are even two new additions to the Business Intelligence area: Access Services and Visio Services. The biggest advantage of these new additions is that they don’t require Microsoft Access or Microsoft Visio on the client computer; also, both are available through the SharePoint 2010 User Interface (UI).
Access Services provides the ability to publish Access databases into SharePoint 2010; while this might not seem important, it does have a distinct advantage of accessing a central location for tables within an Access published database, and one that is updated through the browser. It does have its limitations, such as no support for VBA, but there is the ability to recreatethe Microsoft Access Database from the site. This last feature is helpful because you can publish the Access database as a SharePoint Site and whenever you are ready to re-consolidate it as an MDB, you can do so. Reporting is also available without the need for SQL Server Reporting Services.
Visio Services provides the ability to share Visio diagrams and Visio data-driven diagrams without requiring Microsoft Visio to be installed on the client computer. Depending on who you talk to, these two new features are not always considered part of the Business Intelligence area—most likely because they are neither as powerful nor as tightly integrated as the other features. Because these are the first versions of Access and Visio Services, given enough time, they will improve and become more powerful and versatile just as Excel Services and other previous new kids on the block have.
Before even creating a dashboard, report, or Excel workbook, it’s of the utmost importance to assess how many end users will be using the Business Intelligence area(s) of SharePoint 2010; in other words, it’s important to plan. As the saying goes, “If one does not plan, one plans to fail.”
This is vital for a successful deployment of the services in the Business Intelligence area of SharePoint 2010. The Business Intelligence Application Services will be relying upon the hardware architecture that was planned and deployed for the delivery of dashboards, reports, and workbooks to the end user. Adding to the situation is the fact that it is highly likely that the users that belong to the Business Intelligence areas of the company are not your typical day-to-day users. Most Business Intelligence users are high profile individuals that make important decisions based on the tools they use on a daily basis. If these are not fast or robust enough, the end result of the Business Intelligence project will not be considered successful; instead it might be seen as a waste of resources.
A Business Intelligence project must provide data immediately, so it is extremely important to plan the processes to deliver the data for analysis while simultaneously supplying on-the-fly analysis for all end users. The system must be able to respond in a timely fashion and accommodate multiple requests at the same time.
Architecture planning is more than just the assessment of the number of servers, memory, and storage required for the project to be deployed. In this scenario, you are targeting resources that will support PerformancePoint, PowerPivot, Excel, and Reporting Services at a minimum.While there are many factors and variables to consider, the following list can serve as a guideline:
Once you have a good estimate of these items, you can establish a preliminary budget. Then you can shop around and determine what you can purchase within your budget—or determine if your budget is large enough to make any of it happen. Sometimes budgets are predetermined and you don’t have much choice regarding what hardware resources can be purchased. If this is the case, it’s important to make the target users aware of these limitations and coordinate how everyone can coexist without affecting performance for the entire group of Business Intelligence end users. Microsoft has published the following links to assist in this process:
http://technet.microsoft.com/en-us/library/ff758647.aspx
http://technet.microsoft.com/en-us/sharepoint/ff601870
www.microsoft.com/download/en/details.aspx?displayLang=en&id=7480
www.microsoft.com/download/en/details.aspx?displayLang=en&id=12768
Additionally, a great tool for capacity planning is an Excel workbook made available by Tihomir Ignatov (Microsoft ). It contains three tabs.
http://tihomirignatov.blogspot.com/2010/12/sharepoint-2010-capacity-planning-and.html
Most of the time, existing hardware for the initial rollout of SharePoint 2010 is used and serves as a starting point for the Business Intelligence initiative. While this is a valid way to start, it is not a recommended situation for the Business Intelligence initiative because it is a very different system when it comes to resource consumption. Keep in mind that every project varies, so the architecture that worked well for a specific project may not work well for a different project. That said, it’s recommended as a starting point for a pilot hardware infrastructure to begin with a separate server for each layer. So in this case, the layers would be Web Front Ends, Applications, and Database. You should also do the following:
- Memory
- Storage
- Hardware
- Etc.
Last but not least, it’s necessary to pinpoint where the majority, if not all, of the data will be coming from. Why do you need to know the data source(s)? Because of storage, as listed previously. It’s recommended to have a structured and performant source for your Business Intelligence inquiries.
Note While there can be many different data sources, I recommend using Microsoft SQL Server Databases and Data Warehouses instead of Lists or Workbooks, particularly since they provide a variety of tools that are useful in the optimization processes.
Data can be stored in SharePoint lists and/or databases. Part of the planning process is to decide which provides the most options. Often SharePoint lists are chosen as the main storage source, but it is by no means the only or the most versatile choice.What follows is a review of the details of SharePoint lists and databases.
Lists are a key component in any version of SharePoint; improvements have been made since their introduction. Alist is very similar to a database table—the keyword being “similar.” The small differences can matter when making use of lists in SharePoint. These small differences include the following:
Note Throttling can be thought of being very much like caching, in which you are indicating how many list items will be retrieved when a request is made.
Even though lists are an excellent way of sharing and collaborating and have improved greatly with SharePoint 2010, it is important to remember the following key aspect:
Note Lists are not database tables, nor should they be used as such.
What is often forgotten is: Where are SharePoint Lists stored? Well, guess where? In the SharePoint content databases. That said, it doesn’t hurt to question if there is the need to have an extra step involved in the process for the retrieval of data, particularly if you can go directly to the source and that source can be optimized in more ways than a SharePoint list.
Below are the top items to reference when planning your Lists (a comprehensive list is available at: http://technet.microsoft.com/en-us/library/cc262787.aspx#ListLibrary
):
Note http://technet.microsoft.com/en-us/library/cc262787.aspx#ListLibrary
is updated with the latest updates made available with the SharePoint 2010 Service Pack 1 Update.
The content database aspect is often forgotten and thus not always planned well; most of the time this happens because of budget limitations. But once the performance starts affecting the usage of SharePoint and Business Intelligence within SharePoint, questions will be asked; many times there are no straight answers to those questions.
Because most applications, if not a large majority nowadays, depend and store the data used directly in databases, it’s of the utmost importance to take into account the following considerations when it comes to the SharePoint content databases:
http://technet.microsoft.com/en-us/library/cc891489.aspx
.http://technet.microsoft.com/en-us/library/cc261687.aspx.
While this list is not necessarily all inclusive, it has been my experience that many companies forget to implement one or more items from this list. The worst chase is when a company doesn’t even bother to address them at any point in time after the implementations have been set to “live.”
When everything is working, things are fine. That’s when this list is easily dismissed and ignored. But when disaster strikes—which it always does; it’s just a matter of time—then it becomes a high priority to recover the data. Stress and pressure become a constant until the data is recovered and the failed systems are working as they should.
If you would like to learn more about managing large lists and throttling, the following links will interest you:
http://msdn.microsoft.com/en-us/library/ee557257.aspx
Web Parts are everywhere within SharePoint 2010. For instance, they are used to display, create, update, and delete data inside SharePoint lists. Web Parts have a variety of uses and they can be used for just about for anything within sites. Within every SharePoint site, a SharePoint Web Part is most likely being used.
Figure 13-1 details the enhanced List View Web Part, which is the ability to configure the AJAX options of the Web Part. The AJAX Options enhancement is a welcome enhancement that does make a difference when used with other Web Parts that support AJAX. A quick example would be by connecting with other List Web Parts to filter them.
In order to illustrate the use of filters in SharePoint lists, an example site called “BI Lists” has been provided. The BI Listssite contains the following SharePoint lists:
Additionally, it contains the following site pages:
The sample is not all-inclusive or extensive but it does serve the purpose of illustrating how filtering can be used to your advantage. The Sales site page contains a Chart Web Part, which serves to demonstrate the Chart Web Part by creating a bar chart report based on the data from the Yearly Sales SharePoint list. The FilterBySalesPerson site page contains Text Filter, SharePoint List Filter, Sales People, and Yearly Sales Web Parts. If you’re not familiar with the Filter Web Parts, they did exist in Microsoft Office SharePoint Server 2007, but they are seldom discussed. They are very useful and powerful, particularly when filtering data from List View Web Parts.
The difference between the Text Filter and the SharePoint List Filter Web Parts is that the Text Filter Web Part the filter values are typed in while the SharePoint List Filter Web Part displays the choices as radio buttons.
Figure 13-2 illustrates the values for the Text Filter and SharePoint List Filter Web Parts.Once the values have been selected, it’s noticeable that the Sales People and Yearly Sales Web Parts have been filtered based on the typed-in value of “Jack” from the Text Filter Web Part.
Figure 13-3 depicts the filtered List View Web Parts based on the typed in value into the Text Filter Web Part.The filtering occurred by creating a connection that goes from the Text Filter Web Part towards the Sales People and Yearly Sales Web Part.
Figure 13-4 illustrates the connection between the Text Filter and List View Web Parts. Why would you want to filter the values within SharePoint lists? Because it’s easier to view the data when it’s displayed and filtered with specific values. In the provided SharePoint lists, there are not many list items, but can you imagine when you have thousands or list items? It can be difficult to distinguish, let alone get a grasp and digest all that data.
|
|
|
|
|
|
|
|
|
|
|
|
|
The Chart Web Part provides the ability to chart from different data sources. The easiest to configure is a SharePoint list. That’s not the only option available; it is very easy to configure the data into a chart because of the intuitive interface that it offers out of the box.
Figure 13-5 shows the data connection wizard. The configuration process is divided among the following steps:
Once the Web Part has been provided with information, the data is charted and displayed by the Chart Web Part. Figure 13-6 illustrates what a chart could look like.
By now, you have probably figured out that this looks like a very light Business Intelligence site within the SharePoint 2010, which is the point I was trying to convey. You can create a very simple, small, and lightweight site that allows you to drillthrough data in your SharePoint lists.The out-of-the-box functionality within SharePoint lists offers the pros and cons outlined in Table 13-1.
List View Web Parts can also display data from external data sources such as databases. This is achieved by using external content types. By combining data within SharePoint Lists and from external data sources, you can truly increase the value of the sites you have created.
The data that will be made available from an external data source will be presented within SharePoint with the List View Web Part. This means you can filter and manipulate it as if it were a SharePoint list.
Excel Services 2010 has improved greatly since what was made available in Microsoft Office SharePoint Server 2007. Because Excel Web Applications are new to SharePoint 2010, it’s easy to get confused with Excel Services. However, both serve different purposes. Excel Web Applications are a limited subset rich client of Excel 2010 and are used to present Workbooks; Excel Services is more of a server-side Excel.
Since Excel Web Applications provide the ability to display workbooks just like an Excel Web Access Web Part, which one should you use? Well, does the workbook need interaction with data from or to Web Parts?If the answer is yes, then the task is most likely suited to use the Excel Web Access Web Part.
Many enhancements and improvements have been made to Excel Services, including the following:
As mentioned, Excel Services can be thought as a server-side Excel installed within SharePoint 2010. With this analogy, add to the thought that when a workbook is being worked upon through the browser, every time it is saved or a calculation needs to be performed, it is being done on the server(s) used by the SharePoint 2010 farm.
All these thoughts are represented with Figure 13-7. As illustrated, a lot of traffic can be generated back and forth just by working on workbooks. This increases exponentially based on the number of users, amount of data, calculations, and the number of times a workbook is saved.
http://technet.microsoft.com/en-us/library/ff191194.aspx
PerformancePoint Services is a new and welcome addition to SharePoint 2010. It once existed as a separate product and is now tightly integrated within SharePoint 2010. Because it is integrated into SharePoint 2010, the dashboards are now deployed into a folder within a document library. This allows for securing the content of the dashboards.
Creating dashboards is done through the Dashboard Designer, which is usually installed and started through the Run Dashboard Designer button of the deployed BI Center.
Before creating the BI Center site, consider the following: a BI Center site is a site not a site collection. While this might not seem that important initially, when managing, maintaining, and backing up one or more BI Centers, it will become quickly important for the planning and configuration of these BI Center sites.
The answers to the previous questions will clearly indicate a structure that will be used to create BI Center(s). It’s recommended to create a top BI Center(s) and create BI Centers under it; this will provide a centralized BI Center Site structure. Other questions include:
This is just a quick list of questions that can give you a sense of what needs to happen before BI Center sites are created plus how and where the dashboards will be developed and published.
Reporting Services has improved with each version of SharePoint. This latest version has better integration and better charts. When developing the reports, take into account these new and improved charting controls that provide a very attractive and easy way to represent data in reports. A major consideration point is the integration mode to use between SharePoint 2010 and Report Services. The local mode is not integrated with a report server; it’s also known as native mode. The connected model integrates the Reporting Services Report Server into the SharePoint Farm, allowing its management through SharePoint Central Administration; it’s also known as integrated mode.
Before deciding which type of mode you should use, it’s a good idea to dedicate enough time to research which type of installation fits your needs. They both have pros and cons. Note that switching back and forth between either installation modes is not recommended nor should it be undertaken lightly.
Table 13-2 contains links that may help you decide which type of installation is best for you.
Note I recommend local mode over connected mode because it provides you with a clear separation between SharePoint 2010 and Microsoft SQL Server Reporting Services. The advantage of that separation is the ability for easier management and recovery in case of an upgrade, service pack installation, disaster, etc.
When developing SQL Server reporting services reports it is usual to use BIDS (Business Intelligence Developer Studio) which ends up being added into Visual Studio 2008 at the moment of installation. Yes, that was Visual Studio 2008; not a typo. Visual Studio 2010 is still not supported for report building.
The other tool is Report Builder 3.0, which is a free client side download available at: http://www.microsoft.com/downloads/en/details.aspx?FamilyID=d3173a87-7c0d-40cc-a408-3d1a43ae4e33&displaylang=en.
Sometimes the tool is determined by the budget and/or the target developing users, mostly because if Microsoft Visual Studio 2008 is necessary, so is a license, which requires a purchase. Microsoft Visual Studio Express is an option, but unfortunately the Express version is limited in its functionality; at this point in time, it doesn’t support the ability to develop reports. If the purchase of Microsoft Visual Studio 2008 isn’t possible, Report Builder 3.0 is a free option. In my experience, both developers and non-developers find it very comfortable to build, test, and deploy reports within the Visual Studio 2008 environment and Report Builder 3.0.
In this chapter, you have explored the basics of Microsoft Business Intelligence;reviewed the new additions to Business Intelligence in Microsoft SharePoint 2010; learned about the optimization of the Business Intelligence Resources; got some ideas for utilizing Web Parts for filtering and charting data; learned about some of the enhancements to Excel Services and how to reduce the workload from the SharePoint 2010 Server(s);examined which reporting services mode would best suityour needs; and learned about the available tools for developing reports.
Hopefully this chapter has encouraged you to consider leaping into organizing a pilot BI Center site(s); developing PerformancePoint dashboards, Excel Services workbooks, and SQL Server reporting services reports; charting data with Web Parts; integrating two or more of the developed items in Web Part pages; and deploying the pilot and impressing the executives.
http://technet.microsoft.com/en-us/library/ee683869.aspx
http://technet.microsoft.com/en-us/library/ee663485.aspx
http://office.microsoft.com/en-us/sharepoint-server-help/sharepoint-lists-v-techniques-for-managing-large-lists-RZ101874361.aspx?CTT=1
http://www.microsoft.com/downloads/en/details.aspx?FamilyID=d3173a87-7c0d-40cc-a408-3d1a43ae4e33&displaylang=en
http://technet.microsoft.com/en-us/library/cc261687.aspx
http://technet.microsoft.com/en-us/library/cc262787.aspx#ListLibrary
http://technet.microsoft.com/en-us/library/ff758647.aspx
http://technet.microsoft.com/en-us/sharepoint/ff601870
www.microsoft.com/download/en/details.aspx?displayLang=en&id=7480
www.microsoft.com/download/en/details.aspx?displayLang=en&id=12768
http://dougortiz.blogspot.com/2011/07/sharepoint-2010-capacity-planning-tool.html
http://technet.microsoft.com/en-us/library/cc262787.aspx#ListLibrary
http://technet.microsoft.com/en-us/library/cc891489.aspx
http://technet.microsoft.com/en-us/library/ff191194.aspx
18.219.63.95