Callum

Callum Green's Blog

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

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/

On-Premise Power BI: Part 1 - Technical Preview

Back in October 2016 at SQL Pass, Seattle, Microsoft announced that Power BI was going on premise.  Along with the big fanfare, there was also a Technical Preview released – in which a user could download a pre-built VM from Azure Marketplace and get a first look of how SSRS and Power BI could work together. 

Roll on a few months (January 17th 2017 – to be precise) and the new Technical Preview is available.  This time, you can actually download a standalone copy of Power BI Desktop (for SSRS) and a slimmed down SQL Server Reporting Services 2016 configuration tool.  If you follow the official Microsoft blog, getting set up is really easy.  Just a heads up – there are still lots of limitations with this preview, which can be found at the bottom of the January blog.

The blog will be broken into two parts – with Part 1 focusing on my experiences with the January Technical Preview of SSRS and Power BI.

Technical Preview Feedback

Setting up a local SSRS Server (on my own machine) and deploying a Power BI Report was really easy, which is good feedback in its own right.  Annoyingly, only Analysis Services connections are currently supported within the preview Power BI Desktop application and all of my workbooks use online services or SQL Server databases!  This meant I had to download a tabular model for Adventure Works and create a quick test report using the dataset.  Not a major problem, but an inconvenience nonetheless.

All of my other observations are based around differences between the regular SSRS service and this new offering.  In essence, not much has changed – which can only help a user’s experience.  Here are the things to be aware of:

SSRS Configuration Tool

There are two new options – ‘Scale-out Deployment’ and ‘Power BI Integration’.  The latter is the one we care about.  In effect, you need to tie SSRS version of Power BI Desktop (which is available through the Technical Preview install) to the SSRS Configuration.

clip_image002[4]_thumb

Power BI Desktop

The application looks pretty much the same, with the only differences highlighted below.  The most important bit is saving the report to the SSRS Server.  I thought it would have been under the ‘Publish’ option, but Microsoft opted to put it in ‘Save As’.  The application itself is called ‘Power Bi Desktop (SQL Server Reporting Services)’ and I imagine it will stay this way, in order to differentiate between On-Premise and Cloud versions.

clip_image004[4]_thumb

The final step is to define the SSRS Server.  A grid appears and you can either choose an old URL or define a new one.  As long as you are connected to an Analysis Services model and type in the correct server name, then the report will successfully deploy.

clip_image006[4]_thumb

SSRS Report Server

I deployed a test Power BI Report to the local SSRS Server and there are a few specific options now.  By clicking the ellipses, you can open the report as normal, Edit directly in Power BI Desktop or even download the pbix file.  Notice the ‘Power BI’ icon on the tile of the report, which also helps with distinguish the type of reports in the SSRS portal.
clip_image002[6]_thumb

Conclusion

My first impressions of Power BI On-Premise are good.  Whilst there are still some clear gaps and limitations, the fact that Microsoft are looking at bring SSRS and Power BI together must be a good thing.  They recognize the need to bring Mobile, standard On-Premise and ‘director pleasing’ reports together in a one stop shop.

I am certainly excited about the next Technical Preview for Power BI On-Premise.  Not only should it contain more data connectors and features, Microsoft should reveal more about the long term visions.  Look out for my next blog where I will discuss licensing implications, architecture and some of the common Power BI questions I get from prospective clients.  

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/

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.

Query Parameters in Power Query – Part 2

As promised, here is Part 2 of Query Parameters in Power Query.  My last blog demonstrated how this feature (released in April 2016) could be used to reduce the data volumes in your PowerBook.  This article focuses on how you can easily switch between different parameter values and dynamically display the results. 

Query Parameters are fully explained in Part 1, so I will move straight on to the exciting stuff!

Use Cases & Example

There are plenty of scenarios when you want to filter large data sets based on various criteria.  A typical business case could be looking at a customer table (in SQL Server, a text file, etc.), where the user wants to return only data by a person’s First Name, Last Name or City in which they are from.  This would require more than one parameter if we want to make our filtering dynamic. 

I will talk you through a slightly different example.  Imagine a user wanting to look at the current products their business is selling, but look at the data by:

1.       Product Name ONLY = EnglishProductName

2.       Product Category, Product Sub Category & Product Name = EnglishProductCategoryName, EnglishProductSubcategoryName & EnglishProductName

Query Parameters can easily facilitate this.  To follow my example, download AdventureWorksDW2014 Database (found here) and connect through SQL Server Database. Paste the following SQL Code into the SQL Statement box – like below:  

SELECT DISTINCT

              --DPC.[EnglishProductCategoryName] AS CategoryName,

              --DPS.[EnglishProductSubcategoryName] AS SubCategoryName,

              --DP.EnglishProductName AS ProductName,

              DP.ProductAlternateKey,

              DP.ListPrice As ProductListPrice,

              COALESCE(DP.[Status],'Previous Version') AS ProductStatus

FROM   [dbo].[DimProduct] DP

INNER JOIN

              [dbo].[DimProductSubcategory] DPS

              ON DPS.[ProductSubcategoryKey] = DP.[ProductSubcategoryKey]

INNER JOIN

              [dbo].[DimProductCategory] DPC

              ON DPS.[ProductCategoryKey] = DPC.[ProductCategoryKey]

ORDER BY

       DPC.[EnglishProductCategoryName],

       DPS.[EnglishProductSubcategoryName],

       DP.EnglishProductName

 

NOTE:   You will see the first three columns are commented out.  This is because we will be referencing them in our parameter value.

clip_image002[6]

 

Switching between Parameter Values

I will now demonstrate how to create 2 parameter values that will be used to display the product data in different ways.

The following steps show you how to achieve this.  

1.       We now want to create the new parameter, in the Query Editor, click Manage Parameters from the ribbon.
 

2.       Create one parameter and select ‘List of Values’ as the Allowed Values drop down.  You will see there are two types of list output – one for just EnglishProductName and the other amalgamating the three product columns.   Click OK when happy.


clip_image004[6]

The code for the ‘concat’ function is:
Concat(EnglishProductCategoryName,' - ',EnglishProductSubcategoryName,' - ',EnglishProductName)

Note:     As we are connecting to SQL Server, the ‘Concat’ function is required to bring the product columns together.

3.       Rename the query to ‘DimProduct’.  We now need to insert the parameter into the DimProduct – by clicking the Advanced Editor and replacing EnglishProductName with our new parameter – like below:


clip_image006[6]

4.       You will see the following warning message.  Click Edit Permission.


clip_image008[6]

5.       The message is just warning you that as you are connected to the database, the query could make changes to the underlying objects.  As this is a ‘Select’ query, there is no danger of this.  Click Run.

clip_image010[6]

6.       As we set the default value to be EnglishProductName, this is what we now see in the query pane.  The field in question is called ProductName.

 

clip_image012[6]

7.       To switch to the other parameter value, simply click Manage Parameters > Edit Parameters

clip_image013[6]

8.       Select the other parameter value and click OK.

clip_image015[6]

9.       Now look at ProductName.  It includes all the category and sub category data also.  Wirth the three concatenated fields split by a ‘-‘.

 

clip_image017[6]

10.   That is parameter value switching in a nut shell.  Feel free to experiment with more combinations of values.

Conclusion

Whilst my demo is very simplistic, this is just showing you how powerful a concept Query Parameters can be.  This blog solely focuses on configuring a parameter to display different result sets, depending on what the user wants to see.  Thinking about the entire Power BI suite, we could hook up out parameterized datasets to Dashboards and empower the user to look at KPI’s or trends in data, depending on their role/department.  The sales and customer service departments will focus on similar dimensional data – like customer and date, but the former might want to look at the ‘SalesAmount’ measure, whilst the latter would be interested in ‘RefundedAmount’.  These fields can be contained in one query parameter!

Query Parameters have been around in Power BI for a few months now and the use of the functionality is becoming more wide spread.  Look for more blogs on this subject in the near future.

Further Reading

There are already a couple of great blogs out there and the below links take you to some other excellent use cases:

1.       Chris Webb’s Blog - https://blog.crossjoin.co.uk/2016/05/08/the-m-code-behind-power-bi-parameters/

2.       Soheil Bakhshi Blog (1 of 2) – http://biinsight.com/power-bi-desktop-query-parameters-part-1/

3.       Soheil Bakhshi Blog (2 of 2) - http://biinsight.com/power-bi-desktop-query-parameters-part2-dynamic-data-masking-and-query-parameters/