143031756821422

Useful Windows Tools for Automating Business Intelligence

Business intelligence (BI) helps companies keep track of internal and external records for data analysis. There are many online services providing BI tools for a monthly rates, however why would we pay for these overhead fees when there are powerful tools available for free and little cost.

 

Some of the tools I’ve been using recently include,

 

Windows Power BI

This tool leverages a powerful Excel-like interface that allows you to sync up data reports easily. If you have reports that share a common identifier, Power BI allows you to merge tables together.

I first learned of this tool (and it’s recent late-2015 update) when interviewing for a medical IT company that wanted to leverage Power BI for data consultancy services, as a way to help upsell services to clients. I learned a lot speaking with this employer about how niche data services can be a useful selling point for small web agencies.

After the interview, I looked more into the software and found it was a powerful tool and that you can also link Power BI to OneDrive cloud storage, which make it much simpler to update files. We can tell Power BI to generate dashboards based on a file path on your computer. Then when an updated file comes along, we can tells Power BI to refresh the table and the dashboard will automatically sync up (as long as the path remains constant and file name remains unchanged).

Power BI is free to download and provides Power BI Pro for $9.99/month USD. The free version is fully functional but has limits to how many rows can be refreshed (10k per hour vs 1 million per hour in Pro). Another useful feature in Pro is the ability to publish reports and automatically set a refresh schedule so that your dashboards auto-refresh on a time scale (or even daily).

 

Windows PowerShell / Windows Task Scheduler

If you’re used to creating data reports systematically, repeating the same process over each week/month, then perhaps Windows Task Scheduler can help you out by telling Windows to do certain processes for you. PowerShell is a useful tool for running/editing programs with Batch and PS1 files.

For instance, we can combine PowerShell and Task Scheduler to help automate BI processes such as creating CSV files, editing Excel documents, and web scraping for data sets or source code.

 

The takeaway point here is that combining Power BI / Onedrive with PowerShell / Task scheduler allows for a robust system to creating/automating dashboards for business intelligence needs.

Simplicity is the best way to treat things sometimes, so if we have raw datasets to take a look at then we can begin projects in Excel. Excel allows us to model data quickly with Pivot Tables and Pivot Charts, however Excel is too limiting when we have updated reports. We don’t always want to recreate a data visual with each new report.

Rather, it’s more convenient in the greater scheme of things to focus on business intelligence automation.

 

Have an idea for a project or a question?

Contact me at:

will[at]mindbodymetrics.com

@mindbodymetrics

 

 

Leave a Reply

Your email address will not be published.