dataflows and reusability

[db042190]

Hi, i finally got a dataflow (df) added so I started poking around to understand if this was the missing link in pbi related to reusability.  Here is what I found at least for what results from "adding a table" in dataflows.  Please tell me if I got this right.

1.  you cannot manage roles on a df table in the service

2.  in the service you cannot use a df to create a dataset or report. You are limited and a recommendation is made to get the desktop if you dont see what you need

3.  in the desktop, df's are reusable (via get data) from the service but you have to "re" manage roles each time you use a df to build a new dataset

4.  even if the service sees data in your df table, you will not see the data if you use it in a new desktop report and request a reload.  At least as far as i can tell so far.

5.  datasets published with roles managed retain the managed roles even if picked in a new report.

6.  unfortunately, if what you really want are building blocks (eg that really complicated sales query with roles, separate from that really complicated manufacturing query with roles) that can be either standalone in some reports or merged in others if necessary, I dont see it being possible in pbi.

7.   if you choose an existing dataset in a new report, you dont get the grid icon.  Just the model and viz icons.

[db042190]

thx Aniya.  I think you misunderstood #6.  I think maybe your answer confirmed what i see as a disappointment in pbi.  By "really complicated sales query" I mean a query that stands alone just as often as it gets meged with other queries.  The idea is some sort of governance in which we help the user to avoid shooting himself in the foot and avoid him needing to know too much about sql.  From what i can see, we cannot embed RLS into that query so that we can avoid stating over and over the RLS retsriction (reusability).  In fact,  by definition, with datsets being the only place we can embed rls, any time a dataset needs something slightly different from the last time we used that complicated sales query , we are likely re entering rls restrictions.  This seems to me to defeat the whole concept of self service bi.  I'm just trying to understand if i'm missing something in a product that is so broad, I'd have thought this would have been considered.  Dont want to be difficult.  

[v-yueyunzh-msft · Accepted solution]

Hi, @db042190 

Based on your 7 questions/understandings provided, here are my answers and understanding based on my research:

1. you cannot manage roles on a df table in the service

Yes, this feature didn't be announced yet, but the newly published feature "Power BI datamart" last year can achieve the access control, please check this link:

Control access to datamarts (preview) - Power BI | Microsoft Learn

2.in the service you cannot use a df to create a dataset or report. You are limited and a recommendation is made to get the desktop if you dont see what you need

Yes, if you want to achieve the directly report developing using the data ETL component in the cloud Power BI service, please also consider using the newly published feature "Power BI datamart" to achieve this:

Create reports using datamarts (preview) - Power BI | Microsoft Learn

3. in the desktop, df's are reusable (via get data) from the service but you have to "re" manage roles each time you use a df to build a new dataset

Yes, for the direct access control, it's contained in the datamart feature as mentioned in point1, please check this link:

Control access to datamarts (preview) - Power BI | Microsoft Learn

4. even if the service sees data in your df table, you will not see the data if you use it in a new desktop report and request a reload. At least as far as i can tell so far.
Yes, this is because when you open the dataflow in the PBI service, it's actually opened in the Power query online, which means the loading preview data for first 1000 rows for your data transformation steps preview.

5. datasets published with roles managed retain the managed roles even if picked in a new report.
Yes, this is defined in the dataset and will be saved except you republished it

6. unfortunately, if what you really want are building blocks (eg that really complicated sales query with roles, separate from that really complicated manufacturing query with roles) that can be either standalone in some reports or merged in others if necessary, I dont see it being possible in pbi.
If you can manage to combine all the tables/queries into one dataset, I think the requirement like "complicated sales query with roles" can be achieved using the dynamic rls using DAX UserPrincipalName()

Dynamic Row Level Security Made Easy for Beginners (powerbiconsulting.com)

7. if you choose an existing dataset in a new report, you dont get the grid icon. Just the model and viz icons.

Based on my test, if you choose an existing dataset in a new report using the live connection mode, it will only show report when published to the workspace because the data is live connected to the dataset, but if you convert the live connection to the Direct query mode to generate a DQ data model or this report, it will also contain the dataset when published to the workspace, please refer to this official blog:

DirectQuery for Power BI datasets and Azure Analysis Services (preview) | Microsoft Power BI Blog | ...

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly