There are different ways to get data into Excel for use with Power BI. You can also get data from a bunch of different sources.
You can use Excel Data Import, Power Query and Power Pivot to pull data in. Sometimes, you may receive errors when trying to connect to these data sources.
If it is an on premise data source, such as SQL, Oracle or DB2, the best thing to try first is to rule out Excel, or the add-in (Power Query/Power Pivot). In support, we typically use a Universal Data Link (UDL) file to try and test connectivity. If this works, and the items within Excel do not, then something may be wrong within Excel. But, if the UDL does not work, then we have something more generic and not related to Excel, Power Query or Power Pivot. In order to get the items in Excel working, the UDL should be able to function properly.
To create a UDL file, I start off with a new text document on the desktop.
Then I will rename that file to test.udl. Make sure you are showing file extensions within Windows. Otherwise your file will really be test.udl.txt. After you have renamed it, the icon will change.
If we double click on test.udl, we will see the Data Link Properties dialog which will default to the Connection tab. This also defaults to the Microsoft OLE DB Provider for SQL Server which is pretty old.
Depending on what you want to connect to, you will probably want to change the Provider to match the database you are connecting to. Even if it is SQL Server. For SQL Server, I recommend the latest SQL Native Client provider. If you were connecting to Analysis Services, you would want the latest provider for it. In this case, Microsoft OLE DB Provider for Analysis Services 11.0. The 11.0 is for SQL 2012.
The Connection tab will look different depending on the Provider you have selected. Going forward, I will show the dialogs for the SQL Server Native Clint 11.0. On the Connection tab, we will want to fill in the appropriate server information, followed by the authentication that we want to use.
For just testing connectivity to the server itself, I don’t usually fill in the Database, or click on the Drop down. When you click on the Database Drop Down, it will actually have to connect to the Server to get a list of Databases, so I just skip it and click on “Test Connection”.
If it failed, it will give you an error specific to the Provider you are using. Typically something like unable to connect, or server does not exist.
I won’t go into troubleshooting SQL Server Connectivity here. Instead, you can check out the blog posts at the PSSSQL Blog related to connectivity here.
So, what do we do for Cloud related data sources? Those will not be as easy to test outside of Excel. We will try and talk about things to look for with those within this blog site in later posts. One example is a blog I had posted on the PSSSQL blog before we had the Power BI Support Blog created related to trying to connect to a SharePoint List using OData.
Adam W. Saxton | Microsoft SQL Server Escalation Serviceshttps://twitter.com/awsaxton