Callum

Callum Green's Blog

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/

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.

Sunburst Charts in SQL Server Reporting Services 2016

The Sunburst is the second of the new chart types in Reporting Services 2016.  It was made available by Microsoft in CTP 2.3.  The aim of this blog is to describe what a Sunburst chart does and provide a guide on how to create one.

What are Sunburst Charts?

Like Tree Maps, Sunburst charts are an ideal candidate to display hierarchical data.  They work particularly well with ragged hierarchies and clearly indicates where relationships exist.  A ragged hierarchy is when some levels do not automatically roll up to its parent, e.g. an organizational hierarchy.  The CEO may have two managers – one who has a subordinate under them (COO)and the other who is not responsible for anyone (Secretary).

clip_image002

This is how a ragged hierarchy looks in a Sunburst Chart:

clip_image004

Source: Koen Verbaeck (www.mssqltips.com)

The Sunburst is just like a Donut chart but with multiple layers.  You can see there is an extra ‘ray’ coming out from Feb, as this is the only month that contains weekly data.  The hierarchy may in fact contain a weekly leaf member throughout but because Feb is the only month with values at the week level, none of the other members are visible.

 The whole purpose of this chart type is to help a user visualise how an outer ring (child member) contributes to an inner ring (parent member).  If you have a hierarchy that does not aggregate naturally, I would advise using other SSRS visualisations. 

Creating a Sunburst Chart

I will now show you how to create a simple Sunburst chart in SSRS 2016.  The functionality is available through bothReport Builder and Visual Studio.  For this demo, I will use Report Builder.

1.      Navigate to Report Builder through your configured SSRS web service and choose Blank Report.

2.      Set up your own relevant Data Source, dimension Datasets and report Parameters.  I suggest using a simple fact table and dimension from AdventureWorksDW2016.  A copy of this database can be found here.

3.      Select Insert > Chart > Insert Chart from the Menu Pane.

 

 

clip_image005 

4.      Select the Sunburst chart and click OK. 

clip_image006

5.      Create the Sunburst Dataset, which is derived from the fact table.  The SQL for my chart is as follows:

SELECT

             DC.DateKey,

             CalendarDate,

             'Week ' + CAST(CalendarWeekNumber AS VARCHAR(2)),

             CalendarMonthName,

             CalendarQuarterName,

             CalendarYearName,

             COUNT(*) AS No_Of_Games

FROM          [Warehouse].[DimDateCalendar] DC

INNER JOIN

             [Warehouse].[FactResults] FR

             ON  FR.DateKey = DC.DateKey

INNER JOIN

             Warehouse.DimLeague DL

             ON DL.LeagueKey = FR.LeagueKey

WHERE         DL.LeagueName = 'Premier League'

GROUP BY

             DC.DateKey,

             CalendarDate,

             'Week ' + CAST(CalendarWeekNumber AS VARCHAR(2)),

             CalendarMonthName,

             CalendarQuarterName,

             CalendarYearName

 

The query is bringing back the number of Premier League football matches played on a given day.  It only contains data from 2010 to 2015.

 

image 

6.      Now let’s bring in just one level of the hierarchy and a Sum of the No_Of_Games.

clip_image008

 

7.      If we preview the chart, it still looks very incomplete. 

clip_image002[6]

8.      If we add more Category Groups, it will add additional rings to the outside of the chart.  Eventually building up a number of layers. 

clip_image012

9.      The chart now looks like a Sunburst but it is very difficult to make sense of what is going on.  Therefore, we will add some colours, labels and groupings to the data.

10.   Right Click on the ‘No_Of_Games’ Value property and ensure Show Data Labels is ticked.


clip_image013 

11.   The Sunburst can be partitioned (by colour) depending on the desired attributes.  For simplicity, we will use ‘CalendarYearName’. 

clip_image015



12.   Add any additional chart headers, logos, etc to your report and Save the report.  Click Run to view it.


clip_image016 

13.   That is it - the report is now ready.  You can clearly see a pattern in the data and each year represents a colour. In addition, it is easy to see what proportion of quarters, months and weeks make up a given year.

Conclusion

The Sunburst chart type is a very visual chart type and not to be used to interrogate numbers at a granular level.  It is far better to spot anomalies within a hierarchy or highlight any bias towards a certain segment.

One major drawback I found was that it is very difficult to view or report on accurate information, especially when the data becomes more granular.  This is the age old issues with Pie Charts, which is why the BI community frown upon this chart type.  In the example above, the most outer ring is very busy and virtually impossible to analyse.  I would definitely advise using Tree Maps for more detailed analysis. 

There are also a couple of known limitations in CTP 2.3 for Sunburst charts.  Tooltips only show in the outer ring when an expression is defined and you cannot control the different slices.  On the other hand, Pie Charts enable a user to set a PieStartAngle, as well as a variety of other property options.  I am sure a lot of the missing functionality will be brought in – either in newer CTP releases or when the SQL Server 2016 is fully available.

For more interactive and aesthetically pleasing Sunburst charts, I would advise using either Power BI or D3 components (for web developers).  Not only is it more configurable but you can offer some really cool features like removing ‘ray’s, drill through and chart zoom.  A pre-configured D3 example can be found here.  I had a quick look through the SunburstChart Properties in SSRS 2016 and cannot see such options.

References/Future Reading

For more information on Tree Maps, I recommend the below resources/blogs:

·        Adventure Works DW 2016 CTP 3.0 database download - https://www.microsoft.com/en-us/download/details.aspx?id=49502

·        Koen Verbaeck - https://www.mssqltips.com/sqlservertip/4030/how-to-create-a-sunburst-graph-in-sql-server-reporting-services-2016/

·        Sorna Kumars Muthuraj (MVP) Web Blog  - https://www.youtube.com/watch?v=LKSecJ4_lzE

·        D3 Chart Type Gallery - https://github.com/mbostock/d3/wiki/Gallery

Tree Maps in SQL Server Reporting Services 2016

The current instalment in my SSRS 2016 blog series focuses on one of the two new chart types – Tree Maps.  They were made available to Reporting Services 2016 CTP 2.3.

What are Tree Maps?

Tree Maps are designed to analyse quantitative measures that fit into a hierarchy.  To put it simply, this enables a user to visualise how an attribute is performing against its parent category and in turn, how this category performs against other categories.  The size of rectangle (in the example below) is proportionate to the numeric quantity.

clip_image002

Source: MSDN (Microsoft)

Creating a Tree Map

I will now show you how to create a simple Tree Map chart in SSRS 2016.  The functionality is available through both Report Builder and Visual Studio.  For this demo, I will use Report Builder.

1.      Navigate to Report Builder through your configured SSRS web service and choose Blank Report.

2.      Set up your own relevant Data Source, dimension Datasets and report Parameters.  I suggest using a simple fact table and dimension from AdventureWorksDW2016.  A copy of this database can be found here.

3.      Select Insert > Chart > Insert Chart from the Menu Pane.

 

 

clip_image003

4.      Create the Tree Map Dataset, which is derived from the fact table.  The SQL for my chart is as follows:

SELECT       DT.Country,

             DL.LeagueName,

             DT.TeamName,

             SUM(LT.TotalPoints) AS Points

FROM         [Warehouse].[VwFactLeagueTable] AS LT

INNER JOIN

             [Warehouse].[DimTeam] AS DT

             ON DT.TeamKey = LT.TeamKey

INNER JOIN

             [Warehouse].[DimLeague] AS DL

             ON DL.LeagueKey = LT.LeagueKey

GROUP BY

             DT.Country,

             DL.LeagueName,

             DT.TeamName

 

The query is bringing back the total number of points each English football team has accumulated since the 2010/11 season.

 

image

5.      Create Values, Category Groups and Series Groups (as shown below).  These work in exactly the same way as other versions of SSRS.


clip_image005

6.      Right Click on ‘Sum(Points)’ and select Series Properties.


clip_image006

7.      In the Series Data tab, select the Tooltip Expression.

clip_image008

8.      In the screenshot below, I have concatenated the team name field and the value together, as the Tree Map will truncate descriptions that do not fit in a section.  The logic could be different for your own Tree Map.  Click OK to confirm changes.

clip_image010

9.      Add any additional chart headers, logos, etc to your report and Save the report.  Click Run to view it.


clip_image012

10.   That is it - the report is now ready.  As you can see, ‘Man United’ are not visible until you hover over the applicable section.

Conclusion

Designing and implementing a Tree Map is very easy in SSRS 2016.  Although this is a new chart type, you can set up a simple example in minutes.  A BI analyst, report user or a company CEO can use Tree Maps to quickly spot well or badly performing products/features/departments.  As this chart is hierarchical, you are able to analyse different levels of data in one visualisation.

Once we have grasped the concept, we can begin to create far more sophisticated Tree Maps.  Although I will not be touching on them in this blog, there is plenty of material out there already that goes into greater detail. 

References/Future Reading

For more information on Tree Maps, I recommend the below resources/blogs:

·        Adventure Works DW 2016 CTP 3.0 database download - https://www.microsoft.com/en-us/download/details.aspx?id=49502

·        MDSN Blog - https://msdn.microsoft.com/en-us/library/mt416753.aspx

·        Sorna Kumars Muthuraj (MVP) Web Blog  - https://www.youtube.com/watch?v=mj2eqelJt7E

·        Using Tree Maps in Excel (Microsoft) - https://blogs.office.com/2015/08/11/breaking-down-hierarchical-data-with-treemap-and-sunburst-charts/

My next blog will focus on another new chart type – Sunbursts.   

Customise Parameters Pane in SQL Server Reporting Services 2016

I will be writing a series of blogs that specifically focus on SQL Server Reporting Services (SSRS) - illustrating new features, new chart types, enhancements and general improvements. 

In this article, I will be looking at the new and improved Parameter Pane that was made available in CTP 2.4.

What is a Parameter Pane?

In short, this is a ‘drop down’ field in SSRS.  Any attribute that can be sliced, diced or filtered are strong parameter candidates for a dashboard report.  The screenshot below illustrates how the current Parameter Pane looks in in the current version of SSRS. 

clip_image002[6]

Current Limitations

Although still a very powerful feature in SSRS, the current nagging issue is that developers have very limited control of where a parameter can be placed in a report.  This is always at the top of the page and reliant on parameter ordering in Designer.

It looks like Microsoft have finally listened to the SQL Community and finally rectified this.  Although it has taken what feels like forever, it is good news for any budding Reporting Services developer out there. 

New to 2016 CTP 2.4

I will now demonstrate how easy it is to customise your parameters in SSRS 2016.  As this is only available in CTP 2.4 and above, you will need to use Report Builder to make use of this functionality.

1.      Navigate to Report Builder through your configured SSRS web service and choose Blank Report.

clip_image004[6]


2.      On the View tab, select the Parameters checkbox to display the parameters pane.  For simplicity, tick all of the checkboxes available.

clip_image006[6]

3.      You will now notice a pane appears at the top of the design surface and looks like the below.


clip_image008[6]

4.      Assuming you have a data source and data sets configured, to add a parameter to the pane, carry out the following:

a.      Right click an empty cell in the parameters pane, and then click Add Parameter.

clip_image009[6]

b.      There are other ways to add a Parameter – click here to see these.

5.      To move a parameter to a new location in the parameters pane, drag the parameter to a different cell in the pane.  In my example, I have placed Country in the centre of the screen, but State and City left and right respectively.

clip_image011[6]

6.      To access the properties for a parameter, there is now an alternative option.

a.      Right click the parameter in the parameters pane, and select Parameter Properties.

 

clip_image012[6]

 

7.      To add new columns and rows to the pane, or delete existing rows and columns, right click anywhere in the parameters pane and then click a command on the menu that displays.  These options are shown in the above image but after adding 3 new columns and rows, the Parameter Pane now looks like the following:


clip_image014[6]

 

The white grids clearly indicate where the new columns and rows are positioned.

8.      To delete a parameter from the pane and from the report, you can use the traditional methods, as well as:

a.      Right click the parameter in the parameters pane, and then click Delete.

clip_image016[6]

image

9.      Now Run the report to visualise how the drop downs will show in a report.

clip_image017[6]

10.   That is it.  You can now view the parameters.


clip_image019[6]

Conclusion

Although this is a step in the right direction, the parameters are still limited to the top of an SSRS report.  However, you can develop custom workarounds, such as Excel-style Slicers - suggested by Simon Sabin in his blog. 

It is surprising that Microsoft have not come up with a fully configurable Parameter Pane as the current workarounds are not perfect.  I am hoping further enhancements are added before the full version of SQL Server 2016 is released. 

References/Future Reading

For more information on Report Parameters, I recommend the below resources/blogs:

·        Koen Verbeeck’s SQL Server Blog - https://www.mssqltips.com/sqlservertip/4088/customize-the-sql-server-reporting-services-2016-parameter-pane/

·        MDSN Blog (Customize the Parameters Pane) - https://msdn.microsoft.com/en-GB/library/mt574039.aspx

·        MDSN Blog (Chang Parameter ordering) - https://msdn.microsoft.com/en-GB/library/dd255258.aspx

·        Simulating Slicers in SSRS Reports - http://sqljason.com/2012/07/simulating-slicers-in-ssrs-reports.html

 

Look out for future blogs on SSRS2016; one particular interest is Report Builder, which Microsoft have apparently greatly improved. I have yet to fully dissect all of the new features so we will see whether the changes are significant enough to improve on the poor adoption seen previously.