Skip to content

Useful Tools - 7. page

Observations on a Writing a Custom Report with Java, Quickbooks, Jasper Reports, Google Spreadsheets and Google Drive

Image courtesy of Sean MacEntee: https://flic.kr/p/9P4CwC
Image courtesy of Sean MacEntee

A recently released project is using java and spring to pull data from quickbooks, a mysql database and google spreadsheets, munging the data in various ways, and using jasper reports and jfreechart to generate a good looking report and a CSV of transactions that will give our brokers weekly updates on how they are doing compared to their goals for the year. I then upload it to Google Drive and send an email notifying each realtor that they have a new file.  It’s always nice to release useful software–feels like a new day dawns.

A few observations from this project:

  • The tech was interesting, but it was actually more interesting to see how the needs of tech drove the business to ‘tighten up’ their processes. Whether that was making sure there was one place to look for user type data, or defining exactly what constituted achieving a goal, or making sure that any new realtors who joined created business goals (and committed them to writing), the binary nature of software forced the business (or, more accurately, people in the business) to make decisions. Just another example of business process crystallization. This also meant deferring some software development. Where the business couldn’t answer a question definitively, rather than force them to do so, we chose to defer development.
  • I’m glad that jasper reports makes it so easy to generate PDFs–you basically create an XML file (I was unable to find a spec, but there were plentiful examples) and then put tokens for dynamic content. Then you compile the XML file, give it a map of said tokens and values (which can be text, numbers, dates or images), and then export the object to PDF. Note that I was not using Jasper in a typical way–reporting from large amounts of similar data via a data connection–because I wanted different PDFs for each user. Perhaps there was a way to do this elegantly, but I was just trying to get stuff done. Creating a grid in jasper was interesting, though.
  • JFreechart had a very weird issue where on stage the graph labels were bolded and italicized, but not on production. Since we make every effort to keep these two environments in sync and they were running exactly the same code, this was a mystery. Finally solved it when we discovered java was different (same version, different vendors: openjdk vs sun java). Had been running this way for years. Oops.
  • Interacting with google spreadsheets is great for the business but a pain in the butt for developers. It’s great for our business because it is extremely easy for someone who is not a programmer to create a ‘database’ that is versioned, backed up, almost always accessible and helps them ‘get stuff done’ in a measured way. It’s a pain for developers because it is a ‘database’ and not a database–no referential integrity or data typing. Also, google provides cell based access and row based access, forcing you to choose. And the java libraries are old. Beats excel though–at least it is accessible from a server. We ended up writing a library to wrap Google’s Java SDK to make some common operations easier.
  • Pushing to google drive is interesting–I alluded to this in my last post but you have to be ready for failure. I ended up using a BlockingQueue for this–I throw files (a data structure defining the file, actually) to be uploaded on the queue, then consumers executing in a different thread each take one off, try to upload it, and if it fails, put it back on. I considered using a third party durable queue like IronMQ, but thought it was overkill.
  • Using the Quickbooks SDK, with all the accounting data exposed, lets you build some pretty powerful graphs that are useful to the business. But the docs are jumbled, with a lot of them aimed at developers who are building integrations to sell to Quickbooks users. Support is OK for standard operations, but for things like renewing your token, you have to drop down to the REST API (see my SO question) This article does a good job of outlining the various projects but as a dev you’ll have to ignore certain sets of information–never fun when getting up to speed.
  • We do a lot of backend processing and spring and maven and a custom assembler that generates a tarball when using ‘maven install’ have been great. I also finally figured out how to work with maven to use ‘release:prepare’ and ‘release:perform’ for releasing libraries, as opposed to going my own way, and that has made things much much easier. Learn your tools, folks!
  • I’m once again astounded by the richness of the java library ecosystem. There doesn’t seem to be very much that I can think of doing that doesn’t have at least one, and probably three, java implementations.
8z APIs Business Cloud Computing Java Useful Tools | moore |

What’s wrong with this announcement?

Do you see anything wrong with this announcement, which I received as an email attachment, of what looks to be a very interesting discussion of hunger, food and faith?

faithandfoodsummit flyer

There is no URL.

There is no URL!

Folks, if you are having any kind of gathering or event that you’d like to be shared online, please please provide a URL.

Benefits of a URL:

  • Easy to share
  • Can be updated if details change
  • Lives forever, so can be referenced in the future
  • Can hold vastly more information than a flyer

There are many easy ways to create a URL for your event.

  • Facebook
  • EventBrite
  • Google Sites
  • Weebly
  • wordpress.com
  • Google Forms

And I’m sure I’m missing dozens of other options.

Repeat after me: I am having an event, and my event deserves a URL.

Useful Tools | moore |

How to collect usage statistics in your phonegap/cordova application

My company recently wrote a couple of mobile applications. Since one is for consumer use (search for ‘8z neighborhood’ in the App Store/Google Play if you live in Colorado or the Bay area and want to check it out), I wanted to know what type of users were downloading and installing it, what was being used, and other general usage statistics.

I asked a mobile app vendor we’ve worked with what they used for usage stats, and they said Flurry. I also looked at Google Analytics, Mobile–this is a nice q&a explaining the major players in the mobile analytics market. We didn’t want anything complicated, just basic stats with the possibility of collecting further information in the future, so I went with the vendor recommendation. Flurry also works with the two platforms we were targeting: IOS and Android, as well as many others.

Flurry is zero cost, but nothing’s free–they want your data and you grant them a “right, for any purpose, to collect, retain, use, and publish in an aggregate manner” all data collected from your application. I’ve seen similar TOS from most of the free analytics vendors, so this was no surprise.

To use Flurry with cordova/phonegap, and with plugman, I was ready to plugmanify an existing Flurry phonegap plugin. Luckily, someone else had already done it. All I had to do was update the plugin to work with Cordova 2.9, and to use the latest IOS7 compatible Flurry library.

After you install the plugin (I recommend doing so in an after_platform_add hook script), you simply add this to your code after the deviceready event fires: window.plugins.flurry.startSession(sessionkey). I inject the session key using a hook script, because I wanted a different key for stage and production builds, and also for each device platform (Flurry requires the latter). Because hooks only get the root directory as context (although this is supposed to change) I had to put some logic in the javascript to call startSession with the appropriate key:

 App.config.flurryid = "";
    if (window.device && window.device.platform) {
        if (window.device.platform == "Android") {
            App.config.flurryid = /*REP*/ 'notreallyanandroidflurryid' /*REP*/ ;
        }
        if (window.device.platform == "iOS") {
            App.config.flurryid = /*REP*/ 'notreallyaniosflurryidxxx' /*REP*/ ;
        }
    }

Although I have not used any of the more specific event tracking, the basic statistics are a great start (including new users, retained users, device versions, etc).

Don’t fly blind when you release your phonegap/cordova mobile app–use Flurry or something simliar.

8z Android Mobile Technology Phonegap Useful Tools | moore |

Easyrec: a recommendation engine worth looking at

I love recommendation engines.  These are the software that Amazon has everywhere showing “users who bought this also bought” recommendations.

I love them because they are an easy way to leverage the wisdom of the crowd to help users.  They also get better the more data you feed into them, so once you set one up, it just makes your site better and better.

For a while, I’ve wanted to explore mahout as a recommendation engine solution, but felt intimidated by how much work integration would be.  Luckily, I did a bit of searching and turned up this stackoverflow question about java recommendation engines.

Looking at some of the alternatives, I dug up easyrec, an open source recommendation engine.  Rather than solving a couple of different machine learning problems like mahout does, easyrec focuses on recommendations.

It also has a javascript API (for both sending information and displaying recommendations) and a demo installation you can use on your site, so it is trivial to integrate into a website to see if it works for you.  I did run into an issue with the demo server, but a post to the forums got it resolved in a few days.

Easyrec has support for generating recommendations for more than one kind of item (so if you want to display different recommendations within specific categories of an ecommerce site, that is possible) and is self hostable in any java container (which is recommended if you are going to use it in any commercial capacity).  You can also build the recommendations off of the following actions: views, rating, or purchase.

You can also customize easyrec with java plugins, though mahout definitely offers far more options for configuruation.

I haven’t noticed any speed changes to my site with the javascript installed, though I’m sure adding some more remote javascript code didn’t speed up page rendering.  I noticed an uptick in time on site after I installed it (small, on the order of 5%).

If you have a set of items that are viewed together, using easyrec can leverage the wisdom of the crowds with not much effort on your part.  It’s not as powerful or configurable as alternatives, but it drop dead simple to get started with.  It’s worth a look.

Java Useful Tools Web Applications | moore |

My ODesk experience

A few months ago, I had a friend who mentioned that he was investigating ODesk to find help for his software project.  I’d heard of ODesk before and was immediately interested.  I have a directory of Colorado farm shares which requires a lot of data entry in the early months of each year, as I get updated information from farmers.  So, I thought I’d try ODesk and see if someone could help with this task.

