Power BI – Creating a Data Source

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 wizrd.

clip_image001

Select the type of data source, for this example I am going to select SQL Server

clip_image002

Data source usage

Select the type of usage that should be enabled

clip_image004

Connection Info

clip_image005

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.

clip_image006

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 domain\username format.

Then Click next.

Data Settings

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.

clip_image008

clip_image010

Notes:

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.

clip_image012

See the informational button at the top of the page for a breif expliantaiton

clip_image013

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.

clip_image015

At this point you are ready to connect to your on premise data from excel or Power BI Designer.

Advertisements
This entry was posted in Power BI and tagged , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s