Exploring Excel 2013 for BI Tip #14: Sparklines and Pivot Tables

7 01 2014

As I mentioned in my original post, Exploring Excel 2013 as Microsoft’s BI Client, I will be posting tips regularly about using Excel 2013.  Much of the content will be a result of my daily interactions with business users and other BI devs.  In order to not forget what I learn or discover, I write it down … here.  I hope you too will discover something new you can use.  Enjoy!

Sparklines and Dashboards

There are a lot of visualization possibilities with Excel. When creating dashboards, sparklines are a good visualization of what happened over a data series. My goal was to add sparklines to a pivot table so it could be added to a dashboard. After many failed attempts, I was able to get the following to work.

On the INSERT tab, you will find the Sparklines options. In my pivot table I am going to add Line and Column Sparkline visualizations using the MyVote submission counts.

image

Here are the steps that I used to add this visualization to my pivot table.

First, I created a pivot table with Submission Count as the measure, the rows were the Poll Categories, and the columns are the quarters of the year. Here is what the original data looks like.

image

In this case, I kept the Grand Totals for both columns and rows turned on. I am going to use these areas as the targets for the sparklines. I am going to use lines for trends over time on the Grand Total column. Then I am going to use the column visualization to show the category distribution on Grand Total row.

Adding the Line Sparkline

To add the line sparkline, select all of the data cells (no grand totals). Next, select the Line Sparkline option. This will open the Create Sparklines dialog. In the dialog, you can see the Data Range is already populated with the highlighted cells. The Location Range is empty as shown below.

image

Next, you select the columns in the Grand Total column, and that cell range will be added to the Location Range field. This will put the sparklines in those columns and they will match the data trend. For clarity, the final step would be to change the column name to “Trend” and change the font color to white so the text is not seen. Here is the result.

image

Adding the Column Sparkline

Next up, we will add the Column Sparkline. Highlight the same cells as before. Once the cells have been highlighted, select the Column Sparkline option. Select the Grand Total row for the location. This will show the distribution within the quarter for the categories. Changing the font to white does not hide the value in this case. I actually reduced the font size to 1 to make it nearly invisible. (There is no transparent font available.) Here is the result.

image

I also added lower right corner by selecting the Grand Total column cells as the data and that cell as the location to get a consistent look at distribution. One other note, the Grand Total row is called “Trend” as well because they have to have the same name. But, overall, this was the look I was working toward.

Limitations and Nuances with Sparklines

Now for the stuff that doesn’t work as you would like. Sparklines are technically not part of the pivot table. As a result, the table needs to be static in shape. This means rows and columns need to stay the same in count and position.

I am going to add a category slicer to my example. When I select the Entertainment category, all of the sparklines are “stranded” in space. Quarter 2 disappears because it has no data and as a result the trendlines are no longer in the table. This is also true for the columns as four categories are eliminated by the filter. Worse yet, if you look at the filter, you will notice we have no poll submissions in the News category. When that is added the sparklines will end up in the last data row as opposed to the Grand Total sections.

image

Sparklines are a nice tool to have, but you need to understand what is the best way to use them in the context of what you are doing.

Reference and Credit

I ran across this during my search for how sparklines work in pivot tables: http://answers.microsoft.com/en-us/office/forum/office_2010-excel/how-do-you-insert-a-sparkline-into-a-pivot-table/e072570d-b367-41f1-b2d6-2dbe939db311.  As I note with the limitations to my solution, the forum post above calls out some alternatives which allow for more dynamic approaches, but they also involve coding. Furthermore, the comment from Andrew Lavinsky (MVP) confirmed that this was possible and that it is supported in SharePoint Excel Services.





Exploring Excel 2013 for BI Tip #12: Icon Sets

3 12 2013

As I mentioned in my original post, Exploring Excel 2013 as Microsoft’s BI Client, I will be posting tips regularly about using Excel 2013.  Much of the content will be a result of my daily interactions with business users and other BI devs.  In order to not forget what I learn or discover, I write it down … here.  I hope you too will discover something new you can use.  Enjoy!

Using Icon Sets

