Key differentiators with PowerBI ETL-capabilities

1

Hi,

In our organization there’s a debate whether to use PowerBI for ETL or data flows in the future.

What are the key differentiators of EasyMorph to PowerBI’s/data flows ETL capabilities ?
How do both products compare ?

Thanks

2

To my shame I’m not very familiar with PowerBI ETL. Typically, EasyMorph stands out because of:

100+ actions/transforms

Powerful workflow capabilities:

Powerful automation capabilities:

Automatically generated documentation in plain English

3

Thanks. Personally I appreciate:

  • the sandbox in EasyMorph which allows testing a lot of small things very quickly that seems more cumbersome in other tools.

  • Sending emails using the mail action

  • The “find in actions” option to trace where column is used in the flow.

  • The speed by which a data flow can be designed.

  • Iterations

  • The simple GUI

  • Search options on fields, values

Things that could be improved:

  • Sampling records in all types of import files (e.g. get first 1000 rows of a DSET file)
  • More statistical functions
  • Running SQL-queries with a given batch size to append to a CSV so that it can be run on a pc with less RAM available.
  • Generating parameters from column values.
  • Be able to capture the expressions in for example create new column as column values
  • More connectors: e.g. to Azure Blob storage.
  • Higher performance to read big CSV-files ( > 1 mln records)
  • Capture the DDL statement when using create table action without having to create the table in the database.
  • Allowing keep matching and mismatching database rows when one only has read only rights on the database.
    Facilitiating incremental loads from database to other data stores.
  • Native integration with R and Python.
  • More view options (zoom in/out, auto arrange data flow to make it more clear
1 Like
4

I’ve used a variety of BI/charting tools over the years, and recently have been using Power BI. But it fell flat on its face for a specific situation I had (inability to export a long matrix visualization). Unfortunately I had already used Power BI both for data importing (as well as various custom column calculations) and visualizations on that project. It was relatively fast for me to change course and generate the table I needed in EasyMorph, but I had already wired up lots of visualizations in Power BI from the data it had itself transformed. So now I wish I had done all the ETL work in EasyMorph (as a separate tool), and then used Power BI to visualize. Instead now I have a hodgepodge in two places (that “matrix” table in EasyMorph but other reports in Power BI, and some data transformation done in one place and some another).

From my perspective, it’s far easier to understand what a data transformation is doing when seeing the intermediate tables in an ETL tool rather than dealing with complex formulas in Power BI that I needed to summarize a variety of values (for instance I had a nest of summarize/countrows/distinct/selectcolumns/calculatetable functions to do something that would be much more understandable in EasyMorph). In general, I feel like once I need to step into a complex calculated column in Power BI that maybe that’s the time to just do that in EasyMorph. Also, Power BI has a major issue with how it deals with tabular “visualizations” — logically these are just tables that you should be able to manipulate in subsequent calculations, but Power BI just treats them as dead ends. In addition, I think it’s easier to quickly see data quality issues at the ETL step in EasyMorph before starting to do any visualizations.

In summary, maybe some rules of thumb would be (perhaps I’m writing this for my own reference more than anything else!):

  • If you are doing very light transformation of the data (rename a column or two) or light joining (with very clean data), then use Power BI alone for data import
  • If you start feeling like you need complex functions to calculate columns, use EasyMorph for that
  • If you need any specific features that Power BI data importing does but EasyMorph doesn’t, then use Power BI
  • Otherwise, use EasyMorph for ETL
2 Likes
5

Hi @reynsnivea -
I was solely relying on PowerBI’s manipulation + DAX (when it made sense) before going to EasyMorph. But we also had a number of data sources outside of Microsoft.

Your question is an interesting one but here is my take on it.

If you are “all-in” with Microsoft with PowerBI Premium Licensing and you have the Dataflows you can live in PowerBI because your data set is going to be stored in PowerBI and with that in mind, if you are going to reuse it for other Analysis & Dashboards then you have a solid, repeatable process its very easy to traverse the steps in PowerBI to see the changes and calcs. They even have some nice performance tools now as well to help find bottle necks!

However, if your stack doesn’t revolve around Microsoft for everything then it gets to be a little bit murky. Here are some examples from my side:

I have some very standard reporting that we use for Customers that I don’t really manipulate the data other than to reformat some things, therefore PowerBI works okay from just connecting to my source and defining the joins in the model.

But, as the base PowerBI file starts getting into the 100’s of megs (even with compression) those on-premise connectors start bombing out and you get mysterious timeouts because the host machine(s) has to do a lot of work (Again if you are in the full on msft space, probably not an issue for you)

For me, we are in Redshift (Panoply) which PowerBI’s ETL capabilities don’t match 1-1 with RedShift, so I had to do some of that work in other tools (like EasyMorph) before I can get it how I want it in PowerBI to avoid downloading data directly before processing. So then the question is, why not do it all in EasyMorph…right? But then it becomes a documentation problem for how the data ended up that way. So its a balancing act.

Other things of note that I’ve run into are around more alert style reporting. The team wanted those alerts (with underlying detail) to come to their inbox directly (in excel). PowerBI (with pro licensing) can only accommodate 8 refreshes per day and will only send out a PDF file.

That is just my experience in that space. Hopefully that helps you out!

1 Like