Pages

Monday, December 24, 2012

Introduction to Excel GeoFlow (Beta 1)

I have been playing around for a couple of weeks with the beta release of Excel GeoFlow and I  have got very mixed feelings about the product. No, I am not trying to be the Grinch in this Christmas season and I must say that I sure do like the direction in which Microsoft and this product is going – just that it is not even half as ready as I would have expected (but to be fair to them, it is just the beta 1 release). That said, there are a couple of features that I am really excited about and I thought of jotting down a quick introduction to GeoFlow for those who are interested.

image

1) How to get the beta release of GeoFlow
If you would like to try GeoFlow, send a mail to
geoflowbetarequest@microsoft.com and wait for the team to respond. You must have Excel 2013 for GeoFlow to work.

2) Current Limitations
- Related tables are not supported in this release.All the data should be in one table.
- Backend data changes are not reflected in the visualization in this release and this will mean deleting and recreating the visualizations.
- No Undo/Redo is supported in this release.
- No way to slice and dice data within the visualization
- No drill down functionalities from a higher level (say, states) to a lower level (say, counties) which is there in tools like Power View.

3) Create a basic GeoFlow report
GeoFlow requires your data to have some geographical fields in it. It can be in a variety of formats including latitude and longitude, street address, city, zipcode, state, country, etc. Be aware that you will need internet connection for the geocoding to take place. For the purpose of this demo, I have got the population and personal income data by year of all the counties in the United States. Follow the steps below to create a basic GeoFlow report:-

a) After installing GeoFlow, you should now be able to see the 3D Map button in the Insert tab. Click on any of the cell within the data table and then select the 3D Map button within the Insert tab. Click on the Explore in 3D option as shown in the image below.

1 Insert 3D Map button

b) Choose the geographical field based on which you need the map. In my case, it is the StateName field. Click on Map It button to proceed.

2 Choose geo field

c) Click on the Population field to see the states visualized by this measure.

3 Basic map of population by states

By default, you have got the Column chart visualization (which can be clustered or stacked if a category is present). This can be changed to a bubble map or a heat map visualization also.

4 Bubble map

4) Navigating the 3D Map
Navigating the 3D map can be tricky so it is useful to know all the navigation features. To use a mouse to navigate in the GeoFlow 3D environment, do any of the following:
·         Double click rapidly on any portion in the globe to zoom closer to it.
·         Use the scroll wheel on your mouse to zoom in and zoom out.
·         Click and drag the globe in any direction to pan without changing the pitch.
·         Hold the Alt key, and then click and drag to change the pitch.
·         If you lose your point of focus, zoom all the way out with the scroll wheel to reset the globe and view.

To use the Keyboard to navigate in the GeoFlow 3-D environment, do any of the following:
·         Tap the arrow keys up, down, left and right to pan and spin the globe.
·         Hold the Alt key and then tap the left and right arrow keys to orbit the current target, or the up and down arrow keys to increase and decrease the camera pitch.
·         Tap the plus (+) and minus (-) keys to zoom in and out.

5) Interesting Features
There are quite some interesting features in GeoFlow and these are some of the main ones in my opinion:-

a) Handles multiple geographical levels:- We can add multiple geographical levels to GeoFlow. To add the counties (AreaName field) to our basic report, click on the Edit button in the Layer Manager as shown below.

SNAGHTML5b930dd

Then select the AreaName field and map it to the County in Geography section. Then click on Map It button.

SNAGHTML5bba4d4

Now you should see the map visualized by counties. It might take some time for all the data to be processed on the map and it depends on the number of rows that you have. When the processing is done, you should see the message as Finished in the green bar below the map.

SNAGHTML5bdb42f

You can easily toggle back to the states by selecting the StateName radio button in the Map by section. However, as I mentioned before, drill downs are not possible (as far as I know in this release). Vote to up this request in Connect by clicking here.

b) Visualizing Data over time:- We can visualize the data over time if we have a date  or time field. In my dataset, I have the year but this is not enough as GeoFlow requires a date or time field. So I had to make a new filed called DateYr which converts the year to a date field by adding 1/1/ before every year. Vote here to up this request to have the time play axis use any level of the time dimension. Now in GeoFlow, drag and drop the DateYr field to the Time section. Now you will get an additional section for time settings which has values like Time Accumulation, Instant and Persist the last.
SNAGHTML5cafdfb

You can also see the time play axis on the bottom. Click on play and you can see the data changing over time.

c) Adding Annotations and Textboxes:- This is definitely one of my favourite features. We can now add an annotation to a particular data-point by right clicking on it and then selecting the Add annotation option.

image

Similarly, you can add a textbox also. The only difference between them is that an annotation is bound to a data point while the textbox is more like a sticky note on the screen. The textbox stays at the same position even when the map is panned or zoomed.

d) Find a Location:- The find a location feature is pretty handy in case you have a lot of data points and want to directly zoom to a particular location.

SNAGHTML5ddebe2

