data democratization using power bi

[powerbiexpert22]

I  am trying to build "data democratization" process in power bi . below mentioned are the key objectives of the process. I am using premium capacity license, Can it be possible to acheive below in power bi primarily if possible?

1. Visibility of all Reports , Datasets and Workspaces to New Users:

• new user i.e . users who do not have pro or premium license should be able to see the list of all reports , datasets and workspaces along with high level description of each report ,datasets and workspaces. 
• new user should be able to see all shared datasets if available in power bi

2. Automated process to get Power BI report access:

• once user see the reports list along with their descriptions , he/she should be able to raise request to get report access
• specific person example, owner of reports will receive access request via email or any other mechanism and will provide approval or deny the request
• new user should receive notification email once request is approved by the owner along with report link or deny notification
• new user should be able to select request type which can be "view" access for providing read only access of reports and "edit access" for self service capability for generating reports based on shared datasets

Note:

• above points mentioned should be handled in power bi primarily if possible or any other generic tools such as Service Now, JIRA. example , process for raising request to get report access can be handled in power bi if possible or any other tool like JIRA etc

[Greg_Deckler]

@powerbiexpert22 Well, for visibility, you could share datasets, etc. with All Company group or something similar. However, this will actually give them at least read access to it. So, if you want to expose content items that they do not already have access to then I think you are going to need to create a separate site that exposes this information. You can get this information from the Power BI REST API's. Perhaps something like using a PowerApp or Power Pages to do this would work (or just any old website) and the workflow could be handled by Power Automate.

[powerbiexpert22]

Hi @Greg_Deckler ,

thanks, how I would give shared dataset access to all company group? can it be done for reports also?

what do you mean by exposing content items?

I am not sure if I can utilize apps feature for this however if we use apps then we need to first create apps for all existing reports which would be additional task for me

[Greg_Deckler]

@powerbiexpert22 Well, I Azure tenant came with an All Company group with an email address of allcompany@[tenant].onmicrosoft.com. So, in theory you could share a content item with that group and then everyone would have the rights you specify to that item. Or you could use a similar, not built-in group, an "everyone" group.

[powerbiexpert22]

Hi @Greg_Deckler ,

how i would provide a consolidated view of all reports, worskpaces and datasets ?, think this like data catalog of reports which would provide consolidated view of all BI reports information 

[Greg_Deckler]

@powerbiexpert22 In the Service, there is a Browse feature as well as OneLake Data Hub. However, these are only for content that they have access to. If you want to expose content that they do not have access to, that is where you will need to build that yourself and populate this information via the Power BI REST API.

[powerbiexpert22]

Hi @Greg_Deckler ,

If i pull the content using rest api then how i will present it to the user, will it be in the form of power bi report which updates dynamically as soon as new report or workspace is created? or it would be some document on sharepoint ? what are your thoughts on this?

[Greg_Deckler]

@powerbiexpert22 Well, oddly enough, you can't use Power BI to pull data from the Power BI REST API which doesn't make a lot of logical sense but that's how it is. Well, I guess you *could* use Python or R step. So one way would be to use PowerShell as a tenant admin using something like this:

Get-PowerBIReport -Scope Organization

That would then create a file that you could import into Power BI, do some data transformation and then build a report that lists out the links to the reports. However, none of that will help you with a custom access and approval workflow. Well, maybe but it would be pretty difficult to have the report be automated and would likely be very manual.

The other way would be either a Power App or custom website or an R/Python step in Power BI, which you could have it access the Power BI REST API by creating an access token. For a website/Power App since you have full availability to code, you could have the code automatically render the links to the report however you want and include buttons, for example, for requesting access to individual reports. With Power BI, it would likely be import mode and you would probably face the same challenges as before in terms of an automatically generated versus manual report.

[lbendlin]

you can't use Power BI to pull data from the Power BI REST API 

You can, as long as you use a custom connector with AAD auth.

GitHub - migueesc123/PowerBIRESTAPI: A Microsoft Power BI Data Connector or Power Query Connector fo...

[Greg_Deckler]

@lbendlin OK, can't do it natively other than through R and Python. Figures the community would once again fill the gaping holes in Power BI functionality left by Microsoft's dev team...

[lbendlin]

Power BI has built in features for "Automatically Install App"  and "make all content discoverable".  The very first thing we did in our company was to shut both of these down.  There needs to be a balance between making it easy for users to discover data subjects and reports, and inundating them with hundreds of thousands of reports they may not need.

[lbendlin]

- how big is your company? How many reports do you expect to exist? 

- how old is your company (ie how many legacy processes)?

- is your company a "pure" Azure shop or is it a hodgepodge of thousands of heterogenous offerings?

- does your company have the strategic willingness to invest in data stewardship and data subject certifications (and maintenance)?

[powerbiexpert22]

Hi @lbendlin 

please see below answers

- company size is around 18000 employees. there would be around 300 plus reports

-  company is 30 years old, we have 7 diferent departments and for each department there are 5-6 sub departments

- we are currently using Azure Data Factory for building ETL pipelines and using Snowflake as data warehouse where data beling collected from ERPs , CRMs, ecommerce and marketing platforms

- Our company do not have strategic willingness to invest in data stewardship and data subject certifications (and maintenance)

[SaiTejaTalasila]

Hi @powerbiexpert22 ,

You can create a report with report name, owner and hyperlinks to your reports and make it public after publishing the report to premium capacity.If required you can make that report link available across your websites.So, the users will be able to use that report and uses the hyperlink and raise the request for access.As per my knowledge some people will use Microsoft forms or even servicenow integrations and Sailpoint iiq queues to manage access related things.In our organisation as part of tenant admin team we have a report to track the report details based on report name or workspace name.So, if anyone reach out to us we will be able to provide the report owner details.

I hope it will be helpful.

Thanks,

Sai Teja 

Thanks,

Sai Teja 

Thanks,

Sai Teja 

[powerbiexpert22]

Hi @SaiTejaTalasila ,

please see below steps which I am planning for this process. we currently use service now, sharepoint and JIRA tools. please let me know if below process looks fine to you. i hope below should be doable  

1. we will pull all metadata (report, dataset and workspaces) from Power BI using rest api and store in snowflake database

2. power bi report will be created using database mentioned in previous point

3.  report will be converted to an app

4. app will be assigned to organization level group where all employees can see that app

5. subcribe button will be provided in front of each report in power bi app 

6. once user click on subscribe then service now incident will be raised, integration between power bi and service now will be done using power automate 

7. report owner will receive email of report name and type of access (read/write)

8. report owner will manually assign user to a report or workspace or share dataset with read or write access

[SaiTejaTalasila]

Hi @lbendlin ,

Even that works.if possible you can improve the process once the request is raised in service now it should submit a request to a Sailpoint iiq group and the group owner will receive access request once the approval is done by owner the user will get access automatically to the power bi app.

Step7 and step8 you can try to automate it .

2nd option -

report owner gets the user email and access type in an excel file by the end of every day and he can use power shell and rest API commands and provide the access with in less than 5mins for multiple users.

I hope it will be helpful.

Thanks,

Sai Teja 

[lbendlin]

That may work for a small company. In an enterprise environment you better use enterprise level user access management tools.