Once you have a Power BI gateway setup to ensure data flow from your on-premises data sources to Power BI service in the cloud, next step is to create reports using Power BI desktop and build reports using data from multiple on-premises data sources.
Note: If you didn’t have a gateway setup already, please follow my earlier post to set it up before you continue reading this post.
All on-premises data is stored in SQL server instances and spread across few data warehouses and multiple databases built and managed by your internal IT teams.
Before building reports, you need to ensure following key points:
- Each data source should be having connectivity to your gateway with minimum latency, this should be ensured.
- Every data source intended to be used within reports needs to be configured within a gateway in the Power BI service
- List of people needs to be configured against each data source who can publish reports using this data source
An interaction between on-premises data sources and cloud services is depicted below:
Before you build reports, you need to setup on-premises data sources in the gateway to ensure Power BI service knows which data sources are allowed by gateway administrator to pull data from on-premises sources.
Login into https://app.powerbi.com with Power BI service administrator service credentials.
- Click on Manage gateways to modify settings
- You will see a screen with gateway options that your setup earlier while configuring gateway on-premises
- Next step is to setup gateway administrators, who will have permission to setup on-premises data sources as and when required
- After gateway configuration, you need to add data sources one by one so published reports can use on-premises data sources (pre-configured within gateway)
- You need to setup users against each data source within a gateway who can use this data source to pull data from on-premises sources within their published reports
- Repeat above steps for each of your on-premises data sources by selecting appropriate data source type and allowing users who can use them while building reports
Upon reaching this step, you are all good to create reports.
- Open Power BI desktop
- Select sources you want to retrieve data from
- Just ensure while creating reports, data source details are same as what was configured in Power BI service while you were setting up data sources.
- Great! once you publish reports to your Power BI service – your gateway will be able to connect to relevant on-premises data sources if you have followed steps above.