In our previous post, we discussed why we recommend that you use PowerShell for data integration.  If you’re a data professional, you may have some objections.  (Indeed, if you’re not a technical person, you should probably skip this post).

First, PowerShell isn’t great for data transformation.

For example, if you’ve used SSIS or a similar tool, you may be used to mapping data in your integration tool. That, indeed, is how the ETL – Extract/Transform/Load integration model works.  You take data from source 1 (extract), you transform it in your tool, and you push it into source 2 (load).

And indeed, we don’t recommend this paradigm.  Instead, we prefer ELT– Extract/Load/Transform.

Which means all PowerShell is doing is pulling the data and pushing into another database. Once the data is loaded, we then use SQL to transform it for its final use.

The point – we don’t do transformations in intermediate tools.  We do it in the database source or in the database target.

Speed may be your next objection.

Purpose built tools can be faster than PowerShell. 

But as our customer, you’re a solid mid-market company our customer with revenue anywhere from 50 to 500 million dollars.  And customers like you don’t have “big data”. It’s rare that any of your integrations process more than 100,000 records at a time. 

A decent server can process this quantity of data within minutes.  And indeed, in the world of Rest APIs, the speed constraint is most often the API, not PowerShell. And again, as a mid-market company, you’re not looking for instantaneous, 7×24 performance.   Ease of support and training are much more important.

if you do have tens of millions of records to load at a time or you need instantaneous updates, we’ll agree that PowerShell is not the answer. And we’ll admit that we’re not the right partner for you.

Adam Jacobson

Adam is founder and president of Red Three Consulting. He has over 20 years of experience in ERP consulting and BI consulting. Adam has particular expertise in complex accounting and other multi-company and international reporting challenges. Prior to founding Red Three, Adam was a partner in United Systems Consultants where he ran its 30-person Lawson software practice. Outside of work, he serves as board member and treasurer of the Riverdale Y. When not working, he spends his time answering his son’s political questions and cycling, swimming and reading.


Leave a comment

Get tips and insights delivered to your inbox

Start a conversation with us

Call: 917-848-7284

Request a Consult