Database Notes

Date 04/19/2013

I want to thank all members of the Database Committee, John Healy, Jon Frisbee, Ed Stockavas and Ray Nelson along with the Ad-Hoc Committee for a spirited discussion regarding the allocation process. Sheldon Berrett from Frontier Airlines, Aixa Alvarado from Copa, and Tim Cawby from Federal Express were especially helpful in pointing out ways that the algorithm could be improved. I would like to thank the observers, Dennis Williams, Lloyd Martin, and Daniel Kenny. We made several code changes on the fly and the 2012 allocation was completely rerun four times. Here are the ideas that we entertained:

1) Adding one audit to each of the members that received 6 or 7 audits during last year’s allocation
2) Splitting out the 42 "least" desirable audits and giving one to each member before running the allocation
3) Giving each airline an international allocation within their first 10 priorities. an International for a non-US carrier would be one that would require overseas travel.
4) Tweaking the allocation methodology to add more "randomness" to the process.

The algorithm/process is essentially the same as before with the following changes.
1) The order of the line is changed each time through depending on the allocation number of the airline priority. It works like this; There are up to 500 passes through depending on the number of vendors that are allocated. The airlines line up for each priority run in order of the allocation number they choose. If for instance Short Brothers, which has an allocation number of 2, is your priority number one you will be first in line for the first pass through.
2) If your current priority is taken on any pass you get your next priority before the next run through.

Even after these changes the effects on the allocation was minimal. It very simply comes down to the number of passes that you stay in the process. If you happen to choose allocations that other members do not choose you will get them, it is that simple, and it is by design. If you have a large usage listing you will be in the process longer and by design end up with the lower prioritized audits, unless you were fortunate enough to pick vendors that other users did not. The size of an airline's usage list should correspond to the "value" received for being a C.A.S.E. member. If an airline has 500 vendors on its usage listing that should roughly mean that every year 250 of them will come up for C.A.S.E. allocation. With a maximum, like last year, of 18 audits that would mean the auditor would only have to visit 7% of them. Even if you were to say that the fully loaded cost of a domestic audit was $1,000USD and that the cost of an international audit was 3,500USD there would be a potential savings of well over $200,000USD per year. While it is understandable that international audits can cost significantly more than those undertaken domestically it is hard to imagine a scenario where the savings for a large usage airline is not significant.

We did not perform an exhaustive review of all least cost queue/routing algorithms and certainly there are many ways to approach allocating audits to members that can involve high level queue theory. Many years ago one of the first software packages I developed was a least cost routing algorithm for telephone switching equipment used for the predecessor company of MCI/Worldcom. The software was in use for a number of years as it chose the best / least cost path automatically for calls across a variety of carriers that owned the copper/fiber. There is a whole branch of mathematical research devoted to queues and while I have more theoretical and practical knowledge than most there are certainly many academics that are way beyond my knowledge-base. I could certainly research other advanced methods or we could contract a high level academic to approach the problem, but I am not sure the cost to find a more equitable approach will do anything other than move burden amongst a small minority of members.

1)  Preparing to place the new website online at the default web address.  If you have any comments, questions, or find any data that needs to be updated or changed please email Sam or Jerry.

Date 11/5/2012

1) Developing new web site and framework.  will be ready for viewing by November 8th 2012.  The new website will incorporate all of the new features of Web 2.0.  Including transparent selection of tablet, smart phone, or monitor templates.

2) Continuing review of new proposals for Allocation.

3) Preparing the change of the database to new Citrix / RDP virtual server.  This will bypass firewall issues by using SSL (Secure Socket layer for authentication)  Users will receive an icon on their computers which will enable the database to react like a local application.

Date 5/31/2012

1) Sending email links to our new survey application is working as designed.  Repair Stations are given the opportunity to answer some basic questions, in an anonymous fashion, regarding their interaction with a C.A.S.E. auditor. Results will be collated and discussed at the next full meeting.

2) Emails on UPAUD's are now sent to the auditor regardless if the Company is on their Usage.

3) Repair station was given an area on the server to hold all items regarding membership.

4) A copy of the main ACS database was depopulated and made available for the Repair Station Section.  A meeting is being setup for June to discuss and test.

5) Other items discussed during the Spring meeting are in the development database and ready to be pushed to the main database. 

Date 3/24/2009

These are the items discussed for database and website update.  We will go over these with the Chair of the database committee and the GM and make sure we do not miss anything.

1) Look at the two year training date and let it go out to 25 months.  It will be a hard field instead of a calculated field.  The check will be no greater than 25 out and no less than a month out. (Review at next meeting)

Override for 1A, 2A, 3A, P and P - Hide 5A(Done 5A - not sure on Override)

2) Master allocation and sub allocations.  System will provide for allocation numbers to refer back to a master allocation number.  Pool transmittals will be used to add the new allocations for companies that have multiple locations under one Repair station certificate.  We will also look at this for companies that are too large to get an audit accomplished by one auditor.  Will interface with Rex Graft on particulars. (In work - database work complete - need standards for the running during allocation)

3)  Look to change the text fields used for comments and change to memo fields.  We need to make sure that all of the fields are changed.  Not sure if there will be an unintended problems. (Issue in database -errors during testing of change to memo fields - in review)

4) Remove A449 from Pool transmittal put email address on form, where the upper a449 is currently located. (Unclear of removal of A449 - we added for a reason, is this now obsolete?)

5) Test pool usage and make sure of the functionality of each process.  Have an email broadcast on Pool and schedule transmittals.(Done in test database)

6)  For audit transmittal emails - see where the company name gets pulled from.

7) Examine move and combine on Pool Transmittals. (unclear if this is a problem or by design)

8)  Per Jim look to change the language of Last Entry Date to Last Audit Date(Done)

9)  Submit button on My unprocessed audits.(Done)

10)  Get the 2A Allocation into the system. (Done)

11) Remove the drop downs on the auditor form - leads to confusion.(Done)

12) Reallocate form - put schedule trans button on it that fills in the allocation number and the member id / auditor.  Gene currently uses the Supplier Flag 2 to mark them manually.

13)  New way of members to get P and P and other files.  I created a link to the database system on 3/24/2009 for testing.  Uses the same username and password for each user. (Done)

14) Send out a pdf form once an UPAUD is performed to the email listed on the Audit transmittal.  It will be a questioneer that is to be sent back to a TBD email address.

15)  Recheck all form sizes.(Done)

16)  Work with Vinny on the online membership area - can it be linked to a member transmittal.  May also be able to put this code on the database server and removed one level of indirection.

17)  Change Page to Cell Phone on Member form. (Done)

18)  Add to manual how to create pdf's for any report.(Gene and Christy need to add)

19)  Have a database backup available for training purposes.(Done)

ARSS -------------------------------------------------------------------------------------

1)  Make the repair station section database identical to the Air Carriers.(Done)

2) Test membership transmittal (Need to test)

3)  Work toward a standard mail out audit form that can be send back in PDF form to a central depository much like the ACS certs. (In Work)

Who's Online

We have 239 guests and no members online

C.A.S.E Calendar

Last month April 2018 Next Month
week 14 1 2 3 4 5 6 7
week 15 8 9 10 11 12 13 14
week 16 15 16 17 18 19 20 21
week 17 22 23 24 25 26 27 28
week 18 29 30