4/28/2015

Easy ETL Using Power Query

PowerBI seems like the logical next step for Business Intelligence in the world of Microsoft.

It's found in Excel.  It's easy to use.  It's powerful.  Tons of connectors.  Can build models, which can be pushed to Tabular SSAS.  Pull from on-premise data sources.  As well as the cloud.

I've been working with the M language for a few months now.  I love it.  Easy to use, flexible, it's fun to write.  It's a blend of Wizards with custom coding with database design and data modeling and reporting and graphs and pivot tables which allows self service BI, in the Cloud, with auto refreshes, even against SSAS databases.  And you can export to Excel worksheet or Power Pivot.  One of the best features is the ability to massage data, as we all know most data must be cleansed, parsed, merged, appended, etc. and it's fully capable of handling the ETL tasks.

I think it will challenge traditional SSIS.  And traditional SSAS.  And traditional SSRS.

It just keeps getting better with every new version.

Now I will say that I lost some source code because of the inter-phase not connected to TFS.  And I write the code in notepad++ with the add-in for M language instead of within Power Query.

So the Power Query plug in, provides data connectivity to a variety of data sources, it stores data in Excel or Power Pivot models, you can perform ETL with custom code or Wizards and stores the code for later use and you can do reporting from within Excel through Pivot Tables or Pivot Charts.

It does everything a business intelligence tool needs.  And it's accessible in the cloud.

I see this as the future of BI in the Microsoft space.

No comments:

Post a Comment

We Interrupt this Broadcast