Callum

Callum Green's Blog

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

clip_image002

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.

clip_image003

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.

clip_image004

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.

clip_image006

In Live connection, the left-hand pane looks bare:

clip_image008

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

New Features in SQL Server 2016 – Part 2: Analysis Services

This blog solely focuses on the new Analysis Services features of SQL Server 2016 CTP2.  For anyone who may missed it – click here to view my opening blog on the Database Engine.

Although there have not been any major Analysis Services (SSAS) enhancements in CTP2, it is pleasing Microsoft are still looking to improve this part of the BI Stack.  The majority of them seem to be geared towards Tabular and DAX, although there are plans to release further Multidimensional functionality in CTP2 and beyond.

There are five key enhancements for SSAS:

1.      Tabular Model Partitions (Tabular).

2.      Many 2 Many (Tabular).

3.      Analysis Services PowerPivot mode.

4.      New DAX Functions (Tabular).

5.      Enable/disable attribute hierarchies.

Tabular Model Partitions
SQL Server SSAS 2016 CTP2 includes new parallel processing functionality for tables with two or more partitions, increasing processing performance. There are no configuration settings for this.  More information on this feature is displayed below:

1.      IN SSAS Tabular, partitioning big data marts will help drastically when processing new data.  Instead of having to process all data, you can just partition the relevant ones.

2.      In order to create, manage, and process partitions in SQL Server Management Studio, you must have the appropriate Analysis Services permissions defined in a security role.

3.      Parallel processing is also available in SSAS.  This happens automatically, when processing a table with more than one partitions.  Although you can choose to independently process a partition if required.

Many to Many Dimensions (Tabular)

There isn’t too much to show or say here yet apart from the fact that this feature is FINALLY HERE!  Gone are the complications of workarounds (thanks to Marco Russo and Alberto Ferrari), we can now just use the Many to Many functionality in Tabular.  Multidimensional already has this feature, which is one of the reasons why developers have yet to buy in to Tabular. 

Unfortunately, Microsoft have yet to reveal much detail on to how this feature will work.  I just hope it performs as well as the current workarounds.

Analysis Services PowerPivot mode

To install PowerPivot for SharePoint is now a lot easier.  If you already know how to configure SharePoint 2013, this will help you greatly. 

Taken from the MSDN Microsoft website, you simply use the Install Wizard and do the following:

1.      Select ‘Analysis Services’ from the standard Instance Feature.

2.      Choose ‘Analysis Services server mode’ and configure administrators (screenshot below).

clip_image002[4]

 

New DAX Functions

There are many new DAX functions, which are all detailed on MSDN website – click here for further detail.

I have picked out 5 functions that will be highly beneficial when creating an SSAS Tabular cube.  I am looking at this form a very BI focused background so I would recommend looking at all of the new functions to see if they benefit your needs.

1.      TOPn

a.      This is not actually a new function but has been updated.

b.     Now allows TRUE/FALSE/ASC/DESC to specify sorting direction.

2.      CALENDAR

a.      Returns a table with a single column named “Date” that contains a common set of dates. The range of dates is from the specified start date to the specified end date.

b.      See Similar – CALENDARAUTO

3.      MEDIAN

a.      Very simple – returns the median numbers in a column.

b.      See Similar – MEDIANX (uses an expression).

4.      NATURALINNERJOIN

a.      Inner join of a table with another table. The tables are joined on common columns in the two tables. If the two tables have no common column names, an error is returned.

b.      See similar – NATURALLEFTOUTERJOIN

5.      SUMMARIZECOLUMNS

a.      Enables you to group data together and return a summary table.

Enable/Disable Attribute Hierarchies

Again, this functionality is tailored towards SSAS Tabular.  There is a new setting that ties back to the Tabular mode metadata

1.      ColumnUsage property

a.      DAXUsage

                                                    i.     Attribute hierarchies can only be used in measures.

b.     UnrestrictedUsage

                                                    i.     Can be set in the XMLA (in SSDT) or by using an ALTER statement in the Tabular model.

                                                   ii.     An example taken from MSDN:

       <Alter>

  <ObjectDefinition...>

    <Database>

      <Dimensions>

        <Dimension>

          <Attributes>

            <Attribute>

               <ddl500:ColumnUsage value=”ddl500_500”>

               DAXUsage | UnrestrictedUsage

               </ddl500:ColumnUsage>

 

Multidimensional Features

The full SQL Server 2016 release will include some key enhancement to Multidimensional SSAS.  These include:

1.      Netezza as a Data Source (Netezza Data Warehouse | IBM - NDM Technologies).

2.      General performance improvements.

3.      Unnatural hierarchies.

4.      Distinct counts.

5.      DBCC support. Checks the logical and physical integrity of objects in the specified database.

6.      Expose on-premises multidimensional cubes in the cloud with Power BI.

I will provide further information on the improvements, as and when Microsoft announce them.

References

For more information on all of the new SSAS SQL Server 2016 features, the below resources/blogs are highly recommended.

·        Official Microsoft Page -
https://msdn.microsoft.com/en-us/library/bb522628(v=sql.130).aspx

·        Brew your own Business Intelligence Blog –

http://byobi.com/blog/2015/05/ssas-related-enhancements-in-sql-server-2016/

·        Jorg Klein SQL Server Blog -
http://sqlblog.com/blogs/jorg_klein/archive/2015/05/22/bi-on-your-terms-with-sql-server-2016.aspx