Callum

Callum Green's Blog

Handling Web Page Errors in Power BI Query Editor

I was recently asked if it was possible to handle Error Rows in the Query Editor and more specifically, to web pages that do not exist from source. The user broached me with this question after following a previous blog on looping through multiple web pages in Query Editor – found here.

In my blog post, there were no errors from the loop output but in the user’s workbook, they were losing data directly below the expanded error row. My first suggestion was to use the Remove Errors UI option, which would delete the problematic row. However, they wanted a way of treating the errors as an Unknown Member and did not want to lose the data entirely

It is assumed consumers of this blog already have knowledge of the Power BI Query Editor and the UI functions used in the examples.  

Scenario

I created a new workbook that connects to local Council Facebook pages. Each page has a unique Object ID, which will be used as the parameter in the loop. The Council “Camberley” deliberately contains an invalid Object ID. I then proceeded to create a Parameter and Function, replicating the exact steps from my previous blog.

When I invoke the function (through the use of a Custom Column), the following is produced:

clip_image001[4]

As you can see, Camberley Council produces an error in the fnInvokeCouncils column. If we expand the contents (highlighted in yellow), the Facebook page data appears. Upon further inspection, the Farnham and Rushmoor council data are available, but Camberley (incorrect Object ID) and Guildford are not.

clip_image003[4]

The error message is a little misleading but let’s save the debugging debate for another day. The key observation is “Guildford” data is not available, simply because it comes after “Camberley” in the list. Whilst we want to see errors in a Query, we do not want them causing data loss.

Resolution

As I mentioned at the beginning of this article, using the Remove Errors function would prevent the loss of Guildford data. However, the user needs to handle errors as Unknown Members and conform to a typical Kimball Data Warehouse. 

I am sure there are many ways to fulfil the requirement, but here is how I approached it:

1.       Duplicate the existing ‘Councils’ query, naming it ‘Councils Error Rows’.

2.       Switch back to the ‘Councils’ query and Remove Errors, leaving only three records:

 

clip_image005[4]

3.       Expand the fnInvokeCouncils column, opening up the underlying fields and data:


clip_image007[4]

4.       In the ‘Council Error Rows’ query, apply the Replace Errors UI function - inserting the string “Validation Failed”.

5.       Add a Custom Column, writing the following M:

if [fnInvokeCouncils] = "Validation Failed" then 1 else 0

This is a simple IF statement that sets the error rows to 1.

6.       Now filter the selection to only display ErrorRows with the value of 1. This is achieved by using the Filter Rows UI function. The ‘Council Error Rows’ query now looks like the following:


clip_image008[4]

7.       The columns must match the ‘Councils’ query, meaning 4 new Custom Columns are needed. We can hardcode the values and remove any unwanted columns.

 

clip_image010[4]

 

8.       Right click on the previously modified ‘Councils’ query select Reference. Name the query ‘All Councils’. This makes it easier to track the transformations and persists any future changes made to the raw data.  

9.       Within the ‘All Council’ query, select Append Query transformation. Choose ‘Council Error Rows’ as the table to append and click OK.
 

10.   We are now left with a Union of both datasets, containing the Unknown Member and data from all other Councils.

clip_image012[4]

11.   The Unknown Member record is visible within the final Query.

 

clip_image014[4]

 

Summary

I have shown you how to get around two different problems with Error Rows in the Power BI Query Editor. The first is how to retain all correct data, whilst the second is keeping error records and inserting them back into the dataset as an Unknown Member. Both methods are dynamic, meaning that if we added a new Council called ‘Basingstoke’, we would see the underlying data regardless of whether the underlying Facebook Object ID exists or not.

Whilst none of the transformations in this blog are overly technical, there are quite a few hoops to jump through to retain all data from a web page/Facebook. Having said that, I am sure there are a few other ways people could approach this problem. I would be really interested to speak to anyone who does have an alternative solution.

Further Reading

Query Editor Basics (Power BI blog) – http://bit.ly/2pwBdo1
Unknown Members in Data Warehousing -
http://bit.ly/2qTefwe
Loop through Multiple Web Pages using Power Query - http://bit.ly/2q3a8Nc

Contact Me

If you would like a copy of the workbook containing the examples or want to know more about the Query Editor within Power BI, please leave a comment below. My Twitter details are also provided.

