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
