Forum Discussion
shared developer access to Postgresql dataset
Sorry - I incorrectly assumed you were refreshing the data directly to the service via a gateway. Bad assumption on my part! Glad you found that.
Yes, you ABSOLUTELY need that. That was assumed in my post. So, in summary, two ways you can try this:
- Create dataflows to the PostGre data using the gateway.
- Set up incremental refresh on those dataflows for the FACT tables so only recent data is constantly refreshed. You define "recent" - 3 days, 10 weeks, 12 months, whatever. Older data after the first big refresh is static.
- Connect multiple Power BI PBIX files to the dataflows so end users can create reports.
- Publish reports to service.
- User Power Automate to automatically refresh reports when dataflow succesfully refreshes.
Alterntively skip dataflows:
- Users connect PBIX files to PostGre database
- publish to service
- grant users rights to gateway
- users set up scheduled refreshes.
- You can set up incremental refreshes here too so your PostGRE server isn't getting hammered for 100% of the data with each refresh.
There are many other alternatives. The advantage of the dataflow scenario is there is only one connection to the on-prem database. All others are cloud to cloud as the Power BI datasets refresh against the dataflow.
Thanks Ed...my fear is that the dataset would be too large to effectively manage from a single users' desktop, and we need the data updated daily if not more often.
That was why I also recommended incremental refresh.
As far as one user doing the modeling, it depends on the org. There can and often should be a difference between a data modeler and report developer. And with Premium or PPU workspaces, you can edit models in the service via tools like Tabluar Editor, so the model is no longer on the desktop. Additionally, more than on data modeler can modify it, though they would need to coordinate changes. Adding/changing tables is a pretty big deal that can have wide ranging impacts. Editing measures less so. You can get real sophisticated with this using DevOps to manage the model changes.
Data modeling and data refreshes are not related, especially when they are all done in the cloud.
None of this works with a Pro license workspace, requires premium or PPU, but I suspect the size of your model precludes Pro anyway.Thanks for the valuable info Ed! I found this note this morning: https://docs.microsoft.com/en-us/power-query/connectors/postgresql
It looks like I could build a direct connection to our local data warehouse using a "on-premises data gateway", but I'm not sure if this will allow me to get to the same place as your solution? We would want only one developer managing the data model at least to start. And yes we're not concerned with the licensing tier for the developers at this point.
I'm more interested in getting the data refreshed frequently and in a common dataset, so that we can build near-real-time reporting capability for internal uses.
Sorry - I incorrectly assumed you were refreshing the data directly to the service via a gateway. Bad assumption on my part! Glad you found that.
Yes, you ABSOLUTELY need that. That was assumed in my post. So, in summary, two ways you can try this:
- Create dataflows to the PostGre data using the gateway.
- Set up incremental refresh on those dataflows for the FACT tables so only recent data is constantly refreshed. You define "recent" - 3 days, 10 weeks, 12 months, whatever. Older data after the first big refresh is static.
- Connect multiple Power BI PBIX files to the dataflows so end users can create reports.
- Publish reports to service.
- User Power Automate to automatically refresh reports when dataflow succesfully refreshes.
Alterntively skip dataflows:
- Users connect PBIX files to PostGre database
- publish to service
- grant users rights to gateway
- users set up scheduled refreshes.
- You can set up incremental refreshes here too so your PostGRE server isn't getting hammered for 100% of the data with each refresh.
There are many other alternatives. The advantage of the dataflow scenario is there is only one connection to the on-prem database. All others are cloud to cloud as the Power BI datasets refresh against the dataflow.