Twitter:  @CallumGAdatis

Slicer Properties in Power BI: Header or Title?

I recently shared a Power BI Report with a customer and they reported that the “Clear Selections” option (Eraser icon) was not available when they used the Slicer.  It took me a while to work out why this was.

This blog will illustrate how you can lose the “Clear Selections” functionality, depending on what Format settings are applied to a Slicer.  I will also show how to work around the formatting constraints, which will help prevent you from ever experiencing the issue.

In order to follow the examples, you will need access to Adventure Works 2014 SSAS Tabular and of course, Power BI Desktop.  Follow the appropriate links to download what you need.

Use Case

I have created a very simple report (available on request), using a Product Category Slicer and a Map to display Internet Total Units. 

clip_image001[4]

o   Format property.

o   Select All is off, ensuring multi select is allowed.

o   Header is off.

o   Title is on, used instead of Header and configured to look like the below:

clip_image003[4]

After multi-selecting the attributes in the Slicer, I tried to “Clear Selections” – which is normally available as an option like below:

clip_image005[4]

However, it does not appear in the report I created:

clip_image007[4]

The reason I cannot see the “Clear Selections” option is because I am not using a Header.  When I use this instead of Title, the Slicer contains the required feature.

Header vs Title

This made me wonder what other differences are there between Title and Header but in fact, there aren’t many.  The subtle differences to be aware are displayed and described below:

clip_image001[12]

o   Header can have an Outline, which includes the ability to underline text.

o   Header is constrained to displaying the name of the attribute (“Product Category Name”), whereas a Title can be customised (“Select Category”).  You can rename your source data attribute to get around this, however.

o   Title enables you align the text, but this is not possible with a Header.

o   Header contains the “Clear Selection” option.

Workarounds

There are couple of ways to work around the missing “Clear Selections” issue, which I will demonstrate below.


Option 1

I could simply switch from a Title to a Header, but then we would lose the ability to centre align the description.  Instead, we can set both options to ‘On’.

clip_image010[4]

 

After some formatting, the Slicer is pictured below.

clip_image012[4]

o   The Header text is set to White, therefore, it not visible. 

o   You can still see the “Clear Selections” option.

o   However, there is white space in between the Title and Header.  This not only wasted space, but also looks a bit strange from a visual perspective.

o   The actual sections themselves (e.g Bike) are a bit squashed and disproportionate to the Slicer border.

 

Option 2

The other workaround involves a little more work, but gives the impression that only a Title is being used, but with the added functionality of the “Clear Selections” option.  Furthermore, there is no longer the white, empty space.

 

clip_image014[4]

 

o   Two objects were used to produce the result:

·         Text Box

·         Slicer

o   The text box has been formatted and labelled with ‘Select Category’, using the Title property.  This allows you to change the font colour.

o   The Slicer is using the Header option, ensuring “Clear Selections” is available. Title is turned off to reduce the empty space.

 

Summary

So why are there two types of properties for Slicers and other objects like Text Boxes?  I am not entirely sure myself, but occasionally, you may want a Slicer to contain a Title with an Attribute Name as the Header underneath. This gives you a Title > Sub-Title concept (illustrated below).  I do understand why the “Clear Selections” feature is specific to the Header setting, as it directly relates to the Slicer attribute.  

There are certainly ways of solving this issue - here are just a couple of suggestions:

1.       Providing continuity across both the Header and Title format settings.  Quick fix, but not necessarily solving the ambiguity around both options.

2.       The Header setting is contained within the Title, meaning both sets of functionality are merged into one.  It would make the usability of a Slicer (especially from a development perspective) a lot better.

Whatever Microsoft decide to do in the future, I really hope they tidy up and fully define the Settings within Visuals, Slicer and Text Boxes.  Some things are confusing, especially to self-serve Business Analysts who rely on intuitive reporting tools.

Further Reading

Power BI Documentation –  http://bit.ly/2oKxy8V
Power BI Community Blog - http://bit.ly/2oU42OE
 

Contact Me

If you would like a copy of the workbook containing the examples or want to know more about any of the Power BI settings/properties not mentioned, please leave a comment below.  My Twitter details are also provided below.

Twitter:                                @CallumGAdatis

Themes in Power BI