So far we have covered the data bar and color scale options in conditional formatting. Up next is Icon Sets. This conditional formatting option can be very valuable when creating dashboards using Excel.

image_thumb1_thumb

Icons in Brief

Excel has a number of icon sets that can be used to visualize status and trends. These are organized in Excel into groups based on their usage as shown in the image below.

image

Your specific use case including the data measured and the message to communicate will determine which icon set you choose. Often icon sets are used in scorecards and with key performance indicators (KPIs).

Creating a KPI with an Icon Set

When making a choice on icons, it is important to keep in mind what you want to communicate and who your audience is. In the following example we will look at setting up a KPI or status icon set using the tri-color, differing shape set.

image

This is a good choice to represent data that can be shown as “good”, “warning”, or “bad” status.

Using shapes as well as colors is particularly helpful if you have color blind users.

In my example, I am using a pivot table using Power Pivot data. I have a series of poll questions and the number of submissions. Let’s start with the default implementation. First, click into the cell with the value (in my case, Submission Count). Next, select Conditional Formatting > Icon Sets > <<select the shape noted above>>. This will put an indicator in the field as noted below.

image

As you can see only the field selected has the icon set. What we need to do is apply the formatting to the entire column. That is done by clicking in the cell and then clicking the formatting button that pops up by the cell.

image

This button will show the following selections for applying formatting rules:

  • Selected Cells (currently selected)
  • All cells showing “Submission Count” values
  • All cells showing “Submission Count” values for “PollQuestion”

Obviously, you would see your value column and your reference column names. If you have nested reference columns (e.g. a poll type) you would want to select the third option so it only was measured against the values in scope with the initial reference column. The second option will apply the formatting to the entire column no matter the level in the pivot. For this example, we will use the third option. Now look at the column results:

image

As you can see, the formatting has been applied to the entire column. Curious how it was applied? In the ribbon, select Conditional Formatting > Manage Rules. In that dialog, you should see the rule you created. With that rule selected (which is the only rule if you started new), select Edit Rule. In the resulting dialog, you can see that you can change how the rule is applied, the rule time, and the description. Here we will focus on the description. In the description area, you can see the Icon and the values that are applied. By default, it groups it in to percents. This is done by taking the maximum and minimum values and dividing the values into thirds and applying the value.

image

You can change the type from Percent to Number, Formula or Percentile. I often find that I need to use a number for that goal. However, if you use a formula you can refer to a sheet with goals, for instance, that let’s you do some what if scenarios as well.

You can also show the icon only which gets closer to the look and feel of a scorecard. I encourage you to explore the possibilities and create scorecards that can be used by your executives, managers, and other users who need to get an overview of how things are going.





Exploring Excel 2013 for BI Tip #10: The Data Bar

30 07 2013

As I mentioned in my original post, Exploring Excel 2013 as Microsoft’s BI Client, I will be posting tips regularly about using Excel 2013.  Much of the content will be a result of my daily interactions with business users and other BI devs.  In order to not forget what I learn or discover, I write it down … here.  I hope you too will discover something new you can use.  Enjoy!

Using the Data Bar

This feature has been a part of Excel for a long time.  However, as with any tool, some of the oldies are really good.  As a BI architect who worked with SQL Server tools, I am always amazed at what has been around in Excel for years.  So as part of this series, I will also highlight some important visualizations that have been around.

What is the Data Bar?

The data bar is a conditional formatting feature that can be applied to cells in Excel.  Data bars “fill” the cell proportionally based on the data that the formatting is applied to.  Data bars work with pivot table and standard data in Excel.  Our focus will be on using the data bar with pivot tables.

You will find the option to add data bars on the Conditional Formatting button on the HOME ribbon as shown below.

image

image

As you can see Data Bars are one option under Conditional Formatting.  Look for future tips to come on some of the other Conditional formatting options.

Adding Data Bars

The following sample is from the MyVote data generated from the Modern Apps Live! project.  In this sample, I have a simple pivot table which shows the Age range and number of poll submissions as shown below.

image

To add data bars highlight the area to add the bars and choose a format.

image

Advanced Settings

By clicking More Rules … you will be able to apply advanced options.

image

In my opinion, the most important setting to use here is at the top.  When working with pivot tables, you should at least choose Apply Rule to “All cells showing {field name} values.” By choosing this option, if new row values are added, the data bars will be appropriately applied. This also is necessary for filters and slicers to work correctly.

However, this option will also highlight the any total columns (in our case Grand Total was included).  If you only want counts with the row labels, you would choose “All cells showing “{field name}” values for “{field name}”.   You will find that this is the most common option to select when using data bars for data visualization.

Modifying Existing Data Bars

Once it has been added, you can modify the data bar choosing the Manage Rules option in the Conditional Formatting drop down.  This will open a dialog box which has the formatting rules for the selected pivot table.  There is a drop down, which allows you to select the rules for the sheet or other parts of the workbook.  From here you can see all of the rules applied and can edit the rule, create a new rule, delete a rule, or reorder the rules.

Data bars are a simple, but effective data visualization when you need to highlight the variance between values.  With the ability to apply the data bar to a field in a pivot table, it becomes a flexible visualization as well with very little effort involved.





Exploring Excel 2013 for BI Tip #7: Retaining Pivot Table Size After Data Refreshes

23 05 2013

As I mentioned in my original post, Exploring Excel 2013 as Microsoft’s BI Client, I will be posting tips regularly about using Excel 2013.  Much of the content will be a result of my daily interactions with business users and other BI devs.  In order to not forget what I learn or discover, I write it down … here.  I hope you too will discover something new you can use.  Enjoy!

Retaining the Pivot Table Size after a Data Refresh

Let’s start with the fact that these tips are a result of my work with Excel to support BI tasks.  This turns out to be one of those things that power users of Excel probably already know.  I had to find this out and thus I am sharing it here.

The Problem

When I was working on the dashboard for Modern Apps Live! I had some nicely formatted tables.  However, I had two pivot tables stacked as seen in the picture below on the right.

Summary Dashboard

Whenever I did a data refresh the columns would automatically resize to the “one inch” width.  Making the Poll Questions in the second table unreadable.

The Solution

I went looking for how to fix this.  I searched for solutions online and I poked around to see if I could fix this problem.  After all, it was very important to be able to refresh the data on the dashboard.

I was able to locate the solution here. As it turns out, you need to unselect the Autofit column widths on update property on the PivotTable.  You can get to those options in a couple of ways in Excel 2013.  First you can right click on the table to get the short cut menu and select PivotTable Options.

image

Or you can select Options on the far left of the ANALYZE tab on the PIVOTTABLE TOOLS ribbon.

image

Once you are in PivotTable Options, you go to the Layout & Format tab and deselect the Autofit option as mentioned above.

image

Voila! Problem resolved, now my dashboard does not need the columns resized in the pivot tables each time it is refreshed.





Exploring Excel 2013 for BI Tip #5: Cleaning Up Slicers

29 03 2013

As I mentioned in my original post, Exploring Excel 2013 as Microsoft’s BI Client, I will be posting tips regularly about using Excel 2013.  Much of the content will be a result of my daily interactions with business users and other BI devs.  In order to not forget what I learn or discover, I write it down … here.  I hope you too will discover something new you can use.  Enjoy!

Cleaning Up Slicers

In this tip, a follow up from Tip 4 on adding slicers, I wanted to highlight a couple of ways to clean up your slicers to make them more user friendly.

The focus here is around Slicer Settings which can be opened from the short cut menu on the slicer or in the SLICER TOOLS ribbon option as shown below.

image

This will open the Slicer Settings dialog.  The image below shows the setting for my Age Range slicer with the slicer.  The settings you see are the default settings.

image

So how can you change this to be better for the user.  You may need to change the Caption to reflect something better for the users.  You can also remove this if the content in the slicer is self-explanatory.

Next, check your sorting.  If you have the data sorted correctly, use it.  However, you can also do typical alpha sorting. Be aware, that you may have issues with data types like dates or numbers if they are not ready for this type of sort.

The 3 check boxes are the most relevant.  The two selected by default should be used in general.  The next set of images show the visualization of the slicer based on these settings.  In this scenario, we only have data for the 30-39 option.

No options are selected:

image

This gives no visual cues to what data is available or not.  This is the least preferred as there is no clarity to the user.

image

The image above dims the options that have no data by selecting the “Visually indicate items with no data”.  This works great in a short list and you want to display options with no data.  Basically, knowing that no data exists is important to the user.

When you have more options, you can add the option to “Show items with no data last” which will move items with data to the top.  With longer lists this helps provide relevant data at the top of the list.

image

The final option will hide items with no data.  This is clearly preferred for potentially long lists.  My favorite usage for this option is to use this with dates.  This will allow me to source only dates with data for filtering use.  Here is what our list looks like with the “Hide items with no data” option selected.

image

This option is really cool as slicers are interactive and will remove options as other slicers are selected.  I tend to use this a lot as it also saves real estate on the sheet when designing dashboards in Excel.





Exploring Excel 2013 for BI Tip #4: Adding a Slicer

28 03 2013

As I mentioned in my original post, Exploring Excel 2013 as Microsoft’s BI Client, I will be posting tips regularly about using Excel 2013.  Much of the content will be a result of my daily interactions with business users and other BI devs.  In order to not forget what I learn or discover, I write it down … here.  I hope you too will discover something new you can use.  Enjoy!

Adding a Slicer

Slicers are not new functionality in Excel as they were in Excel 2010 as well.  However, they are even more highlighted in Excel 2013.  You can add a slicer from the INSERT tab on the Ribbon in Excel as shown in the image below.

image

When you click this button, you get a data source dialog.  It will show you the available connections in your workbook as well as other connections you have used or are available.  Now in my case, I am using a PowerPivot data source.  In order to use that you need to create your slicer from the PivotTable tools as shown below by right-clicking the field to use as a Slicer.

image

Once you click this, it will create a Slicer on the sheet you are working in with all of the available, unique values from the field.

image

Connecting Your Slicers to Data

Now you have created your first slicer.  However, I would be cruel not to wrap this up by showing you how to connect your slicer to related data objects in Excel.

There are two ways to get to the Report Connections option.  First, in the short cut menu (right-click) on the slicer you will get the option in that menu as noted below.

image

The second way is to bring focus to the Slicer and you will get a new menu option on the ribbon called Slicer Tools – Options.  The Report Connections button there will lead you to the same dialog to relate object.

image

You can also get to the short cut menu from the target data object such as a PivotTable.

The Report Connections dialog allows you to relate the slicer to data objects from the same data source.  It will display objects from all of the objects within your workbook.  This means that you can have slicers filter data across multiple sheets.  This is really cool.

image

In the next tip, I will talk about a new setting which, I believe, make slicers even more user friendly and usable to create a dashboard in Excel.





PowerPoint–My Dashboard and Report Design Tool

20 03 2013

At some point I think that I am becoming a Microsoft OfficeMALL13_Badge_See125x125 specialist as opposed to a BI Architect.  All of this work in Excel and now PowerPoint.  Okay, done with the ramblings.  As I have noted in a couple previous posts, I am working with a team on the Modern Apps Live! conference which is in Vegas next week.  Well, this is another “lesson learned” that I wanted to pass along as a result of doing that work.  (Hope to see you there.)

Using PowerPoint 2013

Microsoft Powerpoint 2013 IconSo I had to create two types of data visualizations for this conference.  Usually, I would use paper or white board to sketch it out and then proceed to make it a reality.  Somewhere along the way, I heard that Microsoft uses PowerPoint to lay out UIs.  Not sure if it is true or not, but it seemed easier and less expensive than Blend or Visio, so I thought I would give it a try.

So, I first needed to create a summary report for a poll within the app that was created.  I used the standard tools with in PowerPoint such as tables, charts, text boxes, and images to mock up my report.  What I liked was I was able to add notations to the mockup for future reference.

image

I had some frustration creating the charts as I wanted them to be representative.  But overall not a bad experience.  The next task I was taking on was working with the dashboards I was going to create in Excel 2013.  I still wanted to lay it out so I knew what I would be trying to design.  This was when I stumbled onto the Storyboarding menu.

image

