One of the many things I love about Power Pivot and Power Query is that these tools have put BI into the hands of users and there is no longer a reliance on highly technical IT skills such as MDX to enable BI reporting. This is game changing for all the reasons you have read about on this blog over the years. Over time, as I have used Power Pivot more and more, I have found increasing value in learning about and working with SQL Server as a tool to manage my data. I know the thought of using SQL Server can be a bit daunting to Excel users (as it was to me at first) but it is actually not that hard to setup and use. Anyone can download and install a free version of SQL Server Express on their PC, and there is a lot of learning material on YouTube to get you started. But there is one thing about SQL Server that I have found to be much more difficult than with other more consumer friendly databases like Microsoft Access – that is how to get the data loaded.
Getting Data into SQL Server
SQL Server uses a tool called SQL Server Integration Services (SSIS) to load data. This is a very powerful piece of software, however I have found it is quite difficult to learn how to use it (as a casual user). I have no doubt that SQL Server professionals have no problems, but there is a big difference between firing up SSIS once every 3 months to load a couple of tables and working with the tool each day. I have never had the time to master SSIS and I cringe each time I have an issue that requires me to edit my SSIS packages. In the past I have outsourced this work to a developer as it just wasn’t worth the inefficient use of my time to try to work it out myself.
Enter Power Query as an SSIS Alternative
Now before I get a million comments from professional SQL Server experts, I am not suggesting that everyone should swap out SSIS for Power Query. I understand the importance of an enterprise strength tool like SSIS and I know it is a great tool for that purpose. I am just talking about people like me that work in the Self Service BI/Excel space, use SQL Server as a tool, but have only limited opportunities to work with SSIS. This is my situation, and if this also sounds like you then Power Query is a great alternative.
The benefits of Power Query over SSIS include: