Exploring PowerPivot for Excel and SharePoint: Blue Yonder Charters

Summary: Explore a project that uses PowerPivot for Excel, Microsoft Silverlight, and SharePoint Server 2010 to aggregate and share information, both inside and outside a firewall.

Applies to: Excel 2010 | Office 2010 | SharePoint Server 2010 | VBA | Visual Studio

Published:   December 2010 | Provided by:   Frank Rice, Microsoft Corporation

Contents

• Introduction to Blue Yonder Charters

• Need to Aggregate Data and Create Information

• Need to Share Information behind the Firewall

• Need to Share Information beyond the Firewall

• Summary

• Additional Resources

Introduction to Blue Yonder Charters

In this article, you examine an application that has been built for Blue Yonder Jet Charters. Blue Yonder Jet Charters is a leading executive jet chartering company in Brazil. They have a fleet of over fifty Brazilian aircraft providing services across the top ten largest cities in Brazil.

Blue Yonder has implemented a new aircraft tracking system that they are using to optimize their fleet. This is driven by money savings but, more importantly, by being a more environmentally-conscious carrier. This system records certain information about various flights such as carbon usage and efficiency. However, not all aircraft are fitted with these devices so manual data entry is required to record some flight information.

Blue Yonder has created a unique dashboard in Microsoft SharePoint Server 2010 at their headquarters in Sao Paulo, allowing them to stay current with their business and making sure that they are meeting their financial and environmental targets, as well as correctly logging flight information. Flight information is stored within a Microsoft SQL Server 2008 database so that senior management can use the new Microsoft Excel 2010 and Excel Services 2010 feature called PowerPivot to drill into flight data when they need to perform more detailed analysis.

Blue Yonder would like to make this dashboard available to staff in multiple office locations across Brazil to ensure that the fleet is tracked correctly. They will use Microsoft SharePoint Online and SQL Azure to achieve this.

In the following sections, you will see examples of the following:

• A dashboard written in SharePoint Server 2010 that provides the tracking of financial and environment performance as well as the management of flights and alerts.

• The ability to use Microsoft Silverlight 4 within SharePoint Server 2010 to chart, list, and enter information.

• The use of PowerPivot to analyze large amounts of flight information in both Excel 2010 and Excel Services 2010.

• The ability to publish the SharePoint Server 2010 dashboard to SharePoint Online therefore providing the solution to all Blue Yonder employees across Brazil.

Need to Aggregate Data and Create Information

As a manager, you need to keep an eye on company performance. You also need to give your employees the ability to log flights that land in Sao Paulo. Additionally, you must be able to share this information with people both inside and outside the immediate organization. The information to share is all about carbon footprints and profitability. To do this, you start your day in Excel 2010.

When you click on the PowerPivot tab and then the tabs at the bottom of the worksheet, you see that there is SQL Server data being pulled into Excel (see Figure 1).

Figure 1. Blue Yonder Charters SQL Server data in Excel

Now what is compelling about this is the amount of SQL Server data that is pulled into Excel. This worksheet contains over 100,000 records. However, it could be millions of rows of data that you are interacting with to build out these reports. PowerPivot can easily access millions of rows of data at one time.

The worksheet contains the beginnings of a simple PowerPivot table (see Figure 2). Using the filters and slicers in the PowerPivot Field List task pane enables you to choose the industries that Blue Yonder Charters works within and the aircraft models in its inventory. Selecting various data values, such as the total carbon footprint values for each aircraft, enables you to quickly aggregate and analyze the data.

Figure 2. Simple table showing carbon footprint by aircraft model by industry

This is great in tabular format but what you really would like to do is get this data into a chart. So clicking PivotChart on the ribbon and specifying the type of chart gives you a chart view of this information (see Figure 3).

Figure 3. PivotChart of the total carbon footprint per aircraft model per industry

This is a great visualization and it does give you a bit of a trend but what you really want to do is display this data in a form that is a little more digestible. Specifically, you want to look at the Banking, Manufacturing, and Technology industries that Blue Yonder works in.

Just click the filter dropdown and then click Banking, Manufacturing, and Technology (Figure 4). There you see a clearer picture of how those aircraft and their carbon footprint range across just those industries (Figure 5).

Figure 4. Filter on Banking, Manufacturing, and Technology

Figure 5. Filtering produces a clearer picture across specific industries

You can also select other aircraft to see the data for those also. So looking at the chart in Figure 6, you see that the BY Classic 5000 or the BY Lightning 100 aircraft in the Manufacturing industry are the two jets in the fleet that are giving the largest carbon footprint. That information enables you to make other decisions but the point is that you have this great way of filtering all of this external data into a format that gives insight and analyses.

Figure 6. Filtering quickly reveals other aspects of the data

And with the click of a few additional controls, you can see that it is not just the ease at which you can create additional graphics that is interesting but where you can end up. For example, using the compelling slicers on the left-hand side of the display lets you rapidly reload and re-graph the data by using PowerPivot behind the scenes (see Figure 7). So depending on which controls that you select, you have flights by city, profit by industry as shown in Figure 8.

Figure 7. Quickly create effective charts with just a few clicks

Figure 8. Slicers enable rapid re-graphing

Need to Share Information behind the Firewall

Next, an important requirement for you is to share this information with people. You could send this workbook out via email, but an even better solution is to use a collaborative platform like SharePoint to have this PowerPivot data and Excel spreadsheet reside on a website where everyone in the organization can access it. So with that in mind, Figure 9 shows the Blue Yonder website. To create this, you worked with the IT department staff and developers to create a compelling site named Charters. The site consists of three main tabs: Dashboard, Flights, and Alerts.

Figure 9. Blue Yonder Charters website

At the top of the dashboard page, you have the latest alerts. When used with SharePoint 2010 workflow triggers, these alerts can be set up to go out in email and on mobile devices.

You also see that the site also contains some Silverlight controls. For example, there is a button that says Analyze with PowerPivot. Essentially what that does is take you to a document library and load the Blue Yonder Excel PowerPivot spreadsheet (see Figure 10). When you do that, you have is the ability to interact with slicers and the data in basically the same way that you had on the client but with the data now residing on the server. You do not have to invoke the client application to work with that data.

Figure 10. Analyze with PowerPivot takes you to spreadsheet in the document library

Another thing to notice on the dashboard is that there are Silverlight Web Parts (see Figure 11).

Figure 11. Silverlight Web Parts

These are Web Parts that take advantage of the Silverlight toolkit and bring in aggregated data across different regions and different data sources. The Silverlight Web Parts are simply stored within the document library of the site and surfaced by using out-of-the-box Silverlight Web Parts in SharePoint.

There are also other great visualizations that can help in analysis. For example, there are three charts that give you environmental insight and three which provide financial insight. These charts are connected directly to SQL Server 2008 by using Windows Communication Foundation (WCF) Data Services which also works when this site is running in the cloud connecting to SQL Azure. The first environment chart (left-side of Figure 11) shows Blue Yonder emissions and carbon offsets over the last seven days. The Least Efficient Aircraft chart (left-side of Figure 12) is looking at which models are flying with the least number of passengers and producing the most emissions.

Figure 12. Least Efficient Aircraft chart

Least efficient aircraft are regularly reviewed by Blue Yonder management and may be pulled out of the fleet for underperforming. The Green Customer Trending chart (left-side of Figure 13) examines customers who produced the most carbon last month and their trends for this month.

Figure 13. Green Customer Trending chart

The Profit By City gauges (right-side of Figure 11) show profits against targets by city for the current month. As you can see, Salvador is doing really well but Brasilia is slipping quite a bit. The Revenue chart (right-side of Figure 12) provides total revenue for the last six months. There was quite a drop in May as costs went up and Blue Yonder was forced to charge more for charters. This had a negative impact on revenue. And the Costs chart (right-side of Figure 13) examines total costs versus fuel costs over the last six months. A big increase in fuel costs earlier in the year hurt Blue Yonder’s revenue and profits.

Also in the context of the dash board, you can log a flight (see Figure 9). Interestingly enough, when you log a flight, that data becomes part of the aggregated data and you are able to enter data into a custom data form which is easily created within SharePoint 2010 (see Figure 14). And you are not just limited to custom dialogs but can customize the ribbon, lists, document libraries, and so forth. When you save the data in the customized form, SharePoint pushes that data into the database which is then loaded into the dashboard.

Figure 14. Log Flight

A new flight has come in from Rio de Janeiro to Sao Paulo and you need to log it. Click on the Log Flight button at the top of the dashboard. This is a Silverlight form being light-boxed into SharePoint for data entry. Assume that you have entered the data shown in the figure and clicked OK. By clicking the Flights tab, you can see the flight data that you just entered in the list.

Figure 15. Flight data from the custom form

Next, you can see the Alerts tab (Figure 16). Here you can log different kinds of alerts which appear on the dashboard – flight delays, weather updates, or dashboard alerts. This is using standard Announcements functionality in SharePoint 2010. These alerts could easily be generated automatically when key events occur in the system such as a scheduled flight being delayed or a Key Performance Indicator (KPI) falling below a certain level.

Figure 16. Alerts tab

Need to Share Information beyond the Firewall

The next question is how can you take this custom site and extend it out to SharePoint Online so that people who are not on-premises can access the same data? You can do this by using a Microsoft Visual Studio 2010 solution. Basically, what you end up with is a SharePoint project with a number of artifacts ranging from master pages which give that nice branding to the site, and several PowerPivot and Silverlight files. The project gives developers an experience where all these attributes can be debugged in the context of a SharePoint website in real-time.

When creating the project, you will want to create it as a SharePoint sandboxed solution, which is essentially a solution that runs in partial trust code (see Figure 17).

Figure 17. Package the project as a sandboxed solution

SharePoint sandboxed solutions enable you to develop for a local SharePoint environment and also enable you to publish the solution to the cloud. To do this, build and package the solution as a Windows SharePoint Package (WSP). Then, you just want to upload this package into the SharePoint Online site. That brings in the sand-boxed solution that you can then activate within the site collection. This enables you to create a new cloud-version of the site based on the Blue Yonder package (see Figure 18) and expose the site to everyone outside the firewall. You can open PowerPivot in the cloud and access the spreadsheet through the browser.

Figure 18. A WSP package enables you to create a cloud-version of the site

Summary

In this article, you saw how to take advantage of PowerPivot for Excel to build out some compelling analysis and then share those with colleagues via SharePoint. You also saw a custom site that had a mix of Silverlight and other common artifacts such as alerts, and showed extensibility in the context of a SharePoint site. You also saw how to extend the site into the cloud by using a Visual Studio 2010 solution.

Additional Resources

Find additional information about the topics discussed in this article at the following locations.

• How to: Install PowerPivot for Excel

• PowerPivot for Excel Download

• PowerPivot for SharePoint

• SQL Server PowerPivot for SharePoint: Deliver and Succeed with BI

• Implementing Self-Service BI with PowerPivot for Excel 2010

• SharePoint Development in Visual Studio