Callum

Callum Green's Blog

DAX Calculated Tables in Power BI

Calculated Tables have been around for a while in Power BI, but today I found a real life scenario for using them. I connected to Manchester United’s Facebook page and plugged the data into Microsoft’s Cognitive Services. In essence, I want to measure Sentiment Analysis and find out how many times a supporter has mentioned one of Manchester United’s rival football teams.

You are probably wondering what this has to do with a DAX Calculated Table, so let me explain.  I have pulled down the Facebook data (from the API), but when trying to undertake a GROUP BY for Likes, Loves, etc. in the Query Editor, Power BI hangs and the query never resolves.  Whilst I cannot pinpoint exactly why this happens, I would guess that the number of API calls to Facebook are exceeded and some form of timeout occurs.

This blog will walk you through how to create a DAX Calculated Table and apply a Group By to get the count of reaction types.  There are a number of articles already out there showing examples of a Calculated Table and I have provided the links at the bottom of the post.

Existing Query

Currently, my query looks like the below:

clip_image002

The only remaining task is to apply a COUNT of all records, GROUPED BY Reactions.Type and id.  If I try and use the Query Editor functionality within the UI, the transformation step never completes. I am left with the following message in bottom right hnd side of the Query Editor:

clip_image004

After waiting two hours for the GROUP BY query to resolve, I gave up.  The alternative is to use a DAX Calculated Table and I will show you how I achieved this:

Calculated Table

In order to create A Calculated Table, come out of the Query Editor, navigate to the Modeling tab and select New Table.

clip_image006

Now we can write some DAX.  Pasting the below syntax into the new Table will achieve the Group By on the ‘Reaction Man United’ query.

ReactionTotalsManUnited = GROUPBY (  

ReactionsManUnited, ReactionsManUnited[id], ReactionsManUnited[reactions.type],  "TotalReactions", COUNTX( CURRENTGROUP(), ReactionsManUnited[reactions.type]) 

) 

Let me break down the code:

o   Calculated Table named as ‘ReactionTotalsManUnited’

o   GROUP BY function, grouping all reaction Id’s (‘id’) and types (‘reactions.type’)

o   COUNTX function applied over reaction type, using the CURRENTGROUP() function to ensure the unique count is made by Id and Type within the ‘ReactionsManUnited’ table.

Finally, to test the new DAX table works, I have created a basic KPI Card.  It is aggregating exactly expected.

clip_image001

Conclusion

Let’s recap.  I have shown you how to use three DAX expressions, albeit nested together in one statement.  This demonstrates how powerful and flexible the language is.

o  GROUP BY

o  COUNTX

o  CURRENTGROUP

I made use of the Calculate Table functionality due to poor performing queries made to the Facebook API.  There are many other reasons for using them, with some good examples provided in Chris Webb’s blog. 

Where possible, you should always use Query Editor (and M language) for ad hoc transformations, although a DAX expression can sometimes get around slow performing queries.  DAX measures are evaluated at run time and in memory, whereas the Query Editor needs to pull down and refresh data after every applied step. 

I would strongly recommend that all budding Power BI developers learn DAX, in order to get the most out of your Power BI reports.  The Calculated Table function is just one of over 200 different expressions within Power BI.

Further Reading

o   Microsoft MSDN – http://bit.ly/2l34vsW   

o   Power BI Blog - http://bit.ly/2lBWRJc

o   Reza Rad’s Blog - http://bit.ly/2lBKjkW

o   Chris Webb’s blog - http://bit.ly/2m3IDlg

o   List of DAX Expressions (Paul Turley’s blog) - http://bit.ly/2mfBZ8y

Contact Me

If you would like a copy of the workbook or have any questions about this blog, please leave a comment below or contact me on Twitter (@CallumGAdatis ).

Row Level Security in Power BI Desktop

In the June 2016 monthly Power BI release, Row Level Security (RLS) was introduced into Power BI desktop.  This is great news for people using the application, especially as the configuration is stored within the Power BI model.  Previously, you had to create the security in the web environment, which could easily be overwritten when publishing multiple times from a desktop workbook.

In this blog, I will show you how to set up RLS in Power BI desktop and how to test it works. My example uses the AdventureWorksDW2014 database (download here), specifically applying permissions for a manager.  Each manager will only be able to see data for the Sales Representatives that report to them.

NOTE:   This article assumes you have prior experience with creating reports and visuals in Power BI.  Please leave a comment if you would like a copy of the workbook.

Report Setup

From the AdventureWorksDW2014 database, import the the FactResellerSales table.  Then add another SQL dataset, pasting in the below query:

SELECT EMP.EmployeeKey

              ,EMP.FirstName + ' ' + EMP.LastName As SalesRepName

              ,EMP.Title As SalesRepTitle

              ,MGR.FirstName + ' ' + MGR.LastName As ManagerName

              ,MGR.Title As ManagerTitle

FROM   [dbo].[DimEmployee] EMP

INNER JOIN

              (

                     SELECT EmployeeKey

                                  ,FirstName

                                  ,LastName

                                  ,Title

                     FROM   [dbo].[DimEmployee]

              ) MGR

ON     MGR.EmployeeKey = EMP.ParentEmployeeKey

WHERE  EMP.Title = 'Sales Representative’


Create a basic bar chart and rep slicers – like shown below.  You should end up with 3 managers – Amy Alberts, Stephen Jiang and Syed Abbas.

clip_image002

Create Roles

To create a role, navigate to Modeling tab (at the top of the page) in Power BI Desktop. You will see a section named Security.  Click on Manage Roles.

clip_image004

Next, we need to create a role for the three managers that were previously identified.  Follow the below annotation and necessary steps to create a specific role for Amy Alberts:

clip_image006

1.       Create a Role.

2.       Name the role as ‘Sales Manager Amy’.

3.       Click the ‘..’ on the QueryEmployee Table.

4.       Add filter (a DAX Expression).

5.       Choose [ManagerName], as we want to filter on the manager’s full name.  This defines what data they can see.

6.       Change the “Value” filter to “Amy Alberts”.  Only data containing Sales Representatives working for Amy will be shown in the report.

7.       Repeat steps 1-6 for Stephen Jiang and Syed Abbas, simply replacing the “Amy Alberts” filter with the applicable person’s name.  Click save to close down the RLS form.  You will end up with the following:

clip_image008

Role Testing

Navigate to the same Modeling tab and select View As Roles.

clip_image010

Select ‘Sales Manager Amy’ and click OK.  You will notice only Amy’s Sales Representative’s show in the report.

clip_image012

You can follow the same steps to impersonate a different manager or to remove the Roles filter completely.

Conclusion

The Row Level Security feature in Power BI Desktop makes it really easy to apply security around the data and what users can and cannot seen.  The ability to adopt DAX filter expressions through the UI enables the user to deploy an effective security model in a simple, easy manner.

Currently, the Power BI desktop security in my example is not being used in a real life situation and the three managers can see all data in the online report.  Look out for the second part of my blog on RLS (coming soon), where I will bring this to life using the Power BI Service.  Tasks include tying emails to security roles/groups and actually impersonating a user to ensure they configured correctly. 


Recommended Reading 

o   Reza Rad’s Blog - http://radacad.com/row-level-security-configuration-in-power-bi-desktop

o   Power BI Community Blog - https://community.powerbi.com/t5/Community-Blog/Row-Level-Security-in-Power-BI/ba-p/31031

o   Devin Knight’s Blog - https://devinknightsql.com/2016/07/01/power-bi-row-level-security/

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