Start Collecting Your Dealership’s Data So You Can Understand & Improve Your Store’s Results – Reposted

This is a re-post of my post on ADP (CDK Global) Expertise today.

What I am about to explain to you will change your life if you are at all frustrated with your store’s performance?

It may sound strange but usually performance issues arise from a lack of feedback to the workgroups contained within your operations. I use the term workgroups because there are numerous groups of employees in your dealership that need feedback to perform optimally. These groups of employees (workgroups) include:

  • An Individual
  • A Team – (usually front end sales teams but this can include techs and other employees).
  • A Department
  • A Store
  • Same stores – a workgroup important for comparing Year over Year (YoY) results.
  • A Division – ( a group of stores by franchise or geographical location)
  • A Region – ( usually geographical)
  • A Country (if you have stores in different countries)

How would you like to improve the profitability of all your store or stores right now?

We will need to use your data from your DMS (Dealership Management System) extracted properly for analysis and presented in a way that ranks your workgroups. The following SharePoint Online interactive report has a tab that ranks the performance of two workgroups; stores and individual F&I Managers:

capture

The premier business data presentation platform, SharePoint Online, is free with the Enterprise Office 365 subscriptions. With SharePoint Online you can control who is able to see each individual report based on their logon. Those who don’t have permission will not even see the report link on the site.

Feedback is extremely important if you want your employees to improve. If your employees don’t get feedback they will either:

  • Get the data on their own or ask for it or,
  • Just maintain the status quo (under-performing) or,
  • In all cases, leave your employ.

The sad part is, the good ones leave (because they are not recognized) along with the not-so-good (because they are not motivated).

We need to take advantage of a person’s or workgroup’s natural competitive instincts!

Critical to reaping the performance improvement that feedback can create is ranking the workgroups. Feedback can also be fine using objectives but that doesn’t get the competitive juices flowing. We have to rank them!

In my experience, when you rank workgroups they all rise, even the bottom individual or workgroup. That’s because everyone wants (on some level) to do good.

There are three steps to making this happen:

  1. Automate the Collection of the Data.
  2. Train your CFO and Controllers on how to use the latest tools to analyze the data efficiently
  3. Train your CFO and Controllers on how to create reports with the latest tools and publish them to SharePoint and Office365.

I have a lot of experience with CDK, InfinitiNet and DealerTrack and so that is what I will stick with here. You can access your data on these platforms but I am not so sure about Reynolds anymore.

Step 1:

To automate the collection of your data all you really need is an ODBC connection, some knowledge of the DMS data model and a subscription to Microsoft Azure. You would then ask someone like me to write a PowerShell script to run on your server to extract the data from the DMS at least daily and move it to a SQL Azure database (or on-premise).

You can skip this step if you are on CDK and you have signed up for their wonderful MDA (Managed Data Access) or DDA (Dealer Data Access) product. Those products serve up daily refreshes of data on a PostgreSQL database on your CDK server already.

Step 2:

CFOs and Controllers and even advanced Business Managers are the ones to analyze the data and create the reports. They need training and I will be setting up some courses here in Las Vegas to train them. I don’t advocate having IT people do this because they just don’t have the knowledge of how these numbers are generated; where they come from and how they inter-relate. So they need training in:

  • The DMS data model i.e., where the data resides and what transactions put it there.
  • How to use Excel 2013 and 2016 with PowerPivot and PowerQuery and other tools to create interactive reports so they refresh automatically when the underlying data is updated. You don’t want a set of gorgeous reports that require your people to manually update them. We will let SharePoint Online handle that.

Step 3:

