Got Wills

Well, a couple of months ago, I made Navajo County’s Parcel Search start getting data from the new Assessor’s database. They have moved from the Arizona Department of Revenue’s CCIS to Colorado Customware Inc’s RealWare product, affectionately known as CCI. All things said and done, it wasn’t too bad of a process.

Obstacle 1

Where is the data located?

With a lot of help from Mohave County’s Systems & Database Admin, Gary Waters, this step wasn’t too bad. I realized what I needed was the DataMart schema… but I didn’t quite understand how I was supposed to populate it. Gary kept telling me about this job they have that populates it nightly, but I couldn’t even find (in Oracle Enterprise) where Oracle keeps it’s jobs. I eventually found a query that would list them for me, but we had no such job.

The next day, or thereabouts, Jeff, one of the other Navajo County programmers, noted that he had found a DataMart application and had populated the schema. As he was explaining how to install it (it’s included in the advanced RealWare install), I realized I had seen it there when I was installing RealWare on my machine, duh. Oh well, all in a day’s work, I guess.

With the help of the SQL scripts that Gary sent along, I started to piece together how the data was arranged in the database. On to…

Obstacle 2

How do I get the data from the internal Oracle server to the external SQL server?

After some extensive Google searches, a lot of reading of the (very boring) Microsoft SQL Server Books Online, as well as a healthy helping of the (very complex) Oracle Database Documentation Library, mostly the part about Oracle Streams, I started thinking this wasn’t going to be a walk in the park.

Well, I took a walk through Oracle Enterprise Manager, right clicking some things in database links, trying to see what was necessary to send data directly from there to the MS SQL Server sitting in the DMZ. Well, it turns out, it’s not that easy. I installed Oracle XE on the SQL server (read somewhere that it was the thing to do). Altered some .ora files and tried to replicate the data… and failed to connect. Well, I googled, tweaked, retried… googled, tweaked, retried, tweaked, retried, googled, tweaked, retried… No go.

After beating my head against that rock that is Oracle for a day and cursing CCI for using Oracle (which I do love, btw, it is what i learned SQL on, but replication is so easy in SQL server) the rest of the night, I had an brilliant idea. You see, I did read that you could use a SQL Server as a distributor for a publishing Oracle database. The whole reason I didn’t go that route in the first place is because the SQL Server is in the DMZ, therefore, while the Oracle server should be able to get to it just fine (hah), It wouldn’t be able to get to the Oracle server, at all (without opening holes in the firewall, which my boss is very against). However, we have about a million (okay, closer to 10 or 20) SQL Servers inside the network (Non-DMZ).

Well, I was stoked. I read some more of the Books Online, with renewed vigor and got started on the plan that would should bring data from the inside Oracle server to the DMZ’d SQL Server. Well, I read all the papers and went through all of the necessary steps, installing all of the .NET crap into the Oracle database. Finally, the moment of truth… I setup the distributor, made sure all of the settings were just right… I had successfully connected to the Oracle database and browsed the list of tables from SQL Server Management Studio. It’s go time… So I click Finish and I instantly get the error saying you have to buy the insanely expensive SQL Server Enterprise to use Non-SQL Server publishers, well, it didn’t say that, but that’s what it boiled down to after a quick google search to decipher the greek that it gave me. However, I was on the right trail…

Now, at the beginning of this foray into Obstacle 2, I had talked to Gary Waters about how he got data from their CCI database to the DMZ’d SQL server. They just punched a hole in the firewall and have SQL Server import the data every night. I told you how my boss feels about holes in the firewall… and he really like real-time data, so that was immediately out of the question. I told him that we could either buy SQL Server Enterprise, which was pretty much dismissed out of hand, due to the current financial state that the county is in… or I could do a nightly import (without punching holes in the firewall, mind). I just needed some space on one of our existing internal servers to accomplish this task…

Well, he gave me the space and I went to work. I built an Integration Services package to import the data from the Oracle server to the internal SQL server. Then I setup replication to send the data to the DMZ. Other than a few hiccups with jobs failing and needing to be setup with different logins, it worked perfectly… Next came making the views for the search…

Obstacle 3

How do I convert the parts of Gary’s populate mcit_webdata that I need to SQL server?

Well, most of the views were very straight forward… I just took the scema off of the table names and they worked. However, there were a few functions that caused problems, the wannabe case statement comes to mind, but I can’t remember what the actual clause (function, procedure, command, whatever) was. Anyway, it wasn’t in Books Online (I had found the command reference and was using that extensively by this time), but a quick google search turned up some useful results. That, paired with the command reference, gave me everything I needed to make the views work properly. Only real problem was that the search time didn’t really improve as much as I hoped it would. A problem that could wait, I decided, and wait it did…

Obstacle 4

How do I incorporate personal property into this search?

Until just this week, the property search was just a parcel search and the treasurer has been riding me on getting the personal property (as opposed to real, parcels and improvements) into the search. Well, I can proudly say that I have finally done just that, as well as reducing the search time from 5 - 10 seconds to pull up the data for a parcel property to <1 to 4 seconds, depending on what data is available for said property.

I decided to try to speed up the search by creating a table that will hold all of the data that will be served to the web, statically, as well as a stored procedure to populate this table nightly after the import from the Oracle database. I actually got this idea from Gary’s scripts, too. Well, that did speed things up, but more importantly, it gave me the idea of how to include the personal property in the parcel search.

Every time this was mentioned to me, it seemed like everyone was suggesting I use a radio button type switch to switch between a search for personal and real property. Then I’d likely need to build a couple of new functions or subs into my code behind to handle the personal property search. However, it struck me that there’s not much difference between the data needed on personal property than that of the real property, like I said before, it’s really just a subset of the data needed for the real property. That said, the only real difference I needed to overcome was that personal property didn’t have parcel numbers…

Well, what I ended up doing was using the tax ID number on the personal property in place of the parcel number. The field that I intended to be for the parcel number was altered to accomodate the tax ID number, as well. The search application had to be altered some, I think I added about 40 or 50 lines of code to various functions and sub routines.

While I was altering the code, I made a few changes that I had been meaning to make, using SQL parameters where I’m getting input directly from the user. It provides some protection against SQL injection attacks, but also makes the error messages from mistyped entries more friendly (I already use an account that only has read access to pull the data to protect from SQL injection attacks).

I made a maintenance plan to run the job that imports data from the Oracle database, then runs the stored procedure to repopulate the table for the web search. After that, it rebuilds the indexes and updates the statistics on that table, then replicates everything out to the DMZ. The time it takes to pull property data in the best cases been reduced to less than a second and worst cases was still halved from the best times before the changes.

Now, if we can just make it through this tax season, I think everything will be moving along smoothly again, although it is looking like my workload is going to double… again. I’m thinking it might be time to revamp my resume, now.