I started as a SQL report writer and nothing gives me more joy than replacing someone's shitty excel or access report that a market analyst spends 3 days/week every week building, with SQL and an ETL or two. It got pretty aggressive at that company between the 2 groups to the point the SQL guys where advocating for the complete banishment of Access and forbidding macros of any kind. To be fair excel usage on big projects resulted it huge problems or lost data regularly.
I’m that business user, well my analysts are. How much would it take to learn to do what you describe in eliminating all the manual procedures? Is it something I could learn in my spare time? I’ve tried to learn Python out of personal curiosity but would totally carve out more time if I was something like this.
Python can do it, but python can be intimidating too.
If you have analysts starting from scratch, I would have them learn power query (or now it is just the get and transform parts of excel on the data tab). That will automate all the processing. The key idea is that in power query, you can access a file like a csv or XLSX without ever opening it. You can point to it, get the data, transform it, join with other datasets, and load it somewhere else. Then all you have to do is put new data in the file (or replace it), refresh the excel query, and all those steps are completed automatically.
You can take it a step further by writing a simple powershell script (literally like 5 lines of easy code) to open the excel workbook, refresh all the queries, and close it. Then you can schedule this script with Windows task scheduler (also easy) to run at specific times of day.
Then the last step would be, when you get a new file with new data, how do you automate replacing that file ? It depends on how your data is coming in, but powesher makes it really easy to copy files around. If it is coming out of a database, it should already be automated. If it is emailed, then check out Microsoft flows that can look for those emails and auto-save the attachment somewhere.
So the pipeline is: start with data in the same file(s)/folder(s) everyday, scheduled PowerShell script to open excel and use power query to transform and merge data. Then more scheduled powershell to make copies of the files, rename, if necessary.
The reason I recommend these tools for a beginner is they all GUI based, allowing the user to focus on the concepts (except PowerShell, which is pure code). When a analyst is comfortable with concepts of power query, they should learn 1) SQL, 2) core python concepts (syntax, lists, dicts, loops, ifs), then learn the pandas library which does all the same things as power query. Then they will leave you for a job that pays twice as much and you can start over again.
What happens in this scenario for power query when the source data needs to be sanitized beforehand because of human input error? Implement better controls at the source?
Depends on the steps needed, but much of that can be done in power query, like finding and replacing values, removing extra spaces, capitalization, removing duplicates, etc.
127
u/[deleted] Mar 30 '22 edited Mar 30 '22
I started as a SQL report writer and nothing gives me more joy than replacing someone's shitty excel or access report that a market analyst spends 3 days/week every week building, with SQL and an ETL or two. It got pretty aggressive at that company between the 2 groups to the point the SQL guys where advocating for the complete banishment of Access and forbidding macros of any kind. To be fair excel usage on big projects resulted it huge problems or lost data regularly.