Because this was my first time, I was cautious.  I worked with only one contractor, and only used about 17 hours of her time.  We worked off and on for about 3 months.  She was based in the Philippines, so everything was asynchronous.  We communicated only through the ODesk interface (which was not very good for a messaging system).

I chose her based on her hourly rate (pretty cheap), skillset (data entry) and reviews (very good).  I made the mistake of contacting her to apply for the job, but letting others apply as well, and in the space of 3 days had over 90 applicants for the position.

After selecting her, and her accepting my offer, I created a data entry account, and described what I wanted.  This was actually good for me, as it forced me to spell out in detail how to add, update or remove an entry, which is the start of the operations manual for my site.

After that, I managed each task I’d assigned to her through a separate email thread.  I did light review of her work after she told me she was done updating, and did go back and forth a couple of times over some of the tasks.  In general, she was very good at following instructions, and OK at taking some initiative (updating fields beyond what I specified, for example).  There were some bugs in my webapp that caused her some grief, and some updates I just did myself, as it was quicker to do them than to describe how to do them.

The variety of work you can get done via ODesk is crazy, and the overall ODesk process was very easy.  You just need to have a valid credit card.  If you are looking to start on a project right away, be aware that some lead time is required (they charge your card $10 to validate your account, and that takes some time to process).

Even though it didn’t save me a ton of time, it was a useful experiment and I’ll do it again next year.  For simple tasks that can be easily documented and outsourced, it’s a worthwhile option to explore.  Though be careful you don’t outsource too much!

Business Technology and Society Useful Tools | moore |

RSS email campaigns

Email is one of the best ways to keep users coming back to your site.  I’d lay out the arguments, but Patrick McKenzie already has.  Go read that article if you doubt my statement.

If you generate a lot of content via your blog, then an email campaign that pulls from your RSS feeds is a great way to generate newsletter content.  If you have two sources of content–a content blog and a link blog, for example–you can have the newsletters pull from both RSS feeds and have them feed different sections of your newsletter.

I am a big fan of mailchimp as an email delivery service (they have a great free plan I’ve used multiple times), and was ready to roll up my sleeves and use their API to read from a set of RSS feeds and generate a newsletter from that feed.  But, luckily, they’ve already implemented RSS email campaigns.  If you want a monthly newsletter and were confident of new articles in your RSS feeds, you can schedule the newletters out for a year, and know that you’d be sending out topical fresh content every month.  (Note that if you didn’t have new articles, the newsletter would still be sent if you didn’t intervene.  And I can’t think of something that would make an internet company look dumber than to send the same content twice in an email newsletter.)

I’m aware of solutions like paper.li that allow you to aggregate multiple sources of content in a pretty package, but mailchimp seems to give you more control and can be used for non RSS email campaigns as well.

Take a look!

Business Email Useful Tools | moore |

Google Analytics Goals and Forms on Google Sites

I recently put together a website for my father’s new book about World War II, and choosing Google Sites was a slam dunk.  I choose a neutral theme, set up a few pages, uploaded some images and logged into Go Daddy (where my father had purchased his domain name) and set up DNS.  Easy peasy.

Two issues came up.  One minor and one major.

First, there is no easy way to point the bare domain at google sites using Go Daddy’s tools.  So seesaw1942.com can’t be redirected to www.seesaw1942.com.  Minor bummer, but just make sure all the marketing contains www.seesaw1942.com

I set up a form to capture email addresses for people who wanted to be informed when the book was actually published (it is now!).  I was interested in playing around with PPC to drive people to the website, but wasn’t able to set up a goal in Google Analytics to measure signup success.  (So I didn’t end up setting up PPC.)

I looked around and didn’t find anything that would help with this.  Here’s an article about goals and Google Forms, but the writer’s form lives on their own server, which gives them more latitude than someone working with Google Sites.

Anyone have any idea how to do this?

Books Google Apps Useful Tools Web Applications | moore |

Simple REST database solutions

I’ve been looking for a simple solution to generate a REST api for a mysql database, with minimal to no coding.  I need to be able to do simple inserts and updates, and some simple querying (including for number ranges and or clauses).

The options are few and none seems to entirely fit the bill.

  • RestSQL is a java layer that has a simple XML configuration.  It supports some advanced features (triggers for biz logic, table composition) but doesn’t yet have numeric comparisons for gets.
  • DBSlayer is a C program that doesn’t do REST, but does JSON results for SQL queries over HTTP
  • PHPRest is a RESTful interface.  From the docs, it is not clear if it supports JSON or any queries more complex than by primary key.
MySQL Technology Useful Tools | moore |

Tips for using your phone as a wifi hotspot

I recently was working remotely.  Typically when I do this, I try to find a coffee shop or library–someplace I can park for free or cheap and get access to wifi.

On this trip, for a variety of reasons, finding such a place was going to be more of a hassle than usual.  I had noticed my phone had wifi hotspot capability in the past, so I decided to try it out.

I have AT&T service, and an Android phone (an HTC Inspire running Android 2.3.3).  With my existing 2GB/month data plan, I could not turn on wifi hotspot cability.

However, a quick call to AT&T customer service revealed that the only obstacle was upgrading to a 5GB/month data plan, and that I could downgrade easily if I wasn’t happy.  The change would take place immediately (or at the next billing cycle if I desired). After a day of deliberation, I placed the order, and started using my phone as a wifi hotspot.

Pluses

  • great to work from anywhere where I can get a decent signal
  • able to use go to meeting, video, browse the web, email, skype–there was really no online tool I was not able to use
  • you can control number of users and apply access control

Minuses

  • Speed was like DSL (I had 2 bars most of the time and was on the 4G network, but the phone was a 3G hotspot–not sure what that means)
  • A variety of things could cause internet access to stop: incoming phone calls, downloading lots of data (photoblogs), having three or more browser tabs downloading data, trying to upload a photo and download data in another tab
  • Internet access would stop working every thirty minutes or so with normal usage, requiring me to ‘reset the router’ by turning off the hotspot and mobile data, then turning them back on

Tips

  • Use an offline mail client (thunderbird is my favorite, even though it is no longer being developed).  This will let you respond to emails without using notepad or some other desktop app to capture your thoughts.
  • Practice patience, and if you absolutely must have bulletproof network access, go to a coffee shop.
  • Focus browsing on crucial pages.
  • Having another phone available (for web meetings or regular phone calls) is really helpful
  • Have someone else host skype calls–trying to host didn’t work, but I could participate just fine if someone else hosted

Even though the experience wasn’t optimal, frankly it is pretty amazing that I can use my computer and have reasonable access to the internets from anywhere that has a decent phone signal.

Technology and Society Useful Tools | moore |

Review of Emergent One

A few weeks ago I sat down with some folks at Emergent One and got a demo of their product.  The reason I reached out to them is because I heard great things about their demo at GlueCon.  My company is considering building a mobile app (who isn’t, right!) and I thought that what EmergentOne offered was a great way to accelerate the server side development of that app.

We are slightly outside of their target market–our API would be solely for internal use of a small team, while it seems like they are aiming at companies who want to make an API available to a larger audience (either external or a larger internal development staff).

Regardless of target market, they have a slick product.  They have a self service web application which can generate APIs directly from database tables.  You allow the app select access to your database.  (I believe only mysql and postgresql are supported at the moment, but I know things are moving fast over there as well.)  You then work within the app to build an API based on the tables in your database.  You can have derived fields as well as fields that map to columns in your database directly.  You can filter your data (so if you only want to expose a subset of your data, you can create an endpoint that only displays that: “users over 40”, for example).  You can also add comments to fields.

After you define as many API endpoints as you want, you can manage access to them with application keys or usernames and passwords.  Automatic documentation with datatypes and whatever comments you have added is generated, and there is a developer portal where it is easy to play around with the APIs and see what you missed.

What they showed me is great, but this product is still in private beta.  That means there are some rough edges.  The biggest hole (a fix for which the demoer promised was coming very soon in their development plan) is that you can’t search against the API.  So, if you have an API exposing your pets table, you can create an endpoint to retrieve all pets, and you can retrieve one pet based on id.  You can create a ‘dogs-only’ endpoint, and get all dogs or one dog.  But you can’t query the dogs-only endpoint for dogs that weigh over 25 pounds and have short hair (or any other type of querying).  I only played with read-only APIs, so I’m not sure how the write-access APIs work.

There’s also always the issue of introducing another vendor into the system.  Since we are looking at this for mobile apps, performance is very important.  It seemed like the demoer was well aware of this issue.  He mentioned an SLA would be likely when they went public, and also talked about some of the steps they are taking to make sure their app uses indexes and other metadata about the tables being exposed to execute as quickly as possible.

I haven’t built an API with any of the other tools out there, so I can’t compare the ease of Emergent One with, say, tools like jboss resteasy that work with a java layer, or usergrid, which autogenerates an API but requires moving data into it.  But I can say that this was a very easy way to go database to API in less than an hour (with iptables troubleshooting mixed in!).  If they get searching right–making it easy to use and performant–this will be a fantastic product.

Cloud Computing Mobile Technology Useful Tools | moore |