Forum Discussion

Chrisp22o's avatar
Chrisp22o
Frequent Visitor
9 years ago

ODBC connection - Large table

Hi,   I am trying to import a large table from a database that I connect to using an ODBC connection.  I am finding that when I try to query the table and also refresh, it is taking a considerable ...
Need Help
Anonymous's avatar
Anonymous
Not applicable
9 years ago

Lots of variables. If the data source provides a Direct Query connection, then you are good. These are pretty well outlined in the data connections in the Power BI data connections. ODBC is the fall back connector for when a more native one is not available.

 

And are you talking about importing all that data into your Power BI desktop file? In that case, I would say that the primary factor is how long it takes to get the same ODBC data connection flowing into PowerBI.com. That should be much faster and also happening in the background (thank you Gateway!) so that you should not really notice or care (e.g. if the data is updating every two hours or so, the data update will all happen behind the scenes and without you need to be monitoring it.

 

Hope this helps.

Scott

  • Chrisp22o's avatar
    Chrisp22o
    Frequent Visitor
    9 years ago

    Hi Scott,

     

    Thanks for your reply.  I could not see the option to add a Direct Query connection from an ODBC source, you can do from SQL Server. 

     

    Im selecting the table to import but then select Edit in the wizard and use the filters on the necessary columns to try and replicate my SQL statement that I previously used for this type of report in Crystal Reports.  I have yet to install the Gateway on one of my servers but when I do as you mention it should run in the background.

     

    Regards,

    Chris

    • v-qiuyu-msft's avatar
      v-qiuyu-msft
      Community Support
      9 years ago

      Hi Chrisp22o,

       

      In Power BI Desktop, DirectQuery mode is not available in ODBC data source. You can refer to this article to see supported data source in DirectQuery mode: Use DirectQuery in Power BI Desktop.

       

      As Progress database is not supported in Power BI Desktop directly, you can use ODBC to connect to Progress database instead. It means we can only use Power BI desktop to get Progress database data in Import mode.

       

      After you publish the report to Power BI Service, you need to create a ODBC use the same connection as in desktop under data gateway. Then set the dataset of this report to use this ODBC data source. See:

       

      On-premises data gateway

      Manage your data source - SQL Server

       

      In addition, please note the maximum file size you can import into Power BI Service is 1 gigabyte.

       

      Best Regards,
      Qiuyun Yu

    • Anonymous's avatar
      Anonymous
      Not applicable
      9 years ago

      Just to confirm, is this all using Power BI Desktop? And what database are you connecting to? e.g. SAP HANA? SQL Server? 

      • Chrisp22o's avatar
        Chrisp22o
        Frequent Visitor
        9 years ago
        I'm using Power BI desktop with a Pro licence. I'm using my odbc connection to connect to a Progress database