If you’re familiar with the Drill Down function in PerformancePoint Services in SharePoint, the new Quick Explore feature in Excel will be very familiar. Quick Explore appears as a small magnifying glass when you select and hover above a measure in an Excel 2013 PivotTable. One can also activate Quick Explore by right-clicking on a measure in a PivotTable and selecting Quick Explore. The most exciting aspect of this feature is that it works via the web in Excel Services. Any PivotTable published in Excel Services 2013 has the full capability of Quick Explore and the field well on the right. In Excel Services 2010, interactivity was limited to navigating hierarchies and applying filters. This is a truly a game changer for Excel Services solutions.
The BI Semantic Model that we defined in Part III of this book contains a date dimension that we imported from the Azure DataMarket. Because that date dimension was identified in our model, Quick Explore is able to produce an automatic trend chart for us. We can see in Figure 29-1 that Excel offers to create a chart showing a Trend Chart of our Distinct Ticket Count measure slices by Year and by Month.
In Figure 29-2, we can see that Quick Explore automatically creates a line chart showing Distinct Ticket Count over time. From this chart, you can see that while you received the largest number of help desk tickets associated with bicycle components, ticket volume dropped substantially since 2008. This type of quick analysis can help you decide which of your product lines may require additional investment by looking at trends.
Another great capability added to Excel 2013’s Quick Explorer is the ability to drill into and slice your data by any dimension in your model. In this case, we’re continuing to analyze the Distinct Ticket Count measure for your Components product line. We saw the 134 tickets plotted over time in our last example. This time, we’ll analyze the state of origin for our ticket creators. To do this, we’ll expand the People table, choose the State property, and select Drill To, as shown in Figure 29-3. You can slice your data along any dimension included in your model.
Examining Figure 29-4, you can see exactly how the Drill To command works. Quick Explore added a filter on the Product Line dimension for the Components product line and State was displayed on the Rows of our PivotTable.
We could have manually created this visualization but once again Quick Explore saves us a few clicks and drills down by adding the filter for Product Line to the PivotTable and adding the State property on Rows. If we want to go back just hit the undo button to return to our previous undrilled state.
In the Drill Down analysis in Figure 29-4, you can see that Washington has 45 tickets. In order to understand that a bit more, you can continue your analysis by adding a trend over time report using Quick Explore and your data dimension again.
You can also add an additional Drill To analysis and dive all the way down to the account name to understand if you have multiple tickets coming from a single customer.
As you can see in Figure 29-5, help desk ticket counts do not appear to be diminishing in Washington State. We can also see that a number of our customers submitted multiple tickets before having their problem solved.
The great thing about this new Quick Explore control in Excel 2013 is that this is not a standalone capability. You can use this in conjunction with all the Excel data visualization features covered in depth in Chapter 23. This includes conditional formatting, data bars, and icons that make your analysis more visual.
Any discussion of new data visualization controls in Excel 2013 would be incomplete without mention of the new timeline control. You can add a timeline from the Analyze ribbon, which appears when a PivotTable is selected. The timeline works just like a slicer or any other connected filter. You can connect to and filter multiple PivotTables or PivotCharts by using a single timeline. The timeline allows users to choose a time granularity such as years, quarters, or months from a date dimension. Then you can interactively filter your dataset by expanding and contracting the length of the timeline, as shown in Figure 29-6.
In Excel 2010, you installed the PowerPivot add-in from SQL Server 2012 in order to build your BISM by importing and relating large amounts of data from multiple on-premise and cloud-based data sources.
A lot of PowerPivot functionality, including the xVelocity Analysis Services engine, is built directly into a new feature of Excel 2013 known as the data model. Straight out of the box, without an add-in, you can manage millions of rows of data, create relationships between data from different sources, and create implicit and explicit calculated fields (formerly known as measures). Without having to go to the PowerPivot window, you can now import and manage data from multiple sources directly in Excel. The PowerPivot add-in now ships with Excel and provides an environment for more advanced data modeling and connects to the same in-memory data model.
The data model in Excel is used to create PivotTables, PivotCharts, and Power View reports. xVelocity stores data that you import compressed in memory, and calculates implicit and explicit measures, which are still created using DAX functions. The data model in Excel features lightning fast processing of data and high compression ratios for smaller file sizes. All data is cached inside the workbook, making it portable. Best of all, because the engine is built into Excel, anyone you send the file to will be able to take advantage of these features.
The PowerPivot add-in provides the richer modeling environment with which you are familiar from Part III of this book. With PowerPivot, you can filter the data at import time, rename tables and columns, create relationships in the Diagram view, apply field formatting, define your own calculated fields, and define key performance indicators (KPIs), hierarchies, perspectives, and custom calculated columns using the Data Analysis Expressions (DAX) expression language. As a reader of this book and as a designer of solutions, you’ll want to activate the PowerPivot add-in and unlock the more powerful features it contains.
PowerPivot and Power View ship with Excel 2013 Professional Plus as COM add-ins, but are disabled out of the box. To enable them, open Excel Options from the File menu of Excel. Figure 29-7 shows the add-ins that have been loaded by Excel. At the bottom of the Excel Options dialog, select Manage COM Add-ins and click Go.
The COM Add-ins dialog box is displayed in Figure 29-8. If you’ve upgraded from Excel 2010 with PowerPivot, then you may see both versions of the PowerPivot add-in. Make sure you select PowerPivot for Excel 2013 and Power View then click OK.
In Microsoft Excel 2013, the data model powers PivotTables, PivotCharts, and Power View reports. If you don’t have an existing connection in your Excel file, you’ll want to create one and pull in some data. Creating a PowerPivot or tabular cube is covered in depth in Part III of this book.
In this case, you’ll work from the workbook that contains your PowerPivot help desk model. To reuse an existing data connection in a workbook, select Existing Connections from the data ribbon. When the Existing Connections dialog is displayed as shown in Figure 29-9, select the existing connection in this workbook and then click Open.
When you open a data connection, you are presented with the Import Data dialog in Figure 29-10. This allows you to choose a visualization for your data. To create a new Power View worksheet in Excel, simply select Power View Report and click OK. Power View visualizations are a bit different from traditional PivotTables and PivotCharts in that they require their own worksheet and can’t be combined with other Excel objects.
If you have been adding new data to your Excel sheet instead of reusing an existing connection, the Add this data to the Data Model checkbox would have been available to let you incorporate the data into the in-memory Analysis Services engine. In this case, you are reusing a connection from another server so the checkbox is disabled.
In Figure 29-11, you see the familiar Power View designer you learned about in Chapter 28. Many usability characteristics of the Silverlight-based designer from SQL Server 2012 have been carried forward to this new Excel-based design surface.
The main distinction is the tight integration of the context-sensitive Office ribbon and the field well displayed to the right. The Power View add-in to Excel exposes the Power View, Design, and Layout tabs in the ribbon, which will be contextually exposed when you select a relevant object such as a Power View chart. Another difference from the previous version is that the field well features a tab for active data currently displayed in this Power View and a tab to access all data from the data source.
Over the next few pages, we’ll walk through some exciting examples of the new functionality built into Power View in Excel 2013, including support for Bing maps, hierarchical drill down, and KPIs.
Power View maps bring a new dimension to our display visualization by adding the context of geography. Based on visualizations from Microsoft’s Bing maps, Power View supports zoom and pan as you would expect with Bing or Google maps.
Power View will call back to Bing via a secured web service call for geocoding, so it asks you to enable content before proceeding. You can map a location simply by adding it to the field lists selecting the Map visualization.
Begin by adding State from the People table and Distinct Ticket Count from the Tickets table. As you can see in Figure 29-12, this experience is identical to what you would expect from the web-based Silverlight designer from SQL Server 2012.
With the Table control highlighted and the Design ribbon activated, select the Map data visualization. Each state in the data becomes a dot on the map. The bigger the value, the bigger the dot. When you add a series with multiple values, you will see pie charts display on the map with the size of the pie chart showing the percentage of the total. In Figure 29-13, we see that Power View identified our State column as a Location and placed Distinct Ticket Count as the size of the indicator on the map.
On the Layouts tab, you have more options for customizing the look and feel of you maps. First, enable data labels to clearly indicate location. This will be especially useful when you have locations that are close to one another and you want to be able to tell them apart when drilling down. Choose the position of your data labels, as shown in Figure 29-14.
Just as easy and a bit more impressive, we can use the Map Background drop-down from the Layout ribbon to choose an aerial satellite photo rather than the default road map background (see Figure 29-15).
Note
Remember when using maps that the background tiles are streamed live from Bing maps, so an Internet connection is required.
In traditional charting, we think of a chart as having an axis and a series. The data points are plotted along an axis, which has a scale indicating the relative value of any position. A secondary axis called a series is often used for grouping and comparison. In our examples, the primary axis is state, along which you plot the distinct count of help desk tickets. To add additional meaning to your data, you can add an additional series of the product line to explore the correlation between location and trouble for a given product line.
On a traditional line or bar chart, these series would be shown as either stacked upon each or clustered next to each other for comparison along the primary axis. Power View maps takes the approach of adding additional meaning to the location indicators by changing then into pie charts. The size will continue to scale up or down depending on ticket counts and whether we have additional information about the product line those tickets are associated with.
To enable this series, just add ProductLine to the color field in the Power View field well, as shown in Figure 29-16.
If your data model was created in Excel 2013 or SQL Server 2012 SP1 or higher has a hierarchy created on the Diagram pane, you can use it in Power View. In our example, the data model could have a hierarchy called Location, consisting of the fields State → City. In Power View, you can either add each field one at a time to the design surface, or you can add Location and get all the fields in the hierarchy at once.
By adding City to the Location field in the Power View field well as shown in Figure 29-17, we can enable drill-down on our map. Drilling down is easy as well. Just double-click a location and the map will zoom in, as shown in Figure 29-18.
The map visually focuses in via flyover transition and expands the cities in Washington, providing detailed information about help desk ticket distribution by product line in each city within Washington State. At this level of detail, having the data labels is really valuable. You can individually highlight each location to get additional detail, but the label displayed right on the map makes it much faster to find your way around. In addition to drilling up and down, we can also zoom in and out using the zoom control shown at the top of Figure 29-18.
In our last example, we saw pie charts overlaid on maps signifying the distribution of our tickets by product line. Pie charts are a new addition to the visualization options in Power View 2012. Pie charts are simple to understand and can be highly interactive in Power View. You drill down when you double-click a slice or cross-filter your pie chart with another chart. In the following example, we start with a simple pie chart (Figure 29-19) and add meaning and depth by defining slices and drill-down capabilities.
From an empty view, add Product Line and Distinct Ticket Count to a new table by selecting them in the field well.
Choose Other Charts → Pie from the Design ribbon.
Also useful is the ability to define slices and drill-downs within a color area on a pie chart.
In the field well, drag State from the Person table to define slices.
Then add Product from the Queues table as a drill-down below Product Line in the color area.
In Figure 29-20, we can see slices defined within each product line showing the relative distribution of tickets by state. When we hover over individual slices, we are provided addition information showing the state and ticket count for a particular slice.
Drill into the Bikes product line by double-clicking the red slice in Figure 29-20. By doing so, you can see the breakout by product shown in Figure 29-21. You can see the ticket volume for each state defined as slices and the details by hovering, including the state name and distinct ticket count. To drill back up, click the drill up arrow in the upper right corner of the chart next to the filter icon.
When dealing with many columns of data charts, maps may not be the easiest way to understand your data. Power View 2012 gives you the table for basic columnar layouts and the matrix for more advanced scenarios where you want to place a dimension across the columns. A common example is a trend over time report or Ticket Counts by Product Line by State.
New in Excel 2013, when you have a matrix with multiple fields in either the rows or columns area, you can collapse the matrix to show only the top, or outermost, level. You double-click one value in that level to expand to show the values under that one in the hierarchy.
Start with a matrix of data such as the one shown in Figure 29-22. We have Product Line and Products in rows and the sum of Distinct Ticket Count and Average Days to Closure as values. Notice that our users will need to scroll down to see all product lines despite a relatively small data set in the example.
Select the matrix control and activate the Design ribbon and the Show Levels drop-down. Choose Rows – Enable Drill Down One Level at a Time to collapse our matrix. This process is shown in Figure 29-23. In this example, we also selected a background color and a professionally designed theme from the Power View ribbon to dress up the report a bit.
In Figure 29-24, we see a much more compact list of product lines displayed. On hover, they display an indicator showing that drill-down information is available. In addition to the gray background color and theme we selected in our last step, Power View also now supports the ability to add a background image to our view and control the transparency for a polished look. Select Set Image from the Power View ribbon to choose a file from your computer, as shown in Figure 29-24.
The result of our work is shown in Figure 29-25. In this view, we show a drill-down into the Bikes product line help desk ticket status overlaid on an image of riding bikes from a recent marketing trip to India where the company is looking to expand. We can also clearly see the indicator next to our selected row should we want to drill back up to the Product Line view. In the lower right corner, we still show our Ticket Count by State bar chart.
By selecting Washington from our bar chart, we see that our drilled-down matrix updates to reflect just the Ticket Count for Washington State where 13 of the 33 tickets for our road frames originated.
As you can see in this example, adding drill-down to our matrix helped us focus on a smaller set of relevant data. Cross-filtering with other charts in the view allows us to get new insight into our data (Figure 29-26). Finally, new themes and background images enable us to bring context and power to our data.
Key performance indicators (KPIs) are a quantifiable measure for gauging business objectives. A KPI visually evaluates the performance of a measure against a target value. KPIs are natively supported in PowerPivot or Analysis Services. The target can be either a fixed value or related to another measure in the data model.
New in Excel 2013, you can add key performance indicators to Power View reports. You can actually create KPIs if the data model your Power View sheet is based on contains calculated fields. You create calculated fields and KPIs on the PowerPivot tab in Excel, or by managing the data model in the PowerPivot window. Then you can add the KPIs that you create to your Power View report. Tabular models created in SQL Server Data Tools can also contain calculated fields and KPIs. You can create views based on KPIs in Power View using he KPIs in your tabular model.
To highlight the KPI functionality of Power View, you will add a set of key performance indicators to your model. Inside Excel 2013, from the KPI menu on the PowerPivot ribbon, select New KPI. The KPI designer is shown in Figure 29-27.
Begin by selecting a base field for your KPI. This is the value you will evaluate and score. You can either compare this base field to another measure that contains our target or against a set of fixed, absolute values.
You can also define the pattern for evaluation against your target including higher is better, lower is better, closer to target is better, or further from target is better.
Finally, choose an icon style that will be mapped to the results of your KPI.
In the example shown in Figure 29-27, a KPI is defined against the average days to closure measure. More than 40 days to close a ticket makes that ticket yellow and more than 80 makes it red. Lower numbers are better. Finally, choose the stoplight style indicators with different shapes for red, yellow, and green. It’s that easy to define a key performance indicator in Excel 2013! If our target value had been stored in a measure, you could compare against that measure and map your KPI to percentage distances from the target.
Note
In PowerPivot 2012, you can also create KPIs, but SQL Server 2012 SP1 or Excel 2013 is required for compatibility with Power View.
Start again with a new blank Power View sheet in Excel 2013. Notice that the new KPIs appear under the Tickets table in the field well (Figure 29-28). Just like in previous examples, the KPI status indicator can be dropped directly into a table or matrix. In this case, add a table with Product Line, Product, KPI status, and KPI value.
As strange as it may sound, another new feature in Excel 2013 is the ability to add formatted text and pictures to a Power View report, including hyperlinks. In Figure 29-29, you see the text added and formatted using the Insert Text Box on the Power View ribbon. Notice that a hyperlink in the text was identified and automatically becomes a link.
It may seem strange that things like formatted text boxes with hyperlinks, images, and pie charts are exciting new features. SQL Server 2008 R2 was released in May 2010 and first introduced the PowerPivot tabular database. This new feature brought the ability to process millions of rows of data down to every desktop running Excel 2010 with the ability to share and collaborate with Excel Services in SharePoint. It took the process of designing cubes from months to minutes. Two years later in SQL Server 2012, the professional BI story merged with the PowerPivot model, providing a continuum of tools and services built around this new BI Semantic Model. The shiniest newest feature was that Power View provides highly interactive data exploration, always presentation ready, and never more than a few clicks from your answer. Less than a year from the release of SQL Server 2012, these features are going native into Office and rolling across every Office desktop. Despite the newness of Power View, this year has shown significant investment and growth for the Microsoft BI tools as a part of Office 2013.
As we learned earlier in this chapter, pie charts are quite sophisticated in Power View with the ability to drill down when you double-click a slice or cross-filter a pie chart with other elements of your view. Let’s add a pie chart to the view with Distinct Ticket Count by Product Line. To save some width on the view, set the pie chart legend to appear at the bottom using the Layout ribbon, as shown in Figure 29-30. When you select a slice of the pie, the KPI table on the left will cross-filter and show only the status of products in the selected product line.
Using a simple line chart, you can add a trend to your visualization. Choose the year from your date dimension and the sum of the distinct total ticket count. This simple chart gains a lot because of the ability to cross-filter based on selecting a product line in the pie chart. This technique is shown in Figure 29-31.
At the end of the day, dashboards help us communicate with data. For years, long tabular reports have littered the trash cans of corporations across the world. Excel and PowerPoint have evolved over the years to make basic charting seem bland and boring. A really great solution should have a dashboard with just a bit of sex appeal so it stands out and gets the message across.
By setting a dark background color, you can create more contrast between the text and the background. The Adventure Bike Company is all about adventure so select a background image aligned to that message. These are easy tasks in Excel 2013. Both the background image and background colors are selected right from the Power View tab in Excel.
Finally, in the very upper right corner of the Excel 2013 window is a new Full Screen Mode button (Figure 29-32). Full Screen Mode hides all toolbars and chrome allowing our dashboard to fill the entire screen.
The final dashboard we produced with Excel 2013 is shown in Figure 29-33. Our solution features key performance indicators defined in our BI Semantic Model, calculated measures and columns derived using Data Analysis Expressions (DAX), and highly interactive charting and data visualization made possible by Power View inside of Excel 2013. As in previous versions, workbooks can be saved to SharePoint for collaboration and will remain interactive viewed in the browser.
We began this book with a discussion about choosing the right tools for the job when designing a solution. Our goal stated up front was to write less code and create better solutions that will evolve with your needs. As you can see in this chapter, Excel and Power View, while great tools in Office 2010, continue to evolve and will bring even greater capability in the future.
By leveraging the power of your platforms, you can quickly create lasting solutions that are agile, flexible, and that evolve as platforms evolve. The business intelligence apps for SharePoint you create following the patterns in this book will come together quickly, provide robust functionality, and will continue to benefit from new platform investments in SharePoint and SQL Server. We hope you’ve enjoyed building this solution from the database to the user interface, the BI Semantic Model, and finally the data visualizations. In the next part, you will learn about configuring the technologies required to host the application you’ve just created.