Callum

Callum Green's Blog

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/

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.

New Features in SQL Server 2016 – Part 3: Reporting Services

I am pleased to announce Microsoft have made some improvement to Reporting Services (SSRS), in the upcoming version of SQL Server 2016 CTP2.  With the new acquisition of Datazen and advances in Power BI, SSRS seemed to be falling behind – both in terms of functionality and look and feel.

In the Preview version of SQL Server 2016, the following features have been announced or improved:

·        Built-in R Analytics.

·        Improved DPI in Report Builder.

·        Reporting Services Subscriptions.

Within the SQL Community, there has been a lot of talk around some of the existing SSRS functionality being improved and made more modern.  As SQL Server 2016 CTP2 has only been public for a couple of weeks, not all SSRS features are yet available.  The below are expected to be in the full release, although they have yet to be officially announced.

·        New Parameter Panel, chart types and design.

·        Power View included in SSRS.

·        Mobile BI.

Improved DPI in Report Builder

DPI (Dots per Inches) on new devices make rendering reports very difficult in the current version of SSRS.  Although these devices have high DPI, this means that more pixels need to be drawn and the display can look very small.  SSRS 2016 will be able to handle high DPI, without distorting reports.

1.      Windows 8.1 DPI Scaling Enhancements.

a.      Optimizing the usability and readability of high-DPI displays.

b.     Empowering developers to optimize app-specific scaling based on display DPI.

2.      High DPI and Windows 8.1.

Reporting Services Subscriptions

The following changes have been made to the Reporting Services subscriptions, depending on native SSRS mode and SharePoint.

Native and SharePoint:

1.      Subscription description.

a.      Include a description of the report as part of the subscription properties. The description is included on the subscription summary page.

2.      Change subscription owner.

a.      Starting with the SQL Server 2016 Community Technology CTP2 release, you can change subscription owners using the user interface or script. This helps when carrying out routine tasks e.g. when users leave or change roles in your organisation.

 

Native ONLY:

1.      Enable and disable subscriptions.

a.      User interface options to quickly disable and enable subscriptions. Disabled subscriptions can be easily re-enabled.

2.      Shared credential for file share subscriptions. Two workflows now exist with Reporting Services file share subscriptions:

a.      Reporting Services administrator can configure a single file share account, which is used for one to many subscriptions.

b.     Configure individual subscriptions with specific credentials for the destination file share.

Built-in R Analytics

The enables Data Scientists to use R scripts to plug into SSRS reports.  R is a very powerful analytical tool that can cluster data and create powerful trending capabilities.

1.      Previously integrating R with SSRS involved an unofficial plug in (to Visual Studio) from Codeplex.  To download the add-in, click here.  I have also provided a link to Jen Underwood’s blog, found under ‘References’.

2.      New built in R component will enable Data Scientists to interact and tests R scripts in a transactional environment.

3.      Create SSRS reports referencing R scripts.

clip_image002[4]

New Parameter Panel, Chart Types and Design

According to Gilbert Quevauvilliers, SSRS will undergo quite an overhaul in SQL Server 2016.  Some of the improved features include:

1.      Parameters panel will be more interactive and respond in a similar way to the Slicers in Excel.

2.      New chart types being provided, similar to the ones in Power BI.

3.      HTML to replace Silverlight, which will ensure reports can render in the most used browsers.  Rendering speeds will also increase.

Power View included in SSRS

The talk on Social Media sites (like twitter) is that Power View will be added to SSRS 2016 CTP3.  This could give the following benefits:

1.      Almost any data source will plug in to SSRS.

2.      Better, more modern chart types – improving user analysis.

3.      Potentially the ability to write native M into an SSRS report.

Mobile BI

SQL Server 2016 will support Mobile BI and data visualisation.  The devices currently supported are Windows, iOS and Android devices. 

1.      Users can now visualise and interact with data more easily, without additional charge.

2.      Ability to connect to enterprise data sources and use Active Directory for user authentication.

3.      Deliver live data updates on mobile devices and personalise data queries for different users.

References

For more information on all of the new SSRS SQL Server 2016 features, the below resources/blogs are highly recommended.

·        Official Microsoft Page -
https://msdn.microsoft.com/en-us/library/ms170438(v=sql.130).aspx

·        Gilbert Quevauvilliers BI blog

https://gqbi.wordpress.com/2015/05/07/bi-nsight-sql-server-2016-power-bi-updates-microsoft-azure-stack/

·        Matt Landis Blog -
http://windowspbx.blogspot.co.uk/2015/05/sql-server-2016-reporting-services-ssrs.html

·        Jen Underwood’s R in SSRS Blog -

http://sqlmag.com/sql-server/codeplex-r-graphics-project-reporting-services