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.
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
APP: Service Related Info
ASROUT1: Some Type of System Table
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
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.
About John Donnelly
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:
- Microsoft SQL Server 2008
- AS400 (DB2) SQL
Data Presentation Experience:
- Excel Services
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!
About John Donnelly