Because this data is confidential and because we need to make sure it is correct this step also falls on the responsibility of the financial side of the business. I recommend this be handled by the CFO, Controller and/or Business Manager. In larger dealer groups the CFO or Controller can hire someone with this expertise but they need to understand car dealership transactions and accounting. There are two kinds of reporting in the dealership ( I teach all the competencies needed for both):

  1. Permanent periodic reports. These include rankings, Over-Age Inventory, Heat sheets, A/R Aging, CIT, Incentives, Upcoming Lease Terminations Reporting etc. These reports need to be built once and automated by reporting in their own SharePoint page with automatic refresh with proper permissions.
  2. Ad-Hoc reports. These are reports that you need to create one time or on-the-fly to do analytics prior to making a major business decision. In either case properly trained financial people need to prepare them.

I will be sending out an email announcing a seminar in the spring to lay this out for Dealers, CFOs and Controllers in detail. If you want to be on the list you can email me at jdonnelly at jdonnelly dot com or give me a call at 702.498.8777

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. I can get you up and running with a 15 minute phone call.

The beauty of PowerShell is that I can write you a cmdlet to do whatever it is you want done. We then import those into your PowerShell ISE and then you can run it with different parameters as often as you want. The output you get back can be opened directly in Excel (if that is how you want it) by PowerShell. I can even write the cmdlet so that it emails the information around or in many other ways.

The reason I like PowerShell is because running the report is easy for those who don’t know PowerShell and yet I can make PowerShell do pretty much anything you need.

I will write your custom cmdlet quickly and for a fixed fee because, not only am I adept at writing PowerShell cmdlets and modules, but I know DMS data models very well.

I would estimate that most extraction cmdlets will be quoted in the $150 to $350 range but if you want a cmdlet to not only extract the data but send email reports or marketing it could (depending on how elaborate) cost upwards of $1,000 – $2,000.

Monthly support for the first 3 cmdlets is free and add $100 a month for each batch of 3 thereafter.

Email me now for a quote at jdonnelly at jdonnelly.com

 

Using MS Query in Excel to Access ADP’s new MDC PostgreSQL Data Server

Here at Automotive Systems Training we have been reviewing the new MDC (Managed Download Center) product that ADP is offering. It is a PostgreSQL database that mirrors most of the DAP (Data Access Program) Standard Business Data Objects. It’s nice to have a SQL Data Server, however the PostgreSQL database is currently only updated once every 24 hours at night whereas my VBA code and Powershell scripts hitting the DAP data objects directly give me real time data right now.

But in the interest of fairness I am using the very ancient MS Query application (circa 1997) to pull data into my Excel 2010 with PowerPivot CFO Dashboard to let me monitor all aspects of all 28 dealerships every day. And it works. Cumbersome at times, but it works.

Question for those out there that pull data into Excel; what are you using? ADO.Net in VBA code, MS Query. I have been using all-of-the-above plus calling Powershell scripts from VBA. I would like to see what folks are finding to be most robust.

Anyway yesterday I was building a PowerPivot Pivot Table to view daily RO Averages and I had a fit trying to pull Closed ROs that tied to the financial statement counts. Finally I got it to work (within ~1%) by joining the gljedetail table to the servicesalesclosed table. It wasn’t as straight-forward as I had hoped. I am writing this to get it in my head and down on paper how to do these joins in MS Query. In Showcase it was easy as the GUI allowed for multiple outer joins and made it intuitively easy to set them up. In MS Query however, at first glance using the GUI, it tells you basically “ONLY ONE OUTER JOIN ALLOWED PER QUERY”.

However, one can modify the SQL statement directly and have multiple outer joins in MS Query. If you do this though, you can’t subsequently use the GUI to edit the query. You can however continue to edit the query in MS Query using the SQL button which brings up a text editor.

The final query that worked for me (below) selects all rows in gljedetail that have accountnumber 46000, 46100 & 46003 (CP Labor Sale accounts) along with companyid 17 (Anytown Motors) and an accounting date in Sept 2013. These conditions can be seen in the WHERE clause.

The FROM clause then is saying that if the refer (document number – LEFT OUTER JOIN #1) and the cora_acct_code (accountingaccount – LEFT OUTER JOIN #2) in those rows matches (in both ways) any row in the servicesalesclosed table, bring back the data from that row in the servicesalesclosed table and append it to the gljedetail row.

I have put superscripts in places in the query to explain as follows:

SELECT gljedetail_v.accountingdate, gljedetail_v.accountnumber, gljedetail_v.cora_acct_code, gljedetail_v.refer, servicesalesclosed_v.cora_acct_id, servicesalesclosed_v.accountingaccount, servicesalesclosed_v.actualhours, servicesalesclosed_v.actualhourscustomerpay, servicesalesclosed_v.actualhoursinternal, servicesalesclosed_v.actualhourswarranty, servicesalesclosed_v.apptdate, servicesalesclosed_v.apptflag, servicesalesclosed_v.appttime, servicesalesclosed_v.cashierno, servicesalesclosed_v.closedate,…etc…..,servicesalesclosed_v.year, servicesalesclosed_v.cora_acct_code FROM { oj1 ed.gljedetail_v gljedetail_v LEFT OUTER JOIN2 ed.servicesalesclosed_v servicesalesclosed_v ON3 (gljedetail_v.cora_acct_code = servicesalesclosed_v.accountingaccount and gljedetail_v.refer = servicesalesclosed_v.ronumber)4 }5 WHERE ((gljedetail_v.companyid=’17’) AND (gljedetail_v.accountnumber In (‘46000′,’46100′,’46003’)) AND (gljedetail_v.accountingdate>={ts ‘2013-09-01 00:00:00’} And gljedetail_v.accountingdate<={ts ‘2013-09-30 00:00:00’}))

  1. This little “oj” appears to be an indicator to MS Query to expect an outer join coming up.
  2. This LEFT OUTER JOIN statement tells MS Query to return all rows of gljedetail (the first table mentioned right after “oj”) that match the WHERE clause irrespective of the JOIN condition but to return joined rows from servicesalesclosed (the succeeding table mentioned) that satisfy the JOIN condition(s) that is, the condition(s) in the ON clause.
  3. The start of the ON clause mentioned above.
  4. These are the JOIN conditions. In this case there are two LEFT OUTER JOINS (which MS Query GUI doesn’t support). This query works though; you just can’t use the drag and drop etc. I would assume if you added another table to the FROM clause you could add another “And” in the JOIN condition statement of the ON clause.
  5. From curly brace to curly brace is going to be the entire FROM clause including all JOINs, INNER, OUTER, SELF and otherwise.

Any comments or suggestions please feel free. I hope this helps someone.

Converting to Outside Payroll

Got a call from a programmer who was helping a company convert a 5-store auto dealership company from Reynolds and Reynolds in-house payroll to an outside payroll service. The payroll company I had never heard of but they were very astute and specialized from what I could tell.

The impetus for the change seemed to be a time and attendance software vendor who appeared to use this payroll service company to to produce the payroll using data from his system. It could have been the other way around, the payroll company offered the Time and Attendance software as an important part of their service. I did not get a demo of the Time and Attendance system but the installer and I shared a remote PC connection and it seemed very comprehensive. Time and Attendance systems, if properly setup and managed (the critical part), saves dealerships money; serious money.

So the programmer who contacted me needed help extracting check history and YTD balance data from the Reynolds system. The programmer, Jim Barbour of Sequencing, Inc. was very professional and managed the project very well. Jim needed me to pull all check history as well as pay, tax and deduction year to date totals for the employees. I gave him a quote that made sense and I went to work. I was successful pulling what I thought was all the data and I forwarded a file for his review. Upon verification by the client it was determined that we did not have it all. In particular there were a few deduction items on a several employees that were unaccounted for. Having experience with Reynolds file structures, I planned for this possibility and I had built time in for the possibility that we might need to do extra digging for the odd item. I found the offending miscellaneous deduction codes and related year-to-date totals buried in a multi-valued field called “I/D”. I extracted that data which was tricky because I wanted my condition statement to filter for only those values I was looking for in the multi-valued field. It didn’t work that way. Depending on the field attributes one may have to grab it all and filter programmatically. All was well although the flat file output was rather ugly. Jim being a good sport was able to select for and import the few codes into his conversion.

The interesting thing about this conversion was that the client needed an export for tech hours flagged to go each payday to the service bureau (old term I know, I did payroll in the early 80’s on IBM 360’s for a service bureau subsequently purchased by ADP called Stat-Tab……I was the payroll manager!)  I wrote the export query and it worked out well however the concern on the client’s side was how to make sure the technician’s flags (time entries) are only paid once in cases where the flags are adjusted or if the RO is re-opened and closed again (as in possibly a warranty ticket). The answer was two-fold; one was to isolate flags by the Work Date field (in the query) rather than the RO date or any other date field. Work Date is the date the time entry is actually written, not the date the work is done or the RO was finished or closed. If we stick with the Work Date condition, during the pay cycle only those flags or adjustments done during the cycle get paid regardless of what goes on with the RO. In addition, the client had Body Shops and they wanted to pay flags (time) on ROs as the job was progressing regardless of whether or not the RO was closed in the pay cycle. Using Work Date allowed them to pay on work in progress as well.

The 2nd step in the process from the dealership’s side (I am an accountant, CPA actually but not current on my license) was to schedule the Work In Process – Labor (WIP) general ledger asset account (usually 247) by RO number. Many dealerships do this to control flagging irregularities and I believe the client had these schedules already in place. However, they became extremely important under the new outside payroll processing system. On the credit side of the of the WIP account will be the ROs total of cost of labor, i.e., all tech flags in total costed out on the RO when it was closed. On the debit side will be all the individual flags paid against that RO’s total costed labor. The RO control subtotal should always zero after closing the RO and the subsequent payroll is applied. The office, payroll clerk or office manager, must monitor the schedule often at the start of outside processing and every pay cycle thereafter for problems or irregularities. Techs are smart and some are inclined to “game” the system. Researching ROs with balances and resolving with either a back flag (a charge-back) or a correction to the RO cost and/or costing procedure is imperative so it doesn’t happen again.

All in all a workable solution for those who want to take time and attendance and payroll to the next step in these automobile dealership systems.

3rd Party Data Extraction with Reynolds and Reynolds and ERAccess

I had a call from a 3rd Party vendor to the automobile dealership industry who was pulling data from their client’s Reynolds DMS’ and all of a sudden couldn’t do it anymore. Not wanting to become colossal pains to their clients they asked me to help. These are nice folks not pulling a high volume of data but important data none-the-less. Definitely not candidates for RCI. I had to do a little research to get myself up to speed as I hadn’t done much R&R work lately. See my last post about a previous conversation with the RCI team.

Installing the Reynolds Software Manager and ERAccess and is the key. To do that someone needs to help you. Otherwise you will probably be remoting into an ERAccess PC on the customer’s network and working there. Once we have access unfortunately we have to use the R&R proprietary Query (7601) or Report Generator programs to pull the data just like before. I’ve yet to find a 3rd Party extraction tool for UniVerse although it probably wouldn’t be hard to wire up PowerShell using the UniOLEDB Data Provider. The problem is someone with access to the Unix root would need to get to the UniVerse Server Admin and setup an account to show files. That’s not going to happen unless someone with an R&R system wants to experiment with me.

Since ADP and R&R use the same database (Currently Rocket UniVerse, formerly IBM UniVerse, originally PICK) their “Certified” interface would be a .Net data provider (hopefully) or XML service. I have a data provider for ADP but it crashes on Win7 systems. At any rate, we were able to solve the vendor’s problems going forward and get them back in the game. The goal now is to streamline this process with Rocket Software’s assistance and in this regard I will keep you posted.

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….

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.