PowerBI.tips

Tag: Direct Query

  • Power BI Connections: Direct Query

    Power BI Connections: Direct Query

    Now that we’ve solidly gotten through the basics in terms of what connection types are in the opening blog, found here, and detailed out what is included in the default connection type of Import found here, let’s get on with some of the more interesting connections.

    Connection Type Outline
    Connection Type Outline

    Direct Query is the first connection type that we will discuss that extends, but at the same time limits functionality in the tool itself. In terms of data access this connection type allows us to access our data in the source system. The supported data sources for direct query can be found here. This is distinctly different than what we observed in the import method.  When using Import, the data is a snapshot and refreshed on a periodic basis, but with Direct Query it is live. “Live” means that with Direct Query the data stays in the source system and Power BI sends queries to the source system to return only the data it needs in order to display the visualizations properly. There are some pros and cons in using this connection so it is important to understand when you might use it, and when you should probably avoid this connection.

    Cons:

    • When Direct Query is used you can no longer do many of the data mashup actions in the “Edit Queries” section of Power BI. It is assumed that you will have already done this in the backend. You can do simple actions such as removing columns, but don’t expect to be able to manipulate the data much. The Query Editor will allow you to make transformations, but when you try to load to the model you will most likely get an error that looks something like this
    Direct Query Error
    Direct Query Error 
    • The data tab is also disabled in the model layer and thus you need to make sure that all the formatting and data transformations are completed in the source.
    Data Tab no longer present
    Data Tab No Longer Present
    • You can do some minor adjustments to format, but this could be a heavy restriction if you don’t have access to the data source.
    • There are performance impacts to the report that need to be taken into consideration. How large is the audience that will be interacting with the report? How busy is the source system, are there other processes that could be impacted?
    • Troubleshooting skills in source system language
    • Multiple applications required to adjust data ingestion and formatting

    Pros:

    • The Direct Query connection does not store any data. It constantly sends queries to the source to display the visuals with the appropriate filter contexts selected.
    • In the November 2017 release there is a new capability in Power BI allows you to reduce the traffic and enhance this connection method exponentially. The feature is called Query reduction, and allows you to enable an “apply” button on a slicer or filter. The benefit with this option is that you can set all your selections of a filter prior to Power BI executing the query. Before this feature was available, every selection you made would fire off a query to the source database. To enable this feature, go to File -> Options and Settings -> Options -> Query Reduction you will find these options to help with Direct Query Performance.
    Query Reduction Options
    Query Reduction Options

    Note: This enhancement greatly increases the performance of a Power BI report against the data source, but be aware that there could be poor query performance, or aspects of the solution that would require troubleshooting in the data source depending on what queries are being passed. This would require understanding of how to performance tune the source.

    • Deployment of the Direct Query connection requires the use of the gateway previously called the Enterprise Gateway. Note that the Enterprise Gateway is different than the personal Gateway.
    • No data is ingested into the model using Direct Query thus, there is no need to schedule a refresh. Once the dataset is connected to the Gateway, the data source feeds information to the report as the user interacts with the report.
    • It will always show the latest information when you are interacting with the report.

    Direct Query is a powerful connection type in that it produces the most up to date data. However, as we have seen, it does come with some considerations that need to be taken into account. The Pros and Cons of the connection mostly revolve around whether or not the end user can understand and deal with potential performance issues, updating data retrieval processes, and understand the downstream implications of a wider audience. Typically, Direct Query is used in extremely large datasets, or in reports that require the most up to date information. It will most likely always perform slower than an import connection and requires an understanding of tuning and troubleshooting of the data source to alleviate any performance issues.

  • Power BI Connection Types

    Power BI Connection Types

    Get Data – Power BI Connection Types: An Introduction

    Hi, I’m Seth, I am very excited to be a contributing on PowerBI.tips.  Mike has done an incredible job curating fantastic content for the PowerBI Community.  In this first blog I will introduce you to the different types of connections that you can make using the Power BI Desktop. We will identify the various types of connections.  In future posts we will dive into specific examples of usage and tips in tricks.

    When I say “Types”, I don’t mean connecting to databases, Excel, SharePoint, etc. Those are just different data sources. I’m referring how Power BI ingests or interacts with data sources that you want to connect to. Believe it or not, Power BI doesn’t always have to pull all your data into the Power BI Desktop file. Depending on what sources of data you are connecting to, you could not even realize that there are more options, or be uncertain of what they do. In fact, depending on what type of connection you choose you are also altering how the Power BI Desktop functionality works! Now that I have your attention, let’s jump into the good stuff.

    First things first. The only time you will be faced with an option to choose a type of connection, are when you connect to a data source that support multiple connection types. If all you connect to is Excel, you would never see an option in the dialogues because it only supports one type of connection.

    There are really 3 main types of connections. The first is the most widely used, and is the default when connecting to most data sources. It is Import. This connection will ingest or pull the data from the data source and become part of the PBI Desktop file.  An example of where you would select import Is in the SQL Server dialog box.

    SQL Server Import
    SQL Server Import

    You can import data from a SQL Server by clicking Get Data on the Home ribbon.

    Get Date SQL Server
    Get Date SQL Server

    The import connection type allows you to use the full capabilities of the Power BI Desktop and you can manipulate it however you see fit. A way to validate this is by looking at the left-hand navigation and you will see three selections.  The top selection which resembles a bar chart is the Report Page.  This is where you would place all your visuals and develop your report pages.  The second item from the top, which looks like a table is just that, the Data view in a table form.  This lets you see all the data contained with a loaded data table.  Finally, at the very bottom, the relationships selection.  This is where you will see multiple tables and the connections between the tables.  The relationships section feels like working SQL or in Microsoft Access.

    Import Options
    Import Options

    The 2nd connection type is Direct Query.  Notice in direct query mode the third item, relationships has been removed.  The direct query connection type is only available when you connect to certain data sources. The list of the data sources that are accessed using direct query can be found here.  This connection is unique in that the data does not get loaded into the PBI Desktop.  What happens, is that Power BI can communicate in the language of the data source and request information as you interact with your Power BI Visuals. The useful thing about this connection is that the data never leaves the data sources, it is only queried.  Direct Query does limit what you can do from a data manipulation perspective.  Power BI assumes you are already doing all the necessary data manipulations in your source. As a result, you don’t even have the option to mashup data and that selection is removed in the left-hand nav.

    Direct Query
    Direct Query Options

    The 3rd type is Live Connection. There are only 3 data sources that support the live connection method at this time.  All of them are a type of (SSAS) SQL Server Analysis Services. Those types are Multidimensional, Azure Tabular and Tabular on premises. The live connection type is the most unique in that it recognizes the full model or cube that you’ve created.  Power BI Desktop turns off all data prep features.  Thus, the user is given a bare minimum in formatting and report side calculations.  All the heavy lifting is done on the server that supports the model and Power BI is only used as a reporting tool. This connection is used mainly by IT and enterprise implementations. If one looks at the left-hand navigation, you quickly realize that it is the most restrictive in terms of what can be done in the Desktop itself.

    There is a fourth Live Connection that defaults to the connection type, and this occurs when you use the Power BI Service as a data source. This connection is using a SSAS connection, only the end users don’t need to set anything up other than having dataset to connect to in the Service.

    Live Connection Options
    Live Connection Options

    Finally, there are two types of connections that dive a bit deeper than what comes with the Desktop out of the box. Those are Custom Data Connectors and API/Streaming. For the time being, we’ll leave these as just high-level points for now, and dive deeper into them in the specific articles in the future.

    I hope you’ve found this initial primer useful. As this series continues we’ll dive into some of the reasons for using each of these types of connections, why you would want to, and the positives and negatives in choosing which one, provided you have a choice.