2007-09-13

Sleeping late, AT&T, and Brighthouse make for a BAD day!

I awoke this morning to the sound of my annoyed (and rightfully so) wife telling me "Get up, you only have 10-minutes to get out the door!" This rude awakening was augmented by the dense smell of bacon which seemed to permeate the entirety of my house. The reason for this fog was that she was cooking bacon in the microwave for inclusion within my boys' sandwiches today. Waking up with that thick smell is totally different than smelling its aroma in the kitchen after you are showered and dressed. To my senses, at least, it made me feel dirty and greasy from the moment I pried open my eyes.

This bacon-induced affectation merely served to exacerbate my overall mood as having a paltry ten, wait, now nine minutes to get out the door meant that I could not indulge in the morning shower that is my custom. Instead, I had to run into the bathroom, shave, brush, comb, wash my face, and de-odorize in record time.

Waiting to greet me when I emerged from the bathroom was a wrinkled pair of pants and a shirt which both sports a missing button and a small tear at the bottom of a side-seam. "Great, no time to change now...". Out the door I went, coffee-less, with the boys close in tow.

I got them to school in time, but that's where the good news ends. Upon arrival at work, two things hit me in the face. First, I got accosted at the front door by a frantic office manager because our telephone lines were down. Again. Secondly, I noticed while looking up our AT&T troubleshooting numbers, that our backup Internet connection is VERY slow.

Great.

I called all eight (8) of our AT&T contacts going up the hierarchy in the correct order on my AT&T Account Team list. I did this on my nearly battery-dead blackberry, following up with an urgent email copied to all of them. None of the contacts either picked up the line nor bothered to return my call withing 45-minutes. In the mean time I had our Tampa Systems Administrator going up the ladder from the generic technical support page from their web site.

AT&T allowed an individual, neither employed by my company nor authorized by my company, to place a line disconnect order which included (19) lines from our T1!

Amazing!

This is the second time this has happened, despite reassurances of restrictive measures being put in place the first time.

All is fixed, after four hours, now, but they owe us some money and I'm going to collect!

Brighthouse Business Class, on the other hand, is content to tell us that a 40% packet loss and it's resultant 1Mbps bandwidth is nominal when our contract is for 7Mbps. I'd like to go on about this one, but it's not over yet.

Aaack Phhht!

2007-08-23

Web 2.0 Style and GIS growing pains

We're having the web site of one of our child companies reworked, and have after three rounds of reviews decided to go with a Web 2.0 look/feel (yeas rounded corners). I'm a little nervous about the design dating itself too soon.

The best news is because I properly designed the database and administration back end for our main site, we'll be able to add programmatic features for this site withonly minor PHP code chages.

And now for something completely different!

We have got to find "The" way to push GIS technology into the hand of the average staff member better. Right now GIS is seen as a centralized service which produces maps and does analysis for internal and external clients. It is incredibly important, in my opinion, for us to transform GIS into a distributed technology inherent in everything we do in a transparently, viral, non-intrusive way.

This means that the central GIS service group must think in a much more long-term manner and be much more disciplined in the creation of documented tools and standards which users can easily use to get their "real" work done. This means among other things publishing data layers with symbology and in end-user consumeable chunks.

2007-08-15

SQL Trickery Needed: ERP Data Extraction

I need to write a new project status report that shows Effort, Revenue, Cost and the like. The twist is that the Project Managers would like to see this information not just for the Project To-Date, but also for the last period for which there was activity.

We use BST Enterprise as our ERP, which has a table PrjTranSummary that has all the information I need. Now I just need some clever SQL to get the information out. This is a classic UNION example where I'm going to create a Summary Record for each project with PTD information and a Last Occurance Record with the information from the last month there was activity.

