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 . . .