Home » Uncategorized

Automating SQL Queries to Push Information to Business

Most of us working in business intelligence and data science spend a lot of time creating data models, reports and beautiful dashboards. Even though the models are great and reports look fancy, the true value is in your customers actively using it. How often do you find that nobody is actually running the report?

This hurts, but it also makes sense. People get so much information during a day, they really need to be selective. So any important information should pop out of your report or dashboard. But still reports and dashboard are all based on the fact that people run it, pulling the information.

From pull to push with simple emails

One of my customers asked me to create a reports about some stuck accounting documents in their ERP system. This happened sometimes, but not very frequent. Business users would run it each morning to find if any new cases required attention. This made me think that I actually should not write any report, but instead isolate the important cases and have it scheduled to run automatically and send the results by email.

The customer was very happy with the results and we could actually run it far more often than daily, which resulted in faster fix times.

Automating SQL Queries to Push Information to Business

From SQL query to Excel attachment with AutoSQL

When I looked for a solution for my need to send a simple mail with an Excel attachment (you better accept it, email and Excel are the most used tools in any business environment), I actually didn’t find any easy solution. Either I had to use complex solutions requiring me to request a lot of authorizations or I had to build something myself.

This is how I created AutoSQL, a simple solution to connect to any database through ODBC and have it scheduled with Windows Task scheduler. It creates Excel or CSV output and sends the results by mail to the recipients. It is actually also possible to put the results straight in the email.

Business can sit back and relax, waiting for actions to arrive in their mail box

Business people have been extremely happy with the trigger to take action on important, rare events. It is directly actionable information and all context is provided through the Excel attachment. No more puling data and checking reports, just get triggered if action is required.