Microsoft recently released Themes to a Preview version of Power BI.  The concept is pretty simple – imagine having standardized colours that can be applied to charts and matrix visuals? This is where Themes come in. The theory is this will ensure Power BI developers adhere to company policies and can do so quickly and efficiently. The reality is Themes are still lacking a lot of key configurables, but I will get onto that later.

Whilst this blog is not going to show you how to import or apply a Theme, it will demonstrate how to create one and what each configuration means. For a great high level walkthrough of the feature, click here. The aim is to not only show how this makes a developers life easier, but where it can actually be improved too.

JSON Configuration File

I have pasted the following code into Notepad++, which incorporates the Adatis branding and colour scheme.  For many developers, you will recognize the coding language – JSON (JavaScript Object Notation). If you would like to know more about JSON, check out Jason Lengstorf’s blog.

Use the below code as a template for your Theme:

{

  "name": "Adatis",

  "dataColors": [ "#002C5C", "#006FBA", "#81C341", "#F4AA00", "#003876", "#00448F", "#0051A9", "#007ED4","#008DED","#74B238","#8EC954","#DB9800","#FFB60F" ],

  "background": "#FFFFFF",

  "foreground": "#002C5C",

  "tableAccent": "#81C341"

}

Notice there are a number of ‘#’ within the code.  These are Hex numbers, in which Power BI natively uses to determine a shade of colour.  This is common across many reporting/image editing tools. 

Configurations Explained

I will now explain what each line of code means and how it will effect a simple un-themed report.

 

clip_image001

1.       The name of the Theme.

2.       The colours that appear in a pre-set chart colour palette and the order of which a visual applies them e.g. Dark Blue first, Light Blue second, Green third, etc.

3.       Font colour for the matrix or table visual.

4.       Primary background colour for a matrix or table visual.  Even though the setting says’ ‘foreground’, it is more of a background colour!

5.       The table accent applies to a matrix or table visual, displaying as the grid outline or contrasting background colour, depending on what type matrix/table is applied.

 

Applying a Theme

I have created a quick Power BI report, which is using the default colour schemes applied.  The only formatting applied was to the Matrix visual and this was simply changing the style of the grid.  Here is how the report currently looks:

clip_image003

Now let’s import and apply the ‘Adatis’ theme.  You will see some of the visuals have changed, whereas other elements of the report haven’t.  I will explain in greater detail.

clip_image005

1.       The Report Title, Slicers and Titles are not affected by the Adatis theme.  This is because you cannot currently configure fonts in the JSON file.

2.       The matrix visual has updated, using the three configurations form the JSON file:

a.       background (White)

b.      foreground (Navy Blue)

c.       tableAccent (Green)

3.       The Pie and Bar chart have picked up the colours in the order they are specified in the JSON file, using the dataColors property.  However, note the colour of both data points on the Bar Chart – rather than use different colours, it uses the same primary blue colour.  Not ideal if you want to plot one colour for ‘Male’ and another for ‘Female’.

4.       The Waterfall chart hasn’t changed at all.  We would expect the Adatis colours to have been applied, but this visual seems to ignore the configuration.

We can change the Waterfall Chart manually, using the imported Adatis Colour Palette. 

clip_image007

Here is how the chart now looks:

clip_image009

What’s Next?

Whilst the concept of Themes is great, the current preview functionality is extremely limited and very much ‘Version 1’.  I am a little disappointed with how inconsistent the application of a theme is, which were highlighted in my points above.

With the majority of new Power BI features, Microsoft continue to improve the usability and functionality over time, so I am very hopeful more Theme properties will be opened up in the underlying JSON configuration file.  Options such as font colours/styles, consistent theme application (for all visuals) and company logos are all necessary for this to become really powerful.  There were talks of a CSS type of configuration in Power BI, but this has yet to announced or released.  Imagine how powerful and cool that would be?

As a BI Consultant, I am not currently comfortable with demoing Themes to a client, simply because of the clear gaps.  Once the feature is more mature and in GA, I think enterprise companies will really benefit from standardising reports across their business.  For now, Themes will remain a glorified colour palette.

Further Reading

o   Jason Lengstorf’s JSON Blog –  http://bit.ly/2aU1OHS

o   Power BI Report Themes BI Blog - http://bit.ly/2mPq69l

