41

When I applied to and interviewed for this role, the job spec and interview process promised I'll be building self-service dashboards in Qliksense and working on bringing insights to the business as well as doing some forecasting work.

What the job actually involves is downloading reports off of a web server which are Excel files, pasting the data from one Excel file to another, doing some pivots, and pasting them into another file. I do not have access to the source database used by the web server to load the reports.

There is a datamart available that is supposed to be some source data with business logic applied, but none of the data I need is in there. Because of this insane system most of people's time is spend doing copy and pasting because there's no way to automate anything. And I can't for the life of me convince higher ups that as a data analyst I need access to source data to be able to automate.

How do I go about this? My immediate management is aware of this problem, but they can't seem to get through to the higher ups either. Any ideas on how to resolve that issue?

Improve this question
3
  • 1
    Comments are not for extended discussion; this conversation has been moved to chat.
    – motosubatsu ♦
    Jan 26, 2022 at 9:01
  • From what you write and comments, it seems you do have access to raw data, but not to the raw data source (i.e. the "main" or "reference" storage). And that's what you want. Is this correct?
    – Pablo H
    Jan 27, 2022 at 14:33
  • @PabloH No, I don't have any access to raw data, only data that is in a datamart which has been filtered several times, and access to the web interface for some of the source data which has also been filtered. The problem with this system is that we're stuck interacting with the web service having to download everything as Excel files.
    – ectoplasm
    Jan 28, 2022 at 8:55

8 Answers 8

Reset to default
18

You could attempt to automate the process just for yourself. Write a Python program that downloads the spreadsheet files, reads them in from Excel to a Pandas dataframe, does the transformation (pivot table operations can be done in Pandas), and then writes them back out again. This doesn't solve your problem of not getting access to the raw data but it gives you something intellectually challenging to do. When you are finished, others in your group will want your software and you will be seen as helping make the department more efficient. Either that or you will be told to knock it off since no one else can program Python but you will be able to take your Pandas knowledge to a real Data Analyst position.

Improve this answer
6
  • 6
    I think this starts well, but beware of building a Heath Robinson (or Rube Goldberg for the US readers) contraption as a core business system. Fine for you, and might make your life bearable. Having to support such a monster for others to use is maybe even further away from where the OP wants to be than before. And no doubt the next generation of engineers could finally crack access to the raw data, making dependence on the OPs auto-downloader part of the problem.
    – Neil Slater
    Jan 25, 2022 at 7:27
  • 2
    This is based on a bank I worked at where someone in a department that handled loan finalization got tired of all the random paperwork and built a tracking system with Microsoft Access. It really sped up her work and soon her boss was trying to get the whole department to use it. Access couldn't handle it and I got called in from the IT department to make a proper distributed system when the woman who wrote it, and supported it, got promoted.
    – verisimilidude
    Jan 25, 2022 at 7:45
  • 5
    Anecdotally I have seen success at this endeavour lead to reliance without naturally evolving into the "proper" system that you witnessed. In fact I have seen this more than once with Excel solutions specifically. I think it is a risk - will OP manage to repeat your colleague's success, or will they end up with an albatros tied to their neck.
    – Neil Slater
    Jan 25, 2022 at 8:05
  • 1
    Just as a side note, if you don't know/ like Python, R should be suitable to automate this process as well.
    – quarague
    Jan 25, 2022 at 13:38
  • 7
    Don't do this separately for each process task. Instead, develop a wrapper that creates a shadow copy of the database, and run all your processes off the shadow. Make the structure of the shadow match the secret database exactly. Never add a separate extraction for a new process task; add the extraction to the existing wrapper and then use that. - This keeps the Goldberg-Robinson data access method separate from your main automation efforts, which may remain sane. If management ever grants your prayers and allows direct access to the data, then the wrapper can disappear.
    – A. I. Breveleri
    Jan 25, 2022 at 14:09
101

I'll offer a different view on this.

What you describe is an opportunity to streamline the process and ultimately save money. In almost all companies the person who "pays the bill" for what sounds like wasted time and effort will be receptive to alternatives to save money.

Your job is to QUANTIFY what it's costing the company to do this task now and estimate how much can be saved by doing it the way you recommend. So you should be able to come up with a statement like:

"By spending $X over the next N months to modernize this business process we can save $Y per year going forward."

The #1 rule here is "Present SOLUTIONS not problems!"

In cases where there is no interest in saving money, it's time to move on.

Improve this answer
13
  • 24
    In my experience, quantification will be a problem in these cases. The real problem with copy-and-paste is not that it takes a huge amount of time once in a routine, but that it requires disproportionate concentration to execute, and doesn't scale your labour like automated solutions do. The first intuition of management who don't understand this up front, is that such work is exactly what they pay servants for. It will be very difficult to credibly quantify the other work not done, since it hasn't been done yet, or even been imagined by the person burdened with the inefficient ways.
    – Steve
    Jan 24, 2022 at 15:30
  • 8
    @Steve I was going to say exactly this. There is a product manager that spends at the very least 3 days of her month copy pasting in dozens of Excel files for just one report. I think it's a waste of her time but the higher ups think that's what she's paid to do. I submitted a ticket to ask for data access to automate that report and was told it wasn't a priority.
    – ectoplasm
    Jan 24, 2022 at 15:41
  • 40
    @ectoplasm "I submitted a ticket to ask for data access to automate that report and was told it wasn't a priority." That is exactly jwh20's point: you're asking for time&resources to "fix" an issue that management doesn't view as a problem - when you should be showing management that $Y are being wasted every single month (wasted money = problem) and that a one-time $X investment could fix it once-and-for-all.
    – AmateurDotCounter
    Jan 24, 2022 at 21:50
  • 24
    @ectoplasm The less obvious cost here is human error. If you have concrete examples of errors caused by copy-pasting, you could demonstrate it's not just a waste of one person's time but of several people's time, or in worst cases how it negatively impacts the end product. And then quantify that.
    – AmiralPatate
    Jan 25, 2022 at 8:46
  • 12
    Obligatory XKCD: xkcd.com/1205
    – AnoE
    Jan 25, 2022 at 9:38
56

My immediate management is aware this is a problem, but they can't seem to get through to the higher ups either.

If your management is already aware of the problem, has genuinely tried to solve it and has failed, the honest answer is almost certainly that you can't solve it either. Sometimes companies are silly.

Improve this answer
2
  • 7
    I was afraid of this answer :(
    – ectoplasm
    Jan 24, 2022 at 15:37
  • 3
    @ectoplasm - look into the concept of the "Overton Window." Once you understand that, you have to either content yourself with producing the best outcome within the range of options you have, or find a different window.
    – Wesley Long
    Jan 25, 2022 at 0:00
15

Consider the idea that this company really doesn't want to depend on anything that an Excel power-user can't make changes to. It's really counter-intelligent, but there are places where the upper management is so fearful of losing specialized skills that they sabotage any advantages that using technology properly could give the business.

If you learn that this is the case, it's going to be hell to change.

Improve this answer
8
  • 9
    This is a good point, but I'd caution that management rarely know positively there is a more efficient alternative but sabotage it. Rather, they cannot imagine exactly how the alternative is more efficient because they lack the kind of competence necessary to appreciate it. So it is a one-way losing bet - either the alternative will fail and cause waste or disruption, or they'll have a more efficient system which exceeds their competence to oversee or control.
    – Steve
    Jan 24, 2022 at 15:41
  • 4
    @Steve I think you're hitting the nail on the head there with management not wanting a system that they cannot control or oversee. I'm told I can't have access because the data is sensitive, but I can download said data via the web service as an Excel file, so this reasoning is clearly rubbish.
    – ectoplasm
    Jan 24, 2022 at 15:44
  • 4
    @ectoplasm dumb q... is the web service accessible via an api? could a sufficiently motivated python/etc. programmer download these web reports automatically, do the pivots automatically?
    – Joe
    Jan 24, 2022 at 22:20
  • 1
    @Joe Exactly There are modules for this (example tutorial) and this not could be better (as in easier to scale) and potentially more sound (as in test-driven development/reproducible work) and some other reasons EuSpRIG aims to rise awareness.
    – Buttonwood
    Jan 25, 2022 at 0:18
  • 1
    @ectoplasm, if you are able to implement Joe's solution, not only propose this as a solution, but also state that having direct access to the data will save money by being much easier and less error prone than having to parse an Excel file for the same data. It will also save money (take less processing power/resources) to not have to build the Excel file to begin with, since it's just being immediately tore apart again. As another answer states, show management how to save money, since they likely don't understand the technical reasons why they are being inefficient with time and money.
    – computercarguy
    Jan 26, 2022 at 16:18
8

Sometimes in my job, I have to find workarounds to problems that cannot or will not be solved. The workarounds may be overly complicated, when compared with the way that things were supposed to work. But if the original problem is not going to be solved, then that's all I can do.

You've reached a dead end in that the powers that be have decided that you are not going to be given access to that database. So stop complaining and find solutions.

Pretty much anything can be automated if you try hard enough. If a person at a web browser can download a file, then a program pretending to be a web browser can do it too.

Excel has it's own built-in programming language (but I'm no expert in that). Or there is at least one open source software library that can read Excel spreadsheets. So you can read them and extract the data from within a program.

If all else fails, there's tools such as AutoIT. If you can to something by clicking a mouse or typing on the keyboard, then AutoIT can fake you doing it. That's easy if the task is the same every time, but considerably more challenging if you have to cope with things that change.

Improve this answer
2
  • 1
    Chapter 13, chapter 20 by Sweigart's «Automate the boring stuff», etc are interesting reads in this regard. Or his demo at PyBay2016 video, around 17:00 min:ss and later ...
    – Buttonwood
    Jan 25, 2022 at 0:25
  • What one person calls complaining the other person calls crowdsourcing solutions.
    – ectoplasm
    Jan 28, 2022 at 9:12
7

I agree with the answer by jwh20 that the key to convince upper management of something is always by appealing to their bottom line.

But before you make your business case, also try to become aware of any other factors which might prefer the current solution:

  • Are there compliance or security reasons why people should not have direct access to the production database?
  • Is there a reason why they want everything to go through Excel? Perhaps they are afraid of having to retrain people to use different tools or processes? Or perhaps it gives them some abilities you weren't aware of?
  • Perhaps there are internal political reasons why whoever maintains the webserver which does the Excel exports doesn't want anyone to circumvent them?

When you are not aware of these reasons and present good counter-arguments or workarounds to them in your business proposal, then it might get rejected even though it looks profitable on paper.

Improve this answer
2

And I can't for the life of me convince higher ups that as a data analyst I need access to source data to be able to automate. How do I go about this? My immediate management is aware of this problem, but they can't seem to get through to the higher ups either. Any ideas on how to resolve that issue?

You're a data analyst, not a systems engineer. It's not your job to convince management to take a particular course. Seems like an unstated issue is that you feel like you're wasting your time, but you need to remember that when you're at work, it's not your time. They've bought your time and they can spend it how they like. And frankly, if they did automate, that would decrease their labor needs, so the lack of automation may very well be the reason you have your job in the first place.

As @Ruadhan2300 alludes to in a comment, there is in some sense compensation other than money in that employment that uses your skills gives you more experience and increases your value in the employment marketplace. So there is a valid concern that your "compensation", so to speak, is not as high as you were expecting it to be. But that still leaves your explicit question of "How do I change this?" as not the right question, the question is "How do I deal with not getting as much out of the job as I expected?". You can look for another job, you can try to negotiate for a higher salary (although, as I said before, the lack of efficiency probably means that their payroll are already strained), or you can accept it. If your manager can't get the company to revamp their system processes, it's unlikely that you will.

