In this series, you will learn how to add a\u00a0Power BI License to an Office 365 subscription<\/a>, add new data sources, and share your report from a SharePoint online document library<\/a>.<\/em><\/span><\/p>\n In Part 1, we were able to connect our On-Prem SQL Server to Power BI. In this post, we will now connect our data source (in this case a SQL database) to Power BI so we can run some reports:<\/p>\n Our first step will be to add a new data source from the Power BI admin center. In order to do this, simply click on the \u201cdata sources\u201d button on the Power BI Quick Launch, and then click the + sign to add a new data source. For this example, we will click on \u201cSQL Server.” With the latest release of Power BI, we can now also add Oracle databases, SharePoint document libraries, or even custom Power Queries you may have written.<\/p>\n From here, we can now specify the data source connection information. Our first step will be to define how the data source will be utilized. There are three\u00a0options that can be selected in this part of the wizard:<\/p>\n <\/p>\n Our next step will be to define the connection information for this data source. We will want to provide a name and description, choose the gateway we created in the first part of this blog post, specify the data source type (in this case, SQL Server), and define our connection properties. You can either use the connection properties dropdown to define the connection, or if you are more advanced, define the entire connection string. For this example we will just use the dropdown to define the connection properties. Before continuing though, we will also want to define the credentials used by this data source to connect to the database.<\/p>\n When defining the credentials to use for this data source connection, there are a few things we want to make sure of:<\/p>\n Once you have defined the credential settings, click “test connection” to ensure everything is working properly, and click OK to continue the wizard.<\/p>\n Since for this example we are defining an OData connection, we have the option to only expose specific tables and views as a part of the Odata feed. Choose the tables and views appropriate for your needs to expose, and click “Next.”<\/p>\n Finally, we need to define the users who will have access to utilize this data source. These users will need to have an Office 365 account on your tenant. I highly recommend defining a group\/groups and adding your user permissions that way. It always makes administrative work that much easier in the future.\u00a0 When your users and groups have been defined, click “Finish.”<\/p>\n We now have our defined data source ready to be accessed! Now let\u2019s connect and create some reports.<\/p>\n Now that the data source is in Power BI,\u00a0 our first step will be to find out the name of our OData EndPoint. To do this, we need to go to the \u201cMy Power BI\u201d page, which will provide you with a dashboard of what has been defined for you for Power BI.\u00a0 To access it, you need to click the \u201cPower BI\u201d icon on the top links bar of Office 365:<\/p>\n To get the URL of our endpoint, we just need to click on \u201cData Sources\u201d on the Quick Launch, click on \u201cData\u201d on the top link bar, and change our view to \u201cshow : all data sources.” We can see the URL in the location column. Let\u2019s copy and paste it so we can quickly add it in when we move to Excel. The easiest way to do this, is to click on the “\u2026”(ellipsis), which\u00a0will open a box that\u00a0has our full URL selected.<\/p>\n Now that we have the OData feed endpoint, let’s connect and create a simple report. Open Microsoft Excel, and click on the “Power Query” tab. In the \u201cGet External Data\u201d area of the ribbon, click \u201cFrom Other Sources,\u201d and then click \u201cFrom OData Feed.” This will open a box where you can paste in the URL that we copied earlier. That\u2019s it! You will now be presented with all of the tables from your Odata feed, and you can select each one that you would like to pull information from.<\/p>\n In the picture below I have put together a quick example of a pivot table and pivot chart using data from a table from the database. I can customize and define the report using any of the features in Excel, and now I can also define my refresh settings. This will allow you to refresh the report when it\u2019s opened, or on a timed interval!<\/p>\n So far, this really has been pretty easy to set up. In the next part of this series, I\u2019ll show you different options to publish and share your reports.<\/p>\n <\/p>\n Other published posts in this series:<\/strong><\/p>\n Learn how to add a Power BI License to an Office 365 subscription. The second in a three-part series on Power BI.<\/p>\n","protected":false},"author":86,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"_oasis_is_in_workflow":0,"_oasis_original":0,"_oasis_task_priority":"","_relevanssi_hide_post":"","_relevanssi_hide_content":"","_relevanssi_pin_for_all":"","_relevanssi_pin_keywords":"","_relevanssi_unpin_keywords":"","_relevanssi_related_keywords":"","_relevanssi_related_include_ids":"","_relevanssi_related_exclude_ids":"","_relevanssi_related_no_append":"","_relevanssi_related_not_related":"","_relevanssi_related_posts":"","_relevanssi_noindex_reason":"","footnotes":""},"categories":[1],"tags":[],"coauthors":[],"acf":[],"publishpress_future_action":{"enabled":false,"date":"2024-07-22 00:52:59","action":"change-status","newStatus":"draft","terms":[],"taxonomy":"category"},"_links":{"self":[{"href":"https:\/\/centricconsulting.com\/wp-json\/wp\/v2\/posts\/12587"}],"collection":[{"href":"https:\/\/centricconsulting.com\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/centricconsulting.com\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/centricconsulting.com\/wp-json\/wp\/v2\/users\/86"}],"replies":[{"embeddable":true,"href":"https:\/\/centricconsulting.com\/wp-json\/wp\/v2\/comments?post=12587"}],"version-history":[{"count":0,"href":"https:\/\/centricconsulting.com\/wp-json\/wp\/v2\/posts\/12587\/revisions"}],"wp:attachment":[{"href":"https:\/\/centricconsulting.com\/wp-json\/wp\/v2\/media?parent=12587"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/centricconsulting.com\/wp-json\/wp\/v2\/categories?post=12587"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/centricconsulting.com\/wp-json\/wp\/v2\/tags?post=12587"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/centricconsulting.com\/wp-json\/wp\/v2\/coauthors?post=12587"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}![\"1\"](\"https:\/\/centricconsulting.com\/wp-content\/uploads\/2018\/07\/1.png\")
<\/a><\/p>\n\n
![\"2\"](\"https:\/\/centricconsulting.com\/wp-content\/uploads\/2018\/07\/2-1024x578.png\")
<\/a><\/p>\n![\"Adding](\"https:\/\/centricconsulting.com\/wp-content\/uploads\/2018\/07\/3-847x1024.png\")
<\/a><\/p>\n\n
![\"4\"](\"https:\/\/centricconsulting.com\/wp-content\/uploads\/2018\/07\/4-693x1024.png\")
<\/a><\/p>\n![\"Adding](\"https:\/\/centricconsulting.com\/wp-content\/uploads\/2018\/07\/5-1024x819.png\")
<\/a><\/p>\n![\"Adding](\"https:\/\/centricconsulting.com\/wp-content\/uploads\/2018\/07\/6-1024x550.png\")
<\/a><\/p>\n![\"7\"](\"https:\/\/centricconsulting.com\/wp-content\/uploads\/2018\/07\/7-1024x531.png\")
<\/a><\/p>\nConnecting to the Data Source<\/h3>\n
![\"Adding](\"https:\/\/centricconsulting.com\/wp-content\/uploads\/2018\/07\/8-1024x43.png\")
<\/a><\/p>\n![\"Adding](\"https:\/\/centricconsulting.com\/wp-content\/uploads\/2018\/07\/9-1024x254.png\")
<\/a><\/p>\nConnecting Excel to your new OData Feed<\/h3>\n
![\"PoweBI_2_Pic_10\"](\"https:\/\/centricconsulting.com\/wp-content\/uploads\/2018\/07\/PoweBI_2_Pic_10.jpg\")
<\/a><\/p>\n![\"Adding](\"https:\/\/centricconsulting.com\/wp-content\/uploads\/2018\/07\/10-1024x500.png\")
<\/a><\/p>\n\n