I used to work for a company where every app we gave them led to the question: Can we export this data to Excel?

After a while, I decided I had to know why they were obsessed with Excel exports for everything. It turned out that a lot of departments had one person who was an expert in Excel and could write a useful data-analysis app in no time. These apps would spread around the department like wildfire and we, the techies, had no idea they even existed.

Why didn’t they come to us first? Because there was a reputation that the technical team had far too much to do and, if they did ask for it, they might (if they were lucky) get it queued up six months later.

That wasn’t an unfair accusation and they never asked us to support their Excel apps, so no one really thought it was a problem. When these Excel developers left, they always managed to find someone else to pick it up.

You could argue that it meant we were prioritising incorrectly, that important work wasn’t getting done. But I would argue that it freed up the more-highly-paid developers to do more difficult work. What can it hurt?

Now I would forbid software that updates the database being written outside of the development team. And I would refuse to support apps written outside of the development team. But I wouldn’t try to forbid all software from being written by the business itself, and I would happily write data-exports to empower them to do so (as long as that doesn’t expose data that they shouldn’t see, obviously).

I think people are missing the general point here:

If you don’t like all the custom development that’s going on,
forbidding it is solving the wrong problem – you should instead be
asking why they’re going around IT, not just telling them it’s not
allowed. Remember that you (IT) exist to help them do their job
better, and that people don’t use software because it’s cool or neat
or new, they use it because it solves a problem they have.

Why are these apps being created in the first place?

In all the cases I’ve seen, there’s a common reason:

Business groups prioritize their own needs higher than those same needs are prioritized in the context of the whole company

Marketing is only responsible for marketing, so initiatives that benefit their goals seem critical to them, while being considered fluff to other groups, and tend to be prioritized lower when it comes to limited resources like IT. Prioritization only comes into play when they want to use a shared resource – if they keep the project entirely inside their own department, then only the department head has to care about the budget and timeline.

There’s no reason I’d forbid this sort of development, within reason – it eases up constraints on shared resources (mainly IT), and allows each group to empower themselves to solve their own problems (as people versed in advanced Excel are pretty common, since this is a common problem, most departments have at least one).

However, you can’t be expected to solve any problems that arise from these applications, or support them after the original developer leaves the company. As another posted mentions, this doesn’t stop the big boss from demanding that you support it, but if you keep a feel out for the kinds of custom applications or processes out there, you can get a feel for when something becomes critical and you might need to get involved to bring it “in-house.” Also, if something is connecting to and modifying systems that are under IT control, then IT should be involved, if only to ensure the security and integrity of their central systems – however, if it’s something confined to a user’s desktop, why feel the need to forbid it?

But here’s something to remember: Every custom application that’s been developed outside of IT corresponds to a need that’s not being met by IT. There may be a good reason they’re not met – not a priority in the company, very specialized problem, not as good as other options, custom language your IT people don’t know, etc – and the lack of IT involvement may be legitimate, but these solutions were created because some department had a need that IT couldn’t (or wouldn’t) satisfy.

Try to help them solve their problems, and if you don’t have the time or resources, let them solve them on their own. Mandating some language that has a steep learning curve, with the sole purpose of keeping people out of your business, only serves to enhance the elitist attitude most business users perceive IT to have, and in the end, that kind of elite attitude only leads to more of the same problem, as users are afraid to approach IT and are convinced that IT doesn’t understand their needs or wants. Open the relationship – understanding what they need is the only way to keep them from going around you.

One should also consider the case of the companies in which IT department contains incompetent people, while the hidden app would be created by a skillful developer who has a non-developer job within the company. In my experience, those cases are extremely frequent.

Imagine that you have a double profile of a software developer and an accountant. You’re hired as an accountant because this was an opportunity for you to get a well-paid job. You quickly see that your colleagues (and now you) spend hours doing repetitive things which may be done in a few seconds by a program.

You spend a few evenings to write the app which will do all the work. You show it on your personal laptop to your colleagues, and they find it very useful. You want to install it to the company PCs, but you should have the agreement of IT department. You ask for it, but they reject it because they wouldn’t support your application.

Doesn’t it sound stupid?

Aside this particular case, the issue with the support is not very different from one many companies encounter with all  the software, even one written within IT department: if IT department doesn’t enforce best practices, the code will be badly/not documented, written by inexperienced people who don’t care about maintenance and who have left years ago.

To conclude, the main question is the profitability. If you, the IT department, are asked to maintain an app developed by a clerk who don’t understand the most basic rules of software development, it doesn’t matter how enjoyable is this task, you still have to do it if it brings a lot of money to the company. Or you rewrite it from scratch if it’s the cheapest way to get things done.

You cannot fully control it…

I’d say you can never FULLY control it, as employees will always have means to produce rogue code and spread it by alternative means. So there’s not much use obsessing too much over it, once you’ve drafted and enforced a few basic rules and processes, and set up a few tools.

The idea is for you to make it as attractive as possible for people to respect these rules and use these tools, rather than to make it so impossible to do new things that they don’t produce anything.

But you can create a code-friendly environment

Many companies, often very large, do this. A good example being Google, for which representatives have said they use a single SCM for the whole company, for everyone to be able to monitor and look at others code.

I’d recommend you do the following:

• give public access to some areas of your SCM,
• make it easy to request access to continuous integration and continuous inspection servers,
• encourage people to create build jobs for their tools.

The problem is then the proliferation of technologies. Obviously, some will prefer to use X over Y and that’s when it gets harder to have them fit within your architecture. However, it’s not impossible, and if they want their code to get maintained they’ll probably got the extra mile if, well, it’s just one mile.

You could also take a more arbitrary stance and decide that only language L and Stack S are allowed, but then you’ll get some rogue stuff here and there, so I’d recommend to broaden it up a bit. Some CI systems will do wonders with a few plugins, if your employees are willing to write a bit of glue-code or some config scripts to get them to fit in.

Give teams some liberty

It’s important to give teams some liberty to go on a whim and start some small new projects with experimental things. It keeps them on their toes, and you as well as it forces you to consider these technologies rather to remain stuck in a stack forever until it causes problems for you.

So make sure they have the ability to install their own systems for testing their pet projects. But, make sure to have them get into the habit of talking to IT about it.

Talk to IT, get them involved

It’s much better if your employees develop the reflex of shooting an email request to IT and ask them if they can set up something for them and accommodate. They’ll get turn down most of the time, but at least there’s some notion of control and of who should be in charge and gives IT some visibility on the demands from different teams.

Once projects pick up a more critical mass, you can request again and they’ll reconsider. Communication is key, and you need to have your teams of developers, consultants, IT support staff or anyone dealing with code to work together. None of them want stray programs, so it’s in everybody’s best interest. It’s much easier to enforce rules if they’re backing them up themselves.

You cannot stop these “hidden” applications because people make them to solve real-world business problems. All you can do is help them do it the “right” way. And by “right” I mean making it so that the apps can be maintained after the person starting them has left. I recommend using the language suggested in Up or Out: I need you to document this process in detail so that any yahoo can understand it a year from now after you’ve left. Help with setting up version control (and showing them how to use it), a wiki (for keeping informal notes on how work actually gets done) and a simple bug tracking system. If you want to make things really slick, set up continuous integration on a spare server (if you have one).

You’ll see the huge desire for Excel integration (or at least import/export) because all business schools now teach Excel and it is a major tool used in many business courses.

Sarbanes-Oxley and similar legislation outside of the US, combined with privacy laws and internally self-imposed privacy and security process and policies are the “hammer” that can and often are used to clamp down on the shadow-IT phenomenon.

As soon as customer or employee personally identifiable information (or any data you don’t want to get out) starts circulating in these spreadsheets, you have an accident waiting to happen.

Similarly, as soon as one of these skunkworks IT projects takes that Excel spreadsheet and uses it as the data behind an externally-facing web app up that is hacked, you will have your CIO and CEO storming into the office of whoever built that app in a weekend coming to explain the consequences.

And then there is the issue that when you look at these efforts multiplied over the hundreds (or thousands) of departments in a Fortune 500 enterprise, you soon find that your enterprise has over 100 customer “master” databases. Your customers start to complain that they updated their contact information in one place but it is still out of date in 10 others, or that you don’t even know how much business you do with your large partners because the information is spread across 10 shadow IT databases.

All of this gives rise to onerous compliance and audit processes that are no fun for anyone but are the hard facts of life of IT in an enterprise environment.

A good strategy is to look across all of the departments that are doing this and make a case to move their investment in shadow IT into IT proper, making the argument that IT can achieve an economy of scale and do this work more efficiently than the current ad-hoc distributed skunkworks model. This can be a hard sell in an environment where IT budget constraints and delivery velocity gave rise to the skunkworks in the first place, but combined with the audit/fiduciary risks can make a good one-two punch.

The decision to write a new application is often based on a cost benefit analysis of the request; as well as the overall value to the business. All while taking in to consideration many other drivers such as available IT resources, scope of the request, business goals and direction just to name a few. Often times a specific departments request is denied because the department manager/director has failed to show a reasonable ROI or simply doesn’t follow established process.

Regardless of the reason the ‘IT Department’ is often the scapegoat, even if the decision was outside of their control. So even though denial of the request may not reflect poorly on the IT department the perception is often entirely different.

Despite this you’ll find rogue applications in nearly every business organization in the world. Some well written and others that well, contain code that should have never seen the light of day.

While we should do everything we reasonably can to meet our internal customers needs there are times that we simply can not. When that happens they will look elsewhere to address their problem.

If the IT group is actively involved in the project then we can demand adherence to our standards, help the consultant to follow internal coding guidelines, and understand the applications interactions with and demands upon our systems (database, network, firewall, …). Without that involvement we’ll be caught short and spend a great deal of time trying to figure out why our production systems are not meeting SLA.

Whether the IT department condones and supports them or not they can, and will, have a direct impact in terms of support, OLA and SLA commitments that any IT department is measured by.

They are forbidden in our company for these reasons:

• Password protected Excel Macros where the only person who knows the password has left the company,
• Being held responsible for incorrect reports written by inexperienced people b’because it’s IT’
• Being asked to modify a report that we’ve never seen or heard of before.

I understand it can be frustrating for users when IT are busy, and they may be inclined to ‘just do it themselves’. But IT can’t be held responsible for things it has does not know even exists, and if no one is responsible for overall IT, then there are great problems ahead.

If there is a problem here it is with the IT department.

There is nothing wrong with allowing people with specialist business/domain knowledge manipulating and processing there own data.

The IT department needs to acknowledge this and support it. By providing re-usable interfaces, delivering data in convenient formats like EXCEL or Access DBs, and providing flexible tooling (COGNOS, Jasper Reports etc.).

The IT department also needs to re-think its priorities — it is there to serve the business, not to implement the latest methodology, or, install the sexiest hardware.

A frustration many companies, or departments in companies, have is that their IT departments get in the way and make it difficult for them to get their work done or do new things. This leads departments, that feel as if they are being held back by IT policies, to attempt to solve their own problems. Communication is key. If departments are working around IT, what you really have is an IT problem. IT can’t afford to be seen as the enemy. Companies, and especially IT departments, need to realize that they need to work together instead of against each other. If departments communicate with their IT staff (especially the ones who should have oversight) and tell them their needs and how they are working to solve their own problems, IT at least will have the option of helping to solve problems instead of finding out after the fact when a crisis comes to pass. Keep IT in the loop.

There almost aways is a valid need for these special tools, be they applications or spreadsheets.  An IT department has two choices.  They can be enablers or disablers.  In my experience, the disablers lose because they get in the way of valid business needs and become a common enemy. Enablers on the other hand genuinely help a business.

This does not mean that department funded development should be given free reign.  Some requirements should be enforced, such as the following:

• All code must be regularly committed to a version control system run by IT.  IT should freely create accounts and directories to make this possible.  IT may even want to provide some instruction.
• Anything involving PII (Personally Identifiable Information), authentication, authorization, cryptography, data protected by law, or data the business deems critical must involve and be approved by a consultant from IT.  IT/the consultant should provide assistance, libraries, etc to properly protect the business while enabling app development.
• Primary databases should be protected.  Depending on the database, read access should be relatively easy to get and write access harder.  IT may need to provide accounts, logging, or auditing.

Enabling has many benefits.

• IT learns more about meeting the needs of their clients.  This leads to better prioritization and sharing.
• IT is seen as a friend and a benefit, rather than a problem.
• Real business needs are met.
• Business data is adequately protected because IT was involved, preventing the need for back doors.
• Deparment tools are not lost due to turnover and can more easily be moved into IT, if needed.

I couldn’t help but identify with you. The problem you describe seems to be an universal one, spanning cultures, languages and continents.

The things that you can do:

• Restrict the creation of database accounts, asking for a supervisor’s approval. Forcing them to used a local machine as a database server or writing the apps to be stand-alone, greatly diminishes its usefulness.

• Make all interns of careers related to IT to be contracted through IT only.

• Restricting via OS policy the installation of software. Every software installation must be channeled through the IT help desk, requiring a supervisor’s approval. That way the installation of something like MS Access, PHP, Visual Basic, etc., will be more difficult to pass unnoticed.

• Issue a policy stating that every new development, in order to be given support, must be written in, say Java, C#, C++ , or any other language that would require a steep learning curve. That way you reduce the universe of people with “a certain knowledge of programming” to mess around.

• The requirements people must take a look at the “Excel solutions” around the company, because that is a reflect of the IT needs of the corporation.

• Implementing a data warehouse and a final-user friendly data mining and reporting tool. That way you reduce the need for custom made, intern written little apps.

But: not a single thing you do will outpower a phone call from a Big Chief, calling the IT Manager and asking him for you to support the app that an intern made.

One way that my company helps in these sort of situations is to not be language agnostic. If you want an app/program to even be considered it needs to be in a language of our choice (java). We may stretch the rules a bit for some JQuery or js but it would need to be a well composed application that served a critical need. Don’t come and say “I’ve got this PHP app that I need you to host for me” because you’ll probably just be handed a policy sheet.

It’s important to nip the things before they get too big, cause once they are you better have someone you can dedicate to learning it, or rewriting it. Because once that big wig upstairs decides he likes it your probably never going to get rid of it in my experience.

The arrogance of Geeks!

In many cases business users can use the tools to do things that the IT people don’t understand. This is not because they are inherently bad but because they know the business, how it works and how they would like it to work.

For example a software company developed an application to optimize (for cost) access to market data feeds. As an afterthought they provided an Excel plugin so users could access the latest share price via a spreadsheet. Fast forward one year and nearly every trader in the company I worked for had one or more incredibly complex spreadsheets to support their trading strategy. Every now and then they would have a problem with the macro’s and ask one of the IT guys for help, most refused (and they wonder why the business hates us!). I however had a go and while I could fix technical problems with various function parameters and circular references I can honestly say I did not have the slightest clue what the whole spreadsheet actually did. Not because they were badly put together or poorly programmed but because I did not have the knowledge or experience to appreciate the subtlety of what the traders were trying to achieve. Furthermore I would estimate a 5 man year plus IT project to replicate the functionality of one of these spreadsheets in a “proper” programming language as a standard IT project.