Company

DynCorp - a CSC company

Description

One of the world's leading and IT services company.

Web

www.dyncorp.com

Requirements to meet

We have a huge Document Management Repository that is managed through many metadata properties that are assigned to each document. These metadata properties are maintained in two Oracle databases. We needed a quick, easy, and clean way to pull data out of these databases and we have found it in Oraxcel's SQL*XL.

Issues to tackle

1. During the 6 month long data migration from an old legacy database to the new Oracle databases we needed to be able to keep track of how the migration was progressing and SQL*XL made that an easy task.

2. Because the document repository is so big it easy for the customer to archive a document into the wrong place and then not be able to find it within the physical repository itself. They can search and display the metadata but they are unable to browse to the physical location of the document. We have come up with a very efficient way in order to do a step-by-step query with an Excel worksheet using SQL*XL to pinpoint the location of the lost document within minutes.

3. We have been asked if we could produce a report that shows how many new documents are being processed into the document repository by each office on a daily basis. Again we went to SQL*XL to get the job done and the customer likes the results.

4. Our customer requested that we export entire Oracle database tables to be easily used by other organizations that were looking closely at what we are doing within the document management realm.

Approach taken

1. Created a simple SQL query with an Excel worksheet that would tell us how many items had been successfully migrated to that point. Ran this query using SQL*XL to display the data in Excel and utilized the Comment function of SQL*XL. Then dated the returned data and then simply copied the Comment to another cell to be reused for another time later that day or the next.

2. Created an SQL query Comment within an Excel worksheet cell that tells us the ID for the lost document. Then created an SQL query Comment within another cell in the same Excel worksheet that tells us the name and ID of the immediate folder location for the lost document. Then copied the SQL query Comment mentioned in the previous sentence to another cell in the same Excel worksheet and edited the Comment changing the ID to be found to that of the ID just located in the previous query. This new query tells us the name and ID of the folder for the next level above the current location of the lost document. Then we just repeat these steps moving up one level at a time until we finally successfully locate the physical location of the lost document.

3. Created 31 Comments in an Excel worksheet, one for each day of the month for each organization that archives documents into the repository. Each one of these 31 Comments contains an SQL query that is executed using SQL*XL and returns an exact count of how many new documents were archived on that particular day by that organization. This data is then easily linked to a separate worksheet in a report form and other worksheets in chart form to produce the final desired reports.

4. Using SQL*XL we were able to quickly export the desired tables in there entirety to Excel worksheets within the same Excel file. The data could then be reviewed, edited if necessary and then easily imported directly into the other organizations database. This has already been a very big help on several occasions.

Results

SQL*XL has made it very easy for even novice Oracle DBAs like myself and my co-workers to be able to quickly produce data, stats, and even reports that help us manage our data and give our customer what they want. SQL*XL has also helped me tremendously in the Oracle learning process. Oracle's SQL Plus and DBA Studio just wasn't doing it for me. DBA Studio is great for GUI folks like me when it comes to actually seeing the data but its not very stable and its very cumbersome to work with. DBA Studio is very slow and always crashing and hanging up. SQL*XL on the other hand is very stable, quick, and user friendly.