Contact Me

If you would like a copy of the workbook or have any questions about this blog, please leave a comment below.

Twitter:                                @CallumGAdatis

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 ).

Dual KPI Custom Visual in Power BI

On February 8th, Power BI released a new custom visual called Dual KPI. The purpose of this chart is to visualise two measures over time and show their trend based on a joint timeline. The absolute values may use different scales e.g. Sales and Profit.

This blog will not only show you how to set up the new visual, but also demonstrate how changing some of the settings can enhance a report. Adam Saxton posted a YouTube video that also walks through the Dual KPI.

Pre Requisites

In order to follow my example, you will need a copy of AdventureWorksDW2014 database – found here. You will also need to download the following custom visuals:

o   Hierarchy Slicer – http://bit.ly/2kAv4Id

o   Dual KPI – http://bit.ly/2l1qCTp

NOTE:   This article assumes previous knowledge of downloading and importing Custom Visuals into Power BI Desktop. If this concept is new to you, Scott Murray’s blog gives great step by step instructions. 

Prepare Data

Open Power BI Desktop and Get Data. Point to the new AdventureWorksDW2014 database and drop down Advanced Options. Paste in the following T-SQL:

SELECT

        DPC.EnglishProductCategoryName

       ,DPS.EnglishProductSubCategoryName

       ,DP.EnglishProductName

       ,SUM([TotalProductCost]) AS [TotalProductCost]

       ,SUM([SalesAmount]) AS [SalesAmount]

       ,SUM([SalesAmount]) - SUM([TotalProductCost]) As ProfitAmount

       ,[ShipDate]

FROM [AdventureWorksDW2014].[dbo].[FactInternetSales] FI

INNER JOIN

       [dbo].[DimProduct] DP

       ON DP.ProductKey = FI.ProductKey

INNER JOIN

       [dbo].[DimProductSubcategory] DPS

       ON DPS.ProductSubcategoryKey = DP.ProductSubcategoryKey

INNER JOIN

       [dbo].[DimProductcategory] DPC

       ON DPS.ProductcategoryKey = DPC.ProductcategoryKey

WHERE ShipDate BETWEEN '2013-01-01' AND '2013-06-30'

GROUP BY

        DPC.EnglishProductCategoryName

       ,DPS.EnglishProductSubCategoryName

       ,DP.EnglishProductName

       ,[ShipDate]

When happy, click ‘OK’ to continue. The preview of the data will open.  Click Load, as we do not need to edit any data in the Query Editor.  Apply and changes and rename the query to ‘Internet Sales’ – final output below:

clip_image002

Some measures and attributes need to be formatted within the ‘Modeling’ Tab.

o   ‘ShipDate’ = dd MMMM yyyy

o ProfitAmout’ = Currency

o   ‘SalesAmount’ = Currency

The final formatting step is to create a Product hierarchy, based on the three product attributes.  Navigate to the Data tab, right click on the ‘EnglishProductCategoryName’ attribute and select ‘New Hierarchy’.  Drag the attributes into the hierarchy and name it ‘Products’.  It should look like the following:

clip_image005

Create Report Visual

We need to use both the Slicer and Dual KPI custom visual. To achieve this, follow the steps below:

Select the Hierarchy Slicer in the Visualizations menu and drag the ‘Products’ hierarchy on to the Fields box. The slicer will now appear in the report.

clip_image007

Select the Dual KPI Slicer in the Visualizations menu and drag the following measures to the appropriate chart properties box:

a.       ‘ShipDate’ > Axis

b.      ‘SalesAmount’ > Top values

c.       ‘ProfitAmount’ > Bottom values

clip_image009

The chart is now configured and each metric/visual is explained in more detail below. Only the top KPI (Sales Amount) is shown because both use the same calculations.

clip_image002[4]

  

1.       This is a fixed growth percentage, comparing the last (06/30/2013) vs. first (01/01/2013) data point on the graph. The metric acts as a static KPI.

2.       The Sales Amount value for the last data point on the graph. Also a static KPI.

3.       The data point currently being hovered over. This dynamically changes when you move along the axes.

4.       The Sales Amount value for the current data point being hovered over. Also dynamic.

5.       % since metric that looks at the Sales Amount for the last data point on the graph and works out the growth based on the current data point being hovered over. To use the example in the screenshot:

-          Sales Amount for 06/30/2013 = 51,596

-          Sales Amount for 05/17/2013 = 18,442

-          % since:  ((51,596 - 18,442) / 18,442) * 100 = 179.7%

Enhancing the Report

As with all custom visual in Power BI, there are lots of settings that you may never use. I have picked out some that enrich the capabilities of the Dual KPI Chart:

o   Fields

o   Warning State

§  Set alerts around data freshness and view warning messages.

o   Top/Bottom % change start date

§  For the fixed +/- % change on the chart, you can add an override start date. The dates could vary by product category and dynamically impact the % in the visual.

o   Format

o   Dual KPI Properties

§  Show abbreviated values, define multiple tooltips and show stale data warnings.

o   Dual KPI Chart Type

§  Choice of either Area or Line charts.

I have applied the Top/Bottom % change start date functionality and also formatted the chart properties. The report now looks a little more professional:

clip_image002[6]

Further Reading

o   Adam Saxton YouTube Video –  https://www.youtube.com/watch?v=821o0-eVBXo

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

Contact Me

If you would like a copy of the workbook or have any questions about this blog, please leave a comment below.

Twitter:  @CallumGAdatis

On-Premise Power BI: Part 2 – What’s next?

Part 2 of this blog series focuses on the future of Power BI On-Premise and what direction Microsoft are heading in.  This is a little tricky, as there haven’t been many formal announcements of how both SSRS and Power BI will work together from an architectural and pricing perspective.

I will be using this article as a forum to discuss both On-Premise and Cloud variations of Power BI, potential licensing and whether the new offering is actually just ‘Plugging Power BI reports into SSRS’.  The last statement is a little crude but from a business perspective, decision makers need to know what version of Power BI suits their needs.  We won’t get all of the answers right now, but it would be nice to shed some light on what appears to be a very dark room.

Cloud vs On-Premise

What does this mean for Power BI as a brand and more importantly, as a reporting tool?  The original purpose for Power BI was to offer self-service, ad hoc end user reporting.  However, as the product has matured, prospective clients have wanted it to do so much more.  “How can we incorporate Active Directory security”? “How do we share Dashboards within a specific workspace and limit permissions to it”? “But our business uses ultra-sensitive data and it MUST stay on a server in the UK…..”.  These are the types of comments/questions that get banded around a lot at the moment.

This is why Microsoft are offering both Cloud and On-Premise.  We are not yet in a position where definitive pros and cons can be laid out for each option, but this will soon be possible when Microsoft reveal their ultimate strategy.  There are still a lot on unanswered questions, especially around licensing.  A sensible assumption is that Power BI On-Premise will be covered under the typical Enterprise Edition version of SQL Server.  However, will there be an add-on fee for Power BI or will the general license costs go up?  What impact will this have on the Cloud costs?  There are some complicated pricing models for Office 365 users and the simpler £9.99 a month for a standalone Power BI Pro license.  Will the same pricing strategy exist or will On-Premise force Microsoft into a rethink?  I tend to stay away from speculation in my blogs and stick to facts, but I genuinely am interested to see how Microsoft market the variations of Power BI.

Coming Soon

After looking through various blogs and forums (links provided below), the following features/functionality will soon be available before Power BI On-Premise goes to GA.

  • Short-Term
    • Custom visuals
    • Additional data connectors (besides Analysis Services), cached data, and scheduled data refresh
    • Power BI mobile apps (viewing Power BI reports stored in SSRS)
  • Longer-Term
    • R Visuals
    • Support for integrating previous versions of SQL Server Databases (2008 +)  and Analysis Services (2012 SP1 +) with SSRS 2016
    • Support for all data connectors currently enabled for Power BI Cloud

Microsoft have also listed some Power BI cloud features that are not planned for the On-Premise version:

  • Dashboards – The concept of pinning a report and sharing it on an ad hoc basis
  • Q&A (Natural query language)
  • Quick Insights

Another pertinent question is “When will Power BI On-Premise actually be available in the real world?”.  Microsoft are targeting a production ready release for mid-2017, although nothing is official yet.  One thing is certain – it won’t be coming in a Service Pack or Cumulative Update.  Another big thing to consider is migrating from SSRS 2016 to SSRS with Power BI Reports, which Microsoft are promising will be easy.

