Power BI dataflows: Where does it fit in? (Matthew Roche schools Patrick)

no Patrick some guy in a cube and in this video I'm gonna talk all about data flows stay tuned alright so I did a video on where you create a column what you should use it the query editor or push it back to the source and I got a lot of questions about data flows and I was like oh talk about data flows just use the query editor use the data source all right Patrick We Need to Talk you know my office is right behind that wall I really know some of us have actual data flows worth to do data flows work I didn't even know that was a real thing yeah you know they are so I thought everything was just done in the source do we need to talk where's your laptop all right okay guys I'm joined here by the world famous dr. dataflow mr. Matthew Roche the D and pH D stands for data flow okay data flow doctor data flow all right dr. data flow so okay a lot of questions about data flows I did a video and I just kind of didn't even talk about them and people were like what do they fit alright should I use it between the query editor and the data source is the same thing yeah you wanna know more about it so I assume this video was in the context of reuse right so where do I put logic so that it has the most impact your assumption is correct you must have the smartest viewers out there mentioning data flows here because this is exactly where we want them to be a lot of it really comes down to thinking about where should I add data into my end to end no pun intended flow between the source and the end analytics model power bi provides a lot of options and data flows are you know one of the newest and one of the most powerful and it fits somewhere if it's somewhere so that's that's ok wait so Matthew instead of always talking up its head over to my laptop excellent so here we are so we're looking at we're looking at a workspace in power bi so I've already come to the data flows tab and as you can see I have a bunch of data flows I don't do a lot of other work in power bi these days because data flows is where I spend most of my time so you only create data flows I also create some reports and some data sets and other such things but I a lot more time in data flows just as in most bi projects or people with different areas of expertise or different areas of influence working on the earlier parts of the of the end-to-end flow it's my happy place it's all right there I've always always spent my time before we dig in here too deeply in an ideal world we would always have the data that we needed in the data warehouse so it's ready it's prepared for analytics it is in the shape and the format and the location that we need and if we need something edited we can just reach out and say to someone in IIT it's like hey I need this thing and they'll deliver it same day so we don't need to wait which alternate university you live in yeah yeah not that one okay that's the ideal world right in the world that we live in this is where data flows come in so I'm a business user and there's there's a data source let's say for example adventureworks but I need extra data but I need extra data okay and without data flows what I can do is I can simply add new columns into my data model I can do power query inside of power bi desktop so that my dataset wait hold on a second so that my data set will include those additional columns or additional calculations but the issue there is that the data that I add is only part of my one model which reduces the opportunity for reuse so can I do this on a MacBook oh absolutely this is this is I was not expecting that it's like I don't know if you have a MacBook but if you have one you can do this so the the key thing here is the data flows design experience this is of course where you're going it's built into the browser users power query online so as I'm adding new entities into my data flow city I think of it as a table okay so as I'm adding new entities into my data flow I can simply select as I would in any power query tool my data source here we'll put in our connection information so I put in my server name I put in my database name this is the data Mart my adventureworks data Mart sure that I need to get data from and I can pull you let's say the you know two different tables click transform data and use this as a starting point to get the data into the shape that I need for my solution I can do you know any type of transformation that I could do in power bi desktop power query basically building a set of reusable tables let's get those guys out of the way we'll do some renaming because data flows are backed by Asscher storage so it's actually file storage under the hood complex types we'll need to remove these guys I'll get that out of the way it is power query so this is power query online this is what our our colleagues in the power apps world have been using for for months and months now so basically this is not years I don't know I don't know so many months possibly years I'm not a power apps professional but ok but what we have here is the power query experience embedded in the browser this is basically how we can enable any user from any location in any platform to have both power query designer in the browser and perhaps most excitingly the execution process is in the cloud it's it's run inside the power bi service not limited by your laptop so I'm gonna be honest with you this is making me really happy yeah ok so the interesting thing so here we have some some entities that we've pulled out of our central data warehouse but what we want to do the reason we're using data flows here instead of descending sending people to the data warehouse is we want to do a mash-up on top of it so I can pull in data from additional sources so here I've got some promotion data that I've stored in a text file up in the cloud so we've got a ADF process that is producing new promotion data and we can put in our connection string and both add a new entity kind of like adding a new table to the data Mart and we can mash things up so I'll say combine tables merge queries as new so super familiar to anyone who is working in power query and I can say let's have our customer on the left - lets have our promotions on the right we'll have the account ID and our customer gooood as our join well then come over we will continue so basically it's a oh it's it's doing the the the privacy settings exactly just like we would expect and want a power query to do I scroll over to the right and expand out the new column to choose those that I want creating the mashup you create a name like almost a central place for this data can other people use this or that is the beauty of power bi so when I save this mm-hmm well choose save and close is that closing apply yes it is I actually need to click it for it to work so I'll save and close the apply actually comes as the next step because we've created a new data flow the data flow is an object inside our workspace just like a data set or report or dashboard would be I'll give this guy a name so we could remember who I made it flow so you don't need a cube anymore now you can have something that's a little bit more new and exciting cube I like it because it's like exactly and we will now have the option to refresh well remove this try to recover from cache we get that sometimes but now we are refreshing the data which is essentially running the queries that define each of these entities because behind them is each power query query yeah so we're refresh it now this data flow has physical data stored in Azure all managed by a power bi and I can come over into power bi desktop and use this so both I can and any other user who is a member of the workspace with permissions to access it I can come in and say I want to get data from power bi data flows it's going to make a connection using my my Azure Active Directory credentials just connecting back up to that power bi service and here I'm going to see a list of all of the workspaces that I have permission to access that have data flows in them I'll come into my workspace the one that I've just created this I will find that data flow that I just created with you and now I can use this as a starting point and I'll simply load that into my workbook it's going to import that data uses import mode for access today so we'll load that data in and I can now build reports and dashboards and visuals on top of it as I would with data from any source and I can do additional mashups as part of my PBIS file but the most important important exciting thing is that the work that I've done here combining the data from the adventureworks data Mart and that promotions text file I can now have this available in the cloud for any authorized user to have as a building block for their power bi applications and this essentially gives me the ability to do the work that previously I would need to go to IT to do we've now have this reusable data entity like a table in a data Mart or data warehouse backed by a j''r all inside the power bi service easily discoverable that can be used as a building block for other analysts where appropriate so it's actually the million-dollar question of course million dollar question so this can replace my data warehouse absolutely not okay I certainly can so when we think about the data warehouse today the enterprise data warehouse is a very mature technology with all sorts of scale and performance optimizations built-in and it's designed for typically these large scale centrally managed solutions data flows are designed to enable well so the primary use case for data flows today is to close the gap in that data warehouse so behind the scenes these are files in folders so they're their files and Azure data Lake are Azure storage under the hood and it does not have the same performance characteristics and scale capabilities that a modern cloud data warehouse would have today so if you say oh I don't need as your sequel TW or sequel server data warehouse anymore even though a lot of the capabilities are the same that performance and scale from the self-service data flows in power bi at least today are not going to really deliver the performance that you need one of the things that is currently in preview and is coming soon in data flows is what we're calling the advanced compute engine this is essentially how I got your attention 'sounds really exciting so this is our really the best of both worlds where when it is configured in power bi premium the work that's done by an analyst can load data into the data like under the hood but at the same time power bi will automatically index that data and store it in Azure sequel DB so that you can have both the performance of sequel and the direct query access to it so that as you're consuming it it doesn't need to be doesn't need to be import mode only so so this will give you that self service with a lot of the characteristics of the data warehouse all right so so when I just kind of sum this up just a little bit mad yeah so I'm an IT professional I build a data warehouse you are an analyst right you look at my data warehouse I'm missing something for my data warehouse you come to me and you say hey Patrick can you add promotions to my data warehouse I go sure let me have a meeting about a meeting about meeting and it's six months later right and but instead of you waiting yes yeah so I can close the gap today which is really the reason why we have self-service bi tools in the first place it's not to replace IT sure it's to allow IT to focus on these strategic challenges while business is solving their day-to-day problems and because it is a power query every data flow entity is defined by a power query query that's easy to handoff to IT and they can take this and use this in whatever way they want because power query is a core capability and many different tools so that was great right my mind is blown right now all right so what do you guys think you got any questions comments you know what to do post it in the comments below it's your first time visiting the guy in the cube channel hit that subscribe button if you like my video a big thumbs up as always from Adam myself and Matthew Roach thanks for watching and we'll see you in the next video thanks everybody don't know about to don't have Mia

