Chris Webb's BI Blog

Analysis Services, MDX, PowerPivot, DAX and anything BI-related

Self-Service BI Mapping with Microsoft Research’s Layerscape–Part 2

with 6 comments

In my last post I showed how to load data from Excel into Layerscape, the new data visualisation tool from Microsoft Research; in the post before that I showed how to load UK weather data from Windows Azure Datamarket into PowerPivot. Now let’s take that weather data and plot it on a map!

When doing this, the first decision I made with this data was about what to show. The main problem is that the data volumes involved here are very close to the upper limit of what Layerscape is able to handle: in a few cases I crashed Layerscape, but that was when I was trying to load around 150000 rows of data into it; I found that just over 110000 rows of data was ok however. As a result I made the decision to only show data for cloud or rain, not sun (which is ok – if you want to visualise a clear day, you don’t want to show anything on a map I think) or mist or fog (which I was less happy about).

To achieve this I created a calculated column on my Three Hourly Forecast table in PowerPivot called Cloud with the following definition:

=if([SignificantWeatherId]>=7, TRUE, BLANK())

Then, after having created a flattened PivotTable with the data I wanted to display, I dropped the Cloud field into the Values box in my PivotTable and was able to filter it to only show Weather Stations and time periods where there was cloud:

SNAGHTML31b117d

I also created a few other calculated columns:

  • ActualPredictionForStart and ActualPredictionForEnd: the former is mentioned in my previous post, and the latter returns a value three hours after the former:
    =[Date] + (([TimeStep]+3)/24)
    These two values represent the start time and the end time for each row in the Three Hourly Forecast table.
  • WeatherDepth: in the Significant Weather table there’s a distinction made between low-altitude cloud and high altitude cloud, and in LayerScape when you’re plotting data you can control how high off the ground a point is displayed, so the WeatherDepth column contains some fairly arbitrary numbers for cloud altitudes based on the [Code] column. Here’s the definition:
    =SWITCH([Code], 5, 150, 6, 150, 7, 100, 8, 120, 10)
  • WeatherColor: again, in Layerscape you can control the colour of your points and their opacity, which again is useful for displaying dark/light and thin/thick clouds. The only compromise I had to make was to display dark clouds (ie where clouds are described as either black or where the weather is rainy or snowy) in blue rather than black, because I couldn’t get Layerscape to display black – white clouds are shown in white.  To calculate WeatherColor I created two intermediate calculated columns on the Significant Weather table, InitialColor (which contains colours for weather that I’m not displaying in this demo – I thought dust storms should be shown in yellow for instance):
    =SWITCH([Code], 1, "yellow", 2, "white", 3, "yellow", 4, "yellow", 5, "white", 6, "white", 7, "white",  8, "blue", "blue")
    …and Opacity:
    =SWITCH([Code], -99, 0, 0, 0, 1,10, 2, 25, 3, 10, 4, 100, 5, 25, 6, 75, 7, 50, 8, 50, 9, 60, 10, 60, 11, 60, 12, 65, 13, 75, 14, 75, 15, 80, 90)
    …making the definition of WeatherColor:
    =[Opacity] & "% " & [InitialColor]
  • Depth and Color: calculated columns on the Three Hourly Forecast table that copied the WeatherDepth and WeatherColor values down to the main fact table:
    =RELATED(‘Significant Weather’[WeatherDepth])
    =RELATED(‘Significant Weather’[WeatherColor])

This screenshot gives you some idea of the values that the depth and colour calculations return:

image

With this all done I was able to load the data into Layerscape in the way I showed in my previous post, tweak some of the settings for the markers and time decay, and come up with a nice-looking visualisation. The big difference in this case compared to my previous examples is that here we have time series data and Layerscape is able to show values changing over time. The only thing I needed to do to make this happen was to check the Time Series box in the Layers pane on the main screen; with this done I could show the data for a particular point in time or let Layerscape cycle through time showing how the forecast weather changed.

image 

With that done, was able to save my demo as a very basic guided tour and upload it to the Layerscape site from where you can download it yourself :
http://www.layerscape.org/Content/Index/620

I also created a video showing the tour:

 

I’m quite happy to admit that this tour doesn’t show off the full capabilities of Layerscape (I’m not sure my graphics card is up to the job, frankly) and I’ve not taken much time to ensure that the visualisation is as accurate as it could be, but I’m still quite proud if it!

Written by Chris Webb

April 18, 2012 at 4:35 pm

6 Responses

Subscribe to comments with RSS.

  1. [...] Will applications like Layerscape raise the bar for BI products generally? Products for the intelligence community? Update: Self-Service BI Mapping with Microsoft Research’s Layerscape–Part 2 [...]

  2. Hi Chris,
    I am very impressed about this and the two previous posts. But after thinking a while about it, I’m not sure if Microsoft helps us a lot for this kind of self service BI… My question is: What do I have to do if I want to see the weather forecast for Amersham on April the 22nd? As I understood I have to change the underlying query in power pivot, reload the data into power pivot, refresh excel and refresh layerscape. And if I want to see the forecast for London, I have to do the same again.
    I want to read data from a database in the cloud into Excel. Why do I have to use a second “database” (PowerPivot) to manage this?
    In my opinion it would be much more straightforward if I could use Marketplace (or other OData-Feeds) directly from Excel and if a filter in Excel (e.g. on Amersham) goes directly into the $filter-parameter of the feed’s URL. Of course a few features of PowerPivot would be useful in this scenario (e.g. linking tables), but PowerPivot as a data-buffer is not needed.
    I’m working in ERP business and I’m trying to use Excel as a frontend to the ERP-data (including some web-service based calculated data), such that the end-users can do their own analytics without using an OLAP-database. At the moment I see no easy-to-use Microsoft solution in this scenario.

    Frank

    April 21, 2012 at 7:01 pm

    • Well, you don’t actually need to go via PowerPivot – there’s a Datamarket Excel addin that allows you to import data directly into Excel:

      https://datamarket.azure.com/addin

      …and of course you could also use Data Explorer to do this too. However, it’s not very often that you can import data into Excel in exactly the format and layout that you need it, and that’s why PowerPivot as a buffer is so useful: it allows you to get the data in exactly the layout you want by building a PivotTable.

      Chris Webb

      April 21, 2012 at 10:16 pm

      • I did not know Data Explorer. Thank You for the hint.

        Frank

        April 23, 2012 at 11:56 am

  3. [...] Telescope; you can find a more detailed explanation of how it can be used with PowerPivot here and here on my [...]

  4. [...] Telescope; you can find a more detailed explanation of how it can be used with PowerPivot here and here on my [...]


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 3,302 other followers

%d bloggers like this: