I spend many hours of my professional life exporting very large quantities of data from Oracle Databases and evaluating it for user reported problems or data profiling. My tool of choice for this has always been Excel. Up until this class, I had used very basic techniques that got the job done but were very labor intensive. This week, I was evaluating database catalogs to determine if a particular piece of data was stored in any of about 50 database schemas. We use fairy consistent naming standards for our columns in most of our IT controlled Schemas, so I was able to use the “= Find” function to locate a particular naming standard “_nbr” and translate it to a constant that was easier to sort and evaluate. Needless to say, this reduced the number of columns that needed to be evaluated by about 90%. That one new technique saved me untold hours of tedium.
It seem that every lecture so far in this class has had a very positive impact on my professional life. I suspect that is because, once you have a method to get a job done, you rarely have the time or the inclination to look for a better way. It is the classic, “If it ain’t broke don’t fix it” axiom. I have a whole new mind set about that now. Thomas Edison used to walk up to his employees and say “There is a better way --- Find it” I plan on looking for that better way in all of the tools I use on a daily basis thanks to this lesson.
I have used pivot tables in a past career for writing reports. Presently, I perform those function with tools like Business Objects or Hyperion (Brio) as they can subscribe to the data without export (Oracle, DB2, SQLServer) but I think I may find some new uses for pivot tables in my daily work.
No comments:
Post a Comment