I actually like using the shapes in this toolset better.  Turns out this is available when you install Visual Studio Ultimate, Visual Studio Premium (my version), or Visual Studio Test Professional.  More on that can be found on MSDN – Storyboard Using PowerPoint.  This can be integrated into TFS and directly associated to work items.  I am not a UX expert, but I like the ability to add tabs like I will have in Excel and there is even a SharePoint page background.

image

However, as you can see, even if you don’t have Storyboarding you can still effectively build up a PowerPoint slide to look like the report, dashboard, or even SharePoint page.  I was not sure if I would be able to embrace this, but in the end I really like the simplicity and using PowerPoint allows for comments, versioning in SharePoint, and other mechanisms to support dashboard design.

I also wanted to pass along another blog post I found from Jason Zander on the Windows Azure team on the same subject:  My Favorite Features: Creating Storyboards with PowerPoint.  Hopefully this gives you another simple way to mock up reports and dashboards when you can’t find that User Experience Pro.





SQL PASS Summit–Day 3 (Keynote)

12 10 2011
From the PASS president, Rushabh Mehta.

PASS has facilitated 430,000 hours of training and expansion into the global community.  These three days contain massive amounts of training and opportunities to network with other SQL peers, including over 800 first timers.  SQL Server MVP Deep Dives Volume 2 has been officially released here with the opportunity to get a bunch of autographs from the 55 MVP authors in the house.

Ted Kummert’s Keynote highlights:
  • “Some database vendors just decided to get into the cloud last week”
  • Growth of PASS is amazing! Over 240 chapters, 300 MVPs, 79,000+ members, and over 4000 attendees at the Summit.
  • SSAS in the cloud, coming soon?
  • Crescent is now PowerView.
  • SQL Server 2012 (aka Denali) will release in the first half of next year.
  • MS SQL Server Vision: “any data, any size, anywhere”
  • Big Data characteristics:  Large data volumes, tranditional and non-traditional data sources, new technologies and new economics and new insights.
  • They will be supporting Hadoop on Windows. By providing connectors to SQL Server and SQL Server Azure, by investing in a JavaScript Framework for Hadoop, drivers for Excel, by partnering with Hortworks,
  • From Denny Lee: HiveQL – the query language for working with Hadoop data. Hive ODBC Driver in PowerPivot – this driver will be available in the next month.
  • Announcing code name Data Explorer for data enrichment, which will be in SQL Azure Labs by end of year.
  • Data Explorer will be in SQL Azure Labs by end of year.  It allows you to do data classifications.  Looks very much like a variation of Excel.  It allows you to add data from external data in the Azure Marketplace which will recommend data based on the data you are analyzing.  Kind of looks like PowerPivot in the cloud, interesting.
  • Amir Netz was recently promoted to Technical Fellow at Microsoft – early developer of OLAP Services, SSAS and beyond.
  • Amir demos PowerView.  During the demo we discovered that Meet the Fockers was the top comedy.  Oh, the power of BI.  Great demo about this product.  He also announced that export to PowerPoint will be supported in this release.

Quite the whirlwind this morning, but some more cool BI stuff coming soon.  Looking forward to the rest of the day.





Recovering SharePoint 2010 with BI on Windows 7

12 07 2011

While working with Microsoft BI projects, I am often required to deliver the visualizations through SharePoint.  On my current project, I have a SharePoint 2010 development environment on Windows 7 that has SharePoint and all the SQL Server services running.  Last week I was preparing to demo our progress when the site became unresponsive.  The demo site had the wonderfully descriptive message:  “An unexpected error has occurred.”  So, I attempted to open the management console but that page could not be found (404 error).  I turned on the expanded error messaging by modifying the web.config file and checked the log files.  The errors pointed to the inability to connect to a database.  Upon further investigation I discovered that three of our databases went suspect including the admin and content databases.

At this point, I proceeded to try to restore my full SharePoint backup.  Which works in all situations except for recovering the admin database. Based on the online resources, this is to be expected.  After conferring with the coworkers, I was able to determine that the admin database was unrecoverable and the best bet was a reinstall of SharePoint.  After working through some options I was able to get the site back up and running without recreating the entire solution.  The rest of the blog walks through the recovery scenario and calls out the nuances of having BI components as part of the content.

Prerequisites

First, the SharePoint Farm must be Farm backed up.  I did this through the Central Administration console. 
(http://technet.microsoft.com/en-us/library/ee663490.aspx)

image

Reinstalling SharePoint

The next step involves uninstalling SharePoint.  I did this by running Setup and choosing the Remove Option.  (The Repair option did not work.) Once I had SharePoint uninstalled, I proceeded to reinstall it.  After SharePoint was installed, I ran the Configuration wizard. This step recreated the Admin database.  Once this was complete, I verified that the starter site and the Central Administration site were operational. 

Recovering the Farm

You can recover the farm from either the Central Administration site or by using PowerShell.  In this case, I used Central Administration as it is easier to select and understand the options. 

I first browsed to the Backup and Restore menu page. From this page, I selected the “Restore from a backup” option under the Farm Backup and Restore header.

NOTE:  In order to run the backup or restore farm functions, the Administration service must be running.  If it is not running, start it in the Windows Services console.

There are three pages or steps to recovering the backup. 

Step 1.  Select the backup to restore.

image

Step 2.  Select the components to restore.

image

For my purposes, I selected the Farm option which restores all of the options to the farm from the backup.

Step 3.  Select Restore Options.

imageimageimage

The first two selections are the most important.  I selected the “Restore content and configuration” and “Same configuration”.  This uses the same application and site names for the full restore of your site.  The “New configuration” option would have required me to rename all of the applications and services I needed to restore. There were a lot of applications and services to rename which I chose not to do.

When I restored at both the client and on my personal development environments I needed to remove the following services before running the restore:

  • Application Registry Service
  • Web Analytics Service Application
  • User Profile Service Application
  • Search Service Application.

When I did not remove these, my restore failed due to duplicate names.  You may run into a similar issue, so removing them before the restore is easiest.  These applications and services should be removed from the Central Administration site.  Once these services are removed, run the restore.

Bringing BI Back Online

At this point, my site would have been operational except I had a Reporting Services webpart on the homepage of my site.  Because of that I saw an error below.

image

As the error message notes, the issue is with the Reporting Services webpart. The issue is that the full restore did not restore the Reporting Services integration.  The integration needed to be redone manually. 

Once Reporting Services was reintegrated I was able to open the site.  However, neither the Reporting Services or PerformancePoint webparts were working. 

image

These issues are primarily security related.  To fix the Reporting Services issue, I openend the data source and reset the credentials.  Once reestablished, the report webpart came back online. 

To resolve the PerformancePoint issue, I first went to Central Administration to refresh the Secure Store Service key.  I was able to use my passphrase refresh the key.  (NOTE: if necessary you can create a new key.) Next, I went in to the PerformancePoint Service Application settings and reset the unattended service account. 

Once these steps are complete, the site was fully restored.  While the full backup is extremely useful for site failures it will not recover all of the BI components and as such feels very incomplete. I hope this saves someone some time later.





Installing PowerPivot on an Existing Farm

5 03 2011

At my current customer, we are putting together a sandbox MSBI environment with SharePoint as well.  We installed and configured SharePoint, SQL Server, Analysis Services, and integrated Reporting Services.  I was preparing to put together a PowerPivot demo in SharePoint and started to install it.  So here is where the fun began.

First, you need to use the SQL Server 2008 R2 install to

Next, you need to LOGIN using your Farm admin account and from what I could piece together, that user MUST have the following privileges.

  • SysAdmin on SQL Server
  • Local and Domain Administrator
  • SharePoint Farm Administrator

Of course, all of this violates any concept of minimum privileges for users.  While this level of access may not have been absolutely necessary, it is definitely where I ended up to get this working.  (The moral of this story is to install PowerPivot on new farms only?)

Here are some of the links I used to get me pointed in the right direction.  I would be interested in hearing if anyone else has had this issue and resolved it differently.

Now that it is running I can put the rest of my demo together.  This sure seemed more painful than it had to be.








Follow

Get every new post delivered to your Inbox.

Join 696 other followers

%d bloggers like this: