Power BI Exchange

Expand all | Collapse all

Direct query or Import?

  • 1.  Direct query or Import?

    Posted 12 days ago
    Hello. In power bi server version, there are two ways to get data. One is "direct query" which is able to work in database, another one is "import" . Which one is better to work, like edit data, use measures and add kinds of columns. Could you guys offer me one of them or describe these two ways' cons and pros? Thank you.

    ------------------------------
    Munkhtsetseg Bat-Erdene
    Analyst
    99294837
    ------------------------------


  • 2.  RE: Direct query or Import?

    Gold Contributor
    Posted 12 days ago
    It depends on situation.

    A good article showing pros and cons regarding both methods

    http://radacad.com/directquery-live-connection-or-import-data-tough-decision


    ------------------------------
    Farhan Ahmed
    Senior Business Intelligence Consultant
    Xerva
    karachi
    3452523688
    ------------------------------



  • 3.  RE: Direct query or Import?

    Posted 11 days ago
    Thank you :)

    ------------------------------
    Munkhtsetseg Bat-Erdene
    Analyst
    99294837
    ------------------------------



  • 4.  RE: Direct query or Import?

    Posted 12 days ago
    Hello Munkhtsetseg

    Live connection is faster if you connect your PBI dashboard to a data cube. You will be able to create Dax function only if it is a tabular cubo. If it is a multi-dimentional cube, dax function will no be enabled.  No power pivot options. With the live connection you don't need to set any refresh process in your PBI server because it will show you the last information available in the server.

    Import: it is a great option if you want to perform some power pivot data modeling or if you want to create dax functions. You can convine not just data comming from a cube but also from other sources and this option is not available in a live connection.
    It works slowly if you load too much data and you should set a refresh plan in PBI server if you want to see the most updated data.

    If you have all that you need (dimentions and measures) in a data cube. It is better the live connection. Faster and easy.

    If you wat to convine multiple data source and take advantage of dax and power pivot, you should select import.

    Regards.

    ------------------------------
    Gabriel Gómez
    Business Analysis Sr. Advisor
    Panamá
    62354955
    ------------------------------



  • 5.  RE: Direct query or Import?

    Posted 11 days ago
    Thank you :)

    ------------------------------
    Munkhtsetseg Bat-Erdene
    Analyst
    99294837
    ------------------------------



  • 6.  RE: Direct query or Import?

    Gold Contributor
    Posted 11 days ago
    I'd suggest looking at it this way. If you want to just represent a suitable data source using Power BI visualisations with little or no ​transformation of the data, then Direct Query (DQ) is your best option. As stated, you don't face the same issues with available refreshes and it's leaner and therefore faster than import. Plus there is far less storage impact on your Azure space; may not seem much of an issue initially, but once you build up a volume of work in Power BI this will become a factor. If you have control of your data source I'd suggest getting as much ETL done there, rather than in Power BI, if only for efficiency.

    If you have no control of your data source and know you have to do ETL, measures you need are not in the data etc., then import may be your only option.

    Worth remembering that if you select DQ, Power BI will advise you to convert to import if you try to do something DQ doesn't allow. But once you go past that point, you can't go back to DQ - you have to start again. If in doubt, there's no harm in starting off with DQ and letting Power BI convert you to import if you can't avoid it.

    ------------------------------
    Simon Hill
    Manager
    TTX Company
    Solihull
    ------------------------------



  • 7.  RE: Direct query or Import?

    Posted 11 days ago
    Thank you :)

    ------------------------------
    Munkhtsetseg Bat-Erdene
    Analyst
    99294837
    ------------------------------



  • 8.  RE: Direct query or Import?

    Posted 11 days ago
    ​PB Desktop will force you to you a import model if you transform or edit the data in the tables. When it comes to hosting it on the report server its all preference, import will put less stress on your server the data is querying from since you control when that import refreshes also you report will render faster. The opposite with direct query as it can put strain on your server since each time the report is opened it will query the server the data lives on and render slower. Its all a matter of preference and need.

    ------------------------------
    Michael Foss
    Functional/Business Systems Analyst
    ------------------------------



  • 9.  RE: Direct query or Import?

    Posted 11 days ago
    Here is Microsoft's recommendation: https://docs.microsoft.com/en-us/power-bi/desktop-directquery-about
    "In short, the best practice for using import versus DirectQuery is the following:
    You should import data to Power BI wherever possible. Import takes advantage of the high-performance query engine of Power BI, and provides a highly interactive and fully featured experience over your data.
    If your goals can't be met by importing data, then consider using DirectQuery. For example, if the data is changing frequently and reports must reflect the latest data, DirectQuery may be best. However, using DirectQuery is generally only feasible when the underlying data source can provide interactive queries (less than 5 seconds) for the typical aggregate query, and is able to handle the query load that will be generated. Additionally, the list of limitations that accompany use of DirectQuery should be considered carefully."

    Importing data:
    Pros: Access to the full feature-set of PowerBI; best performance (whether Desktop or published to PowerBI.com) assuming proper model design.
    Cons: Scheduled refresh limited to 8x per day (48x if on Premium); PowerBI.com size limitation of 1GB per PBIX; potential data size limit depending on the amount of RAM on your machine; potential security risk if someone else gets their hands on the PBIX as the data "resides" in the PBIX.

    Direct Query:
    Pros: Always gets the latest data - don't have to worry about scheduled refresh; can handle very-large datasets; useful if your organization is not comfortable pushing the data to the cloud.
    Cons: Performance - there is always a lag because of the trip needed back to the source and potential complexity of the underlying M code; limited feature-set depending on data source (e.g. not all DAX functions are available in Direct Query).

    Summary:
    I'd go with Import as often as possible.
    There may be cases where you must use Direct Query including if the data size is simply too big to import into PBI, you want to use RLS at the source, or when you needed up-to-date results as opposed to 8 refreshes a day (or 48 on Premium).  Live Connection should be used when you're querying an SSAS model/cube - you'd rarely (if ever) want to import from an SSAS model.


    More on Direct Query: https://docs.microsoft.com/en-us/power-bi/desktop-use-directquery

    ------------------------------
    Eric Lofstrom
    Principal
    BlueGranite Inc.
    elofstrom@blue-granite.com
    ------------------------------



  • 10.  RE: Direct query or Import?

    Bronze Contributor
    Posted 11 days ago
    Hi @Munkhtsetseg Bat-Erdene, when you write "Power BI server version", what exactly are you referring to? Is it the Power BI service in the cloud (www.powerbi.com) or the on-premises SQL Server Reporting Service with Power BI server?

    The cloud and the on-prem offerings have a similar name but they work in distinct ways.

    Let me know.​

    ------------------------------
    Armando Lacerda
    DesignMind
    San Francisco CA
    415-226-0998
    ------------------------------



  • 11.  RE: Direct query or Import?

    Bronze Contributor
    Posted 10 days ago
    Hi @Munkhtsetseg Bat-Erdene,

    Got it you message. So when you write "Power BI server version" you are actually referring to "Power BI Desktop". Thanks for the clarification.

    Here is my 2 cents:

    Power BI Desktop is your design tool. At the end of the day you will be publishing your dataset and your report to Power BI Service in the cloud.

    In order to benefit from all DAX and dataset features in Power BI you must use import. That will make Power BI copy the data from the original sources into itself and allow you to do all and any kind of calculations in DAX and all other features in the product.

    When you choose "Direct Query" ​you are giving up all data capabilities built-in Power BI and using it just for the visuals. It is a valid choice in many scenarios.

    In summary: unless it is a specific / particular case you should use Import.

    Regards,

    ------------------------------
    Armando Lacerda
    Senior Cloud and BI Architect
    San Francisco CA
    ------------------------------



  • 12.  RE: Direct query or Import?

    Bronze Contributor
    Posted 9 days ago
    Hi @Munkhtsetseg Bat-Erdene,

    Agree with @Armando Lacerda​.  While it completely depends on a specific scenario or use-case, based on your description of what you want to try you should go with the Import option.

    Regards,

    ------------------------------
    Manthan Patel
    ------------------------------



  • 13.  RE: Direct query or Import?

    Posted 6 days ago
    Hi,
    Direct query is usefull for real time reports and dashboards.
    you have to remember that now Power BI support hybrid queries, the same table can be loaded in memory and in direct query.
    this is usefull when you have to link data like SQL Server data with excel documents, the in memory will support this, while you can still have real time data when you dont have to display the excel content for example.

    but...
    make sure your source system can handle the queries. if you are connected to an oltp system which is fullyloaded, your queries may impact the performance.
    if you have a database optimized for querying, then no problem, havign a dedicated server is even better, but always monitor the source database.
    if you dont have the proper rights to monitor the activity of your server, better to go in import mode to have a consistent performance.

    ------------------------------
    jerome p
    5555555555
    ------------------------------