In the last blog post in this series I covered how to install the Power BI – Data Management Gateway, now that you have the gateway setup we are ready to create a data source.
From the Power BI admin center web page go to data sources and click the plus sign to start the new data source wizard.
Select the type of data source, for this example I am going to select SQL Server
Data source usage
Select the type of usage that should be enabled
Provide a Name for the data source, select the gateway server we setup in the last blog post and then provide the local SQL server name and database name, then click on set credentials. This will download and start an application.
Note: you need to ensure you have connectivity to the SQL server for the set credentials step to complete.
Once you have entered the correct credentials, click test connection, once your credentials have been verified you will be able to click ok.
If using windows credentials the username needs to be in the domainusername format.
Then Click next.
You will see all of your tables and views in the database, deselect all selected tables and views and then just select the tables and views you need to publish via the Odata feed.
1. There is a recommend limit of 100 selected tables/views in a single data source
2. Notice how [apm].[Dates] is greyed out, this means you will not be able to publish the table or view to Power BI.
See the informational button at the top of the page for a breif expliantaiton
But what does this really mean and how do we fix the table or view if it is a custom table/view that we added to a database? See 5. b.
“Select tables/views that you want to be included in the OData feed by using the check boxes. You can choose to include all tables and/or all views to be included in the feed. The table/view must have a primary key or at least one non-nullable column. The data types of all the columns of the table/view must be supported by OData. See the supported data types section at the end of this topic. You will not be able to select a table/view that does not meet these requirements to be included in the OData feed.”
Users and Groups
Select users or groups that will be allowed to access the data from Power BI, these users will require a license to be assigned to them before they can access the data.
At this point you are ready to connect to your on premise data from excel or Power BI Designer.