Recently, I was telling a group of City Managers about a job description I wrote several years ago. An attendee asked to receive a copy, which I could not find, so here is my recollection. I hope to someday get all of my articles and essays moved to this blog site. Perhaps I can enlist a granddaughter to help in the future.
The job title, updated to reflect the times, came from me wanting to play off the Input/Output (I/O) computing term. However, the position is much more than IT even though I’d give a full 50% of the necessary skill set to computing sector.
Where do I suggest this position be placed? The Budget Office would be my first choice. This is where data should be directly married to management intellect. I believe the I/O could also be within the City Manager’s Office (CMO), probably reporting to an Assistant City Manager (ACM). Many ACMs reach the CMO via the Budget Office these days. And most City Managers, if honest, would say the ACM position is the best job in the House.
The I/O must be able to speak the language of management and to think like a manager. They must be politically savvy yet not be political. They should also be able to think like a crook.
The I/O must be able to transform operating data into management data. I’ve witnessed software systems being purchased over the last four decades on the basis, hope and promise of management data, analyses and reports. In fact, many if not most software systems produce operating data that in turn generate slightly higher level operating data. But not managerial data.
Operating data include registers, ledgers and details ad infinitum. Management data includes summaries, exceptions, graphical representations and the ability to slice and dice that information in multiple ways. Implied in the transformation of operating data into management data is the need to gobble tons of data in a way that makes sense such that the data can form the basis to support a decision.
Free to Stay in the Research & Development mode
If there is tragedy in data analysis, it is due to a lack of freedom to get into and stay in the R&D mode. If you try to use process people with a full plate to advance into data analytics, then forget it. Process people are budget analysts or accountants or just about anybody dedicated (or trapped) by operating cycles that allow no breathing room to think, explore and develop a skill-set. Even Project people with a full annual schedule of duties are unlikely to be able to take on and advance/excel in data analysis. And there is nothing worse than stop-start situations where one cannot remember where they left off once they pick up an old analysis effort.
The Tools
Obviously the first tool to reach full potential as a data analyst is Excel. To go way beyond basics, the I/O must master some of the more powerful features of Excel that can lead to super models and analyses. The first thing that might come to mind are the use of Excel micros. However, there are many more tools that need to precede macros, and I could make an argument that you can make quantum leaps in number crunching without macros.
The single most powerful tool in Excel is the ability to build pivot tables. This blog is not capable of focusing on the details of Excel features, but the easiest and most powerful tool is the ability to build a pivot table so that a large amount of data can be analyzed with built-in drill-down features to go from high summaries to the lowest detail with ease.
Excel can also import data from a number of sources, such as SQL Server. SQL is how most databases are constructed and stored for everything from your accounting and utility billing systems to building permitting.
However, it isn’t long before the capacity of Excel is challenged even though you can have 1 million rows and 15,000 columns of data in Excel. SQL Server has virtually no capacity limits. Along with database management, there are power SQL query and analytical tools. Yes, now you are stepping into computer program language territory, but one should not fear in light of the results one can get from SQL. However, you cannot dabble in that the skill-set comes with practice, practice, practice. I understand that some business schools and accounting programs now require multiple courses in SQL. There is a reason that is so. One leaps into a broad and powerful world of data analysis with SQL.
The Data Warehouse
Watch the face of your IT Manager when they learn you want to tap into “their” SQL databases. They have a right to be concerned, but they should not be if they are proactive. A tell-tale sign of assistance and cooperation is when you want a data analyst to have access to accounting and other data systems and get a roadblock as opposed to a suggestion to use a data warehouse. In essence, you want to be able to play with the data yet not touch the live systems. For instance, if you wanted to take a deep dive into your A/P system or your Utility Billing system, a data warehouse would include a download of the live system to a separate location as of the night before. Or it could be refreshed upon demand throughout the day. What’s the problem if I am not touching the live data?
Examples
I want to look at the details of our p-card spending by person by vendor for the past five years. I want an aging of our water meters, including the number of meters with over 1 million gallons of water in our system. How many batteries have been replaced in our fleet by vehicle over the past three years? How much overtime is being used in the past 10 years as employees approach retirement and might be spiking their pay? How much salary lag have we accumulated in the system so far this year? Are our water loss and unaccounted for numbers getting better or worse?
I can think of hundreds more, and you could, too.
The Danger Ahead
Several things have been happening over the past decade or so. More elected officials work for organizations where management data is derived from operating data and being made available to them in their operating and executive capacities. They often are appalled at the questions they want answers to that are met with, “we don’t know” or “we don’t have access to that information.” WHAT??? We have $millions invested in information systems and can’t get these logical questions answered?
If you haven’t noticed, the news media have been adding in-house talent or have been contracting with third-parties to analyze massive amounts of public data. So, they ask for a download of your utility bills or your check registers (already online) and then produce an exception report that is placed back into your hands as a story you should already know.
And the local complainers and watchdogs, many retired with analytical skills, can have a field day doing as I have explained.
Conclusion
In this day of forensic audits, you cannot be caught with someone outside your organization telling you something about your business you should already know. But this is not simply a plea for a defensive posture I am talking about here. It’s a plea for professional management.
I often open up a presentation with a statement like “while you are listening to me for the next few minutes, somebody is stealing from you this very minute.”
What I do know is that when something blows up, nobody gets thanked for the lean and mean staffing that also may equate to a lack of oversight where it should be.
But here’s the good news. Take one good analyst and give him or her three things. The right tool set; the authorization to explore any and all systems other than the confidential police data; and the time to be left alone to explore and build queries that can be run to look for exceptions.
I predict the payoff will be big. It will be in the form of trends you otherwise didn’t see happening or heads up to explore yellow flags. It wouldn’t be long before the organization fully realized that everything from operating statistics to revenue performance to spending is under healthy scrutiny. LFM
Let's Talk About It, Lewis! 