I’ve seen a few cases where customers are getting the following error when trying to setup a Scheduled Data Refresh within Power BI, and not sure what to do.
OnPremise error: Sorry, the data source for this data connection isn’t registered for Power BI.
This can show up either when we are trying to test the connection, or when trying to actually refresh the workbook.
There is confusion around this as some customers believe that they have setup the Data Source correctly within the Power BI Admin Center, but in actuality we may not have. I want to go through and show how to determine if they match or not, and what needs to be set.
Let’s start with the Excel Workbook. We can go to the Data Tab within Excel. We can either go to Existing Connections, or just Connections.
We only have one connection called mycube. If we go to properties of that, and go to the Definition tab, we can see the following connection string.
Here we can see the connection, and we also see that the type is Model OLE DB Query. The fact that the type is that, and the connection string is grayed out means that this is a connection for Power Pivot.
In the connection string itself, we can see the following pieces:
These are the three items we care about. Our Provider is for SQL Server Native Client 11 in this case. That comes with SQL 2012. Data Source is our Server Name and Initial Catalog is our Database name.
Power Pivot Connection
From above, we saw it was grayed out and showed a type of Model OLE DB Query, which means it is a Power Pivot Connection. We can go to the Power Pivot Window, and look at the existing connections and see that it matches what we saw in Excel.
Power BI Data Source
On the Power BI side, we can go to the Power BI Admin Center, and then to the Data Sources section and look at what was created there.
We can see that we have one data source called mycube. When we edit that data source, we can see the following for the connection info.
I’ve had comments from folks that they thought if the Data Source name matched the connection name in the workbook, that would be how we matched it up. We actually don’t use the data source name for the matching. You can call the Data Source whatever you want. It is just a friendly name which is used as a label.
The actual matching comes down to three things. The provider, the server name and the database name. To illustrate this a little better, we can click on Connection String and see the actual connection string for the Data Source.
In this case, the Data Source value in the connection string is the server name. The Initial Catalog is the database name. If we compare the connection string in the Excel Workbook, to the connection string for the Power BI Data Source, we can see that the server name/data source do not match.
Excel shows it as being Cube1433, but the Power BI Data Source shows it as mycube. This is why we are getting the error indicating that the Data Source wasn’t registered within Power BI.
We can change the Data Source within the Power BI Data Source Connection String to Cube1433 and save that. When we go back to test the connection, it now succeeds.
Then saving and refreshing the report, this shows successful as well.
We just need to be very specific and intentional about the connection string within the Power BI Data Source. It has to match what we have for the connection within the Workbook.