DataZen

Datazen Custom Map Craziness

I often struggle coming up with ideas for blog posts. Thank you Datazen again for another idea!  After encountering the issue I posted earlier (link to post) when working with maps, I was very anxious to try to create a custom map.  I started working on one with my brewery data.  I ran into several bumps in the process so wanted to share with everyone.

 ***

I had collected several shapefiles through the years playing with spatial data.  I began trying a few of them in Datazen but had a hard time finding any that worked. When I would select them, they would just hang and sit there. Some were very large so I suspected there was a size limit though there wasn’t anything in the documentation. In my testing I couldn’t get anything over 1MB to work. Later Chris Finlan said that the actual limit is 512 KB! Rather limiting but there is a potential workaround by using mapshaper.org to minimize the size. Depending on what type of shapefile you are using it can distort your polygons potentially too much so that workaround only goes so far.

Tweet from Chris Finlan on shapefile size.
Tweet from Chris Finlan on shapefile size.

After getting the size issue nailed down, I continued on with my mission of getting my custom map working. Normally when you use shapefiles they contain a few files. Datazen uses the shp file and dbf file. The shp file contains the polygon information and details to draw the map. The dbf file contains the attributes about those shapes or the data you’ll need to tie to your own data. Below is a screenshot of my dbf file using DBF Viewer 2000 software (free trial then about $50 to purchase after 30 days at http://www.dbf2002.com/).

The original dbf file
The original dbf file

You can see it has a key field (ogr_fid) then zcta5ce10 which is actually field that contains zip code. The zcta5ce10 field is the key I needed to use to tie with my own data containing zip codes. When you use Datazen you can’t select the proper key, and as it turns out it must have the key as the first column in the dbf file! Crazy right?! Using the DBF Viewer 2000 software, I had move the column to be the first in the dbf file.

Updated dbf file to move key column to be first.
Updated dbf file to move key column to be first.

After a bit of head shaking I move on and at this point I was excited to see my custom map working! But wait, why is my data point not where I expect it to be? I was mapping zip codes of breweries in Indiana that have been rated on BeerAdvocate. There is unfortunately only one (though I’d argue that should be more) and it’s Three Floyds which is up near Chicago but the Datazen map wasn’t accurately showing it (see below).

The report displaying the location incorrectly.
The report displaying the location incorrectly.

I opened up the shapefiles in other programs (QGIS/Google Earth) and it was showing correctly both before and after minimization process. After talking with Datazen support it, they sent this info:

 ***

“While I can’t say for certain where things went wrong, I suspect it was due to improper minifying – something got misaligned between the SHP and the DBF and QGIS and Google Earth all read the SHX file to properly map the shapes to the database. Datazen does not do that. We assume a 1-to-1 relation between shape and data: the Nth shape corresponds to the Nth entry in the DBF.”

 ***

I had inadvertently changed the order of the data in the dbf file when I had to edit the order of the columns. Since other programs I use to view shapefiles also use the shx file, there wasn’t an issue with them correctly mapping the shape to the attribute even after accidentally changing the order. A shx file is basically a linking/index file between the shp and dbf files. Be very careful when editing the order of your columns in the dbf file. One accidental click on a column and it can ruin the dbf/shp file from working properly. If they don’t line up, your report can be reporting your data in random polygons! I’m glad I had a very simple dataset and that I knew where Three Floyds was supposed to be located as I may have very easily missed that. Make sure you do some validation on your reports to be safe.

 ***

I’m very interested to see what Microsoft does with Datazen now. I’m intrigued to see what/when any updates to it occur. I’m also interested in others experiences with custom maps in Datazen. Please share in the comments any issues/experiences you encountered.

Advertisements
Power BI, Uncategorized

Custom maps in Power Map

Many folks know I love playing with spatial data. In one my current presentations, Spatial Data – Looking Outside the Map, I do a demo creating a floor plan with spatial data and do analysis and reporting against some hotel data. I am actually on my way to Seattle right now and have the great honor to present this at PASS Summit. In the September update there was an exciting feature released to Power BI, custom maps in Power Map. What it essentially allows you to do is to map your data to any image file you want. Seeing some of the examples of what could be done (http://blogs.msdn.com/b/powerbi/archive/2014/10/07/5-minute-tutorial-for-creating-custom-maps-with-excel-and-power-map.aspx) I was excited to see what I could do with my demo hotel data.

 

I have the click to run version of Office so it runs updates auto-magically. When I opened Excel though I didn’t see the option for custom maps. So I go to File – Account – Update Options and choose Update Now. It runs the update and voila, custom maps.

 

So my data is really typically hotel data that is stored in SQL Server. I like to call my hotel “Plaza Del Hope”. It contains run of the mill information for Plaza Del Hope guests. One of the main things it contains is arrival dates of my hotel guests. I pulled that into Power Pivot and create all the relationships I’ll need. After I have my data in the model in order, now begins the less than super fun part. How custom maps work is that you will use an x, y axis information to detail how your data will relate to the image file. So you will need to create those coordinates. The easiest way to do that is to open your image in Paint. Then you need to make sure your Paint is set to show coordinates in pixels. Mine was not and instead was using inches. If you need to change it then select File – Properties – and then specify the units to be pixels.  Then when you hover over your picture, in the lower left it will display the X,Y coordinates for the image.

PlotPoints

You can see here I am using an image of a hotel floor plan. I want to be able to visualize the data pertaining to the guests staying in the rooms so I need to tie each room to a coordinate. Now comes the tedious portion. I have to go through and get the x,y coordinates for each room on my floor plan. You can see here I put the x axis coordinate, y axis coordinate, and the corresponding room number into the Excel spreadsheet that contained my Power Pivot model. Doing this portion is when I tried to send telepathic messages of my wish to put spatial data support into the Power Map/View/etc tools to the Microsoft folks.

PlotPointsExcel

Once I had that I then pulled that info into my model by clicking Add To Model. I then added the relationships that pertain to RoomNumber.  Now I was ready to start building my custom map. I open up Power Map (From spreadsheet click the Insert tab – click Map – launch Power Map. I select New Scene – New Custom Map. You can name your custom map scene. I set mine to Hotel. Next I select icon next to “Browse for the background picture”.  I then navigated the location for my image that plotted out before. After that I click the Select Pixel Space button and done. I now have a scene that contains the floor plan for my hotel. I wanted the first portion of my tour to contain a view of just the floor plan so I leave this scene alone and create a new scene. I can do this quickly but choosing the Copy Scene option.   This second scene is where I want to start showing some data.

CustomMap

With the second scene active, on the right I will start building my data story. First will specify Geography and Map Level. In the table selection I find the table with the x, y plots (PixelXY). I select X and Y fields and specify they are X coordinate and Y coordinates.

CustomMap2

What I want to show is when a guest is staying in the rooms so I select LastName and drag to Height(Count Not Blank). The height is a bit higher than I need it to be so I change that by on right hand pane, clicking the gear looking icon to change settings.

CustomMap3

The layer 1 info screen inside custom map isn’t providing any value so I remove that. Next I want to add a time dimension so I will do so by going back to the field list and select ArrivalDate. It will then bring up the play capability on my scene. I am able to click play and can see the arrival of our hotel guest over time and see a visual of what rooms are occupied. Pretty cool huh?!

 

The X,Y axis plotting is a bit, well not fun but it is definitely a good start. Hopefully this is a stepping stone on the path until we can get spatial data in those tools. The potential for custom maps is exciting and very interested to see how people begin to use them. Please feel free to post in the comments if you have done anything with them.

 

Below is the full detailed list of steps. Enjoy and have fun with custom maps!

 

  1. Open Power Pivot and pull in data.
    1. Power Pivot – Manage
    2. From Database – SQL Server
    3. Create any relationships between your tables pulled in. Can do various ways including dragging and dropping in the diagram view
    4. Using a date table so marked it as such
      1. Changed back to Data View
      2. Click the Design tab – Mark as date table
      3. Select the date field
  2. Map out the plots for spaces to use in picture.
    1. Open picture in Paint
    2. My paint was showing units in inches as opposed to pixels. To change:
      1. File – properties – select pixels
    3. Hover on a point in the picture where something would be you would want to plot. In my case I’m showing the booked rooms in a hotel.
    4. Capture the x, y axis info. I put in Excel worksheet where I had pulled my data into Power Pivot. I was plotting out hotel rooms so I also put the hotel room number to correlate it to the x,y plot.
  3. Then I click Add to Data Model
  4. Created relationship with my new table with existing ones in model based on Room Number
  5. Create Custom Map
    1. Go back to Excel workbook
    2. Select Insert Tab
    3. Click Map
    4. Select Launch Power Map
    5. Select New Scene
    6. Select New Custom Map
    7. Name your custom map scene – I set mine to Hotel
    8. Select icon next to “Browse for the background picture”
    9. Navigate to and select the picture you plotted out before.
    10. Select Pixel Space button
    11. Click Done
    12. Can now delete the first default scene and our new custom map scene is only one remaining
    13. I will leave this scene containing only the blank view of the floor plan. Can adjust the scene duration.
      1. Hover over scene and click on the gear looking icon to change scene options
      2. On the right I will change the value to 4 in scene duration
    14. Select New Scene – Copy Scene
    15. With the second scene active will specify Geography and Map Level
    16. Find the table that contains the x,y plotting we did before (PixelXY)
    17. Select X and Y fields and specify they are X coordinate and Y coordinate
    18. Click Next
    19. Select LastName and drag to Height – should be Count Not Blank
    20. On right hand pane – click the gear looking icon to change settings
    21. Bring the height down a bit
    22. Remove the layer 1 info screen inside custom map by clicking the x
    23. Go back to the field list and select ArrivalDate. It will then bring up the play capability on your scene.
    24. Click play and can see the arrival of our hotel guest over time and see visual of what rooms are occupied.