Power Query & M for Populating Any Database

[kevhav]

I have been looking for an easy-to-use, cost-effective ETL tools to populate a typical, relational, star schema data warehouse.

Recently, I think I read that the Get Data / Power Query / M experience is now available for creating an Analysis Services tabular model. (Right?)

That got me thinking: "I wish I could use the Get Data / Power Query / M toolset, to populate any target database." Such as an on-premise SQL Server database. Or, say, any relational database on Amazon RDS, like PostgreSQL or MySQL or Aurora. Or, Amazon Redshift.

There is no way to do this currently, right?

If not, why? Wouldn't it be awesome?!

In the meantime, any recommendations for alternatives? Thanks!

[dkay84_PowerBI]

In SQL Server vNext (still in private preview), MSFT has begun to implement the familiar Get Data experience, but the number of data sources (aka connectors) is currently limited.  But yes, this is an easy way to populate a tabular model without using SSIS.

[kevhav]

@dkay84_PowerBI, interesting, thanks!

If it's going to be part of SQL Server, then I wonder, does that mean SQL Server will be the only possible target? Or, like SSIS, will there be many options for possible targets?

[dkay84_PowerBI]

SSAS is where the Get Data experience resides (from SSDT where you can choose to build a 1400 level tabular model for SSAS).  Therefore, SSAS is the only landing spot for the data.  I don't believe there is any effort to have support for other DBs (including SQL).

[kevhav]

Oh, I see.

Well, I wish "Get Data" could be a generic, cloud-based service...

• With all the same (and ever-increasing) options for data sources;
• And the same easy-to-use query interface, but also full power of M for more advanced users;
• And a variety of options for data targets -- the list of which might also be ever-increasing, but obvious ones would include:
  • Analysis Services tabular (Azure and on-premises)
  • SQL Server databases (Azure and on-premises)
  • Other databases like MySQL, PostgreSQL, etc. (cloud and on-premises)
  • Flat files like Excel and CSV

From what I've seen in my (cursory) research, Microsoft would have a(nother) market-leading ETL product to offer. Right?

Or, what else are you all using that you might recommend as a comparable alternative?

[dkay84_PowerBI]

I would be interested to see what the community can share around this.  I can see where a DB agnostic ETL tool would be very powerful to users, but also possibly detrimental to the DB providers as this would make their tech less sticky.

[Anonymous]

HI @kevhav

Take a look at my blog post:

https://powerbi-pro.com/export-data-to-sql/