Conclusion

At this stage, it is difficult to give any concrete information on where Power BI On-Premise is heading.  All we know is that Power BI and SSRS will be working together a lot more closely and the majority of functionality will be available in both. 

The concept of having Power BI reports shared and deployed to physical, on premise servers will accommodate companies worried about moving their data to the cloud.  As Power BI continues to increase in popularity, the overall security and infrastructure model will be scrutinized.  Cloud storage is often falsely labelled as a security risk, which is where the common corporate misconceptions are born.  It will be hard to change this train of thought, which is where the On-Premise Power BI offering will come in handy. Even more appealing is the natural integration with SharePoint and SRRS, enabling companies to use hybrid approaches, as well as not need to migrate old SSRS reports into Power BI.  Everything is managed in one location, thus reducing security risks and costs. 

Now we all sit tight and wait for Microsoft’s next big announcement.  If anyone has more information around Power BI On-Premise, please comment below.  

Further Reading

o   Power BI Reports in SSRS Release Notes - https://msdn.microsoft.com/en-us/library/4c2f20d7-a9f9-47e3-8dc3-c544a14457e0.aspx?f=255&MSPPError=-2147217396

o   October 2016 Technical Preview Blog – https://blogs.msdn.microsoft.com/sqlrsteamblog/2016/10/25/announcing-a-technical-preview-of-power-bi-reports-in-sql-server-reporting-services/

o   December 2016 Feedback Review Blog – https://blogs.msdn.microsoft.com/sqlrsteamblog/2016/12/16/power-bi-reports-in-sql-server-reporting-services-feedback-on-the-technical-preview/

o   January 2017 Technical Preview Blog – https://blogs.msdn.microsoft.com/sqlrsteamblog/2017/01/17/power-bi-reports-in-sql-server-reporting-services-january-2017-technical-preview-now-available/

Buffer() M Function in Query Editor (Power BI)

Whilst I have been aware of the Buffer() M function in the Query Editor of Power BI for a while, I had never really utilised its capabilities until now.  There are two main types of buffer functionality – Table.Buffer and List.Buffer.  To define them simply, Table.Buffer puts an entire table into memory and prevents change during evaluation, whereas List.Buffer provides a stable list, meaning it has some form of count or order.

This blog will focus on the theory behind the general Buffer() M functionality, picking a specific scenario of when it can outperform the standard Query Editor behavior.  I will also demonstrate that this is not always the most effective technique within the same scenario.  The article will not give you a hard and fast rule of when to use the Buffer() function, because it can depend on a number of factors.  These are described further below.

Note:    It is assumed you have existing knowledge of Query Folding and if not, one of my previous blogs should help greatly.

Scenario

I found inspiration from Chris Webb’s example, using the Adventure Works DW 2014 database – available here.

The requirements are:

1.       Obtain the first 10,000 rows from FactInternetSales

2.       Remove the majority of columns, retaining ONLY:

a.       SalesOrderLineNumber

b.      CustomerKey

c.       SalesAmount

3.       Rank the current row based on Sales Amount.

List.Buffer()

Assuming your database exists on a local server and is named AdventureWorksDW2014, copy the following code into the Advanced Editor in the Query Editor screen.

let

    //Connect to SQL Server

    Source = Sql.Database("localhost", "AdventureWorksDW2014"),

    //Get first 2000 rows from FactInternetSales

    dbo_FactInternetSales = Table.FirstN(

          Source{[Schema="dbo",Item="FactInternetSales"]}[Data],

          10000),

    //Remove unwanted columns

    RemoveColumns = Table.SelectColumns(

          dbo_FactInternetSales,

          {"SalesOrderLineNumber", "CustomerKey","SalesAmount"}),

    //Get sorted list of values from SalesAmount column

   RankValues = List.Sort(RemoveColumns[SalesAmount], Order.Descending),

    //Calculate ranks

    AddRankColumn = Table.AddColumn(RemoveColumns , "Rank",

          each List.PositionOf(RankValues,[SalesAmount])+1)

in

    AddRankColumn

You can visibly see the rows loading – one by one.  In total, it takes nearly 1 minute to load all off the results.

Now let’s use the List.Buffer() function in the RankValues step.

Replace:

= List.Sort(RemoveColumns[SalesAmount], Order.Descending)

With:

= List.Buffer(List.Sort(RemoveColumns[SalesAmount], Order.Descending))

clip_image002

The entire transformation (from start to finish) completes in just under 2 seconds!  This is because the List.Buffer function stores the sorted values in memory and therefore, the rank calculation is only evaluated once.  The last query (and previous steps) were being evaluated multiple times.  The M language is both functional and at times, lazy.  In order to prevent the constant re-evaluation, buffer the list into memory. 

The final query output is shown below:

clip_image004

Query Folding

We will implement the same requirements, but this time using Query Folding. 

The third step in our current transformation is called ‘Removed Columns’. This is what prevents Query Folding, as this function cannot be interpreted/translated to the native SQL Server T-SQL language.  All steps below are inadvertently not supported either. 

The way around this is to write SQL Server View (in SSMS) to import just the fields required from the underlying FactInternetSales Table.  The below query will give you the same result up to the ‘Remove Columns’ step.

CREATE VIEW dbo.VwFactInternetSalesAmount

AS

       SELECT SalesOrderNumber   

                     ,[CustomerKey]

                     ,[SalesOrderLineNumber]

                     ,[SalesAmount]

                     ,RANK() over( order by [SalesAmount] desc) AS [Rank]

       FROM   [AdventureWorksDW2014].[dbo].[FactInternetSales]

clip_image006

The final steps are to filter on the top 10,000 rows and Group the Rows together – inserting the following M syntax into the last Applied Step:

let

    //Connect to SQL Server

    Source = Sql.Database(".", "AdventureWorksDW2014"),

    // Connect to SQL Server

    dbo_FactInternetSales = Source{[Schema="dbo",Item="VwFactInternetSalesAmount"]}[Data],

    #"Sorted Rows" = Table.Sort(dbo_FactInternetSales,{{"SalesOrderNumber", Order.Ascending}}),

    #"Kept First Rows" = Table.FirstN(#"Sorted Rows",10000),

    #"Grouped Rows" = Table.Group(#"Kept First Rows", {"CustomerKey", "SalesOrderLineNumber", "Rank"}, {{"TotalSalesAmount", each List.Sum([SalesAmount]), type number}})

in

    #"Grouped Rows"

 

The query now returns instantly (under 1 second).   Right click on the last applied step and select the View Native Query option, to show the underlying SQL.

select top 10000

    [rows].[CustomerKey] as [CustomerKey],

    [rows].

Row Level Security (RLS) using the Power BI Service

Following on from my last blog on RLS, I will now demonstrate how to initiate the roles created in Power BI Desktop.  Whilst the desktop version is great for testing security, you have to configure the report in the Power BI Service to enforce permissions.   

Before doing anything, ensure you have published a report with RLS onto the Power BI Service.  If you would like to use my workbook as the example to follow, leave a comment below.

Configure Power BI Service

1.       Navigate to the Datasets tab and click the ellipsis button (‘...’) on the ‘RLS Part 2’ dataset (or whatever you have called your published report).  The select the Security option.

 

clip_image001

2.       Let’s assign two email accounts to the users ‘Stephen’ and ‘Amy’.  These must use a work domain – Hotmail, Outlook, etc. accounts are not permitted with Power BI.  Click Add when you are happy.

 

clip_image003

 

3.       Ensure the RLS roles are saved.  You will end up with the following:

clip_image005

4.       The roles are now configured.  Both email accounts will only see data for the person they have been assigned to.

NOTE:   You can add numerous email accounts to a specific role.  For this example, the roles are done by person, but you may want to add a whole department containing 10 people to a given role.

Testing Roles using Power BI Service

This is actually really simple.  Go back to the Security settings (shown above).  Click the Ellipsis button and select ‘Test as Role’.

clip_image002[4]

You will now only see sales data for the Sales Representatives that report to Stephen. 

clip_image002[6]

Conclusion

RLS is now out of preview mode and released into General Availability.  Whilst the capabilities are still pretty new, the additional DAX layer over the GUI enables more complex security capabilities.  As with all features in Power BI, regular updates are to be expected – so if RLS currently isn’t fit for your scenario, it could be very soon.


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/

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/

Microsoft BI – Coming Soon to SQL Server 2016

