locked
Power BI for Office 365 > How to connect to the Data Source? RRS feed

  • Question

  • Hello,

    I am new to Power BI and would like to understand one simple point - How to connect to the Data Source?

    I have got a Gateway defined in the Power BI Admin Center for a on-premises SQL Database. The Gateway is up & running.

    Now, I would like to connect to this SQL Database from my Excel file sitting on my local machine. Please note that my local machine can not communicate with the said SQL Server machine.

    How could I do this?

    I have tried connecting from Power Query, Power Pivot and also from the Data tab > connections, but failed from all three ways. I am still not sure how could I select that SQL Database as my data source.

    Is it mandatory to have a LAN or VPN or something setup so that my excel file could communicate with the SQL Server and then once I am done with designing the Data Models & the Power View, I would upload the file on the Power BI Site for others to view it.

    Please guide.

    Thanks!


    -Vinay Pugalia
    If a post answers your question, please click "Mark As Answer" on that post or "Vote as Helpful".
    Web : Inkey Solutions
    Blog : My Blog
    Email : Vinay Pugalia

    Sunday, August 2, 2015 8:47 AM

Answers

  • As the article you linked to states, you need to be able to access the machine running the Data Management Gateway in order to access the OData feed. So you either need access to the data source itself, or the DMG.

    Ehren

    Monday, August 17, 2015 5:51 PM

All replies

  • Unlike connecting to a local data source from the Gateway, connecting to a local data source from PQ in Excel shouldn't require any special setup. Have you tried the following?

    https://support.office.com/en-us/article/Connect-to-a-SQL-Server-database-Power-Query-e5004318-0f2e-46a3-8b15-1559aa3c04db

    Ehren

    Tuesday, August 4, 2015 3:18 PM
  • Hello Ehren,

    I understand that connecting to a local Data Source from PQ is NOT a problem. However, in my case the problem is that the Data Source is not local.

    So, what are the possible options in which the connection could be made to the Data Source?

    Please guide.

    Thanks!


    -Vinay Pugalia
    If a post answers your question, please click "Mark As Answer" on that post or "Vote as Helpful".
    Web : Inkey Solutions
    Blog : My Blog
    Email : Vinay Pugalia

    Tuesday, August 11, 2015 4:28 AM
  • In that case, you'll need to expose the data source as an OData feed. See this article for more info:

    https://support.office.com/en-us/article/Create-a-Data-Source-and-Enable-OData-Feed-in-Power-BI-Admin-Center-9dbd3091-8961-4564-adf6-2a3aeff0b2b4

    Ehren

    Tuesday, August 11, 2015 5:41 PM
  • Hello Ehren,

    Thanks for the reply.

    I have already tried with oData Feed but it too did not help.

    I did search on it further, and had landed on this link which clearly mentions in it's NOTE (3rd parah from begining) that even for oData Feed to work, the connecting excel file should be in same corpnet. Below is the content quoted from that link - 

    NOTE: The Power Query client must be located in the same corpnet with the machine hosting the Data Management Gateway; otherwise, the Power Query client cannot gain access to the data included in the OData feed.

    Please guide if my understanding is correct or there is still something very obvious which I am missing.

    Thanks!


    -Vinay Pugalia
    If a post answers your question, please click "Mark As Answer" on that post or "Vote as Helpful".
    Web : Inkey Solutions
    Blog : My Blog
    Email : Vinay Pugalia


    Wednesday, August 12, 2015 4:11 AM
  • As the article you linked to states, you need to be able to access the machine running the Data Management Gateway in order to access the OData feed. So you either need access to the data source itself, or the DMG.

    Ehren

    Monday, August 17, 2015 5:51 PM