Generating ADP (CDK Global) Accounting Schedules in Excel PowerPivot – Part I

Some background

Last year when I was evaluating the new PostgresQL data server (appropriately named “Managed Data Access” or MDA) for ADP, I asked them after at about the 6 month mark if they had a way to query the appropriate General Ledger tables such that we could analyze accounting schedules in Excel. In particular I was very intent on utilizing new Business Intelligence add-in, PowerPivot. Sadly, there was not a one among the dev team that was an accountant and they showed little enthusiasm. I challenged them to write a SQL query that could bring into a PowerPivot data model what I needed to review 28 dealership’s accounting schedules (to make sure the books are clean you see) in one day.

I am a CPA by education (UCLA Accounting), experience (practicing California Automotive CPA for years) and training (keeping up with technology). I knew early on that to be effective in financial management one would need to be able to analyze data, accounting and other operational data. To this end, I have always been an early adopter of technology that would help me do so. I bought my first PC early on with a copy of Lotus 1-2-3 and never looked back.

Throughout those years the PC and spreadsheet software was limited in its usefulness by the availability of data connections in which to get data into the machine to analyze. When I was starting out there were several EDP (Electronic Data Processing) systems in the marketplace to help auto dealers keep track of things. Even though I have worked with a number of different systems, the ones that seemed to dominate always was ADP and Reynolds and Reynolds (R&R). Luckily both of these vendors had built into their systems a feature to extract data. It was slow and it wasn’t pretty. It wasn’t relational. R&R called it “Query Builder” and ADP called it “RPX” (a la RePort Generator EXtract maybe). They both used the Kermit file transfer protocol running in your Telnet session. You see, ADP and R&R both chose the PICK operating system to develop their EDP system. I don’t know the history of these two companies but I smell a partner fall-out and a competitive firm starting up with the renegade partners.

PICK was awesome but it seems a bit like list processing and it did not have a relational database built in. Apparently the business logic and the data logic was all combined together. You could pull information out of this list or that list and work with them locally in Excel but you couldn’t join lists. The query language (ENGLISH) had no functions except the standard non-logical operators. Why was PICK awesome? Because list items could be functions (pointing to other list items) or stored procedures and that was all powerful before SQL Server 1.0 came on the scene in 1989.

I had the pleasure of working with a large auto dealership group that started out with Saturn dealerships in the early 90s and, as a result, was using the EDS dealership system. The EDS system utilized the IBM iSeries AS400 with a DB2 relational database and I was very happy. I could write SQL statements all day long with extensive joins and functions and pull data using ODBC connections to Excel, Tableau, SharePoint and a host of other solutions. ADP bought EDS and quickly moved to convert its dealership base to Drive.

The sad part of this whole story is that it was only last year when there was a viable, relational database in either Reynolds and Reynolds or ADP. [I don’t mention UCS here because even though they had a relational database (DB2), I believe connectivity was blocked].

So the winner is ADP now CDK Global…..

As a condition of conversion from EDS to ADP was that ADP provide the DAP (Data Access Program) to us as many of you also did. Although I didn’t, many of you spun up your own data servers and put together a relational database using ADP‘s data objects; figuring out the relationships etc. I applaud all of you who went that route because you were able to give your dealerships real Business Intelligence (BI) utilizing such tools as Microsoft’s SQL Server Analysis and Reporting Services while the rest of us waited. At the time I worked for a group that didn’t see the capabilities and/or benefits of BI so it was never a priority. The SharePoint (Excel Services) reporting system I implemented and utilized to keep my managers informed ceased to be maintained after I moved on.

At this point, because of the recent addition of the PostgresQL data server to the ADP (CDK Global) system, I will spend my time talking about CDK Global to any dealership that is interested in besting its competitors. My esteem for ADP arises not only from the new data server but the data model itself is incredible. Obviously it was developed over the years with care and completeness by professionals. It is a pleasure to work with such a database and now that it is relational, I am ready to develop exciting data applications.

In all fairness to R&R, I don’t know if they have anything new in this area but I know Bob Brockman’s attitude toward a dealer’s data and it doesn’t seem compatible with open connectivity. Until someone tells me different, I will cease to follow R&R, UCS or any other system while I pursue developing BI solutions for dealers with ADP. I think ADP is moving in the right direction like no other. Perhaps DealerTrack is also on the right path as they are IBM based but, in spite of their unique operating system, ADP is the front runner. It’s kind of like aligning with Microsoft if you want to build powerful business applications. In this blog and in others you will hear me talk about ADP, PostgresQL and Microsoft products for the most part with the occasional Android and iOS mobile device development thrown in because they are everywhere.

One more thing. I write these how-to type posts so the community can take the work I’ve done and build on it. If you read back through my old posts, I blogged about pulling data from DAP using PowerShell mostly and I didn’t do it right or perfect or even efficiently because I am not a PowerShell expert. But it was enough to get a lot of you started quickly and easily pulling data from DAP and working with it in Excel. Some of you are using Out-SQL now and using PowerShell to MERGE in the data differentials I would imagine.

In my blog posts I typically don’t work through the development completely unless it comes from a project for a client (and the client didn’t mind me sharing it with the community). I’ve included in this post enough information to produce accounting schedules in PowerPivot accurately and efficiently. It should also help you to extend the schedule pivot to include data that isn’t available in an ADP printed schedule unless the data is in one of the schedule fields. That’s not to say that the PivotTable isn’t cumbersome at times and with a little extra time couldn’t be improved. Rather than lay it all out for you I expect you all to run with it and make it better and blog about it on your own blog or put the improvements in the comments below.

Schedule related views in the PostgresQL database

I knew that being able to create schedules in PowerPivot would be possible and would depend on my being able to decipher the following to get a pivot table that balances to the GL, namely:

  • What views would be needed
  • What the relationships between the views are and,
  • How can we pivot the results by date, month, account numbers and schedule numbers

The views involved in the process are:

  • glschedule_v
  • glschedulesetup_v
  • glschedulexref_v
  • glschedulexrefbalfwd_v
  • glschedulebalfwddetail_v
  • gljedetail_v

glschedule_v view

The glschedule_v view gives us all of the control numbers on all scheduled accounts by schedule number. If the GL account appears on more than one schedule, the control number will appear as many times. Here’s the query I ran to inspect this table and the first few lines of output:

glschedule
glschedule_v view (click to enlarge)

Most of the columns in the above query output are straightforward. However, the interesting column here is the currentmonth column. After perusing the file and running a few different queries it seems to me that the currentmonth column gives us all the control numbers for the schedule that existed for the currentmonth whether that month was in fact the previous month or the actual current month. For example, running the above query today (11-30-14) with glschedule_v.currentmonth = “11-01-14” gives me 909 rows of control numbers for the schedule numbers where the “23100” account appears. If I run the same query again with glschedule_v.currentmonth = “10-01-14”, it gives me 828 rows of control numbers for the same schedule numbers. The difference being, it seems to me, that in moving from October to November the net number of control numbers on the schedule dropped due to more control numbers becoming a zero balance than were newly added to the account.

glschedulesetup_v view

The glschedulesetup_v view contains a row for each schedule number setup for the company. This table helps us to restrict the number of times account number detail appears to once even though the account may appear on several different schedule numbers (side-by-side and alone). Here’s the query I ran to inspect this table and the first few lines of output:

glschedulesetup_v view (click to enlarge)
glschedulesetup_v view (click to enlarge)

Off to the right we can see the schedulenumber column which is unique. The interesting column is the account2 column and we can see by the query up top that I selected for records where account2 was null. This means that I only want single account schedules not side-by-side schedules (such as with inventory and flooring together on a schedule). It is possible to add up to 7 accounts on a schedule so there are fields through account7.  For our purposes though, our query works because we reject any schedules with a value in account2 and that eliminates anything other than single account schedules. We are making a large assumption here though; that the dealership has single account schedules for all scheduled accounts. If not, we won’t want to JOIN the glschedulesetup_v view at all. In that case, we will deal with duplications with our slicers in the resulting pivot table.

glschedulexref_v view

The glschedulexref_v view is the one that tells us where in the gljedetail_v view to get the records to populate the schedule. This is the linking table that tells what General Ledger entries still need to be reported on the schedule (i.e. ones that have not zeroed out by control numbers). Here’s the query I ran to inspect this table and the first few lines of output:

glschedxref_v view (click to enlarge)
glschedxref_v view (click to enlarge)

We can see from the output that the xref part of this view are the columns where the names start with dtl. These fields give us information with which to JOIN this cross reference view (glschedulexref_v) with the gljedetail_v view. The columns with names that don’t begin with dtl are column names that would be used to JOIN this view with glschedule_v view. The view is aptly named “xref” because it simply cross references schedule meta-data in the glschedule_v view with schedule detail found in the gljedetail_v view.

So, the goal is to JOIN the gljedetail_v view to the glschedulexref_v view via the dtl columns and the non-dtl columns to the glschedule_v view. First I will present the SQL query which is as follows:

SELECT a.cora_acct_code, a.hostitemid, a.accountnumber, g.accountdescription, a.companyid, c.journalid, c.refer, a.control, c.accountingdate, c.controltype, c.control2, c.controltype2, c.detaildescription, c.postingamount, c.postingsequence, c.postingtime, a.currentmonth, a.dateofoldestscheditem, a.schedulenumber, b.sequenceno
FROM ed.glschedule_v a
INNER JOIN ed.glschedulesetup_v s ON (a.cora_acct_code = s.cora_acct_code and a.schedulenumber::text = s.schedulenumber::text and a.accountnumber::text = s.account1::text)
INNER JOIN ed.glschedulexref_v b ON (a.cora_acct_code = b.cora_acct_code AND a.schedulenumber::text = b.schedulenumber::text AND a.accountnumber::text = b.accountnumber::text AND a.control::text = b.control::text AND a.currentmonth = b.currentmonth)
INNER JOIN ed.gljedetail_v c ON (b.dtlcompanyid = c.companyid AND b.dtlaccountingdate = c.accountingdate AND b.accountnumber = c.accountnumber AND b.dtljournalid = c.journalid AND b.dtlpostingsequence = c.postingsequence AND b.dtlpostingtime = c.postingtime AND b.dtlrefer = c.refer AND b.control = c.control)
INNER JOIN ed.glcoa_v g ON (a.cora_acct_code = g.cora_acct_code AND a.accountnumber = g.accountnumber)
WHERE a.currentmonth={ts '2014-11-01 12:00:00 AM'} AND a.cora_acct_code 'XXX-A' AND s.account2 IS NULL

The SELECT statement needs to contain all the possible columns I might need in my eventual PowerPivot data model and, my primary view to be queried is glschedule_v which I aliased as “a”. In order to get the desired result set we need to setup the following JOINs with the glschedule_v view:

  • INNER JOIN with the glschedulesetup_v (alias “s”) view so we can select only single account schedules and so we can also SELECT for the s.schedulename column so we will have that in our result set and in the PowerPivot data model.
  • INNER JOIN with glschedulexref_v (alias “b”) view ON glschedulesetup_v (alias “s”) view.
  • INNER JOIN with gljedetail_v (alias “c”) ON glschedulexref_v (alias “b”) view.
  • INNER JOIN with the glcoa_v (no alias) view (the chart of accounts view) so we get an account description to include in our results and in our PowerPivot data model.

Finally, in my WHERE clause I place the following conditions:

  • I require that a.currentmonth equals November AND,
  • I require that the dealership code is a.cora_acct_code AND,
  • I require that the schedule detail returned is only for single account schedules (i.e., no duplication of account number detail allowed)

Next time I will show the results in a pivot table and show you how to deal with “Balance Forward” schedules.

I hope you can see that this type of data extraction would not be possible with DAP and that MDA is really what dealerships need to gain incredible efficiencies going forward. Knowledge is power and efficiency.

Stay tuned.

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.