The table looks kind of like this (I've changed the names in case BST gets testy):

 -----------------------------------------------------
| prj_code | accounting_period | effort    |  cost    |
|==========|===================|===========|==========|
| F7940001 | 200706            |   3465.34 |  1159.23 |
| C5265007 | 200703            | 203465.75 | 10159.00 |
| F7940001 | 200707            |   1843.00 |   326.50 |
| F7940001 | 200707            |  16470.50 | 11325.00 |
 -----------------------------------------------------

There is a record for each project for each month during which that project has had activity. So the only hard part is getting a query that will select for each project only the newest record.

This fits the bill. It is inefficient though. I'm sure that with the right index built, it could be much more efficient.

SELECT *
FROM dbo.PrjTranSummary pts1
WHERE NOT EXISTS (
        SELECT 1
        FROM dbo.PrjTranSummary pts2
        WHERE pts2.prj_code= pts1.prj_code
            AND pts2.accounting_period> pts1.accounting_period
    )

2007-08-06

Hitting the wall: Living within Somebody Else's Systems Architecture

We use BST Enterprise for our ERP system. It is a very good ERP, made and supported by a what I consider to be a great company. If you run a professional services type of firm (i.e. engineering, AEC, law, etc.) give them a look at BSTGlobal.com.

No they don't pay me, I just happen to like their software.

Now for the bad part. Like all software publishers, BST has had to make architectural decisions, and these decisions are based on a variety of factors. Some will be groundbreaking like BST's move in the 1990's to move to a web-based client for an entire ERP, and some will be head-scratchers such as BST's idea to use Crystal Reports for not only generationg invoices, but also the calculation of the invoice amount and writing it back to the accounting system. It is with the latter part of the second decision example I have had cause for quiet rumination.

Crystal Reports (Business Objects) is a great reporting writing package. I doubt, however, that NASA would use it for calculating trajectories. Billing systems aren't that much less complicated than differential equations, especially when one consider the re-iterations sometimes necessary. BST should have sunk their developers time into creating a billing system that does all the calculations on the server side and uses Crystal Reports just for presentation the way God intended it.

The problem is that Crystal Reports does everything in "passes" and you only can go through so many passes through the data. In addition, the the ability to show/hide things based on data that is not grouping/section based is very poorly implemented, making it unworkable for many things.

More about this will be forth-coming. Suffice it to say we are in month six of an attempt to get a working custom invoice, and we are just now getting told "we can't do that" because they (BST) are just now finding out they can't do it. So for now:

 -------------------------
|     Crystal Reports     |
|  ---------------------  |
| |         BST         | |
| |       ------        | |
| |      |  ME  |       | |
| |       ------        | |
|  ---------------------  |
 -------------------------

And all of us chose the box we live in . . .

2007-04-02

BST Global Users Conference

BST Global's User Conference, held in Tampa, FL, was an affair well attended both by BST's user base and by their own staff. There were many worthwhile sessions dedicated to key business areas such as:

  • Marketing
  • Operations
  • Information Technology
  • Billing

Many in the crown commented on the great strides BST has made in the areas of Business Intelligence (BI)and Integration. Their Performance Portal product is a true step up from the portals of old and will truly providee powerfull analytics when coupled with SQL Server 2005's Enterprise Edition's Analysis Services for OLAP-based computing.

I personally also noted a new-found willingness on BST's part to begin to see the community of their customers in-house developers as a resource to be cultivated, rather that be looked upon as a potential revenue drain for their consulting force. Anecdotally, I've alway informally looked at the presence of a lively and supported development cottage industry as the hallmark of a mature customer base and a progressive-looking vertical solution vendor. Who better to look to for innovation and risk-taking.

Along these lines, I am working with some of BST's staff to begin a BST Developers Community web site. With input from several other customers, this could potentially benefit a lot of people, even BST.

2007-02-21

MySQL - Awesome Delayed Inserts

MySQL has a great feature that IMHO, isn't written about often enough, Delayed INSERTS. Check the link for the sytax details.

I was tasked with creating a public information site for the FDOT for a roadway improvement project. The site's purpose it to provide the public with information about scheduled lane closures and the the like. The site has a potential to get a lot of traffic as the site will be promoted both on handouts to the locals and on roadside light-signs. Keeping the potential for high-volume traffic and a likely low-tech audience, I designed a simple strict CSS site with great cross-platform rendering with NO JavaScript. The backend is a LAMP architecture. I setup a Google Analytics include for traffic monitoring.

When we submitted the site, however, the FDOTreviewer stated in his comments that he wanted a "hit-counter" on the site for some reason. That got me thinking. If, and that's a big IF, the site gets a ton of traffic (which I'm not really set-up for), the hit-counter would likely be one of the biggest I/O hogs. I decided to figure out the fastest way to both collect hit counts (pageviews is what I actually am tracking) and display them on the site. If I have to collect the information anyway, I might as well grab a couple of other things too such as IP, page viewed, and the referrer because no matter how cool google analytics is, there are still questions it can't easily answer that an SQL query can.

I decided to use a small MySQL table to store the hits for now, but I was concerned about the INSERTS locking the table or taking too long under load to return control to PHP->Apache->TheBrowser. I even tried to see if wrapping the INSERTS in transactions could help me get a delayed/batch effect. That's when I discovered the DELAYED option to the INSERT statement. This option is very useful if you have clients that cannot or need not wait for the INSERT to complete. Be aware that the table will need to use the MyISAM, MEMORY, or ARCHIVE engine. InnoDB won't work, however keep in mind that you can select the engine on a table-by-table basis withing a database (schema).

From the MySQL Docs:

When a client uses INSERT DELAYED, it gets an okay from the server at once, and the row is queued to be inserted when the table is not in use by any other thread.

Another major benefit of using INSERT DELAYED is that inserts from many clients are bundled together and written in one block. This is much faster than performing many separate inserts.

Voila, problem possibly solved. We'll see as traffic and table sizes icrease. Right now I'm using a count of the rows in the table for the "hit" counter. That may not scale. If anybody has empirical data on what the most efficient wat to do this is, I'd love to hear from you.