Dataflows 101

Matt here.  I want to do a brief introduction prior to handing over to Roland.  I have known Roland for a couple of years now – he is a member of the Sydney Power BI User Group.  What I didn’t know until very recently is that he has the same passion for Power BI that I have, and a much better YouTube Channel :-).  I invited Roland to do a guest blog here as a way of introducing you to him, but also so that he can share this information on Dataflows.  If you like the way Roland explains things as much as I do, then please consider subscribing to his YouTube Channel and also his blog.  With that, it’s over to Roland.

***************

Hello, my name is Roland.

I’m the creator of BI-Lingual Analytics and I’m super glad to share my intro guide into Dataflows here on Excelerator BI.

Matt and I have been talking about Dataflows and how they can help business analysts and alike to streamline their reporting process and save some time by handing over some responsibilities to the business users. That discussion led me to create a video tutorial about where to start with Dataflows and share my experience here as well – in a written format.  The link to the video is at the end of this article.

So What Is A (Power BI) Dataflow?

Based on the official MS documentation:

“A dataflow is a collection of tables that are created and managed in workspaces in the Power BI service. A table is a set of columns that are used to store data, much like a table within a database.”

Creating a dataflow – Power BI | Microsoft Docs

I reckon, for many business users this definition may be a little bit overwhelming.

I like to define or refer to Dataflow as Power Query Online.

• Can be used to connect to different data or data sources.
• There is a wide range of data transformation steps that can be done; just like in Power Query.
• And lastly, users can use Dataflow as a source in Power BI Desktop.

Additionally, there are a few more great features built into Dataflows, however, this is just an intro guide, so I’m going try to focus on the most essential features.

If you are familiar with the IT expression of ETL or extract, transform and load, by the end of this post and/or video tutorial you will see that Dataflows can help with business-driven ETLs. Meaning that business users can get the data they need in a shape that makes the most sense to them.

 A Real-life project

 Just like other explorations into the BI world, it all started with a small project where I finally had a chance to explore Dataflows.

My task was to create a new report for my users based on newly acquired 3^rd party data. It meant that I needed to work on getting the data into our network drives, connect that to Power BI, create the DataViz. And of course, we cannot forget about the maintenance – refresh on a fortnightly basis the raw data and from time-to-time update dimension or “mapping” tables.

However, as it sometimes happens, priorities have changed for the business (and for me as well), and I ended up not having enough time to do any of that.

But the business was kept “pushing” me that the report is useful and they would like to see refreshes and occasional adjustments.

So I thought, maybe it’s time to hand over the report. And that’s where it all started.

A Dataflow A Day Keeps Your Users Away?

Here is a brief summary of the idea.

• Create a OneDrive/Sharepoint folder where report users can save 3^rd party data as frequently as they receive it.
• Add DIM tables to the same folder, allowing team leader(s) to adjust customer and product mapping.
• Create a Dataflow to clean the raw data and picks up the DIM tables.
• Connect a Power BI report to that Dataflow.
• Publish report and hand it over to the business.

That sounds like the perfect idea, right?

If you want to see how it can be done, make sure to watch the video as it covers all these points.

Key Benefits Of Using Dataflow For The Business

From my perspective, these are the most important benefits of implementing Dataflows in a reporting suite. And I believe most (non-technical) analysts are going to share the same mindset.

1. Cloud-based solution – Allowing users to access data anywhere, anytime, from any device. For many organisations it wouldn’t make any sense to invest in powerful laptops for the Sales/Operations team. Dataflow can harness the power of Power Query without relying on local IT resources.
2. Single source of truth – Using the same data in all reports is critical in any business. Nobody wants to spend time reconciling reports and results because of two different figures. With Dataflow all users can start from the same baseline, achieving consistent results.
3. Reusing data – Not just about any kind of data, but clean data. As data transformation steps were already created by the Analytics team, reusing data has never been so easy.
4. Sharing data – Dataflow creators can certify and/or promote a Dataflow to allow easier access for others within the company.
5. Connect data – Dataflow connector in Power BI Desktop is a superior data source. Meaning, that users can achieve quicker report refresh times.
6. Automation – If the source data is always available (cloud or on-prem with gateway), report creators can set up automatic or scheduled refreshes for the Dataflow and the Dataset.

All of the above points allow report creators to hand over reports to the business.

Resulting in a reduced workload for the analytics department that in the end will allow them to spend more time analysing the data rather than creating reports.

Video

Final Remarks

As with any other BI tool or Power BI feature, I do believe that there is a place and time for using Dataflows.

Some features might only be available with a premium license (PPU or Premium Capacity) and some data might be too sensitive to leave a “network shared drive”.

But if your use-case and company allow it, I would strongly encourage you to try it and let me know how you feel about Dataflow.

If you want to learn more about Dataflows and how to best utilise them from a business user point of view, make sure to check out my YouTube channel as I’m working on heaps more Dataflow content.