The ADP Data Access Odyssey

I recently undertook a project that is important for all of us in the automobile dealership industry who are seeking better ways to attract and keep customers. In the automobile dealership industry there are only a few key players providing comprehensive computer systems for the automobile dealership. The auto dealership group I work with has 21+ dealerships and has recently converted from the old Saturn EDS AS400 system (purchased by ADP to become ADP ARG) to the ADP Elite (recently rebadged as ADP Drive) system.

BMW Art Car Jeff Koons

I am not a professional programmer however I do enjoy writing code and staying current on the technological improvements in our world. I am a CPA by training and education with a BA in Economics who has worked in the car business since graduating from college many years ago. I have actually been a factory-qualified Dealer in addition to being a General Manager, CFO, Controller, Salesperson and IT Director. One of my current hats is IT Director and so I find myself embarking on this project. I thought it might help to recount my experiences about all things ADP since there is not a lot of transparency on the web in this area. I believe the retail automobile business is relatively unsophisticated in terms of IT. We typically rely on DMS (Dealership Management System) vendors to keep us informed and that leaves us a  bit like a child growing up in a closet; it is hard for us to know what is out there in the IT world. I undertook to change that, at least in my world.

DMS providers capture data, summarize it in various ways and report it back to us. I believe a competitive advantage is available for dealerships that get into that data and mine it for gold. If I can glean some opportunities from my data and my competitor’s are just looking at standard reports, I win.

For the past 10 years I have written IBM SQL data queries using Showcase Strategy for my employer and others. The EDS Saturn system was wide open in terms of Data Access. Showcase Strategy is a very robust front end for querying the DB2 database. However the Saturn EDS system was old when EDS got a hold of it and modified it for Saturn. The redeeming factor though was the ability to extract information using a robust language (DB2 SQL) and a killer front end (Showcase Strategy).

Since Saturn’s demise ADP has decided to drop support for their ADP ARG (EDS AS400) system and we needed to move up to Elite (Drive) or go elsewhere. In the evaluation and negotiation process I was sure to focus on data access as one of our most important requirements.

ADP’s platform is based on the PICK DBMS and this very old system has been built upon for over 25 years I would guess. It is a UNIX box I believe with PICK installed. When James Pick died it seems the DBMS was taken over by IBM. There must be a few installations out there for that to happen eh? Anyway, ADP doesn’t offer much in terms of Data Access out-of-the-Box other than PICK query statements called ENGLISH. There is only access to individual files with no JOIN capability. The selection of functions and operators is not good as well. This was not going to do for us so I kept pushing the ADP reps for a solution.

There is a program that ADP maintains for 3rd party vendors to access a dealerships data for the purposes of building add-on applications. They call it the Data Access Program (DAP). It is a set of APIs to access data objects on the DMS server. ADP was reluctant to offer this because it is a complicated system and most dealerships don’t have the inclination to hire people capable of working with native APIs. I had done a little C# and Windows forms work as well as a lot of VBA so I knew I could get a handle on it.

In the ADP SDK they had a .Net Data Provider so I started my training building Windows Forms apps using ADO.Net. On the sample data they offered for training all went well. They also offered an XML Engine interface which I basically ignored in my training because I figured ADO.Net would serve all our needs. I passed the training and got my certificate. There was also training on how to build custom objects which will be of considerable benefit when I get it to work on my server.

Let me just say that the DAP documentation is so-so. It is all there but you need to look in several places to get the complete picture. The email help desk people are great though so all you have to do is fire off an email if you get lost.

I have approached the DAP implementation for my organization as a slow investigation and learning process. We have been converting dealerships one or two a month all this year and by the time they are all converted in 2011, I will have the DAP program fully under way. I have abandoned the ADO.Net approach because when I went live I kept getting spurious results on my data extractions. When I asked the help desk they said that  the .Net provider is not supported on Vista Win7 platforms and to move to the XML Engine. I then had to get up to speed on the XML Engine. The XML engine accepts soap-like requests, accesses the PICK DBMS, and sends back a response. Simple.

My plan now is to develop a front-end to the XML Engine using PowerShell which will eventually serve as the start of an Excel Application that will help various users in the dealership get the data they need. I envision an Excel Workbook for those job titles that need one with built-in XML requests and the resulting formatting of the data as required. The users can then use PowerPivot to slice and dice as needed. I envision master workbooks on Sharepoint that I maintain and update regularly which might be thought of as a Base Class which all instances of the say “BDC Manager Workbook”  inherit from. I might modify an individuals instance of the workbook on rare occasions. I think this is a good plan because the Excel learning curve is already underway. If the data just appears, they can be trained to use Excel’s powerful Data features to get what they need.

The PowerShell learning curve for me has been steep and since there is little help on the web I wanted to share what I’ve learned. In the next post I will launch into the PowerShell script that has allowed me to explore and understand the ADP DAP program.