Christopher Hawkins
clh at christopherhawkins.com
Sat May 22 13:10:36 CDT 2004
I'll second that. In fact, I will go so far as to say that data conversion *cannot* be estimated, because every dataset is as unique as a snowflake. -Christopher- ---- Original Message ---- From: Developer at ultradnt.com To: accessd at databaseadvisors.com, Subject: RE: [AccessD] On DB Bloat, Bad DB Design,and various CONVERT LEGACY FLAT TO RELATIONAL Date: Sat, 22 May 2004 11:13:14 -0400 >Never, ever quote a flat fee for this type of conversion. Always go >hourly. > >Steve > > > >-----Original Message----- >From: accessd-bounces at databaseadvisors.com >[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of John W. >Colby >Sent: Saturday, May 22, 2004 9:47 AM >To: Access Developers discussion and problem solving >Subject: RE: [AccessD] On DB Bloat, Bad DB Design, and various > > >Arthur, > >Given db1 with its (bad) structure, how does one create the queries >and >execute them to create db2 with its (good) structure? > >Piece by piece. > >I build up a query that pulls data about one entity and a matching >table >for that entity (Claimant, Claim etc). In the new table I add a FK >back >to the original flat data record. If that flat data record doesn't >already have one, I add an autonumber PK to it. In the query I pull >the >entity data and the PK of the flat data record and drop it into the >new >entity table. Move on to the next entity. > >Build Insurer with pointer back to flat. >Build Policy with pointer back to flat. >Build Claimant with pointer back to flat. >Build Claim with pointer back to flat. > >Using pointer back to flat, update the policy Insurer with the >Insurer >ID from new Insurer table. Using pointer back to flat, update the >claim >with Claimant ID from new claimant table. > >Etc. Etc. > >It is a painstaking and time consuming thing. The bigger issue is >the >issue of extracting "just one" Claimant (for example) if the person >has >more than one claim. Now you have a single claimant that needs to be >used in several different claim records so the "pointer back to flat" >doesn't work. In this instance if you have a piece of unique data (a >SSN for example) then you can build additional queries to join the >SSN >to the FLAT record to the new claim record to update the claimant id >in >the claimant table with the single claimant id in the claimant table. > >This whole process is an art, which done once, becomes trivial but >time >consuming. I build a separate database for the conversion process >with >all of these many queries in it. I then build up macros (usually >sufficient) to sequence the queries in the right order. I build a >macro >to do a set of data, then a macro to sequence those macros in the >correct order. This allows you to get the individual queries >running, >then get them running in the correct order, then "press a button" and >run the whole lot at once. > >The entire process for this database took well over one hundred >queries, >in exactly the correct sequence. > >THEN.... comes data cleanup. If the same claimant is entered with >SSNs >with numbers transposed... well they are different claimants aren't >they? Policy holders with a name mis-spelled... a different policy >holders. These are exactly the reason that normalized databases >exist >and because we are coming from a flat file system these exact >problems >exist IN SPADES. Usually the cleanup gets done on an "as needed" >basis. >If a claim is closed, why do cleanup on it? If it ever needs to be >cleaned up then they do. > >I have done this process on two major databases and a bunch of >smaller >ones. The process is always the same, queries sequenced correctly. > >John W. Colby >www.ColbyConsulting.com > >-----Original Message----- >From: accessd-bounces at databaseadvisors.com >[mailto:accessd-bounces at databaseadvisors.com]On Behalf Of Arthur >Fuller >Sent: Friday, May 21, 2004 11:32 PM >To: 'Access Developers discussion and problem solving' >Subject: RE: [AccessD] On DB Bloat, Bad DB Design, and various > > >This is a thread worth pursuing, IMO. Given db1 with its (bad) >structure, how does one create the queries and execute them to create >db2 with its (good) structure? I've been fighting this problem for >the >last few months, and it is decidedly non-trivial. There has to be a >correct way to do it. I'm certain of that. But I haven't found it >yet. > >-----Original Message----- >From: accessd-bounces at databaseadvisors.com >[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of John W. >Colby >Sent: Friday, May 21, 2004 9:05 PM >To: Access Developers discussion and problem solving >Subject: RE: [AccessD] On DB Bloat, Bad DB Design, and various > > >"Colbyize the user" - Out the door, 20,000 feet, without a parachute. > >For those who were wondering. > >It actually came from the practice of certain South American >"leaders", >when dealing with the Shining Path guerrillas (or suspected >guerrillas >in too many cases). > >Back to the subject, the unfortunate reality is that the typical >application designed by the typical user often simply can't be >transitioned economically. > > >I "transitioned" one of these things at one of my current clients - a >call center for the Disability Insurance industry. The main table >was >over 120 fields that represented about 5 different major entities >(Insurer, Policy Holder, Policy, Claimant, Claim, Physician1, >Physician2 >etc.) plus dozens of "lookup" tables (city, state, Policy Type etc). >They started with a "flat file" data dump from their client (the >insurance company) which they just started adding new fields onto the >end of. > >Needless to say, it took months to analyze the entities and write the >queries to extract the data (normalize the thing). Then it took more >months to build the forms to allow data entry, reports to report the >data etc. Had I been called in at the start it would have been a far >less imposing task. > >They hired me 1/2 time for almost a year to get the thing ported and >running, with more than 1/2 of that time just extracting data. They >USED that data every day and couldn't run the business without it. > >I had to build a system of queries that I could sequence in exactly >the >correct order to normalize everything. I had to test it and test it >again to get it all exactly right. Then I had to build up the forms >they would be using after the port. I had to run the normalization >and >have a handful of testers do double entry (in the new and the old) to >ensure that it worked, then I had to train the users in the new >system >(it simply didn't and COULDN'T look or work like the old). Finally I >had to "throw the switch" one night porting the data, and make sure I >was available on site for the next several weeks to handle in real >time >the inevitable issues that arose, to keep the system running and >their >client (and the claimants calling the database users) happy. On top >of >all THAT I had to keep the old db running until I could throw the >switch. > >Perhaps this isn't a "typical" power user database gone wild, but it >could very well be. And the results are VERY expensive. The company >just doesn't function without the database and the cost of >"transitioning" is astronomical compared to a gradual building up of >a >system the right way from the start. > >On a humorous note... the previous "developer" at the company was in >waaaaaay over her head trying to accomplish this stuff. She just >"disappeared" one day, never to be heard from again. My client was >very >nervous that I might do the same thing. > >John W. Colby >www.ColbyConsulting.com > >-----Original Message----- >From: accessd-bounces at databaseadvisors.com >[mailto:accessd-bounces at databaseadvisors.com]On Behalf Of Hale, Jim >Sent: Friday, May 21, 2004 5:47 PM >To: 'Access Developers discussion and problem solving' >Subject: RE: [AccessD] On DB Bloat, Bad DB Design, and various > > >Thanks, Susan- you hit the nail on the head and have prompted me to >put >down some thoughts that have been bothering me for awhile. Hide the >women and children, here goes :-) > ><rant mode on> >I have to start by saying upfront my career evolution has been from >the >finance/accounting user-to power user-to developer lineage and NOT >the >IT side of the world. Frankly, I developed programming skills in self >defense to have control over my own destiny because the IT side of >the >business couldn't/wouldn't keep up (even when it reported to me). My >understanding of Access has been need-based and evolved slowly over >time, i.e. I discovered Access and relational databases when Excel >"database" tables could no longer satisfy my needs. For several years >Access wizards and macros were my "state of the art" and I was happy >as >a clam. My little databases received "raves" and made me the office >guru >to the point I decided to take a college course in Access. It was at >that point I realized I really didn't understand relational databases >and that in fact my "wonderful" databases were all wrong except for >the >fact they produced useful results. While I have since gone on to >develop >my skills much further (yes, Virginia, I eventually discovered VBA!) >the >point is I believe my evolution is more the rule than the exception. >I >am willing to wager that in terms of sheer numbers the vast majority >of >useful, results producing Access databases have been created by the >user/power user cadre rather than developers. I know of a major >insurance company whose IT group recently did a nose count of >personal >Access apps that were floating around the company. The numbers >absolutely shocked them. Here at my company we have several people >who >know a lot about the business but only a little about Access. >Nevertheless they are using the tool to produce very useful results. >This is the true "silent majority" of Access users who are >attempting to >solve problems on a day to day basis. This should not surprise anyone >because after all the product was designed as a personal app. It is a >tribute to the strength of the product and the creativity of >developers >such as those on this list that Access has evolved far beyond a >simple >personal tool. The fact remains, however, personal databases account >for >the vast majority of its use. > >With this as background I am very disturbed by Microsoft's apparent >intent to remove Access from the mainstream of evolving apps. I also >was disappointed in Getz's column a few months ago summarizing a wish >list for the next Access version. It seems to me Access must stay >true >to its roots and those roots are as a personal app. What does this >mean >at the practical level? The self taught user runs into trouble not >with >the small apps that, however constructed, they can still get their >arms >around and validate the results. Its when these apps morph into >mission >critical monsters that have grown in size and complexity to the point >where the non IT professional can no longer ensure valid results that >things usually hit the fan. Typically these apps spin off into space >before developers are called in for pooper scooper patrol. So what is >the solution? It would be tremendous if Access could be given >additional >tools/wizards/internal training screens/magic to ease the transition >in >the database life cycle from user app to developer maintainable code. >While it might be fashionable to always say we should "colbyize" the >users, on their side of the fence they have even harsher terms of >endearment for the IT crowd. It is certainly not realistic to say >users >should keep hands off and leave all the development to the pros. I >believe the more understanding users gain about relational databases >by >hands on efforts the better off we all are. Having users in effect >"prototype" apps by taking a shot at building it also can save time >compared with a blank sheet of paper where the developer is forced to >play 20 questions trying to divine what users "really" want. If tools >could somehow be developed to smooth the user-to-developer >transition we >would all be winners IMNSHO. So I throw it out to the group, what >sort >of improvements could be made to Access to lower the user learning >curve >and smooth the handoff of projects from user to developer? > ><rant mode off> >That's my story and I'm sticking to it >Jim Hale > >-----Original Message----- >From: Susan Harkins [mailto:ssharkins at bellsouth.net] >Sent: Friday, May 21, 2004 8:55 AM >To: 'Access Developers discussion and problem solving' >Subject: RE: [AccessD] On DB Bloat, Bad DB Design, and various > > >Arthur -- do you know who wrote the original app? Was it someone >in-house that had to put together something because s/he was told to? >Access is as much a user database as a development tool -- that's >what >makes it so alluring to such a wide audience. If the boss tells you >the >department needs such and such, and you're not a database developer, >know onlyh a little about Access, you might come up with crap from a >developmental perspective >-- but if the crap works... Of course, eventually, they probably are >going to have to call in someone that really understands the issues, >but >for awhile -- it works. That's not a bad thing -- and I don't know >that >that's even the situation in your case Arthur -- but I think it >happens >a lot. > >And a lot of so called developers produce crap -- especially the >geniuses in other areas that think Access is a toy and that anyone >can >"do it." Those folks irritate me because invariably their stuff is >inefficient and laborious -- but it "looks" difficult and that's what >people expect to see, so they must know what they're doing, right? :) > >My personal favorite is developers that claim it can't be done >without >code. Yeah... Right... > >But, the crap issue -- it's why I don't do it -- I'd produce more >crap >than good stuff in today's environment. I can sling out little stuff >with the best of you, but once you get into the multi-user issues, >I'd >rather visit a dentist. > >Susan H. > >I don't think certs are the answer either Arthur--it is too easy to >get >a certification, and they push you through to fast. You don't even >have >to produce anything original to get a cert--just do their stupid >exercises in the back of the chapters. And, I have seen certified >people, both programmers and network admins, do stupid stuff. > > >-- >_______________________________________________ >AccessD mailing list >AccessD at databaseadvisors.com >http://databaseadvisors.com/mailman/listinfo/accessd >Website: http://www.databaseadvisors.com >-- >_______________________________________________ >AccessD mailing list >AccessD at databaseadvisors.com >http://databaseadvisors.com/mailman/listinfo/accessd >Website: http://www.databaseadvisors.com > > > >-- >_______________________________________________ >AccessD mailing list >AccessD at databaseadvisors.com >http://databaseadvisors.com/mailman/listinfo/accessd >Website: http://www.databaseadvisors.com > >-- >_______________________________________________ >AccessD mailing list >AccessD at databaseadvisors.com >http://databaseadvisors.com/mailman/listinfo/accessd >Website: http://www.databaseadvisors.com > > > >-- >_______________________________________________ >AccessD mailing list >AccessD at databaseadvisors.com >http://databaseadvisors.com/mailman/listinfo/accessd >Website: http://www.databaseadvisors.com > >-- >_______________________________________________ >AccessD mailing list >AccessD at databaseadvisors.com >http://databaseadvisors.com/mailman/listinfo/accessd >Website: http://www.databaseadvisors.com >