PowerShell Cmdlets for Auto Dealerships

I am getting more and more calls for help with CDK Global, DealerTrack, and InfinitiNet data extraction and I want to post now about how I can help best:

  • I will write you a PowerShell cmdlet that will exactly meet your needs

I find the Microsoft PowerShell platform the best for folks that are not technically adept to extract data when, where and as often as they want. It helps to know how to run a PowerShell cmdlet but that is not critical.


Conversation with the RCI Desk at Reynolds and Reynolds

I recently had a request from a company to find out the current status of Reynolds’ approach to 3rd-party data access. Basically I found out that Reynolds decided to move from a telnet client to what this gentleman said was a “proprietary connection”.

In a nutshell ERALink is out and ERAccess is in, and it has been for some time apparently (I haven’t had a Reynolds client in a while). ERALink I believe, was really only intended for remote access (a telnet client) and the nice gentleman at Reynolds told me that now everyone that accesses ERA XT and ERA XTD systems uses ERAccess.

From the outset the Reynolds person knew that I developed custom Intranet (Sharepoint and others) solutions for automobile dealer groups. He knew I only needed to pull data and that there would never be any write-back to the DMS. Yet he steered me away from the RCI program and said that ERAccess was the way to go.

I’ve used ERALink and their Query Builder software extensively in the past and I really hoped that it wasn’t their best solution for a custom programmer like me, so I pushed on.

I asked what the RCI program provided to the 3rd-party vendor; he said that it was really for the guys that needed bi-directional read-write continuous access to the DMS. I said, “cool, but what do you offer? An ADO.Net data provider, XML Engine, what?” He said, “a set of XML APIs that will return the entire record associated with a data object (say a car or transaction) with no filtering ability.” He was really selling me on it NOT being for me.

So I said “ok, I send you a SOAP-like request and you send back some XML.” He said “yes”. I said “sounds like ADP’s DAP program” and I mentioned the Integration Points ADP has defined and I asked if they had a similar deal. He said “yes”.

So then I said, “well I really would love to have an easier time of it than using ERAccess and QueryBuilder so how much would that be?” Of course he wouldn’t say, but he did say that I, as the developer, would be assessed a one-time up-front certification fee and a per dealer installation and monthly fee. I said “what’s the certification fee, about $10,000?” He said “that would be the bottom end, it most likely would be more.”

I told him I was just a little guy developing important custom solutions for Auto Dealers. He said use ERAccess and Query Builder. I yawned.

Interestingly though, he did say that there were 3rd party vendors writing back to the DMS without Reynolds Certification (i.e. hacking) and that surprised me. I also learned that Reynolds is still using the PICK DBMS which is now called IBM UniVerse. Sheepishly he confided that UniVerse was state of the art in 1984 and I said “that’s alright ADP uses the same thing!.”

Until next time….

Accessing ADP’s XML Engine with PowerShell

Creating my first script to explore the various Data Objects in their entirety was a success I felt and a great experience exploring PowerShell’s XML capability. The experience also taught me quite a bit about .Net’s XML capabilities. I reviewed a few objects, by no means all of them because it gets real dry even for a data hound like me. I reviewed the ServiceSalesClosed, ServiceSalesOpen, ServiceSalesTechHoursOpen, ServiceSalesMLSOpen, ServiceSalesPartsOpen, ServiceSalesDetailsOpen, ServiceSalesDetailsClosed, GL Detail, Customer, Vehicle, and Employee.

During this process one day I had the need to pull some data from the General Ledger Detail. It should have been easy using my script but I ran into problems because I needed to filter the results. In other words I had to use the “Selection” tag in the XML Request. The DAP documentation states that the Selection tag accepts “ENGLISH” statements. ENGLISH seems to be the query language of the PICK DBMS which has been improved or upgraded by IBM (I believe) and is called retrieVe which I think might be part of uniVerse (which I think is what IBM calls PICK these days.) Anyway ADP uses the old ENGLISH it seems, not retrieVe. ENGLISH, as I mentioned in an earlier post, has limited operators and no function building capability. Also no way to join files. PICK has multivalued fields which is a challenge for me. An ENGLISH statement in the selection clause allows for unlimited ANDs and ORs. Here is my first selection clause used on the GL Detail object:


The problem with ADP DAP selection clause development is that there is no good source for the Property (Field) Names (called DICT names in ADP speak). The help desk advises you to use an Object exploration tool to look into the “Desc” property of the file on which the object depends and, if the developer described things properly, the DICT name would be there. The problem is that it is not always clear what file the object gathers its information from. Needless to say I invested a significant amount of time searching for the correct DICT name for the property I wanted to filter on. I now have an Excel spreadsheet that houses all my successful ENGLISH statements and what they do for which object.

The above ENGLISH statement says to filter the XML Response to only include GL entries for Account 205 (which would be Contracts in Transit) with an Accounting Date in August 2010. I learned later though that the BETWEEN operator is not inclusive; so I received no 08/01/10 or 08/31/10 entries!

It was very frustrating trying to build successful ENGLISH statements. There are so many wonderful fields available for which I could not find a successful DICT name that I decided that I would need to filter the response after it is returned from the server. This seems inefficient as hell but until I figure a better way to get to these DICT names and, in order to have the most robust data extraction system possible, I need to filter the response……. with XPath….yikes!

As I mentioned before I have never delved this far into XML processing so I reviewed everything I could find on the web about XPath and PowerShell and tested and experimented.

First I developed a script without using Select-XML and then one using it. In the first script which I will develop in this post I used an XPathNavigator object and passed in XPath query strings so here it is:

$Url = http://xxx.yyy.zz.aa:pppp 
$webclient = new-object System.Net.WebClient 
$soapMessage = @" 
<?xml version="1.0" encoding="UTF-8"?> 
<eoapi:Request xmlns:eoapi=http://www.adp.com/xxxxxx version="1.0" ShowUnknownElementErrors="Yes"> 
   <GetSet TimeoutSeconds="1800"> 
# Upload the Soap payload ($SoapMessage) to the URL:PORT of the ADP XML Engine ($Url)
$result = ([XML]$webclient.UploadString($Url, $soapMessage))
# Create an XPath Object by eliminating the duplicate reply tags starting the XML string at 2nd Reply tag (one with the group attribute), then convert that string to Xml ([xml])
$xpath = [System.XML.XPath.XPathDocument][System.IO.TextReader][System.IO.StringReader]($result.reply.session.get_InnerXML() | out-string | %{$_.substring($_.IndexOf('#Run the query and store the OuterXML property of the results in $qryResult
$qryResult = ($navigator.Select($query) | %{$_.OuterXml})
#Add Reply Tag to query result to create Root
$qryResultXML = [xml]('<Reply>'+$qryResult+'</Reply>')
#Date conditions are handled in the pipeline (until I figure out how to convert in the XPath expression) then send required fields to a CSV file.
$qryResultXML.reply.Employee | ?{[dateTime]$_.DateAdded -gt [dateTime]"2010-04-01"} | Select HostItemID, CustNo, NameCode, AccountingAccount, DateAdded, LastUpdate, CustOrCompanyCode, FirstName, MiddleName, LastName, Name1, SSN, CreditLimit, DealerField1, DealerField2, DealerField3, DealerField4, DealerField5, DealerField6, DealerField7, DealerField8, DealerField9, DealerField10 | Export-CSV "C:\Users\John Donnelly\Desktop\Employee.csv" -NoTypeInformation
#Open Excel
$excel = new-object -comobject Excel.Application
#Make Excel visible
$excel.visible = $true
#Open the CSV just created
$workbook = $excel.Workbooks.open("C:\Users\John Donnelly\Desktop\Employee.csv")

Step by step:

In this instance I am perusing the Employee object. The field names I use are fairly common. The first several lines are the same as in the previous post. I use a WebClient object to send a request to the XML Engine and store the results in the $result variable.

I then create an XPathDocument object and pass in the $result.reply.session InnerXML (slightly modified) using the object’s get_InnerXML() method

I then invoke the CreateNavigtor() method on the XPathDocument object and create a navigator object and store it as $navigator.

The fun part was writing a few queries which I have listed with all but one commented out. I could not figure out how to get DateTime to work so I put all DateTime filters in the pipeline later.  In this script the uncommented query pulls all employees who are coded as persons not businesses (interesting distinction eh?) . The date query commented out indicated that I can’t figure out how to write that one. Anyone care to comment?

I then invoke the Select() method of the $navigator object and pass in the query storing the OuterXML of the result in $qryResult. then in order to have an XML object to put in the pipeline I need to give it a root tag, in this case a Reply tag.

Next I select the Employee node like so; $qryResultXML.reply.Employee and pipe that to a foreach to filter out the DateAdded properties we don’t want and then Select the fields we want and Export –CSV.

This took me about 5 hours to put together because of the dearth of PowerShell related discussions about XML and my lack of coding experience.. I really want to thank Dan at PluralSight for his write-ups on XML and PowerShell. They were wonderfully specific enough to help me through this.

This script is somewhat a kluge but it works and will serve as the basis for more refined queries in the future. Thanks for listening and if you see anything strange, please comment and help out those who might rely on my work in the future.

Digg This

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.