Channel: Guy in a Cube

Views: 58,078

Rating: 4.9629941 out of 5

Keywords: power bi dataflows, power bi, power bi data prep, power bi dataflow tutorial, power bi desktop, power bi self-service data preparation, power bi tutorial, power bi tutorial for beginners, power bi video tutorial, dataflows in power bi service, dataflows power bi, bi, dataflows, power query, data flow, dataflow, self-service data preparation

Id: lXq9GDfpv0Y

Length: 13min 36sec (816 seconds)

Published: Wed Oct 02 2019

Reddit Comments

[u/timusw]

This is pretty wild. Anyone with working experience with dataflows care to share about their implementation?

[u/perkcocets]

My experience has been horrible with data flows. From unknown errors when I copy and paste from desktop M to data flows. To full loads taking minutes and incremental load taking hours or failing. Hasn’t been a good experience so far.

[u/InfinitePermutations]

Why can't we use direct query on a data flow, the benifit I see is having massive data models created that don't need to be loaded into memory

[u/that_name_is_in_use]

So if i have understood correctly...

Where you would normally pull a data source into desktop and edit your entities(tables) there i.e. additional columns, measures, lookups, relationships.

Dataflows allows you to:

1. Separate out the datasource at an organisational level.
2. Combine / mashup datasources where there is an applicable relationship.
3. edit existing dataflows to incorporate new datasources?
4. Reuse dataflows in multiple reports/dashboards?

Does that mean that the dataflow only has to refresh once, where as if you have linked a datasource to multiple reports the datasource is repeatedly being refreshed taking up computing resource?

When he removes the columns in the tables he imported, that had [Table] in the field. Why did he do that? Im struggling to understand.

[u/mixedliquor]

Having not watched the video yet, I am trying to figure out where PBI can sit in my workflows. We’ve moved to O365 and SharePoint, but I use R for almost all my data processing. The learning curve associated with PBI seems prohibitive for much more than visualization.

What drives me nuts is there’s certain things I have to do in the desktop app but I then have to load it in the SharePoint module.. unless I’m missing something major it seems like making a smooth workflow will be much more effort than I can afford at the moment.