Category Archives: Postgres

Customer retention metrics

Last night (Tue July 19th), I was fortunate to be able to speak to the SVForum Business Intelligence special interest group (SIG).

After introducing the audience to DASHbay, I took them through an implementation we did using our Quick Analysis practice, which leverages open source software (especially BIRT and postgresql), cloud computing (on AWS), and rapid, iterative development.

The implementation itself was a dashboard, built with BIRT in less than a week and showing metrics for account acquisition and retention. The metrics help any business track not just how well they are acquiring customers, but how well they are keeping them.

Account retention dashboard
Our customer was able to get at the metrics via a URL to a server running in the cloud, set up just for them. It’s a great way to leverage cloud computing: no IT procurement costs or delays, and you only pay for it while you need it.

We talked about DASHbay’s Report Server product, which among other features, allows us to capture any useful piece of the report, and include it in any web page. It also provides permissioning and authentication, taxonomy for organizing reports, and more.

I got an excellent reception from the audience, and was pleased with the reaction and discussions afterwards. Thanks to all who attended!

If you didn’t get a chance to be there, please get in touch so we can talk to you more about our Report Server for BIRT, our Quick Analysis Service, or many custom BI and Data Analytics services. Customer Retention is one very useful application which we can provide, but our tools and techniques are applicable to most common business analysis problems.

Terry

Working with Postgres on Windows via ODBC

The most straightforward way to hook up Excel or most other Windows programs to a PostgreSQL database is ODBC.
ODBC lets you connect to a local or remote Postgres.
To do this, you need to get an ODBC driver for Postgres.   But which one?    In most cases, you’re going to want the free official ODBC driver from Postgres.  Its documentation is so poor that I overlooked it for a day before figuring out that it’s the real deal.  The driver’s homepage makes it look like the last version was in 2005, but in fact there has been releases in October 2010 (and by the time you’re reading this, probably more recent ones).
Get the Windows installer here: http://www.postgresql.org/ftp/odbc/versions/msi/
Make sure you install the right version.  Even if you have 64 bit Windows you’ll need the 32 bit ODBC driver if the program that’s connecting to the database (like Excel) is 32 bit.  For Excel specifically, File > Help says whether it’s 32 or 64 bit.
The official installation and configuration FAQ: http://psqlodbc.projects.postgresql.org/faq.html
Set up a DSN
Once you’ve installed the ODBC driver you need to make it available to programs like Excel.
You do this via your computer’s “Data Sources (ODBC)” panel.   There’s a 32 bit and a 64 bit version, you gotta make sure you’re looking in the right one: http://support.microsoft.com/kb/942976
Once it’s open, click either the User DSN or System DSN tab:
  • User DSN: is available just for the current user.
    • If you’re just using it within Excel or Access, this is the one you want.
  • System DSN: is available for all users and services on the machine.

Click ‘Add…’ and select a PostgreSQL driver.  There’s two to choose from:

  • PostgreSQL Unicode: use this if your database was set up with the UTF-8 character set.
  • PostgreSQL ANSI: use this if your database was set up with a LATIN character set.
Fill in your server and database details.

  • Data Source: make a friendly name of the data source that you’ll later use when looking up this DSN in Excel or other programs.
  • Database: use ‘postgres’ to connect to all databases, or type the name of a specific database.
  • Server: domain name or IP address of server
  • User Name: postgres database username
  • Password: postgres database password
Go Forth And Use ODBC
Now go to the program in which you need to connect to Postgres and use whatever ODBC connection UI it provides.