The Platypus Hunter's Revenge 2

[lespaul]

Well, it's been almost a year since I posted 'The Platypus Database', and an amazing thing has happened:

I am the Platypus Hunter! I have been assigned the project of NORMALIZING our database!

In fact, I have been designated unofficial DBA with the objective of becoming reclassified as the official DBA in the future.

Of course, now I'm daunted by the fact that I have to take 20 years worth of data and migrate it to the new tables! I would welcome any suggestions from those of you who have done something similar in the past.

I have downloaded 'DeZign for Databases' which seems to be a decent Data Modeling tool. I have started with the tables that I am the most familar with and that went well. I've now moved into some of the easier areas (like Human Resources) before diving into some of the areas of the court that I don't know as well.

My plan is to analyze the existing tables and determine the data fields that need to be transferred to the new design; create the data modeling diagram of the normalized data. Once the data model has been approved, create a utility that will migrate the data. I am expecting approximately 80% of the information will transfer via the utility. Out of the remaining 20%, 95% of those will need slight massaging to get the data migrated and the other 5% will need massive massaging to migrate. Do those estimates seem reasonable?

Are there any other issues that I should be aware of when tackling a project of this magnitude? I'll admit, this is the biggest thing I've ever done!

Thanks!

Leslie

 

[sleipnir214]

I think a task worthy of your attention would be to identify old data no one uses any more. There's no point transforming that data if no one needs it.



Want the best answers? Ask the best questions!

TANSTAAFL!!

 

[lespaul]

Yes I am eliminating duplicate data and non-used data. I have been talking with the users about what kind of information they currently have that they need to keep (one user said, "You mean you're going to talk to us about what we want?!" and was amazed when I answered "Yes!").

I have gotten information from the users about historical changes that they would like to be able to review (when a defendant's address is changed they would like to see a history of the address, when an employee changes to a new "Perm#" (sort of like a job code), HR would like to be able to see that history). Neither of those options exist currently, but have been included in the new table designs.

Leslie

 

[MDXer]

Why not build a warehouse that contains older data?

"Shoot Me! Shoot Me NOW!!!"
- Daffy Duck

 

[BJCooperIT]

MDXer makes an excellent point. Nothing flip-flops like a user's decision on what data is important. It depends on who you ask and on what day of the month/week.

We did a data conversion for a client who wanted all ther current data in MS Access converted to Oracle. They claimed they definitely did not need the archived data in IDMS system. A year after the conversion they determined they needed the IDMS data converted. After that was loaded into Oracle, they decided, since the IDMS data was incomplete, that they want it out of the main tables into a data warehouse. If they get their own way, we will have to rewrite queries and reports to gather data from two sources. Had the specifications been complete from day one, this could have been prevented.

^{Beware of false knowledge; it is more dangerous than ignorance. ~George Bernard Shaw}
Systems Project Analyst/Custom Forms & PL/SQL - Oracle/Windows

 

[willir]

First Leslie, well Done !!!

And with the incredible experience you continue to demonstrate (especially in desing and SQL), I couldn't think of a more qualified person to handle the transition.

You do want to
- Allow access to archival data. ("yes we absolutely don't need it" -- until the aditors show up)

- You know about design. The test will be when to use common sense or normalize to the fifth level.

- Determine the number of transactions and how big the database will grow. Since you have a basic idea on the design, you can guesstimate the number of transactions. I am not sure how much space indexes take, and the overhead - probably want to double or even triple the calcuations. It would be kind of yucky to champion Access only to find you need something bigger.

- Migrating the data will be dependent on the curent platform. I usually being the information across in a comma deliminitated CSV file into one table. HINT: Add a yes/no field to the raw data -- use it to track a successfully converted record. HINT: Add a date field, and use it to track the date the raw record was exported from Platypus. Then develop / build a migration module / system. (Since there is bound to be bad data, straight SQL may not work, or may miss somethings.) The conversion module takes work to get the kinks out. Sicne you are working with a flat file, you will probably have to work through each record and update several tables as you loop through. A form that reads the raw data can be used to process quirky records with a few command buttons.

- Play with your SQL statements to test that the data returns the info requried.

- Play with the indexes

- Decide on security and infrastructure issues.

- Then move on to your forms and reports. Spend a lot of time on the first forms with record locking, security, network bottlenecks. Then use a similar stregy on the remainder of the forms.

Whoops, and they are entering data into Platypus while you are working on the aforementioned?? Well, you have the code that now. You know when the previous records were exported / imported, and you know which records have been normalized.

And you know all about the system admin stuff - backups, security, compct and repair. Don't forget system documentaiton.

Oh yea, find a steep, bottomless canyon and deep-six the original platypus.

Wow, great stuff.

 

[jtb]

just my 2 centavos... fire anyone who always uses "select *" in the most horrific and public way possible...

It's taken us over a year and (5 million dollars in overtime, rework, and performance penalties) and we're not free of all of the stored procedures that create as much as 25-second delays on each read that actually only needs a screenful (since that's all that's displayed)...

And we can't even get rid of NT 4 and SQL 7!!! (we are hoping to fix that before Dec 31)

Standards can save your job... not using them can cost your whole company theirs...

JTB
Have Certs, Will Travel
"A knight without armour in a [cyber] land."

 

[willir]

Leslie

I realized I forgot to include comments on coding in my previous post...

JTB is correct, in that you have to plan the future. If Access is fine now, you may still be proactive and plan for the future by making your code more portable...

http://www.mvps.org/access/tencommandments.htm

http://www.mvps.org/access/lookupfields.htm

Further to this, try an modulerize your code as much as possible. Easier maintenance down the road.

And MichaelRed's comments on project management

http://www.tek-tips.com/viewthread.cfm?SQID=599266&SPID=181&newpid=181&page=1

 

[lespaul]

(Thanks Richard!! I'm really excited!)

We are actually using an AS400/IBM iSeries for the database. That's where the data is currently stored and where the new tables will also be created and stored. And we will continue to store the old data and the programs required to view it, but one of the reasons for this is to clean up the data. For instance, I can find criminal cases that have hearings set for non-existent charges!

One of the other major reasons is to be able to then create an integrated environment for the court's processes (using Delphi). Our current user environment is the AS400 "green screen" developed in RPG.

And space isn't going to be a problem! As far as the amount of data, in the three largest tables in the criminal side of things the Case MasterFile has 1,615,614 records; the defendant MasterFile has 1,609,936 and the hearing mf has 8,992,592. So there's A LOT of data!! But at least 75% of each of those records is duplicated in EACH of those tables! The hearing table has all the defendant information in it as well as some of the case information. The case information is in both the defendant file and the case file, etc.!

I like the idea of the flag to indicate whether or not the record was successfully migrated and when! And I have really started working on more modular code. It's amazing how well that works!

On a semi-related subject, I may have the opportunity to attend the 'Borland Conference' in San Jose in Sept. I have never been to an event like this and wanted to get opinions on whether or not they are useful and if I'm likely to learn some techniques that will be helpful in this big project! There are sessions on Design, Development, Implementation and Management. But I don't have any personal experience to decide if this information will be just a bunch of BS from marketing or if it is actual useful information! (I need to submit a request and provide justification.)

Thanks!

Leslie

 

[OhioBill]

Lespaul,

Congratulations on your new position.

My experience with conferences, seminars and trade shows is that the reality falls somewhat short of the marketing description. If you did not attend the Borland conference, what would you attend instead?

 

[willir]

Ha ha ha...

My comment is if the employer did not pay, would you pay your own way? There are worthwhile conferences, but you have to take into consideration that the vender may be using the conference for Pr to push their products.

...Here is our wonderful solution, and all you have to do is buy our product...

Still, sounds like a very nice break, and networking with your colleagues is perhaps the most important activity.

sighhhh

 

[lespaul]

Thanks for the input. If I don't go to this conference then I can go somewhere else. I was offered the chance about a year ago to go to a conference and got passed over. I was told that I would get to go to the next one. Well the next one offered was an AS400/iSeries specific conference that our RPG programmers will get WAY more out of than I would! So, I passed on that event. When I was notified of the Borland Conference, I asked my boss if I could go. His response was "provide me with a hard copy of the conference information and justification."

The website indicates that there are special prices available for Borland products at the conference, but it doesn't sound like that is the focus of the event.

If I had the money, I would probably pay for it myself.

Les