The Idea

Lately one of my customers came up with some interesting requirements: They want to store geotagged pictures on-premise and visualize these pictures in a map view (like you might know it from Skydrive.com, Flickr, etc). I have built this demo to show how easy and flexible it is to build a solution like this with Power BI. This solution only uses onboard features of Excel 2013 and SharePoint 2013 and doesn’t need a single line of custom code.

The Solution

In this demo I use a couple of surf shots from my last surf trip to Fuerteventura. These pictures include GPS coordinates as EXIF tags, which have been added by my Nokia Lumia 920 while taking the photo. These EXIF tags will be used for visualizing the locations in my gallery. To demonstrate the real power of Power BI, I use Power Query to pull data from a website called MagicSeaWeed.com. This websites provides live data about wave sizes, wind directions as well as general information like the seabed of surf spots around the globe.

1 

The PowerView Worksheet displays a map, visualizing the locations as well as the current wave sizes (represented by the size of the bubbles). As soon as items get selected in the map or filters list, the list at the bottom gets filtered. Searching for certain items (e.g. locations) also works because it’s a built in feature of PowerView. 
In the bottom list the user can see a thumbnail preview and can click a hyperlink which opens the picture in Internet Explorer in it’s original size.

2  

Architecture

The pictures are stored in a SharePoint document library where GPS data is read from the pictures and thumbnails are created instantaneously after the upload. PowerQuery accesses the library and also pulls data from a public website. Eventually everything gets loaded into a PowerPivot data model where joins and transformations occur. The PowerView worksheet uses the data model to visualize the data and it provides a user interface for browsing the pictures. Users open the pictures directly from the SharePoint library when they click the hyperlink in PowerView.

Architecture

Implementation

In the next segment I want to highlight a couple of key elements of my implementation.

Preparing the SharePoint Library

  1. Create a new Picture Library by clicking Settings > Add an App
  2. Select the Picture Library App
  3. Open the Library Settings and  add 2 new columns of type Single line of text:
    • wic_System_GPS_Latitude
    • wic_System_GPS_Longitude

When you upload a picture with EXIF data SharePoint and the columns are named exactly like described, you will instantaneously see the GPS data displayed in your library.

Note: I also enabled and used the Document ID Feature which lets you to reference a picture by one unique URL, even after it has been moved. More information about Document IDs in SharePoint can be found here.

3

Convert Latitude and Longitude from Degrees to Decimal in DAX

PowerView requires latitude and longitude in a decimal format in order to display the correct location in the map control. Since SharePoint provides GPS data formatted by degrees, I needed to transform latitude and longitude in DAX. This is how I converted the initial EXIF GPS data format into a format which can be used in PowerView:

String operation to retrieve degrees 
(same for latitude and longitude)

=LEFT([Wic_System_GPS_Latitude],FIND(",",[Wic_System_GPS_Latitude])-1)

String operation to retrieve minutes
(same for latitude and longitude)

=MID([Wic_System_GPS_Latitude],FIND(",",[Wic_System_GPS_Latitude])+1,LEN([Wic_System_GPS_Latitude])-FIND(",",[Wic_System_GPS_Latitude])-1)

Deviding minutes by 60 and considering N,S,E,W

=IF(RIGHT([Wic_System_GPS_Latitude],1)="N",[Degrees_Lat]+([Minutes_Lat]/60),[Degrees_Lat]+([Minutes_Lat]/60)*-1)

=IF(RIGHT([Wic_System_GPS_Longitude],1)="E",[Degrees_Long]+([Minutes_Long]/60),([Degrees_Long]+([Minutes_Long]/60))*-1)

Thumbnails

SharePoint automatically creates thumbnails for every picture uploaded. These images with lower resolution get stored in the folders [YourLibraryPath]/_w and [YourLibraryPath]/_t

There the images are also stored with a slightly different filename. The file WP_20131002_013.jpg for example gets saved as WP_20131002_013_jpg.jpg
That’s why I also used DAX text operations to generate the filename of the thumbnails after loading the original filenames into the data model.

When images are on an external site, Power View accesses the images anonymously. That’s why anonymous access for the picture library needs to be enabled. To enable Anonymous Access to a specific library follow these steps:

  1. In the Central Administration click Manage Web Applications
  2. Select your Web Application and open Authentication Providers from the ribbon
  3. Click Default and make sure that the check box Enable anonymous access is selected
  4. Navigate to your site collection and select Settings > Site Settings
  5. Select Site Permissions and click Anonymous Access from the ribbon
  6. Make sure Anonymous users can access: Lists and Libraries is selected
  7. Open Library Settings of your picture Library, select Permission for this picture library
  8. Select Stop Inheriting Permissions and open Anonymous Access
  9. Make sure View Items is checked

PowerQuery to retrieve Surf Reports

4

The 3 surf spots displayed in my demo are from 2 different pages on the magicseaweed.com website:

http://magicseaweed.com/Fuerteventura-Surfing/55/

http://magicseaweed.com/Fuerteventura-North-Coast-Surfing/304/

It was really easy to pull the data from these 2 locations and combine them with PowerQuery, though.
Just use the Append Feature in the PowerQuery ribbon to combine the two workbook queries.

5 

Does it also work in Power BI for Office 365?

The only problem when deploying this specific implementation to Office 365 are the thumbnails. The Office 365 implementation of PowerView unfortunately does not support external images (images which are not processed into the data model) yet. As a workaround you could load the thumbnails into a BLOB in a Windows Azure SQL Database, though. Then you could use these BLOBS directly in the PowerPivot data model, which is a supported way to display preview images also in Office 365 PowerView.

Download

I have created a second version of my workbook, in which I point the picture URLs to a public share. This keeps the PowerView part working, even if the SharePoint backend is not available. Obviously, adding new photos to the NoSharePoint version and refreshing the full data model is not possible.

The following Software is required to successfully run the demo:

Excel 2013
PowerQuery Preview for Excel (http://www.microsoft.com/en-us/download/details.aspx?id=39933)
SharePoint 2013 (if you want to create your own gallery)

Enjoy!