Tag Archive for Excel

Bringing Data to Life with Excel Power Map

If you have spent any time building, designing, viewing, or otherwise interacting with data visualizations, sooner or later you are going to come across Charles Joseph Minard’s iconic graph of Napoleon’s ‘Grande Armée’ as it advances on, and retreats from, Moscow in the fall and winter of 1812.

If you are unfamiliar with the graph, the wide yellow band represents the size of the army at various points along the advance, and the black band represents the same during the retreat, which is accompanied by the temperature plot along the bottom. To put the numbers in perspective, the army started at 400,000 men, reached Moscow with 100,000, and wound up in the end with 10,000.

The graph is stunning in and of itself, especially when one considers that it is about 200 years old. I have kept a post-card sized copy pinned to my cubicle wall over the last 10 years as an example of what can be done with data and the right visualization.

But for all its richness, the graph is still just a two dimensional piece of paper. Playing with Excel Power Map one day, I thought, “What if I got my hands on Minard’s original data? What could I do with it?” The result, which I will walk the reader through creating in this article, is shown below. Even better is the movie clip that can be created from within Excel, which can be seen here.

Now that you have seen the result, I will walk you through how to get there. And of course, it all starts with data. The dataset I pulled off the internet (http://www.cs.uic.edu/~wilkinson/TheGrammarOfGraphics/minard.txt) looked a little sparse when I first plotted it. I took some liberties with that original data and interpolated many of the points. This was done by taking two adjacent points in time, determining the average army size, latitude, longitude and date between them and coming up with a third point half way between the two. If that did not fill out the graph sufficiently, I took the averages again, between the middle average point and the two original points, in essence creating ‘quarter points’ or ‘third points’ as needed. Purists may argue that I have destroyed the fidelity of the data, and I would argue back that I’m not attempting complex predictive analytics, only trying to ‘pad’ my graph with enough data points such that it closely resembles Minard’s original. I played around with simple circles, the size of each representing the size of the army at that particular point, but settled on the bar graph instead.

The temperature data was also modified by A) putting all the points on the same latitude and somewhat lower than the army’s path and B) duplicating it in the Fahrenheit scale for those of us not too familiar with the Celsius scale. Because of the up and down nature of the temperature plot, straight line interpolation would not have been appropriate. My resulting data sets of army size and temperatures can be found in the following Excel document. Also of note is the fact that Excel does not recognize dates prior to the year 1900, so all dates in the datasets are moved forward by one century to 1912.

Now to the fun stuff. For this you will need Microsoft Excel 2013 with the Power Map add-in installed (http://www.microsoft.com/en-us/download/details.aspx?id=38395). After installation, activate the add-in by clicking File > Options, select the Add-Ins page, select “COM Add-ins” in the “Manage” combo-box at the bottom, click Go, and enable “Microsoft Power Map for Excel”. Back on your workbook, on the INSERT ribbon click the Map button.

The first thing to do with any mapping exercise is to set the geographic references. On the right side, under Army Stats, check the boxes for Latitude and Longitude, and click Next (not shown). On the next page, drag the Survivors field to the HEIGHT box, the Direction field to the CATEGORY box, and the Date field to the TIME box as show in red below. At this point, after just three mouse clicks and three drag and drop operations, you have a fully interactive map (powered by Bing) with browsing controls as shown in the blue boxes. And we’re just getting started!

Let’s clean some things up. Hover your mouse over the “Tour 1” title and notice the popup. Change the title to “Napoleon’s March.” Click the Layer Manager icon, then the Change Layer Options icon (gear) next to Layer 1. Rename the layer to “Army Statistics” using the edit icon (pencil) next to the layer name (not shown).

To add the temperature plot is very simple as well. From the Home ribbon, click Add Layer. At this point, you will follow the same steps we did for plotting the army, starting with the geographic references of latitude and longitude, but this time we’ll be taking data from the Temperature fields using those fields in the same way, and plotting Temperature as the HEIGHT, Scale as the CATEGORY, and Date as the TIME. The only difference is to select a Clustered Column visualization instead of the default Stacked Column.

Of course, to be true to Minard’s original, we can further modify the plot colors to yellow and black using the Layer Settings. I have also reduced the height and opacity of the temperature plots and changed their colors so as to better contrast the army.

The result is good, but unfortunately still shows 1812 data plotted on modern day Europe. We need to fix this. On the Tour editor (left side of the screen), click the Change Scene Options button. Then in the right side, click Change Map Type. Select New Custom Map, in the dialog box, click the Browse icon, and locate this map of Russia 1812.jpg. After applying the custom map, don’t be discouraged by how your data looks; we’ll need to make a few adjustments. Set the X Min and Max values to 19 and 41, and Y Min and Max to 50 and 60, respectively and the Y Scale to 120. Click Apply to view how the changes affect your map. Make adjustments as necessary until the army plot starts at the Niemen River on the left and ends at Moscow on the right. Your final settings may be different depending on your hardware and resolution, etc. It should be noted that the jpg referenced is a conical projection and Excel does not allow ‘bending’ the plot to coincide with the curved lines of latitude, or converging lines of longitude which are clearly visible on the map. Not much we can do about that. It’s the best map of 1812 Russia I could find on the internet.

(Disclaimer: Custom Maps may not be available in your current version of Power Maps. As of September 2014, it was only available to Office 365 clients and not included in the latest download as posted at the start of this article. I don’t know if that has been rectified.)

The last thing we’re going to do is to capture the playback video and add a soundtrack. To set the playback speed, on the Play Axis toolbar at the bottom, click the settings icon. Slide the Speed control at the bottom until the Scene duration reaches just over 60 seconds. Alternatively, use the spinner control to fine tune the duration in seconds. For the soundtrack, what better music than Tchaikovsky’s iconic 1812 Overture, which, by the way, was commissioned by the Tsar to commemorate Napoleon’s defeat at this very battle! An excerpt sound clip can be found below, and is the final 61 seconds of the timeless classic.

From the Home ribbon, click Create Video (second button from the left). Choose a quality and click Soundtrack Options. This is fairly straight forward from here. For the video in this start of this article, I selected the middle quality (Computers and Tablets at 720p), and removed the option for looping the soundtrack.

As you can see, using Excel Power Map is easy, and can yield exciting visualizations, even with 200 year old data!





SharePoint 2007: Problems opening Excel files from a document library

While our current focus, in terms of SharePoint, is on 2010 and looking ahead to SharePoint 2013, we support several clients that have 2007 environments. Occasionally, these environments present us with some interesting challenges.

Recently, I was doing some work for a client that reported several problems opening Excel files from a SharePoint document library.

  • Users were being prompted several times to enter their credentials when opening the Excel file even though they were already logged in to SharePoint.
  • After an Excel file was checked out from SharePoint and then opened, the document would be empty. I checked the SharePoint drafts folder and the file was only a few KB instead of around 1 MB like it should be. Once the check-out was discarded the document could be opened read-only without a problem.
  • Users received an error message when trying to open an Excel file, “The file you are trying to open, ‘filename.xls’, is in a different format than specified by the file extension. Verify that the file is not corrupted and is from a trusted source before opening the file. Do you want to open the file now?”. This error seemed to be related to the previous problem but it did not come up every time a file was blank.
  • Users receive an error message when trying to open an Excel file, “Error: Updates are currently disallowed on GET requests. To allow updates on a GET, set the ‘AllowUnsafeUpdates’ property on SPWeb.”

None of these problems were consistent. They did not occur for every user or for every Excel file which seemed odd.

My first thought was to try to address the issue of the users being prompted for credentials even though they were logged in. I have seen this issue many times. The client has multiple domains so I assumed it might related to cross-domain authentication, however users in the same domain as the SharePoint farm were experiencing this problem.  Even so, I put the SharePoint site in the Intranet zone which often corrects problems like this but it had no effect. I also checked the Credential Manager in Control Panel to make sure there were no old credentials stored but again, this did not help. I even tried adding a registry entry “AuthForwardServerList” in the WebClient Parameters section. This related to an issue in Windows 7 that caused intranet sites that used fully qualified domain names to be treated as internet sites even if they were in the intranet zone. Unfortunately, this also failed to correct the problem.

At this point I decided to look at some of the other errors. As I researched the error about GET requests and AllowUnsafeUpdates I found some references to problems opening image library files when Blob Caching was enabled. I checked the web.config file to see if Blob Caching was enabled for Excel files and sure enough it was. I also found some information that indicated that Blob Caching caused the http headers to be changed and although it seemed like a long shot, I was hoping this might resolve more than just that one error. I removed the xls extension from the Blob Cache line and tested again.

Even though the errors had been somewhat inconsistent, I had been able to reproduce them, but after making the change to the web.config I could not reproduce them. Not just the one or a couple of them, I could no longer reproduce any of the problems. I asked the users who had experienced these problems to test and they also were not able to reproduce the problems.

Key Improvements to Master Data Services are on the way

SQL Server 2012 – formerly known as “Denali” – will be available March 7, 2012. My colleagues at Blum Shapiro have been participating in private technical sessions with the SQL Server team over the past 6 weeks to learn of the many improvements. Today, I want to share some details on what you can expect with regards to Master Data Services.

Silverlight to the Rescue

We’ve been working with Master Data Services since the release of SQL Server 2008 R2. No question that it is a flexible Master Data Management platform, but those of you who work extensively with the product are aware that the Master Data Portal, the primary way for Data Stewards to interact with the master data, has a very unfriendly user interface. In 2012, the data entry grids are all Silverlight based. This makes for a much improved experience for both filter and CRUD operations.

This is what the Explorer View looks like in the Master Data Portal. Note the improved Record Detail interface to the right.


Excel – the Microsoft BI Swiss Army Knife

Microsoft loves to extend the usability of Excel. No surprise then that 2012 includes an Excel Add-In for Master Data Services. Authorized users can insert, edit and delete master data records within Excel. Even better, data administrators can create entities and attributes directly in Excel! This is a huge benefit to MDS model builders who need to quickly get new master data entities created in Master Data Services. Simply select the data you want to import into your model and click Create Entity.


The thing that interests me most about the two improvements is that it seems to be highlighting the focus that Microsoft has on Master Data in the hands of Information Workers. For most of my MDM clients, Master Data can be found mostly in Business Systems, not on a file share. I think the question Microsoft is posing here is: are you sure you know where your master data is created? This is a challenging question for many organizations.

These are just two. Next time around we’ll talk about the improved Staging infrastructure.

Brian Berry is a Director of Technology Consulting with BlumShapiro, focusing on Microsoft Business Intelligence solutions, with a strong focus on Systems Integration, Master Data Management and PerformancePoint Services. He has been helping companies optimize their investments in Microsoft technology for over 12 years.