Also, access to the database is not really relevant to whether the process can be automated (with many databases, you'd have to run a bunch of queries anyway, if there's an output file size limit). Unless the web server has anti-scraping measures in place or something, downloading 20 files is trivial to automate (and if there are anti-scraping measures, that's more of the issue than access to the database is).

Improve this answer
5
  • 2
    So you're suggesting they may have deliberately lied in the job description because they knew qualified people wouldn't want to work for them if they knew what they'd actually be doing? And your response to that is to knuckle under and take the paycheck?
    – Sneftel
    Jan 25, 2022 at 10:25
  • 15
    Counter point to "when you're at work, it's not your time". Every day I spend at work not doing the thing I trained to do is a day that the technology I'm trained to use advances one more step and I don't keep up with it. If the company doesn't use my skills, I will slowly lose my skills and won't be able to apply them as effectively later, won't be able to get as many jobs in my field and generally will be held back in my career. It is in my interest to ensure that my skills are being used properly, or to move to somewhere that will do so.
    – Ruadhan2300
    Jan 25, 2022 at 10:53
  • @Sneftel Huh? I'm saying that their inefficient use of workers meant they had to hire extra workers, and OP was likely one of those extra workers.
    – Acccumulation
    Jan 25, 2022 at 18:49
  • 4
    "when you're at work, it's not your time." - To huge extent it is. Future employment opportunities depends on what you do at work. If you were told you will be data analyst and took the job to get more experience as data analyst and progress your career in that direction, then allowing yourself to be a copy-paster instead is a waste of your time. With the exception of unskilled labor and career dead ends, money is not everything employee is getting out of his employment.
    – Mołot
    Jan 26, 2022 at 1:05
  • The combined point about part of compensation being the expected exercise and development of skills is huge.
    – coppereyecat
    Jan 26, 2022 at 19:15
1

Sounds Like Your Company Values Security Over Efficiency

What you perceive as inefficiency may be the company's approach to whitebox security. A system can only be said to be whitebox secure if it is resistant to cyber attacks from people inside your own company. When a company lacks the resources to design a system that can not be hacked by people with direct access to and understanding of your system's internals, the next best thing is to just limit how many people can access the system directly at all. To elaborate, when you need to hire 20 people to work with your data, that gives you 20 chances that one of those people will become a disgruntled employee and intentionally sabotage or steal sensitive information, 20 chances someone will get a virus that makes its way onto your data server, 20 chances someone will use a weak username/password making your data server easier to brute-force, 20 chances someone accesses the database from a compromised, personal device, etc.

To minimize attack surfaces, some companies may use 1-2 employees to act as gatekeepers to distribute only non-sensitive, 1-directional information for use by other employees. These employees hopefully have specialized cyber security training that you do not, but either way, this minimizes the company's cyber security risk.

In fact, some laws have caveats in them that require you to limit the number of employees that can access certain kinds of data to the bare minimum for exactly this reason. So, if their database contains PII from certain countries or states, Credit Card numbers, Medical Records, or Industry Secrets, giving you direct access to that data could be a violation of law or contract. If any of this is the case, then they are doing exactly what they are supposed to be doing. If you want to streamline the system, the best option may in fact be to create a better system for automating the excel sheets instead of trying to get access to the source data.

Improve this answer
2
  • I get two answers to my questions: 1) the data is too sensitive, and 2) source data is not appropriate for analysis because it is not documented and not terribly logical. None of this makes sense to me because I can download said sensitive data via the web server at any time. No other database I have access to is documented either and yet I have access to them. Plus I'm sure they can just give me access and the scripts they use to produce those Excel files. It really does seems that there's office politics involved.
    – ectoplasm
    Jan 28, 2022 at 9:18
  • @ectoplasm Even if it is data they want you too see does not mean it is data they want to let you to have direct access to, and just because a company is not legally required to limit user privileges, does not mean a company shouldn't/wont. I can't say anything for certain without knowing more about your company's management style, but I've never worked for a company that gives out direct database access lightly, and I have seen systems where Excel/CSV exporting was added specifically to create a layer if separation between the live database and employees.
    – Nosajimiki
    Jan 28, 2022 at 15:55

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .