The PERFECT Power BI dataflows use case

No video

The PERFECT Power BI dataflows use case

Рет қаралды 63,648

Күн бұрын

We get a lot of questions about Power BI dataflows and when to use them. Patrick explores a use case that screams for using dataflows! Are you taking this approach?
📢 Become a member: guyinacu.be/membership
*******************
Want to take your Power BI skills to the next level? We have training courses available to help you with your journey.
🎓 Guy in a Cube courses: guyinacu.be/courses
*******************
LET'S CONNECT!
*******************
-- / guyinacube
-- / awsaxton
-- / patrickdba
-- / guyinacube
-- / guyinacube
-- guyinacube.com
**Gear**
🛠 Check out my Tools page - guyinacube.com/tools/
#PowerBI #Dataflows #GuyInACube

Пікірлер: 82

@JorgeSantos-zx6gg Күн бұрын

I am using dataflows to make ETL, but I am each day less sure I am in the right path. To add to this we tried to use the medallion architecture and we created workspaces for Test and Prod. Can you imagine de nightmare of moving connections from one ws to another? I think I need to go 10 steps back and start reading, because something is not adding up. There should be a easier way.

@danneubauer6474 Жыл бұрын

My main use case for data flows is to bring data which requires an enterprise gateway into the power bi service. If many reports need TableX, and have to go through a gateway to get it from the source, I will instead load TableX into a data flows and point all the reports to the dataflow instead. Now I hit the gateway, and source system once instead of many times. We just need dataflow query folding to make this better.

@eugeniopazrodriguez8911 Жыл бұрын

Do you have to refresh the dataflow before refreshing the dataset?

@danneubauer6474 Жыл бұрын

@@eugeniopazrodriguez8911 yes, the dataflows need to be refreshed first. On my team, we currently use Power Automate flows to make this a snap. First flow refreshes the dataflow. Second flow triggers when the dataflow refresh is complete and then triggers the dataset refresh.

@aybarsacar9375 5 ай бұрын

Hey mate, this is my current use case, we have an enterprise gateway which we don't want to expose to business users, so i have created a dataflow with a couple of tables.. How do you recommend me to consume the dataflow into a semantic model which can be then given access to other users?

@RajRao Жыл бұрын

The perfect use case I have found is if your data source is slow and multiple datasets will use the same data or a major portion of the data. In this case the data flow centralizes the data and as it's already in Azure, datasets and even pbi desktop can load it a lot faster. We do this for our on prem data sources, which can be a lot slower to load via a gateway.

@SranioDrljaca Жыл бұрын

This is execly how do we us it in our organization for last 2 years. It makes empowerment much easier: Employee can write his/her own Power Query, if it is useful for everyone, he/she send it to us (PMO) and we copy/past it it in Dataflow so that everyone can use it (project managers, controller, delivery officer...). Additionall to creating single source of truth, it makes their Power BI files faster, as transformations are already done (PMO even created Staging process and almost nothing is transformed in Power BI File).

@user-bk8db7ud6k Жыл бұрын

Here is a "real world" use-case for a dataflow that "normal" people working at a business can relate to. A company has a huge list of parts that it uses in its manufacturing process. If someone needs a list of the parts, they refer to an Excel spreadsheet that has them all BUT... The Excel sheet is a DISASTER containing obsolete parts, test parts and all kinds of other junk. The work-around is to have someone clean-up the Excel list in Power BI and create a dataflow out of it. Then everyone at your entire company has access to the best parts list ever and can use it in any of their reports.

@lolgunner1 Жыл бұрын

Sounds to me that the data source could be the issue. If a database is the data source and the excel is used just to send the data around, a regular power bi data source might be enough for you. If the excel is the data source, shouldn't that excel file specify which parts are obsolete and which are not?

@user-bk8db7ud6k Жыл бұрын

@@lolgunner1 LOTS of companies have lots of garbage data mixed in with their "real" data. That is the problem, and a nice ETL job and Dataflow can help with that.

@lolgunner1 Жыл бұрын

@@user-bk8db7ud6k yes, it might be wishful thinking to fix it in the source but if you just want to show a cleanup up list, a power bi data model is enough. I do realize now that if other people want to do something with it, a data flow is the better option (besides fixing it in the source of course!). Thanks for the insight!

@louism.4980 3 ай бұрын

Love how you made it so simple to understand!

@leecolbert7943 Жыл бұрын

Hey Patrick, are there any plans to allow dataflows to be edited by users in an AD or AAD group? It appears only one user can edit this despite the ability to share the logic in the dataflow.

@vzarkova Жыл бұрын

I cannot give you more than 1 thumb up for this question unfortunately. So relevant!

@Pokemink Жыл бұрын

Yeah, no kidding.. This needs doing yesterday.

@davidlopez-fe2lb Жыл бұрын

great question, hoping Microsoft step in a provide a solution for this. For now our team has one workspace that houses all our dataflows, and our team are admins, so if someone needs to take over a dataflow they could, but would be a lot nicer if it could "live" with an AD group as opposed to one person.

@kevindiedrichlong Жыл бұрын

I have had some recent success using the Value.NativeQuery() function within Power Query and it appears to be significantly faster than folded steps within individual queries. Patrick, is this true? Are there advantages to writing native queries vs. allowing Power BI to construct?

@hamzahlydick5501 Жыл бұрын

Great question! Hopefully I get a notification if/when answered

@danrolfe7862 Жыл бұрын

I use native queries because we have parameterized our Dataflow to support Dev/qa/uat/prod release stages of the individual database objects. We added parameters for database name and schema to support this and separate tenant environments as well I am more comfortable with SQL and we don’t mind paying the compute cost on Snowflake for the expedited processing of our row level conditionals, flags and aggregation. Speed in this regard would be subjective. If youre dealing with hundreds of millions of rows and also trying to do dozens of steps in your data flow you’ll see a performance impact - but for relatively small data sets I would guess that the elapsed time delta would me negligible, and most likely not recognized by your end users- since the dataset reads from a cache of the completed dataflow for presentation.

@scottbradley1194 Жыл бұрын

Thanks! Great video. I love dataflows and use them as much as I can. One big drawback I keep running into is assigning permission. I wish I could give someone build permission to the dataflow without having to give them access to the workspace.

@myusrn 2 күн бұрын

I used satellite data flow to create an end of week or month snapshot of metrics in my data, a single record at a time in azure data large storage gen2 hierarchical storage where output gets persisted. Then I used power BI data source support for azure data lake storage to read in all the records and present end of week or end of month metrics trend chart. No need to get data source owners to create this recurring job for my requirement in their upstream layer. Is this considered an acceptable or intended use scenario for power BI data flows?

@allmazd Жыл бұрын

Well, the perfect case if you aren't admin of your sql database otherwise you can do views or even stored procedures which will do the exact same thing. Anyway this is another good method to utilize your data. As always thank you for your work!

@jaybetz2070 5 ай бұрын

Explained beautifully!

@picious Жыл бұрын

great mzq in the back !!!!!! D'n'B!!!!!!!

@HeyJack13 7 ай бұрын

Does setting up a dataflow allow me to not depend on a gateway? At the moment, I have to have a personal gateway set up on a remote desktop, which isn't ideal; I want to try to get away from this and wondering if a dataflow will aid in getting away from this dependency.

@PranayPawar1991 Жыл бұрын

This is what exactly we're doing in our model 👍🏼😀

@baw5xc333 Жыл бұрын

Can we create a dataflow from an OLAP cube and then another dataflow from another data source and then connect them both?

@mariapradeepm 10 ай бұрын

Q: Thanks for the video but wondering why we do the same logic we put in SQL and create a shared dataset to centralize and ask other developers to reuse it? Could you please share your thoughts, it would be really helpful!

@margotperez3240 Жыл бұрын

Love your videos! ❤

@JhanviReddy Жыл бұрын

How do we use data flows connecting to Azure SQL server without a Gateway,both are cloud but still needs Gareway to refresh the data any pointers?

@rafacasima Жыл бұрын

Glad to see that I'm not the only person who had to deal with "but I aways did this spreadsheet this way" and used the same solution, i.e. "ok, I will organize it myself" 😂

@davidcadman3562 Жыл бұрын

Thanks. Since the few days ago lineage view in the service no longer shows arrows from my dataflows to my dataset - I find this troubling in an OCD kind of way. Is this a known issue?

@ZawirRunner Жыл бұрын

Dataflow can only be created on the service am i right? Or it can be created on the desktop as well? Thanks

@ThePPhilo Жыл бұрын

Great video, Is there a way to automatically apply visual folder structures and hierarchies to a dataflow once it is imported into a data model (tabular editor maybe?) We have many Dataflows that we want to reuse across many reports and many developers. It's a PITA to have to recreate the folder and hierarchy each time though (especially if you want to be consistent)

@ThePPhilo Жыл бұрын

I think I answered my own query, setting a display folder is as simple as: Model.Tables["X"].Columns["Y"].DisplayFolder = "test"; Set up a script once with a row for each item and then run it each time you use the dataflow in a model. Hierarchies look more complicated as I think they use a feature that's not officially supported in PBI at moment so I will stick to doing those manually.

@Fernando_Calero Жыл бұрын

I love me Dataflows!

@vignesh.s4145 11 ай бұрын

How can we refresh data for single table in dataflow instead of entire dataflow?

@pauloneufneufneuf Жыл бұрын

This is neat. If it can be used on non SQL data sources, then it's even more valid - cases where there is no option to add anything like a view on the source.

@jahliveabi 2 ай бұрын

Dataflows are fine for simple transformations, but what if we need to join multiple tables with complex transformations and DBA is not willing to create DB views.

@Noxictyz Жыл бұрын

Is this a good use case? I have a production mirror and an archive server. I use the same native query against both for a fact table. I put incremental refresh on the table from archive. In a dataset I append these tables to get one neat table. It seems good...refresh went from 3min to 20s in cloud on first try...10m rows....but is it the best way?

@user-ex3hb1hi2n Жыл бұрын

Good afternoon. Does anybode know how to convert to text base 64 mime header

@Abdullahkbc 10 ай бұрын

Hey Patrick, I am a bit confused. You can also use that query while creating your dataset. and then you can connect that dataset afterwards. I think. the only disadvantage is you cant edit dataset later in Power Bi service. Could you elaborate it please?

@adityabeheranicky 11 ай бұрын

How to pass parameters to dataflow during it's scheduled refresh.

@akhilannan Жыл бұрын

Would have been cool if Dataflow could just store the transformation step like a 'view' and it goes to actual source whenever somebody access Dataflow from Power BI Desktop. This would avoid the refresh dependency on Dataflow.

@fluffigverbimmelt Жыл бұрын

Have PowerAutomate refresh your dataset after the dataflow has >successfully< completed

@GaryMillerUK Жыл бұрын

I don't think it's made clear that although the use of dataflows enforces the upstream ETL, you still can transform the data once you select the dataflow in the load dialogue

@DzinoRC Жыл бұрын

Hello, Do these steps with adding new columns requiring premium or embedded capacity or will it work also with PRO license?

@DookyButter Жыл бұрын

Yes.

@ernestillo2025 Жыл бұрын

There is any way to set gender value as a parameter and on run time calling the dataflow pass the value? thanks!

@naveen200848 Жыл бұрын

I can make simple meaning of dataflow. Powerquery on powerbi cloud. That's it.

@ovenmitt545 Жыл бұрын

My team has been using dataflows as a stepping stone for all of our various analysts and we've been debating on going forward if we should start creating data marts since they allow multiple people to edit them vs a dataflow only allows the owner to edit them (IE someone is on vacation and a dataflow is broken, that team just has to wait until that person returns)

@DookyButter Жыл бұрын

Dataflows are managed through the workspace, and multiple users can be setup with permissions to edit the dataflows in that workspace. We cross train our team so that we never have one person critical to a dataflow. Possible solution, perhaps?

@ovenmitt545 Жыл бұрын

@@DookyButter I wasn't aware that was possible outside of using a "service account" as the owner of a DataFlow. So far when researching it, only the owner of a DataFlow is able to edit it. DataSets and DataMarts are the only things that allow multiple users to edit based on workspace permission

@atanasprodatanasov9233 Жыл бұрын

What's the tune at the beginning?

@balajis8318 Жыл бұрын

How to create data model relationship in data flow?

@DookyButter Жыл бұрын

You can't. You can only serve the dimension and fact tables in the dataflow so that users can model the relationship manually. This is one of the tradeoffs between datasets and dataflows. A dataset will have all of the relationships built out. A dataflow will not, which allows consumers much more flexibility. Flexibility and governance are your tradeoffs between the two, essentially.

@abramephraim1839 Жыл бұрын

I am not sure I understand the advantage of using a Dataflow over a Dataset (PBIX). A Dataset can do the same thing and more. With a Dataset, for an example, you can set up a single table like the video shows that can be reused in many other Datasets by connecting to it via Analysis Services in either import mode or direct query. Datasets have more capabilities than a Dataflow like direct query to a source, creating measures, calculated columns and tables in DAX, among other things.

@1888spin Жыл бұрын

Used as a custom flatten table for users who do not want to use the data warehouse or as shared data model so they can export it into Excel unfortunately.

@cristianprifti Жыл бұрын

Would also be amazing, if access could be granted individually for dataflows, instead of whole workspace access.. :(

@ChuckSteel Жыл бұрын

Starting a project where I think data flows are the best solution. Unfortunately, the source uses a custom connector, which doesn't work through the data gateway.

@RuiCaio Жыл бұрын

Why doesn't work? Install the custom connector on the Gateway. That's it.

@ChuckSteel Жыл бұрын

@@RuiCaio Invalid or missing credentials error. The connection works when configuring it in the gateway settings, but the dataflow won't load it.

@RuiCaio Жыл бұрын

@@ChuckSteel works locally?

@ChuckSteel Жыл бұрын

@@RuiCaio Yep. I think the problem is the connector authenticates using OAuth, but since the gateway is running as a local user, that doesn't happen. I don't know if a custom connector can pass OAuth connection via a data gateway.

@RuiCaio Жыл бұрын

@@ChuckSteel can you use a service principal account?

@johnnywinter861 Жыл бұрын

Those pesky DBAs! So grumpy and uncooperative! ;P

@hamzahlydick5501 Жыл бұрын

😂

@jameslaine2472 Жыл бұрын

I'm still not even sure what a "dataflow" actually is.

@MrSparkefrostie Жыл бұрын

As far as I understand only the dataflow author can access the data flow or is this wrong?

@hamzahlydick5501 Жыл бұрын

Haven’t had an opportunity to use dataflows, would love to know if this is the case

@rocketrod67 Жыл бұрын

No, you can grant access to multiple developers. You may want to limit the number of developers so you don't have too many people making changes

@MrSparkefrostie Жыл бұрын

I need to add the user to the workspace I suspect then, I am hoping to avoid, that way its read only, ie a user wants to access all may data in excel instead of BI, they could get data from the dataflow into excel.

@tekfury305 Жыл бұрын

DataFlows are useless if you have facts in it like a fact table because of missing Role Level Security. Even used as a Dimension it is the same in many cases. If you can not restrict the access to an table it is useless. Or did i missed something?

@stefaanlouette Жыл бұрын

I have the same opinion as you, great stuff but what about security or restrictions? What would be the best way to handle that?

@rwlpfrench Жыл бұрын

@@stefaanlouette Row Level Security is only permitted in a Dataset. You could have dataflows which are dependant on Master Dataflow. It depends how configure the PBI service IMHO

@gavinbroughton Жыл бұрын

If a DBA tells you no to centralised business logic, ask their manager. If that doesn't work, leave!

@pauloneufneufneuf Жыл бұрын

DB could be vendor supplied - customer schema changes void warranty / get blown away when vendor upgrade runs.

@RajRao Жыл бұрын

I don't think this is the perfect use case. The example shows a sql source. If it has millions of rows and down stream users need only a small fraction of those rows, all their datasets would become slow, as they will be scanning a file on azure storage. If it's being done for reuse, then take a box of chocolates to your DBA and ask them nicely to create the view in a separate reporting schema and tell them how many reports will use it.

@pauloneufneufneuf Жыл бұрын

Vendor supplied DBs maybe off limits for modification by a customer

@RajRao Жыл бұрын

@@pauloneufneufneuf agreed, that would be a good case for dataflows