Callum

Callum Green's Blog

Coming Soon in Power BI - June 2017 & Beyond

Whilst I wasn’t fortunate enough to attend the Microsoft Data Summit on June 12-13 2017, I managed to view the keynote through a live webinar.  The majority of this session contained details of what is coming soon in Power BI, more than likely over the next few months.  We all know the ‘roadmap’ can be as long as a piece of string, but I am hopeful the features mentioned in this blog will all be available by the end of the calendar year.

Without further ado, I will move onto the features.

Out Now

The below features were actually released in the June Power BI Desktop Download.

ü  Data bars for new table & matrix (preview)

o   This is the concept of having spark lines within a table or matrix, providing trend analysis in a visual way. 

ü  Fonts in Visuals

o   You can now alter the font in a visual, the same as the functionality within a text box property.  These small configurations actually make a big difference.

Coming Soon

 

ü  New SKUs for power bi premium specifically targeting embedded workloads...  EM1 and EM2 SKUs starting at $625 per month.  The difference pricing tiers are:

 

ü  Embed using new SKU’s into SharePoint and MS Teams easily

o   I wander if the code will be completely free?

ü  New Visio diagram control – auto mapping to data model entities.  Copy and paste a Visio diagram into power BI and it hooks up the data elements.

o   This looked really slick in the Microsoft demo but previous experiences with this functionality un SharePoint was a little clunky and fiddly.

ü  Embed a Power APP into Power BI – with write back functionality 

o   This could replace MDS for certain scenarios, depending on the how complex your reference/master data is.   

ü  New Quick Measure gallery – a new DAX measure, upload it to the gallery and have your name referenced in the Product.

o   Chris Webb recently had this privilege, with a link to his blog series here.

ü  Better Custom Visual support.

o   New button to install custom visuals from store without having to download and import first.

ü  Ability to remove stop words in the Word Cloud Custom Visual

ü  Drill through to other report tabs using Page Settings.

o   This is huge for Power BI. Something seemingly so simple is not available but I for one am very excited for when this feature is released.

ü  Bookmark pane to facilitate a ‘save state’ (all current filter settings).  In addition, it will support analysts who want to tell a story or provide a walkthrough.

o   Show/hide visuals on a page when building story

ü  Create buttons

o   Turn a button into a report page link

o   Use buttons to show/hide visuals

o   Again, a simple, but MASSIVE feature for Power BI.  This really helps with the user’s reporting journey.

ü  Quick insights type functionality built into visuals and desktop to answer “why” questions -  using AI

ü  Waterfall chart improvements

ü  Cortana Improvements

o   More integration

o   Using colleague names in Cortana to find PBI workbooks

o   Conversational Q&A – refine answer with further questions

ü  What-If Analysis

o   Like Excel

ü  Annotation support for presenting back to customers. 

o   In effect, you can doodle or draw on top of a Power BI report.

Further Reading

I would advise subscribing to the Power BI Blog, in which you will hear of any new announcements to features, etc.  In addition, there are some other great pages out there too:

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

o   Power BI Premium Capacity White Paper - https://aka.ms/pbiewhitepaper  

o   Chris Webb Blog Page – https://blog.crossjoin.co.uk/

Contact Me

If you have any questions or thoughts, please leave a comment below.  My Twitter details are also provided.

Twitter:                @DataVizWhizz

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 (@DataVizWhizz ).

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:  @DataVizWhizz

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.

October 28th Technical Preview for SSRS – Including Power BI in SSRS

Today at the Pass Summit, Microsoft announced some cool new features that are coming into SSRS 2016.   I attended the ‘SQL Server Reporting Services: What’s New and What’s Next’ session (presented by Chris Finlan and Riccardo Muti and there was certainly some very exciting news for anyone in the BI sector.

There will be a technical preview available from 28th October, in which you can download a pre-configured Virtual Machine in the Azure Marketplace.  This includes sample reports and data, enabling you to try the features whilst Microsoft work on a complete preview for on premise R&D. This blog lists the new feature from a high level.  I will announce more information as and when it is available.

Features

 

1.       Power BI reports in SQL Server Reporting Services.

a.       I won’t steal Microsoft’s thunder and instead, redirect you to their blog written on October 25th:

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

b.      In essence, you can create a Power BI desktop report and hook it straight into your on premise SSRS reporting solution. Amazing, right?!

2.       Report Manager.

a.       List View is back!  You can again view reports the old fashioned way.

b.      Show Hidden Items is now unchecked by default.  Sounds insignificant, but it is the little things that sometimes matter.

3.       Report Comments Section

a.       This is really cool.  A user can write a comment against a given report and even ‘snapshot’ the report as an image and upload it.  This is a good way to start an internal company discussion, as well as trace how a visualization has changed over time.

b.      All comments and images are stored in the Report Server db, which helps for auditing or plugging the data into an external tool/text file.

4.       Other – More information to be announced soon

a.       Direct URL link from a KPI.  If you click a KPI, it will take you to a specified link, without the need to select it form an option.

b.      Mobile Reporting auditing.

c.       Better support for generated MDX.  The current date workaround will no longer be needed and make everyone’s life easier. 

d.      General performance.

Current Preview Limitations

 

As this is an interim SSRS Preview release, there are some current limitations.  These are more specific to the Power BI reports in SSRS:

-          Power BI reports that connect “live” to Analysis Services models – both Tabular and

        Multidimensional  (cubes). No other data sources are currently available.

-         Custom Visuals not supported.

Coming Soon

 

As soon as I get back from the Pass Summit (and over my jet lag), I will be downloading the preview and trying out the cool features.  Please come back to my blog page to check out my findings and more importantly, feel free to comment on any quirks/issues/limitations that you have come across yourself.

It is certainly exciting times for the on premise enthusiast out there.  I had lost all hope for SSRS, but with 2016, Microsoft have rekindled my love for enterprise data visualisation.

View Native Query Function in Power Query

Following on from my last blog (found here), I wanted to share a new Query Folding feature in Power Query.  The View Native Query functionality was actually released in the June 2016 Power BI update but it was hardly advertised/promoted by Microsoft.

What is View Native Query?

In a nutshell, it enables you to check if each of your step by step transformations in Power Query are being folded back to the source.  I have shown techniques of using external monitoring tools, as well as an in-built M function.  However, the View Native Query feature makes our lives so much easier.  I will demonstrate how easy it is to use.

Example

I loaded up an old Query Folding Power BI workbook in Power Query, which was connecting to the AdventureWorksDW2014 SQL Server database.  If you want a copy of it, feel free to comment below and I’ll get back to you.  However, you can do this with any workbook and need to follow the simple instructions below:

1.    Right click on the last step of your transformation and a new option called View Native Query should be available.  Left click to open the SQL query.


clip_image0024_thumb_thumb

 

clip_image0044_thumb_thumb

2.    Whilst the above SQL written at source is not the most efficient, query folding still means the data is brought back into Power Query far quicker than it would if done within the Power BI application.

 

3.   Now copy and paste the code into SSMS, This is exactly the same code that SQL Server Profiler would produce.  Execute the query.

 

clip_image0054_thumb_thumb

 

4.    In effect, this is replicating the transformation steps in Power Query.  The below screenshot is taken from Power Query and note the top 10 results from this and the above output.

clip_image0064_thumb_thumb

The coolest thing about the View Native Query feature is the fact you can check every step in the Power Query transformation.  I only demonstrated the final step, but you can go back to each one and find the underlying query folding logic that has been applied.

Why is View Native Query not available?

If you get the below screenshot, the answer is simple – your transformation step doesn’t support Query Folding! 

clip_image0074_thumb_thumb

NOTE: View Native Query is ‘greyed’ out.

If possible, move any steps that aren’t supported to the end.  Power Query will attempt to query fold, until there is a step it cannot.  All steps after this (even if query folding is possible) will not be supported and could drastically slow down the remaining transformations.

Conclusion

This feature is something that should have been made available a long time ago.  Whilst the power of query folding is undeniable, the methods of testing it were long and arduous.  With View Native Query, we can easily check where, when and more importantly, when folding is NOT happening.  Debugging slow performing transformations is so much easier now and you can in fact, swap steps around and folding will still persist where possible.

I would advise every Power Query developer to make use of this simple feature.  If there are any other Query Folding techniques, tips or tricks out there, please contact me.  Look out for future blogs, where I will be looking at the performance of query folding on very large data volumes (100 million + records) and whether the automated SQL Server code eventually becomes too unwieldy and poorly performing.   

Query Folding in Power Query

Query Folding in Power Query isn’t an entirely new concept but when I first heard of it, I thought “What is Query Folding”?  There are probably a number of you out there who think the same, so let me explain it.

When carrying out transformations (e.g. Sort) in Power Query, it is possible that some will be sent back to the source.  In other words, Power Query doesn’t attempt to sort millions of records within the application itself, but will use the source to undertake the work.  This can drastically improve performance within Power Query.

Examples

I will now demonstrate some basic Query Folding techniques in Power Query.  Until recently, it wasn’t easy to know if a transformation was performing Query Folding.  This blog primarily talks you through monitoring outside of Power Query, but I will also touch upon how this is now possible to do within the application.

External Monitoring Tool

The monitoring tool used in this example if SQL Server Profiler.  For a simple video walkthrough on how to use Profiler, click here.

1.       Start a profiler session, ensuring you connect to the server where the AdventureWorksDW2014 Database resides – available to download here.

2.       Now we can connect to the database in Power Query and more specifically to the dbo.FactProductInventory table.

3.       I will perform three Power Query transformations:

a.       Filter Rows

                                                               i.      ProductTotal > £50000

b.      Group By

                                                               i.      DimProduct.EnglishProductName

                                                             ii.      SUM(Unit Cost) As ProductTotal

c.       Transform > Trim

                                                               i.      DimProduct.Status

NOTE:   This blog does not show you how to carry out the Power Query transformations but the workbook can be provided on request.

4.       The transformed query looks like the below:

 

clip_image002

 

5.       Now let’s take a look.

 

clip_image004
clip_image005

clip_image006

Points of Note:

1.       The Filter Rows Power Query transformation on TotalUnitCost is implemented as a WHERE clause in SQL Server.

2.       The Group By also becomes a T-SQL GROUP BY.  With the SUM function also being applied to find the total value for the EnglishProductName Group By.

3.       The T-SQL functions LTRIM/RTRIM are combined together on the Status column, replicating the functionality of the Trim feature in Power Query.

Within Power Query

Rather than show you this feature myself, I will point you to a great article by Chris Webb, which describes how you can easily check for Query Folding within your Power Query workbook.

In summary, there is an out of the box M function called GetMetaData that can be nested within your transformations.  This will tell you the source of your query and if it has been query folded.

Supported Transformations

Here are some other transformation types in Power Query that support Query Folding:

-          Filtering (on rows or columns)

-          Joins

-          Aggregates and GROUP BY

-          Pivot and unpivot

-          Numeric calculations

-          Simple transformations, such as UPPER

Current Limitations

Although there is still no official list (from Microsoft) of the sources that support Query Folding, here are some of the ones currently known:

-          Relational sources (SQL Server, Oracle).

-          OData sources (e.g. Azure Marketplace)

-          Active Directory

-          Exchange

-          HDFS, Folder.Files and Folder.Contents

This means all a lot of other data sources do not support Query Folding.  For example, a flat file does not support Keep Top 5 Rows in Power Query, whereas SQL Server would use the TOP function or apply a filter in a WHERE clause. Whilst the lack of supported sources can be seen as a limitation, I would argue that any form of Query Folding, even if it were just SQL Server, is a big plus point.

You can deliberately prevent query folding, although the only time I see a benefit is if you are connecting to a server running at full capacity or during a large ETL.  There are far more instances when Power Query will not apply query folding, because of limitations to the sources functionality.  Check out Ken Verbeek’s blog if you would like know more on this.

Conclusion

This article is just scratching the surface of how beneficial Query Folding in Power Query can be.  The key takeaway is remembering to put the transformation steps (that can be folded back to source) at the beginning.  Steps that cannot be folded should be applied as late as possible.  If this is adhered to, you will see huge performance benefits – especially on large datasets.

If anyone out there would like to share other data sources that use Query Folding or just have a general interest, feel free to comment below.