Hope Foley's Blog

Another day in the life of a SQL Server DBA

Datazen – Connecting Drill Through to Navigation Apps July 27, 2015

Filed under: DataZen — hopefoley @ 12:29 pm
Tags: , , ,

I just accomplished something very cool in Datazen and I couldn’t wait to share with everyone! I was working on a POC for a client with Datazen. We were doing some review and the client had an interesting request. We were going to add a drill through to information that would contain an address. The client said “If this could be a link that a phone’s navigation would recognize that would be slick”. I thought to myself “oh that would be very cool” and got a little nerd giddy at the thought of trying to do that.

 ***

After doing a little searching I found that you can link to a map using the format: http://maps.google.com/?q=term. The “term” portion is where you would update it with your search. So in my case I can put the address. It has flexibility as well so you can do something like this if you would want: http://maps.google.com/?q=statue of liberty.  When you use that link it brings up the map below.

Lady Liberty

Lady Liberty

Back in Datazen I created a Simple DataGrid that contained the fields I wanted to display including address. I then read the documentation on the Datazen website documentation for custom url drill throughs (http://www.datazen.com/docs/?article=server/drill_throughs_to_other_dashboards_or_custom_urls). The example made me think I had to have a selection be passed to the url. I didn’t want to do that, I just wanted it to pass a field I had displayed in my DataGrid.  However when I started playing with it, it showed my fields contained in the DataGrid as well! Sweet!  So I created a drill through for my DataGrid. In the expression window I put “http://maps.google.com/?q=” then selected the field (SimpleDataGrid3.Address).

***

When testing this drill through on my iPhone it didn’t work (insert sad trombone music).  It would kick me out of DataZen app and go to my home screen but nothing more. It was like it was trying to do something but wasn’t working. I did some more research and found this link (https://developers.google.com/maps/documentation/ios/urlscheme). It refers to the url scheme to launch navigation from apps. So with information from that article I changed the beginning of my link to comgooglemapsurl://maps.google.com/?q=. I test again and it works!

The custom url drill through

The custom url drill through

When I clicked the drill through it would open Apple Maps with that address displayed. Cool egh?! It also then reminded me that I hadn’t changed my default to Google maps which is now resolved as well.

 

Datazen Custom Map Craziness June 15, 2015

Filed under: DataZen — hopefoley @ 12:09 pm
Tags: ,

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.

 

BlueGranite Self-Service Whitepaper June 5, 2015

Filed under: BI Resources — hopefoley @ 2:49 pm
Tags: , ,

Wanted to share with everyone a whitepaper my company, BlueGranite, has out on self-service BI and making those projects/initiatives successful.

BGSelfBI

 

 

Datazen – Map Not Working? May Never Guess Why May 28, 2015

Filed under: DataZen — hopefoley @ 5:30 pm
Tags: , , , , ,

I recently started learning Datazen, along with a lot of folks since Microsoft purchased them. If you do searches on Datazen, there is information out there but not an enormous amount. There is a great overview video by my friend/colleague Megan Longoria at http://www.blue-granite.com/video-datazen-overview. There is also some nice info at http://www.datazen.com/docs/. However there appears to be a HUGE void in posts that detail overcoming issues with the product. I’m sure that will change as more start digging into it. Here’s my first post to help fill the void. That and my karma can always use boosting so I wanted to get this post out asap in case it helps someone getting started as well.

 ***

Many know I love all things spatial so I was naturally drawn to doing one of the maps in Datazen. I get my data situated just right in Excel (another potential post there). There isn’t much to configure when setting up a map but I then do that. You can see what I had below.

MapBeerData

I have some simple brewery/beer data (shocking I know). I wanted to visualize the location with a bubble and the size of the bubble based on beer ratings. I tried address, city, and then even state with no luck. I had hoped to get a more finely defined location but I knew I could get state by the examples and documentation here (http://www.datazen.com/docs/?article=server/configuring_visualizations). It just wouldn’t work at all and kept returning blank map with no bubbles when I would preview. After a stern cussing, I start to look at the sample tables of other map examples. One of the table examples is below.

StateExample

I noticed they had state spelled out fully. Thinking to myself “No way that’s the problem. No way it’s that picky”. I start to give it a shot. You can see above I had used the state abbreviation. I add a column to my Excel data with the state fully spelled out and put that as the key. Lo and behold that fixed the issue!

BeerMap

Finding that Datazen is as finicky as a spoiled toddler and I’m just getting started. There’s a lot of potential there but Microsoft, you have A LOT of work to do!

 

Cleveland Rocks – SQLSaturday Cleveland 2015 February 17, 2015

Filed under: Uncategorized — hopefoley @ 2:21 pm

Heading home now from SQLSaturday Cleveland. I know I say all the SQLSaturdays are good but this one really was fantastic! I am a little amazed at how many passionate people are in that area that help with that event. I think a lot of that has to do with Mr. Allen White (Twitter|blog) and his constant encouragement of others in the community. It shows and is inspiring!

***

So I head out from Indy on Friday with Warren (Twitter|blog) who graciously agreed to drive. Which thanks to that and technology, I was able to finish up work while on the way. He then drops me off at Harness Cycle spin studio. Real life Super Woman Erin Stellato (Twitter|blog) set up Joey (Twitter|blog) and I to do a spin class there that she instructed! Is there anything she can’t do!? :) Thanks Erin for setting that up!  Was really nice to have a fitness activity that for a change that wasn’t running related.

 ***

From there we head to Ironwood Café to the speaker dinner. They setup a feast! When the waitresses started bringing food, they didn’t stop! Well done there organizers, I was impressed. After that I head back and chat for a good long time with the dynamic duo Data Chix, Audrey (Twitter|blog) and Julie (Twitter|blog).

 ***

Event day comes and I get myself over to Hyland Software at a reasonable time in the morning. I was lucky again and have a session right after lunch so wasn’t rushed. The Cleveland folks are lucky with that venue they have at Hyland. It’s a great facility! I am super jealous! After getting there I head to the speaker room which was setup with an amazing spread! Brewnuts (donuts with BEER in them!!), healthy options, and homemade goodies including caramels from Erin (see I told you…Super Woman!).

 ***

I was able to sit in on Joey’s lunch session and show support to one of the great SQL community sponsors, SIOS.  After that I had my session which was on spatial data again. I really enjoy that one as it shows the potential analysis with floor plans and spatial data. I had several say they enjoyed it and even two who told me it was their favorite of the day!

 ***

After my session was over I went to watch my homegirl, Julie’s session on SSIS. She was doing a decent amount of tweaking to it so was there for some morale support in addition to learning. She’s a pro and it was great. I know I have run into some of the same things she went over. SSIS can be, well let’s just say non-intuitive at times. She goes over many of these spots that can make folks bang their heads on their desk.

 ***

Last session of the day I caught Michael J. Swart’s (Twitter|blog) Drawing Conclusions talk. He is such a great artist and he used his illustrations to convey some SQL Server lessons. Good stuff and glad I was able to catch that one. That and I got to listen to him say “about” (he’s Canadian).

Michael Swart caught a join this big :)

Michael Swart caught a join this big :)

After the prize drawings we all went to Ambrosia to decompress and have some dinner. It was a Hungarian place. The food and company was great! And I found out I do like stuffed cabbage…who knew?! The staff seemed a bit overwhelmed but it happens. After that a few of us went over to Bar Louie for one more beverage and chatting.

The crowd at the end of the day for prize drawings.

The crowd at the end of the day for prize drawings.

Thank you Cleveland folks for having me speak again! Looking back on the weekend I really do feel blessed to be a part of this community. Good people, good times!

 

Spatial Data – Outside the Map slides February 13, 2015

Filed under: Uncategorized — hopefoley @ 4:21 am

Wanted to make sure to get these slides up for the user groups I have presented to lately.  Hope you enjoyed the session!

SpatialDataLookingOutsideTheMap

 

SQLSaturday Nashville 2015 February 9, 2015

Filed under: Uncategorized — hopefoley @ 4:02 pm

I am just wrapping up a great start of SQLSaturdays to begin 2015. The first one was in January with SQLSaturday Nashville. I know I have mentioned it before but Nashville has a special spot in my heart. My very first SQLSaturday I ever presented at was in Nashville so I always try to make it down there. That and Nashville is full of some of my favorite SQL peeps!

 ***

So head down with my husband, Rod (Twitter) on Friday afternoon. Love it when he’s able to go with me so he can be a part of this side of my world. We love traveling together too so it’s a win win. We make it down in time to head to the speaker dinner which they had at Tamera (Twitter|blog) and Kerry’s (Twitter|blog) house. God bless the brave souls who open up their houses to host parties related to their events. I love the intimacy/coziness of those parties. I just can’t imagine putting the added stress of cleaning house/etc on top of organizing an event. Dinner was great and Tammy/Kerry/family were great hosts!

 ***

Event day we head over to the venue at Lipscomb University. It’s a really nice space for the event. Being the swell guy he is, Rod agreed again to take pictures of the event (see them here). I get situated and begin doing preparation for my session. I did my latest incarnation of spatial data presentations. I enjoy doing that one so much! It went well and got some good feedback so I think folks enjoyed it.

Talkin spatial

Talkin spatial

After the event Rod and I went to grab some dinner. I enjoy me some food and was really excited to try a local specialty called “hot chicken”. We heard a song about it from Dom Flemons and figured if it’s good enough to write a song about it, it had to be good! Thanks to Louis’s (Twitter|blog) recommendation we go to Big Shakes and it was amazing! Like I needed more reasons to get down to Nashville but I now have hot chicken to add to the list.

Good times at after party!

Good times at after party!

From there we go to the after party at Athletic Club Bar & Grill. Was nice as it was at the hotel so didn’t have to worry about driving anywhere and could relax. Such a good time! Between the tales of “Ha-va-ee” from Robert Verell (Twitter) or Gareth’s (Twitter|blog) swamp people impression, I thought I was going to pull a muscle laughing so hard. Great meeting new folks and being with old friends. Thanks to all at Nashville for having us and congrats on another excellent event!

WIT panel discussion

WIT panel discussion

One of the big lessons/impressions I took away from Nashville was from lunch at the event. I was asked to be a part of the panel for the WIT lunch discussion. During all WIT talks I always think of how I am with my daughter. I had a little revelation while we were talking. I want to make sure I help her understand that no matter where you go, there will always be people who know more than you do on any given topic. There are those who are just genuinely conveying knowledge and know more than you. There are also those douchebags that make themselves feel better by making others feel stupid. Whether it’s someone who is trying to make you feel like an idiot, or just someone who knows more and is trying to help, don’t EVER let your lack of knowledge dissuade you from anything! I know I have been there, I think we all have. I hope I can help both my kids to understand that as it’s not just a lesson for women but all of us. Keep learning and embrace that you don’t know it all and you never will. And when you encounter the douchebags, use those feelings to light the fire under your tail to get better!

 

 
Follow

Get every new post delivered to your Inbox.