OK, before anyone yells at me: Yes, I know the On-Premises Data Gateways are now part of Power Platform, not just Power BI. I learned how to do everything in this post because of my work with Power BI, so that’s why I named the post this way.
The Power Platform team has released a preview of their PowerShell module for managing your Data Gateways. You can install it now from the PowerShell Gallery using PowerShell Core. I’ve been working with the cmdlets for a few weeks now, and even contributed code to enhance two of the cmdlets.
Install-Module DataGateway
It’s PowerShell Core Only
You read that right, and it’s the first module I can remember working with which is PowerShell Core only. Before you go trying to install the DataGateway module on Windows PowerShell, just open PowerShell Core instead. If you don’t already have it, you can find instructions for how to install PowerShell Core here.
If you’re running VS Code or Azure Data Studio on Windows, you might need to switch the version of PowerShell you’re running in order to use this module. See this short post on how easy it is to do that.
What Can the DataGateway Module do?
The DataGateway module comes with 23 cmdlets. So far, the ones I find myself using the most are Get-DataGatewayCluster, Get-DataGatewayClusterDataSource, & Get-DataGatewayClusterDataSourceUser cmdlets. (Those last two are the ones I did a very small amount of work on to make more user friendly.) You can pipe them all together and get the data source & permissions data out of your On-Premises Data Gateway connections. This makes it very simple to gather the data you need for an audit, or other governance needs.
Other important questions these cmdlets can answer are things like:
- How many On-Premises Data Gateways do I even have in my organization?
- How many of those are Enterprise vs. Personal Gateway.
- How many member-servers do I have in a Cluster?
- What version of the Data Gateway software are these machines running?
I have included a Gist with some of the data I’m collecting out of the
On-Premises Data Gateways with PowerShell. I hope you find them useful
NOTE: I am a Power BI Admin and am an Administrator on every On-Premises Data Gateway I have run these cmdlets against. If you’re a Power BI Admin but not an Administrator on an On-Premises Data Gateway, you may not get all the results you need. Furthermore, in my testing, it seems like you need to be at least a User each of the Data Sources in order to retrieve it. Two things I want to stress about that last sentence: 1) My testing has been limited to only two tenants to which I have admin access. 2) Again, these cmdlets are in preview, so the behavior may change as more work gets done on finalizing them.
It’s great to see the Power Platform team joining the other product teams which allow you to easily manage and report on your assets, with PowerShell. ??
The post Get the Governance Data You Need Out of Your Power BI Gateways with PowerShell first appeared on SQLvariations: SQL Server, a little PowerShell, maybe some Power BI.
