cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
dusdau Frequent Visitor
Frequent Visitor

Performing Data Staging/Transformations in Power BI vs SQL

Hi All,

 

My boss and I are having a discussion about doing data transformations in Power BI (Power Query) vs doing them in SQL with views/sprocs/T-SQL. As a result of that I am trying to come up with some advantages and disadvantages to doing our transformations in either platform. I am hoping the group could help add to this discussion.

 

Here is what I have so far...

 

Power BI

 

Advantages to doing transformation in Power Query:

  • Fewer tables/views/sprocs muddying up SQL databases - Resulting in cleaner SQL dbs, less maintenance & less chance of breakage (who moved my cheese!?)
  • Doesn't require SQL Server access - A business analyst or other user can see what is being done or takeover Power BI models if they know Power BI but don't have access to SQL
  • Easier to see all transformations - easier to see what transformations have been done on data in Power Query (rather than going back and forth between SQL and Power BI)
  • Faster once you are proficient in Power Query/M language

 

Potential Concerns

  • All logic tied to Power BI - if need to move to another platform (Tableau...gasp!) may be harder to reuse

 

SQL

 

Advantages to doing staging/transformations in T-SQL views/sprocs etc:

  • Can do more complex queries with T-SQL (I've run into a couple that I just had to do in SQL as it was too complicated in Power Query)
  • Possible re-use of objects with other applications
  • Provides another layer for logic not tied to Power BI

I realize there may be various reasons why you would do things in both Power BI and SQL, just looking for some thoughts and guidelines from the group.

 

Personally I am trying to do things in Power BI if I can, but if it gets too complex then i revert to SQL.  Thoughts?

 

Thanks!

Dustin