Hi @chenne ,
Your use case for enabling flexible Power BI dashboards while adhering to OAuth2 and scheduled refresh constraints is common, but it involves a few critical considerations with the Power BI architecture:
Key Issues in Your Current Approach:
1. Personal vs. Non-Personal Gateway:
- Personal gateways are tied to the user's Power BI Desktop session and aren't meant for shared use or production workloads.
- Non-personal (standard) gateways are designed for organizational use, but they require careful configuration to work with custom connectors.
2. OAuth2 Authentication Challenges:
- Power BI's service environment (for scheduled refresh) doesn't inherently support token acquisition flows in custom connectors during refresh. You need to implement a mechanism to persist and refresh the access token securely.
3. Credential Management:
- Storing credentials in queries is insecure. Power BI’s gateway and custom connectors allow for secure credential storage and retrieval, which is preferred.
---
Proposed Solution:
1. Implement Gateway Support for Your Custom Connector:
- Ensure Your Connector Supports Gateway Compatibility:
- In the `DataSource.Kind` definition of your custom connector, make sure to include the `SupportsDirectQuery` and `Authentication` properties.
- Example:
m
[DataSource.Kind="YourConnector", Publish="YourConnector.Publish"]
shared YourConnector.Feed = (optional credentials as record) =>
let
... // Implementation of your connector
in
Source;
YourConnector = [
TestConnection = (dataSourcePath) => { "YourConnector.Feed", null },
Authentication = [OAuth2 = []],
Label = "Your Connector"
];
- Gateway Configuration:
- Install the on-premises data gateway (standard mode) for your organization.
- Add your custom connector to the gateway folder on the gateway server. This ensures that your connector is recognized during data source setup.
- Restart the gateway service to pick up the connector.
- Data Source Setup in Power BI Service:
- Go to the Power BI Service -> Manage Gateways -> Add Data Source.
- Choose your custom connector, and configure the credentials using the OAuth2 flow.
2. Implement Token Refresh Logic:
- Modify your custom connector to manage token refresh transparently.
- Use a function to check token expiry and refresh it as needed.
- Securely store tokens using Power BI's credentials store (encrypted).
3. Enable Scheduled Refresh with Gateway:
- Once the gateway and credentials are configured, users can create dashboards in Power BI Desktop and publish them to the Power BI Service.
- Scheduled refresh should work without requiring manual token updates or personal gateways.
Addressing Customer Concerns:
1. No Need for Personal Gateways:
- The non-personal gateway setup with your custom connector eliminates the need for users to set up their own gateways.
2. Secure Authentication:
- By leveraging OAuth2 and the gateway's secure credential storage, sensitive credentials aren’t exposed in the query.
3. Flexibility in Querying:
- With advanced queries enabled in the connector, customers can fetch and manipulate data flexibly.
---
Additional Recommendations:
- Consider Power BI’s REST API for Direct Integration:
- Instead of having users define advanced queries, consider pre-building datasets or templates that use your API.
- These templates can be distributed to customers, making integration even simpler.
- Leverage Power BI Embedded for Public Dashboards:
- For public dashboards, you could use Power BI Embedded, where your application manages the token generation and refresh securely.
By implementing these steps, you can provide a seamless, secure, and scalable solution for your customers. Let me know if you need further technical details on any of the steps!
Please mark this as solution if it hepls. Appreciate Kudos.
