InfinitiNet Data Access

Infiniti USA requires its dealers to use a certain software in managing their dealerships. It is called InfinitiNet. I haven’t worked with an Infiniti Dealer in a few years so they may have made some changes to the name, description and platform but I don’t think so.

InifinitiNet is an IBM AS400 based system which is good news for us data extraction and mining people. With the right connection string and Data Provider, we can access using Excel (Microsoft Query), Excel (VBA), PowerShell, ADO.Net etc

The file structure is fairly intuitive and is easily explored with IBM’s ISeries Navigator.

I have maps of many of the more commonly accessed tables in the InfinitiNet database if anyone is interested.

Here’s a sample SQL statement pulling Warranty RO information:

SELECT “WAADIV”,”WAACUS”,”WAAVEH”,”WAASCI”,”WAAAWC”,”WAAAWO”,”WAASTS” FROM I0996.FILEZ.WAA WAA

As you can see the WAA table holds most of what we need to know about warranty ROs. I wrote this SQL statement to help a dealership get a handle on their warranty claim metrics that Infiniti uses to determine if a dealer is a candidate for a warranty audit.

Here’s a sample list of the tables in the FILEZ database:

ADM: Name & Address Master File
ADS: Email Addresses
ANA: Null
APP: Service Related Info
ASROUT1: Some Type of System Table
ASU: Null
AUD: Null
BAB: Only 2 records maybe accts??
BAR: Has a lot of records BAR Date and BAR UID, BAR Name
BAS: Looks like Customer ID and Some EIN#s
BAT: Looks like a batch ID file
BHD: Null
DCBDY: Holds avail car body codes and their desc
DCCLR: Holds colors
DCINT: Holds interior colors
DCMAK: Holds car makes
DCMDC: Holds car model codes?
DCMDL: Holds car model names
DEA: Holds deals! – a zillion fields
DRA: Holds Chart of Accounts! Or might be a report file showing all the accounts going into a report
FFS: Think this is financial statement – holds lines and pages
FII: F&I Inventory Master Interface Download File
GAA: Accounting Header
GCA: Accounting Detail
GAH: General Ledger History File Summary (Just like Saturn’s GLMSTHPF)
GAM: General Ledger Accounts Master File
IAM: Parts Master File
VAM: Vehicle Master File
VRM: Vehicle Owner File
WAA: RO Header
WJA: RO Jobs
WJM: Job Master File
WPA: RO Parts
WEA: RO Expenses
WAS: RO Split Coverages
WSH: Service History Header
WDH: Service History Detail
WAP: RO Parts Expenses Totals
WAT: RO Totals
ESH: CRM Sales History
GAS: GL Schedule Reports
GAT: Period Date Table
GBA: GL Document Addt’l Info
GBT: Document Type Table
GCT: Table Title File
USM: User ID Master
UST: User ID Access Table
VAT: Vehicle Tables

If anyone has a particular issue they need data on, leave a comment and I will try to help.

Extracting Data from Car Dealership Computer Systems

Recently I received an inquiry as to my availability to consult for a company that needs to extract data from Automobile Dealership Computer Systems. They build websites for dealers and wanted to extract vehicle inventory data. Here is my response;

December 6, 2010

Dear Mr. Jones,

I enjoyed talking with you last week about your data extraction needs. I have been involved in data extraction while creating Business Intelligence solutions for auto dealerships for the past ten years. I currently am contracted with ADP to prepare extractions for their ADP ARG (AS400) clientele who are (were) mainly Saturn customers. I also have worked with a large dealership group here in Nevada building business intelligence Sharepoint solutions which are heavily dependent on data extraction. The following is a summary of my skills as they relate to this project:

Data Extraction Platform Experience:

  • ADP – UNIX, PICK – Both Telnet and DAP (Data Access Program)
  • ADP ARG – IBM AS400, DB2 – (System modified by EDS for Saturn Dealers)
  • Reynolds & Reynolds – Query Builder – Telnet
  • Arkona – IBM AS400, DB2
  • InfinitiNet – IBM AS400, DB2
  • With the advent of Microsoft’s Windows Communication Foundation and ADO.Net there’s really very few systems that can’t be mapped given access.

Data Extraction Methods and Languages Experience:

  • ADO.Net
  • Microsoft SQL Server 2008
  • PowerShell
  • XML
  • AS400 (DB2) SQL

Data Presentation Experience:

  • Sharepoint
  • Excel Services
  • ASP.Net
  • Silverlight

Based on our conversation last week I put together the following proposal.

That’s it. I include it here in case any one might be looking for similar abilities.

Happy New Year!

XML and PowerShell Links

Links to helpful articles and Blog posts on XML and PowerShell.

During my search for information on XML and PowerShell I came across these links that I used many times. My desire to blog about XML and PowerShell was motivated by the fact that I had a hard time finding answers to my questions.

https://www.pluralsight.com/community/blogs/dan/archive/2006/10/28/41337.aspx
http://www.pluralsight-training.net/community/blogs/dan/archive/2006/11/28/43561.aspx
http://www.w3schools.com/xpath/
http://thepowershellguy.com/blogs/posh/archive/2007/12/30/processing-xml-with-powershell.aspx
http://powershell.com/cs/blogs/ebook/archive/2009/03/30/chapter-14-xml.aspx
http://www.kirupa.com/net/readingXML_pg1.htm
http://powershell.com/cs/blogs/tobias/archive/2009/01/17/xml-part-1-playing-with-rss-feeds-and-xml-content.aspx
http://powershell.com/cs/blogs/tobias/archive/2009/02/02/xml-part-2-write-add-and-change-xml-data.aspx
http://msdn.microsoft.com/en-us/library/ms256060(VS.80).aspx

If there are more let me know.

Using PowerShell to Request, Filter and Manipulate an [XML] SOAP Response

PowerShell’s Select-Xml commandlet is used to filter a SOAP Response.

I’m back after a few weeks of car business busyness. I think it is important to post some alternatives to the initial script I wrote to pull data off the ADP DAP Server. When I began this project I didn’t do a lot of research and I thought the Select-XML commandlet was some 3rd party script and not part of PowerShell 2.0. I wanted my script to be usable without any 3rd party snap-ins or functions so I used the XPathDocument method called Navigator instead of Select-XML. Last time I presented my discoveries as it related to using those .net objects.

This time I will show you the script I wrote using Select-XML which is a little cleaner. There were some modifications of the XML output because of differences in the way the commandlet works compared to using the XPathDocument object.

The script has the same XPath query options and for now, I just uncomment the one I want to use and comment out the rest. I have added some Excel formating statements to help make the output more readable.

#With this Script we pull the Employee Object into Xml and we have three places we can set conditions:
#   1) In the $soapMessage Selection Statement 
#   2) In the $query XPath expression statement - uncomment the one you want or make a new one
#   3) In the Pipeline (line 37 or 39) where we select the Employee node and restrict by DateAdded (Need to do this until I figure out how to convert strings to dates in XPath Expression)
$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"> 
   <Session> 
   <Connection> 
      <Product>Product</Product> 
      <Server>Server</Server> 
      <Password>password</Password> 
      <Pooling>No</Pooling> 
   </Connection> 
   <GetSet TimeoutSeconds="1800"> 
    <Qbject>Employee</Qbject> 
    <Group>DealershipCode</Group> 
    <Selection></Selection> 
    <MultiFlag>E</MultiFlag> 
   </GetSet> 
  </Session> 
</eoapi:Request> 
"@ 
# Upload the Soap payload ($SoapMessage) to the URL:PORT of the ADP XML Engine ($Url)
$result = ([XML]$webclient.UploadString($Url, $soapMessage))
#Write a XPath queries - Uncomment the one you want to use, comment the one's you don't
#$query = "/*/Employee" # This query returns all Employees in the object
$query = "/*/Employee[CustOrCompanyCode='PR']" # This query returns all Employees in the object that show as Persons as opposed to 'BS' for businesses
#$query = "/*/Employee[FirstName='DAVID']" # This query returns all Employees whose first name is 'DAVID' (case matters)
#$query = "/*/Employee[[dateTime]DateAdded > [dateTime]'2010-04-01']" # DOES NOT work....still Working On This One
$qryResult = Select-XML -content ($result.reply.session.get_InnerXML() | out-string | %{$_.substring($_.IndexOf('<Reply group='))}) -xpath $query | %{"<Employee>"+$_.node.InnerXML+"</Employee>"}
$qryResultXML = [xml]("<Reply>"+$qryResult+"</Reply>")
#Uncomment the following and comment out the next statement if you have pipeline conditions
#$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
#Uncomment the following and comment out the preceding if you have no pipeline conditions
$qryResultXML.Reply.Employee | 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")
#Get the first sheet by name
$sheet1 = $workbook.worksheets | where {$_.name -eq "Employee"}
#Make first row bold
$range = $sheet1.range("A1:w1")
$range.font.bold = "true"
#Put a border around it
[void]$range.borderAround(1,3)
#Format the various field columns
$sheet1.columns.item('m').NumberFormat = "#,##0.00_);(#,##0.00)" # for numbers
$sheet1.columns.item('l').NumberFormat = "000-00-0000" # for telephone numbers
$sheet1.columns.item('e').NumberFormat = "mm/dd/yy;@" # for dates
$sheet1.columns.item('f').NumberFormat = "mm/dd/yy;@"
$sheet1.columns.item('b').NumberFormat = "@" # for general (makes all entries nicely left justified even if a number)
#Select the Range in use
$WorkBookNew = $sheet1.UsedRange
#Autofit all the columns
[void]$WorkBookNew.EntireColumn.AutoFit()

Here’s my thought process building this rather inelegant solution:

The SOAP request, response variabe and XPath query lines are the same as last post. The major difference is that I am now using the Select-Xml commandlet to filter the SOAP response. Granted this isn’t the most efficient way to select the data. If PICK’s ENGLISH database query language was half as robust as XPath’s I would skip the Select-Xml statement all together and filter using the SELECTION tag of the request.

The power and new part of the script is this monster line that loads the $qryResult variable. We use the Select-Xml commandlet with the -content and -xpath parameters in use. The -content parameter requires a valid xml object so we use the get_InnerXML() method of the XMLDocument object $result.reply.session and strip off the extra “Reply” tag as in the previous post. The -xpath parameter holds a valid XPath query string and I list several examples. The one that is uncommented selects only those Employee nodes where CustOrCompanyCode=’PR’ meaning the employee is a person as opposed to a business (‘BS’). Go figure.

There’s more to this line but here’s the line to look at before we go on:

$qryResult = Select-XML -content ($result.reply.session.get_InnerXML() | out-string | %{$_.substring($_.IndexOf('<Reply group='))}) -xpath $query | %{"<Employee>"+$_.node.InnerXML+"</Employee>"}

Next the Select-Xml returns an XMLDocument object that is piped into a Foreach and wraps each set of child elements in its own Employee tag. We need this or we would just have the children in a string not grouped by Employee. You can think of it as wrapping each set of children of the node queried in an Employee tag.

Now we need a Root so we wrap $qryResult in a Reply tag and type it [xml]. Again if it were not well-formed the script would error out here.

$qryResultXML = [xml]("<Reply>"+$qryResult+"</Reply>")

Next we use dot notation and access the Employee Nodes of the XMLDocument object:

$qryResultXML.Reply.Employee

and pipe it into a Select-Object commandlet to grab the child items we need. I selected all in the script so I can delete each time what I don’t need. The resulting objects are then piped into the Export-Csv commandlet and saved locally.

Here’s the whole line:

$qryResultXML.Reply.Employee | 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

I then instantiate an Excel Com Object and open th csv file just created. I then activate the proper sheet and start formatting the first row and the columns so dates, phone numbers, dollar figures and other columns present well.

Next I autofit all columns used so I can see all the contents of the cells.

That’s it for now. Any comments or suggestions will be appreciated.

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:


AND WITH ACCTG.DATE.EXT BETWEEN “08/01/10” AND “08/31/10”

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"> 
   <Session> 
   <Connection> 
      <Product>Product</Product> 
      <Server>Server</Server> 
      <Password>password</Password> 
      <Pooling>No</Pooling> 
   </Connection> 
   <GetSet TimeoutSeconds="1800"> 
    <Qbject>Employee</Qbject>
    <Group>DealershipCode</Group> 
    <Selection></Selection> 
    <MultiFlag>E</MultiFlag> 
   </GetSet> 
  </Session> 
</eoapi:Request> 
"@ 
# 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

ADP DAP, PICK, and PowerShell

ADP’s DAP program allows the developer to access data objects on the UNIX server. There is a standard set of Business Data Objects that include groups of data fields related to each other. For example, in the Service Department objects there are objects called ServiceSalesClosed and ServiceSalesOpen, the former holding data relating to all the Service Transactions that are finished (i.e., closed) and the “Open” service sales are still in process. Inside the objects there are up 400+ fields of data.

My plan was to jump in and understand the XML Engine front end that ADP created and see what it could do. I decided that PowerShell would be the best way to experiment and learn.

I am a beginner at PowerShell especially as it relates to Xml so any readers that cares to offer helpful hints and/or critique, please do. Also feel free to correct my verbiage. I know that I am not using the terms root, node, child etc correctly at times. Also please note the back tick to indicate a line continuation. I have note found a good code inserted for PowerShell script for Windows Live Writer yet.

My first script was designed to set up the request as specified by ADP and use the .Net WebClient object to submit it. The request has a tag for Selection filtering but I really wanted to see all there was so I ignored that parameter.  Voila I received a response!

Here’s the script:

$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">
   <Session>
   <Connection>
      <Product>Product</Product>
      <Server>Server</Server>
      <Password>password</Password>
      <Pooling>No</Pooling>
   </Connection>
   <GetSet TimeoutSeconds="1800">
    <Qbject>TheDataObjectName</Qbject>
    <Group>DealershipCode</Group>
    <Selection></Selection>
    <MultiFlag>E</MultiFlag>
   <GetSet>
  </Session>
<eoapi:Request>
"@
# Upload the Soap payload ($SoapMessage) to the URL:PORT of the ADP XML Engine ($Url)
$result = ([xml]$webclient.UploadString($Url, $soapMessage))
# Eliminate the duplicate reply tags by starting the XML string at 2nd Reply tag (one with the #group attribute), then convert that string to Xml ([xml]) and store in $xmldata
$xmldata = [xml]($result.reply.session.get_InnerXML() | out-string | %{$_.substring($_.IndexOf('
$xmldata.reply.ServiceSalesClosed  |  Export-Csv "C:\Users\John Donnelly\Desktop\Output.csv" -NoTypeInformation
$excel = new-object -comobject Excel.Application
$excel.visible = $true
$workbook = $excel.Workbooks.open("C:\Users\John Donnelly\Desktop\Output.csv")

Explanation:

I set the $Url variable for the address of the XML Engine front end with IP and Port as follows:

$Url = http://xxx.yyy.zz.aa:pppp

and I then instantiate a WebClient object to handle the request response as follows:

$webclient = new-object System.Net.WebClient

I then setup the request payload as a variable called $soapmessage per the ADP DAP specifications (encoding, namespace etc):

$soapMessage = @"
<!xml version="1.0" encoding="UTF-8"?>
<eoapi:Request xmlns:eoapi=http://www.adp.com/xxxxxx version="1.0" ShowUnknownElementErrors="Yes">
   <Session>
   <Connection>
      <Product>Product</Product>
      <Server>Server</Server>
      <Password>password</Password>
      <Pooling>No</Pooling>
   </Connection>
   <GetSet TimeoutSeconds="1800">
    <Qbject>TheDataObjectName</Qbject>
    <Group>DealershipCode</Group>
    <Selection></Selection>
    <MultiFlag>E</MultiFlag>
   </GetSet>
  </Session>
<eoapi:Request>
"@

(FYI the Qbject tag above should be Object but WordPress won’t allow that particular tag.)

We invoke the WebClient UploadString method and supply the 2 parameters, URL and Payload . The result is returned and stored in a variable $result: as follows:

$result = ([xml]$webclient.UploadString($Url, $soapMessage))

The reply comes back with a header then a root Reply node with child nodes for connection error code and message. If there is a connection error you will receive error information here. If the connection is successful the response will include a Session Root node. If the Selection parameter is successful (in our case there isn’t one so it was successful) then you will receive a response as follows:

<Session>
  <ErrorMessage />0
  <Reply group="TheDealershipCode" object="TheDataObjectName" type="GetSet">
    <TheDataObjectName>blah blah blah</TheDataObjectName>
    <TheDataObjectName>blah2 blah2 blah2</TheDataObjectName>
 <ErrorMessage />0
 </Reply>
</Session>

If the Selection parameter generates an error you will see error information in the ErrorCode and ErrorMessage nodes in the first Reply node.

In my initial evaluation I didn’t need any data other than the TheDataObjectName nodes. The $result variable is typed as XmlDocument so we can use the dot method of extracting the inner Xml string. I created a new variable ($xmldata) by using the get_InnerXml() method to pull the Xml as a string from the Session Node (exclusive of the Session tags) as follows:

$xmldata = [xml]$result.reply.session.get_InnerXML()

Another way to say this (probably in my mind only) is that we drilled down to the session level (node) and grabbed the XML as a string. We then pipe it out:

| out-string | %{$_.substring($_.IndexOf('</pre>
and we then create a substring of the Session InnerXML starting with the&nbsp;Reply tag which happens to have an attribute for group which does not exist in the Reply tag we don't want.

At this point $xmldata is a string which we strongly typed as [xml].

The string is checked for well-formedness when typed so we know we have good XmlDocument object going forward.

If we take a look at the InnerXML for this XmlDocument object it will look like this:


<Reply group="TheDealershipCode" object="TheDataObjectName" type="GetSet">
  <TheDataObjectName>blah blah blah</TheDataObjectName>
  <TheDataObjectName>blah2 blah2 blah2</TheDataObjectName>
</Reply>

I then use dot notation to select the TheDataObjectName nodes and pipe them to the Export-Csv commandlet and save it to a file on my desktop with no type information:

$xmldata.reply.ServiceSalesClosed  |  Export-Csv "C:\Users\John Donnelly\Desktop\Output.csv" -NoTypeInformation

I then open the file in Excel to take a look.

If anyone has better suggestions on the script or comments please share.

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.