e) Creating and Saving Tours:- I have obviously saved the most exciting feature for the last. Now you can create tours by clicking on the New Tour button and then including the scenes in the tour by selecting the Capture Scene button.

SNAGHTML600fded

Scenes will transition automatically and effects are automatically applied. A scene is auto-saved when you capture a new scene. If you want to change a scene, for example if you want the scene to show a different angle or a different time subset, select the scene, make the changes and then click on Save Scene, Once you have created a tour, you can play it by selecting the Play Tour button. I have posted a video of a sample tour that I created.

Excel GeoFlow

After creating a tour in GeoFlow, the tour is automatically saved back to your workbook. You can create multiple tours by clicking on New Tour. Saving your Excel workbook will save each of the GeoFlow tours you created with the worksheet it was created from. Now you will be able to see a new option to Manage Tours also.

SNAGHTML6063add

To summarize, there are some exciting features but clearly, a lot of work needs to be done before it can be used by data professionals for analysis. With this, we come to the end of today’s post. Wishing all of you a very Merry Christmas from my side and be good!

13 comments:

  1. Hi Jason,

    I have a question on SSRS Report Error bar chart and Stock Charts. Sorry iam commenting on this post as it is not related.
    Can we display different markers or colors for High and Low value points in Error bar chart,
    similarly can we display different markers or colors for High, Low, Open, Close points in Stock Charts.
    I am unable to find a solution till now.
    Hope you have a solution for it.

    ReplyDelete
    Replies
    1. As usual, no out of the box solution. The best I could do was to duplicate the value (so now I have 2 measure values which are the same), and then set one value to one color (say green) and the other value to another color (say red). Now I go to the CustomAttributes property and set the ErrorBarStyle as Lowererror and for the other value, I set it as UpperError. So one measure will only show the upper marker and the other one shows the lower marker. And since they overlap, it looks like the markers have different colors. But the problem is that the body line of the error bar will also have both these colors...

      Delete
    2. Or you could try making a stacked column chart and set an image as the marker (2 images in this case which will be the two lines in the color you want). The lower part of the stacked column chart can be transparent, and for the upper part, set the PointWidth property as .05 or something, so that it looks like a line and not a bar. Very difficult to say something more without knowing your complete requirement, send me a mail if you have further issues.

      Delete
    3. Thanks Jason for your reply. Can you give me your email id. I will you the pic of the required stock chart

      Delete
    4. send a mail to jason143 at gmail dot com

      Delete
    5. Can you highlight the diiferences between SSRS Map option and the Excel Geo Flow?

      Delete
    6. There are a lot of differences between SSRS Map Option and Excel GeoFlow. Let me note down some:-
      1) Excel Geoflow will have dynamic panning and zooming, while SSRS Maps will always be static (the entire page will have to refers if you need to pan or zoom).
      2) Excel Geoflow can do the geocoding based on either the latitude/longitude information, or even name. For SSRS Maps, we will need to have a shapefile/spatial data and then join the analytical data with the spatial data based on the join column.
      Because of the same reason, SSRS doesn't need internet connection (unless you add the Bing Tile layer) while GeoFlow requires internet connection.
      3) SSRS Maps are 2D while GeoFlow maps are 3D. For the same reason, you can make column charts on GeoFlow but not on SSRS Maps.
      4) SSRS Maps can also work with Polygon or Line layers in addition to Point Layers, while GeoFlow works only with Point Layers. As a side effect, you can do Polygon shading (or chloropeth mapping) in SSRS while you cant do it in GeoFlow
      5) You can make your own custom layers in SSRS which extends it's mapping capabilities to beyond geospatial uses. However, GeoFlow is strictly related to GeoSpatial analysis.
      6) You dont have the time play feature in SSRS

      Delete
  2. Hi Jason,

    Thanks for the info re: sign up. Got my Connect request yesterday and have been playing around with it using your blog post and the instructions provided by MS.

    One question, are you using Win 7 or Win 8 as your OS? On identical spec laptops with Excel 2012 64bit, Win 8 does not display the Geo Flow window when selected.

    Just wondered if you'd encountered the same thing if you've tried using Geo Flow on Win 8.

    Good blog post.

    ReplyDelete
    Replies
    1. Hi Andy

      Im having the same issue with opening the map. Did you manage to resolve the issue?

      Brad

      Delete
  3. Hi jason,
    We could find add in of Geoflow,
    please help where we can find

    ReplyDelete
    Replies
    1. Check out my recent post - http://www.sqljason.com/2013/04/download-geoflow-preview-for-excel-2013.html

      Delete
  4. Hi Jason,
    I have a problem while trying to run Geoflow, when i am clicking on Geoflow icon its showing "Geoflow encountered an error and can't be launched,Please check your internet connection and try again.
    But i have checked i have proper internet connection. So please provide me the solution for this.

    ReplyDelete
    Replies
    1. I also faced this issue when my internet connection was flaky. Close and try again when the internet connection is more stable (worst case, you might have to reinstall, though always closing and opening in a place where the internet connection was stable always worked for me).

      Delete