After attending the Pass Summit 2016 a couple of weeks ago, I attended a number of sessions that provided an insight into the direction Microsoft are heading with BI.  I thought I’d share this with the community.

Looking back to October 2015, the official Reporting Roadmap blog from Microsoft stated their intent in dramatically improving the visualisations, integration and harmonisation of both on-prem (SSRS) and cloud based services (Power BI).  Whilst reporting appeared to be a renewed focus, they are constantly driving other areas of BI - such as analysis services and database/data warehousing development in the cloud.

Now, for the interesting bit.  Here is what we can expect in the SQL Server 20916 BI Stack in the near future:

-          SQL Server 2018 CTP (to be released within the next month).

o   This seems very early, considering 2016 has only just been released!  Let’s wait for an official Microsoft announcement.

-          Azure DW

o   Auto Create Statistics

§  Currently statistics have to be generated and updated on the fly.

o   Replicated Distributed Table

§  One that will excite any cloud based SQL Server developer.

§  This will reduce data transfer between distributions/nodes and consequently improve performance.

-          Azure Data Lake

o   General Availability (GA) is imminent.

o   Future Features:

§  Polybase, so that you can connect the two big MMP platforms (ADL and DW).

§  SSIS connectors (released with GA) for Store.

§  Python and R in U-SQL.

-          SSIS

o   Lots of new connectors, including:

§  ADLS.

§  CRM.

-          SSAS

o   Migrating Power BI Models into Tabular.

§  This is coming very soon apparently, but will be developed in Azure SSAS first.

o   Object Level Security in Tabular

§  We currently have row level, but there are talk to secure a physical object, not just a row.

§  Even better news - Microsoft want to integrate the two together, which will make security awesome in Tabular.

-          SSRS

o   Supporting (not pinning) Excel reports in RS report.

§  This will come, but Power BI is the focus right now and we may have to wait a while.

-          Power BI

o   Additional and better Pivot Table functionality.

o   Integrating Active Directory dynamically.

o   Potential to use Direct Query and Imported modes together – as a hybrid.

§  Functionality is possible, but performance needs to be weighed up by Microsoft before anything will emerge.

o   Quick Calcs.

§  Only ‘Percent of Current Total’ currently available.

§  Potential is to offer lots more – such as YTD, MAT, Current Year vs. Previous Year, etc.

§  This is for the users who aren’t familiar with DAX.

o   Template organisational Content Packs.

§  The ability to give the user the ability to personalise colours, fonts, etc. within a structured (organisational) content pack.

-          Power BI Embed

o   Application developer will be able to limit user sessions and therefore, reduce the charge per 1 hour costs that come with it.

 

There are some features/issues Microsoft do not plan to change.  Although, the good thing about Microsoft is that they are community driven, so if you feel strongly about anything (and get support from your peers), they may change their minds.

-          SSRS

o   Q&A, Query Editor (Power Query), R integration, etc. not being developed.

§  Pretty obvious really.  Whilst they are going to introduce almost everything from Power BI, some elements of functionality are just not needed for on pre purposes.

§  R Scripts may come one day, but not a focus right now.

-          Power BI

o   Source Control

§  No immediate plans to integrate with TFS or modularise the pbix files (for a more developer based solution)

§  Not surprising as this is a self-service tool, not a development team.

§  Work around is to upload pbix files into OneDrive and use the versioning as an element of Source Control or add a file into Visual Studio.

§  Keep Voting on PowerBI.com if you want this! (Currently 278 votes).

·         https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/9677517-source-control

-          Power BI Embed

o   Licence model

§  ‘Speak to Marketing’ is what we were told.

 

Is everyone excited?  I certainly am.  Microsoft are openly admitting that any new BI related feature will be developed in the cloud first, but this was pretty obvious.  For all you on-prem fanatics (me included), at least the bugs/undocumented features will be ironed out before we get to use them!  My personal favourite has to be the direction SSRS is going.  It was recently labelled a ‘mature’ product, but now RS has suddenly become a cool and current tool to use.  The fact Power BI and SSRS will become almost the same product, tells us all that on-prem is still an important part of the Microsoft licensing strategy.

 

I am sure there are lots of other features coming into the BI stack over the next 6 months to a year and these were just the ones I had heard about.  Feel free to comment if you have additional ones to share.