Matthew Campbell - BI, Spatial, Temporal, etc.

For those of you who follow my blog, I must apologize for being away for the past few months.  My wife and I gave birth to our fourth child in early November and our world has been a bit upside down every since.  When people find out that we’ve had four children in four years, their reaction is usually one of either respectful amazement or utter bewilderment.  To be honest, we are happy and appreciative to have four beautiful, healthy children…but I completely understand Bill Cosby when he said ‘And the reason we have five children is because we don’t want SIX’.  We are one less, but the message still applies. 

A big Thank You to all of those who live in Charlotte who have helped my wife and I through the last couple of months…be it helping with a meal, dropping by and spending time with the kids, etc.  It has meant a lot to us and we really appreciate it.

It is also the New Year so Happy New Year to everyone out there!  I am looking forward to getting back into my blog again and writing more about spatial and business intelligence.  Sadly, I missed the deadline last month to submit an abstract to SQL Rally.  Not that I thought I could contend with the more seasoned speakers out there at this point, but I would have liked to have given it a shot. I’ll be looking for some other upcoming events to get back into the swing of things.

I presented ‘Going Spatial with SQL Server 2008’ at SQL Saturday #48 in Columbia, SC this past weekend and had a really great time.  Big thanks go out to K. Brian Kelley and the SQL Saturday organizers, speakers, volunteers and attendees for making this such a great opportunity to learn and network.  I understand that any event of this size involves a tremendous amount of hard work…a lot of which goes unnoticed if everything runs smoothly, which it certainly did.

Special thanks also to those who attended my session yesterday.  I wanted to go ahead and post the presentation along with the code which was shared so you can continue to explore using spatial data.  Click here to download the files.

There were a couple of questions which came up at the end of the talk which I wanted to include here.  One was around being able to pull spatial data out of SQL Server and transform them into KML.  I suggested starting with the SQL Server Spatial Tools project which does have some KML processing options included but they may only be for importing of KML data into SQL Server.  For exporting, you may want to look at the SQL Server to KML project.

Another question came up around production-izing the geocoding logic that we covered during the presentation.  One of the challenges that you’ll face as  you start geocoding thousands of addresses at a time is dealing with daily query limits that service providers will enforce.  These are often in the form of X Number of Requests / 24 hours.  So, you’ll need to be sure to only geocode a certain number of addresses per day.  Also, if your requests come in at too fast of a rate (100 in a minute, let’s say), then your requests can also be denied.  Most services have a specific response status that tells you that you’re being denied for this very reason.  As such, if you want to production-ize mass nightly geocoding, you’ll need to build in some throttling logic that will manage this situation.  I haven’t posted any logic around doing something like this but will look to post this soon for anyone who might be interested.

As for the sessions that I attended, I really enjoyed Jessica Moss’s ‘Making Reporting Services Work For You’, Mark Tabladillo’s ‘Data Mining with PowerPivot 2010’ and Andy Warren’s ‘Creating a Professional Development Plan’.  To be honest, I didn’t realize until I went to Andy’s session that he had actually sat in on my presentation and that he liked it.  I had a slight Sally Field moment…’He liked it…he really really liked it.’  I also got some really good feedback on the presentation from the attendees and am really looking forward to expanding on the topic for future events.  Thanks again, everybody, and well done!

I’ll be speaking at SQL Saturday #48 on Going Spatial which will be a Beginner-level class for anyone looking to get started with the spatial features in SQL Server 2008.  The event is taking place on October 2nd at Midlands Technical College - Airport Campus.

There will be a number of great speakers, many of whom are currently or have been colleagues during my time at Mariner.  If you’re within driving distance to Columbia, definitely come check it out.  It’s totally free and you’re bound to learn a ton.  More event details can be found here.

To give you an idea, below is a list of some of the sessions being given by current and former Mariner colleagues at SQL Saturday #48…and this is only a small percentage of the sessions being offered.  Hope to see you there!

Attribute Discretization in Analysis Services
William Pearson
Intermediate

Creating Custom Components for SSIS 
John Welch
Intermediate

Delivering BI Solutions Rapidly using Vulcan 
John Welch
Intermediate

Design and Implement Like Edison
William Pearson
Beginner

Documenting Your Integrated Microsoft BI Solution 
William Pearson
Intermediate

Getting Started with MDX 
William Pearson
Beginner

Information Visualization - Making great Charts 
Wayne Snyder
Intermediate

Managing Database Schemas With VS201 DB projects 
Rafael Salas
Beginner

Planning your ETL architecture with SSIS 
Rafael Salas
Beginner

Processing Flat Files with SSIS 
John Welch
Intermediate

Unit Testing SSIS Packages 
John Welch
Intermediate

It seems that a lot of attention is being paid to the ascendancy of the Android over the last couple of years.  Note the incredible growth trend below in the Android OS market share as it compares to the other Smartphone OS’s out there.  Yes, we know…Windows Mobile is all but dead already (or is it?):

(Credit: NPD Group – click here for original press release)

The Blackberry has never held much of a ‘cool’ or ‘hip’ status like the iPhone but has been the predominant player in the business world.  That may remain constant, but for the time being, Blackberry, while having the largest share, isn’t driving innovation in the mobile market.  So the fact that Android has surpassed the iPhone seems to be speaking to the descendency of the iPhone and the coronation of a new ruler of cool mobile technology…at least when using the democracy of the mobile marketplace as our guide.  This battle will only continue to expand into different channels as the first Android-based tablets hit the streets and then…gasp…when we have an Android desktop OS.  You can only imagine the blood that will run in the streets when both Apple and Microsoft have to defend their territory against Google. 

I can tell you one reason that I’m an Android fan…the ease with which any developer can create an Android application.  This is a particularly important issue if you’re a PC user like me and building spatial applications for the mobile market.  Both iPhone and Android demand that you join their ‘club’ if you want to sell apps to their users.  Understandable, I guess.  For .NET developers, both mobile platforms require that you code in a different language…Java for Android and Objective-C for iPhone (more on this below).  However iPhone takes this one step further and forces all developers to develop on a Mac. 

To be clear, I’m not wild about coding in Java for the Android.  I’ve always felt that .NET tools were better than for any other language.  But I realize that in order to target the Android, I need to code in Java so I just stomach it.  But at least coding for the Android I don’t have to go out and buy new hardware.  Up until recently, there used to be a tradeoff for this….NET developers could code in .NET languages using Mono which would then translate the .NET code into Objective-C for the iPhone and iTouch, but Apple put the kibosh on that with the iPhone Developer Program Agreement released this past Spring stating the following:

Applications may only use Documented APIs in the manner prescribed by Apple and must not use or call any private APIs. Applications must be originally written in Objective-C, C, C++, or JavaScript as executed by the iPhone OS WebKit engine, and only code written in C, C++, and Objective-C may compile and directly link against the Documented APIs (e.g., Applications that link to Documented APIs through an intermediary translation or compatibility layer or tool are prohibited).”

So, from a developer perspective, I start to wonder…is this just one example of how Apple can come out with the epitome of cool only to wreck itself with draconian policy and control structures?  This is a technology-centric view, certainly, but if we look across the spectrum of touchpoints that Apple has had with society at large, is this how they have treated everyone?  Is this how they have treated their suppliers…their partners…their customers?  Is it a fundamentally flawed overarching corporate vision within Apple that pushes it to want to strictly control its connections with the outside world?  Is this what eventually leads to a slide in market share like we’re seeing with the iPhone?

So what is one to do when faced with the rigid structures that Apple pushes down?  One option is to work within what they allow…from my technology-centric view, I can work with what they offer to target the iPhone audience?  I can stick with web-based applications which have limited functionality.  I can pay someone to do it for me if I don’t want to fork out the loot for a Mac.  I can pony up and buy a Mac to start developing. Or, I can wait and see if the iPhone continues to decline in coolness in the hopes that this will drive policy changes.  I’m tempted to choose the last one, honestly, but realize this may be a childish impulse.  Like that hot nightclub spot where the lights slowly dim over time, will Apple start letting in new breeds of clients to stay open…or will they simply change the game completely and revamp? 

The iPhone is certainly not dead but, no doubt, will need to reorient themselves.  I would hope that someone might be TRULY bold within Apple and envision a different model for themselves where revenue is not dependent upon control.  Sadly, I picture the visionaries at Apple in strategy meetings using words like ‘Then they’d have to…’ rather than ‘we could support…’.  In my opinion, if Apple makes it easier, not harder, to target their audience, they will most certainly benefit…moreso than how they would benefit monetarily by forcing me to buy a Mac.

This always happens.  It’s an idea that gets my creative juices flowing…an idea that I think I can tackle with the technology that I already know.  The exact technology that I plan to use is of no consequence…just know that that to make my idea a reality, I will need to use technology.  Technology beyond your basic remote control…we’re talking code here.  I sit down and hack it out.  I think I’m making great progress until I hit a stumbling block.  I figure that issue one out and move on.  Ain’t no stoppin’ us now!!!  I’m getting close to making my idea a reality…and then it happens.  A wall so palpable that you can smell the bricks.  Is there way around this wall?  Hmm.  Not that way.  How about…no…that wouldn’t work.  What the heck is wrong here?  The wall in front of me happens to be the one undocumented, tiny f*cking piece of information that I need to complete what I’m trying to do.

SharePoint Stand Alone Install

I had this great idea that if I had a standalone SharePoint image that I could then copy and re-use in Virtual PC, then I could quick create proof of concepts quickly and easily.  Okay, so let’s build a standalone SharePoint virtual.  Enter Martin Kearn:

http://blogs.msdn.com/b/martinkearn/archive/2007/03/28/how-to-install-sharepoint-server-2007-on-a-single-machine.aspx

Martin gives an excellent series of steps on how to get a standalone server setup.  His instructions are not geared towards my specific situation so I don’t fault him for the problem that I would eventually have.  If you are like me and trying to setup SharePoint in VirtualPC, you might want to setup the DNS Host (A) records as the loopback (127.0.0.1) since your image’s IP address could change based upon your location.

This one minor derivation cost me…to the tune of hours of time trying to figure out why in the hell I couldn’t log into my SharePoint sites (Intranet, MySite or Intranet) in the end.  I received a security dialog prompting me to login at which point I used my SharePoint service account which had Administrator access.  You know you’ve got a problem when it keeps returning for you to specify your credentials…until you get the inevitable:

HTTP Error 401.1 – Unauthorized: Access is denied due to invalid credentials

This is when I start to groan…technology bites me again.  What the Mother-Loving ‘F’ (I have young kids so I’m trying to stop swearing).  But this is not the coup de grâce moment…no, that is saved for later...like that most tragic of movie endings that leaves you decimated.  I spend a bit more time…I’ve phoned a friend, I’ve prayed to the Google gods…and there it is:

http://www.jppinto.com/2009/06/you-receive-an-http-error-4011-error-when-accessing-a-moss-sharepoint-2007-site-on-a-web-front-end/

Who in the hell is JP Pinto, Seasoned IT Dude?  I have no idea, but he mentions something that makes me think that it might just fix my problem.  Specifically this statement:

I have noticed that this happens on Windows 2003/2008 Server SP1/SP2/R2 when using Host Headers and Alternate Access Mappings on a web application in MOSS 2007. If you can access the site from remote machines and cannot access the site from the server itself, then this might be your issue.

Did you catch that?  ‘I have noticed…’  That’s what we are reduced to anymore in technology.  I just lost days of my life looking for an answer and JP Pinto is telling me that the only reason that I’m not jumping out a window at this point is because he’s paying attention.

The long and the short…I try Option #2 which is changing the registry…CHANGING THE REGISTRY…and my SharePoint virtual is working perfectly.

I almost couldn’t believe it…so much so that I had to create another virtual with a different OS to see if this worked there too…and it did! 

I should be happy, right?  My problem is solved…I can move on!  Why should I view this as a moment of grief?  Why…because this is all too commonplace.  Because it was painful.  And because it took me two days to find the answer. 

My wife asks me how would I have solved this problem before the Internet…I must say I have no idea.  Call support?  But the complexity of systems anymore outright DEMANDS full Internet access (blogs especially) in order to solve technology problems.  I must say that I giggle when I hear that an organization that I’ll be working with has web filtering in place that will restrict my access to the Internet.  That’s like outlawing hammers on a home construction site.

My #1 destination when I’m trying to figure out a technical issue is blogs anymore.  Unfortunately, finding the specific answer to the problem I’m having leads me far and wide and it’s the techniques that I employ in that process that determine how soon I’ll resolve my issue.  And for those blog posts that do solve someone’s issue, you find comments at the bottom like:

‘You’re the man, this solved my problem.’

‘Thank you thank you thank you’

etc.

So, thank you, JP Pinto…you are not only a Seasoned IT Dude, but for today you are my SharePoint god for the simple fact that you kept your eyes peeled and added something to your blog so the rest of us jabroni’s can benefit from your awareness.

It turns out that, yes, this is documented amidst the THOUSANDS of other documented issues out there in the vast technology landscape…in case anyone wants to read Microsoft verbiage on this, realize that this relates to IIS 5.1 and later:

http://support.microsoft.com/kb/896861

On a recent client implementation, I was upgrading a client to use the Eldos BizCrypto Transport library which includes a few SFTP components that we were using.  I had been doing remote development on a virtual machine for a few weeks and was at the stage of dropping off the final deliverables.  The Eldos software had been installed on the production box.  I had tested an SSIS package with the new components on the production box to verify everything looked good.  I finally decided to verify that the components would work when called from a job when I started getting job failures.  I opened up the job history and found a most ugly error message:

The connection type "SFTP.BizCrypto" specified for connection manager "SFTP" is not recognized as a valid connection manager type. This error is returned when an attempt is made to create a connection manager for an unknown connection type. Check the spelling in the connection type name.

I was stumped.  I knew that the components had been installed.  I had tested them interactively via running a test package in BIDS.  Why in the world was it not able to find the connection manager type?

This was oversight on my part in not verifying whether there was a 64-bit version of the components installed.  It turns out that the BizCrypto transport library is only a 32-bit library.  As such, I needed to make sure that any packages that would be utilizing these libraries ran in the 32-bit runtime.  Simply open the job, then open the job step that calls a package utilizing these controls, switch to the Execution Options tab and check the ‘Use 32 bit runtime’ checkbox.  Once that’s done, you should be good to go.

I’d seen this issue pop up before when working with Excel data sources since in the past there was no 64-bit driver there either.  I was able to breathe much easier once I realized this was the issue and the job started running as expected.

It turns out that using your cell phone while driving is actually good for you.  I’m being facetious of course.  We’ve all had our run-ins with morons on the road who are completely oblivious to what’s going on around them…or sometimes we play the role of the moron (if you are in an area where drive-and-dial is legal). 

Cell phones have become ubiquitous in this country yet they are not proving to be a major cause of automobile fatalities as I would have expected.  Below, you’ll see the two trend lines for percent of wireless penetration (pulled from CTIA website) and automobile fatalities (pulled from the National Highway Traffic Safety Administration) between 1995 (when most of you hadn’t gotten your first cell phone) to 2008.

Obviously, this is a weak inverse correlation, but aren’t you at least a little bit surprised that fatalities are trending downward since 1995?  Good news for those of us who already know that the best way to play it safe while driving is to pay more attention to those text messages we’re getting!

I was hobnobbing with my fellow Education peeps at Mariner this week when I was informed that we won a Partner of the Year award for Education K-12 at the Microsoft Worldwide Partner Conference this week.  This is incredibly exciting for us as the team has really put forth so much hard work to produce some pretty innovative technology for the K-12 space.  In fact, we’ve been working at it for a long time without much public visibility so it’s exciting to see news like this start to emerge.

See the link below for all the details on the award:

http://www.microsoft.com/Presspass/press/2010/jul10/07-15PublicSectorPartnersPR.mspx

If you want to know more about what we’re working on, see the link below:

http://www.mariner-usa.com/solutions/education.aspx

Cheers!

Matthew

So, I’m definitely a late bloomer when it comes to Facebook and Twitter.  I didn’t sign up for either until about a year ago and even then I’ve rarely hopped on since.  As I’ve been blogging over the last year though, I felt I needed to give Facebook a little more of my time to let friends and colleagues know what I’ve been working on.  Over the last week though, I’ve come to realize just how sucked into the vortex we’ve gotten.  It made me wonder how many lifetimes are spent in an average day on Facebook.  What do I mean by this?  If you add up all the time spent on Facebook in one day, how many lifetimes would it equal?

I started in the Facebook Press Room where it says that people spend over 500 billion minutes per month on the site.  Let’s write that out…

500,000,000,000 minutes

Okay, now for some quick math (rounding for ease):

500,000,000,000 minutes / 60 = 8,333,333,333 hours (worth of time) per month

8,333,333,333 hours / 24 = 347,222,222 days (worth of time) per month

347,222,222 days / 365 = 951,294 years (worth of time) per month

Now, if you Google ‘Average Life Expectancy’, at the top of your search results is a statistic from the World Bank stating that as of 2008, average life expectancy (for first world countries, I’m guessing) is 78.43902.

So…

951,294 years / 78.43902 = 12,128 lifetimes per month (I rounded up a lifetime for simplicity)

12,128 lifetimes per month / 30 days = 404 lifetimes per day

So, on average, collectively and globally, we spend 400 lifetimes per day to Facebook.com.  Wow, I better go share this with my Facebook friends right now!

I published out a Tableau dashboard on Real Estate Sales in Mecklenburg County (NC) to Nanaroo.com.  For now, I have it setup as a separate tab on Nanaroo titled ‘Real Estate Sales’ (click here to go straight to it).  It currently reports on the last 6 months of sales as reported by Mecklenburg County. 

I expect that I’ll update this extract on a monthly basis as well as continue to add more spatial intelligence features along the way.  If you want to follow the development of this tool, please follow this blog where I’ll post updates.  For a quick walkthrough of what the dashboard does, see my previous post titled My First Tableau Dashboard.

Enjoy and please post comments on your thoughts.