Adatis

Adatis BI Blogs

Direct Query vs Live Connection in Power BI – Part 2

This instalment of the Power BI blog series focuses on the two other differences between Direct Query and Live Connection in Power BI.  If you haven’t done so already, be sure to check out Part 1, which concentrated on Quick Measures and Relationships. Feature Differences Find below a Power BI Direct Query screen, focusing on the final 2 differences (highlighted green): o   New Hierarchy o   Change to Import Mode New Hierarchy Hierarchies are very useful with Power BI, especially when wanting to drill up and down within levels of data e.g. Day > Month > Year. Let’s try and create a New Hierarchy in Live Connection mode: New Hierarchy does not appear in the list of options. This is because hierarchies are typically created within the OLAP Cube, therefore, it makes sense why the option is not available.  However, I would argue that Direct Query allows you create a hierarchy, so why can’t Live Connection? Direct Query stores the hierarchies within a smaller Power BI model (Tabular Cube) running on a report developers local machine.  Ironically, Live Connection does exactly the same thing when you create an ad hoc measure in Power BI. It is surely a matter of time before hierarchies are also supported. Change to Import Mode Direct Query mode supports the ability to easily switch to Import Mode.  This is a useful option, especially for a self-serve analyst wanting to make transformations and shape the data.  By right clicking the highlighted option below, a simple wizard appears: When the import completes, the data (from tables in the database) will be stored in a Tabular cube on the local machine where Power BI Desktop is running.  If we navigate to the same area within Live Connection, notice there is no option to change to Import Mode (“click to change”: Once again, I am not really sure why this feature isn’t support in Live Connection mode.  I can understand why it would be more difficult to convert a Multidimensional Cube (MDX) into a local Tabular Cube (DAX), but if Live Connection already points to Tabular, it’s an exact copy.  To vote for Import Mode functionality within Power BI Live connection, click here. Coming Soon Part 3 is the final instalment of the blog series, specifically focusing on the underlying Power BI Data Models in Direct Query and Live Connection. Further Reading Other than the Power BI Blog, there are some other great pages out there too: o   Power BI Blog - http://bit.ly/2rkLGoq o   Import Mode vs. Direct Query - http://bit.ly/2t4ragx o   Direct Query in Power BI - http://bit.ly/2nUoLOG o   Live Connection in Power BI – http://bit.ly/2tfJr5L   Contact Me If you have any questions or thoughts, please leave a comment below.  My Twitter details are also provided. Twitter:                @DataVizWhizz

Direct Query vs. Live Connection in Power BI – Part 1

There are lots articles out there that compare Import Mode vs.Direct Query, but people rarely talk about if there are any differences between Direct Query and Live Connection.  “Wait. Aren’t they the same thing?”  Well, not quite. The first big difference between Direct Query and Live Connection is the type of data source used for the connection.  The former uses a database connection (typically SQL Server), whilst the latter requires an Analysis Services OLAP Cube. This blog series won’t explain what Direct Query and Live Connection can do (found here and here), but will instead highlight the other subtle differences between the two connections.  Feature Differences There aren’t any features I can find that are available in Live Connection and not Direct Query.  However, there are a few the other way around. I will first show you a Power BI Direct Query screen, focusing on the 2 of the 4 differences (highlighted green): o   Quick Measures o   Relationships Quick Measures This feature was first released in April 2017 and is available in Import Mode and Direct Query.  It enables a non-technical Business Analyst to create relatively complex DAX aggregations, with the use of a nice Wizard.  To access Quick Measures, right click on a measure or attribute and select Quick Measures.  Let’s try the same thing in Live Connection mode. You’ll notice that Quick Measures is missing from the list of options. I find it bizarre that Live Connection doesn’t support Quick Measures, especially when using a Tabular Cube as the connection.  The Power BI DAX language and engine are the same as Tabular, so you would think the two are compatible! Please vote for this feature to be added into Live Connection - http://bit.ly/2umsiJy. Relationships There are two tabs displayed on the left-hand pane in Direct Query mode. If you click the highlighted tab, it opens a Relationships page – where you can begin to join datasets (from the database) together.  I created a manual relationship that joined DimEmployee and DimDate together – as shown below.  No relationships are created in the underlying SQL Server database, but instead stored within the Power BI model. In Live connection, the left-hand pane looks bare: There is no option to create any form of Relationship against the Live Connection Tabular Cube.  This kind of makes sense because a BI Developer would be the person responsible for creating relationships within an OLAP Cube.  I would argue that if you want the ability to mashup data or create your own relationships, you shouldn’t be connecting to a Cube in the first place. Coming Soon Check out Part 2 of my blog series - available here.  The focus of this article are the Add Hierarchy and Change to Import Mode features. Part 3 will conclude the Trilogy, where I go off-piste slightly and focus on the Data Models in Direct Query and Live Connection. Further Reading Other than the Power BI Blog, there are some other great pages out there too: o  Power BI Blog - http://bit.ly/2rkLGoq o   Import Mode vs. Direct Query - http://bit.ly/2t4ragx o   Direct Query in Power BI - http://bit.ly/2nUoLOG o   Live Connection in Power BI – http://bit.ly/2tfJr5L   Contact Me If you would have any questions or thoughts, please leave a comment below.  My Twitter details are also provided. Twitter:                @DataVizWhizz