Mobile Reporting in SSRS 2016 and 2017

If you are a Reporting Services practitioner, the Microsoft mobile report story can be a little confusing with two very different choices.  As I’m preparing to deliver a full day preconference session for the PASS Summit on October 31st titled “Modern Reporting with SQL Server 2016 and 2017 Reporting Services” and a general session during the main conference tiled “Clear Skies and Not a Cloud in Sight – Power BI On-Prem”, this has me pondering about what my mobile reporting message will be.  Being part of the Microsoft MVP Program and participating on a number of product team advisory boards provides insight into how things often work at Microsoft and how products tend to evolve and align over time.  I am forever enthusiastic about using and promoting Microsoft BI and reporting tools, but I am not bound by any covenant to adhere to a simplified marketing message from Microsoft or any other company.  To that end, I share some thoughts about the current state of mobile reporting…

This is the story of two products – or rather one product that is now a service and another product that is now a component of another product.  A few years ago, Microsoft began to formulate a mobile usability story among many fragmented tools.  They had a really good reporting product: SSRS, and they had a pretty good self-service BI capability offered as a bunch of Excel add-ins; namely: Power Pivot, Power Query and Power View – but it didn’t do mobile.  They bought Datazen which was a decent mobile reporting and dashboard tool, designed primarily for IT developers and semi-tech-savvy business pros to quickly create mobile dashboards using traditional data sources.  Datazen wasn’t really a self-service BI tool and wasn’t really designed to work with BI data in the true sense.  It was a good power user report tool but was young and needed to be refined and matured as a product.  Datazen became “Reporting Services Mobile Reports” and was integrated into the SSRS platform as a separate reporting experience with a separate design tool, optimized exclusively for use on mobile devices using platform-specific mobile phone and tablet apps.  Since initial roll-out, product development stalled and has not changed at all since it was released with SQL Server 2016 Enterprise Edition.

Meanwhile, the “Power…” Excel add-ins evolved into Power BI, a free downloadable desktop tool that requires no other Microsoft products and a cloud-based service that requires no other investment but a monthly per-user subscription.  Now there are multiple deployment, scaling and licensing options.  Power BI has been wildly successful and Microsoft continues to sink a ton of resources into the ongoing development, support and innovation of the Power BI platform brand.  Today, Power BI has a very solid mobile story.  Like the Datazen/SSRS Mobile Reports product, it runs on all modern mobile devices in platform-specific phone and tablet apps.  Reports render to HTML5 so it works in all modern web browsers on desktops and mobile devices.  In short, Power BI is all that – with a very bright future; and SSRS Mobile Reports is well, it is what it was when it started – and its future is a little unclear.  And, to make the choice just a little more cloudy, Power BI integrates with Reporting Services in a number of different ways.

Yours truly started writing a book about SQL Server 2016 Reporting Services with Mobile Reports about two years ago when the Datazen was all the rage and Power BI was just a marketing tag line for Excel-based Power Pivot and SharePoint.  In my Wrox Press book, “Professional SQL Server 2016 Reporting Services with Mobile Reports” I thoroughly covered the SSRS mobile reporting capabilities and I still believe it to be a good choice for certain scenarios.  Now that I’m working with consulting clients to add mobile and self-service features on top of reporting and BI solutions, I tend to discuss the capabilities of Power BI before introducing SSRS mobile (if ever).  So here’s my boiled-down analysis and advice at this point in the rapidly-changing mobile BI landscape…

If you need self-service BI that can be viewed on mobile devices, use Power BI.  If it needs to run on-premises without cloud services, the new Power BI Report Server may be the right ticket but some of the mobile capabilities are still in the works.  Either way, SSRS And Power BI are are good pair of tools that Microsoft plans to support for the foreseeable future.

If you need mobile reports developed by IT pros or dedicated report designers in an on-premises (non-cloud) solution for your company, SSRS Mobile Reports should be a consideration but also consider whether Power BI will meet your needs.  If so, it may offer better options and capabilities in the long-term.  Make sure you understand the data latency and volume limitations, and keep in mind that SSRS Mobile Report Reports requires a SQL Server Enterprise license.  Like SSRS Paginated reports, there are no user restrictions for a licensed server.

Consider the licensing options for Power BI.  If you need to support hundreds of users, you will either need to budget for monthly individual user Power BI Pro licenses, SQL Server Enterprise with Software Assurance and/or Power BI Premium to support any number of read-only report users (retail list price is ~5K per month).  Also consider that SSRS Mobile Reports is an Enterprise license feature.  If your company is a SQL Server Standard license shop with no plans to upgrade, your choice is pay monthly per user for Power BI Pro or to make do with standard paginated reports.

Following are links to my upcoming sessions at PASS Summit 2018:

Modern Reporting with SQL Server 2016 and 2017 Reporting Services

Clear Skies and Not a Cloud in Sight – Power BI On-Prem

…and here are the slides from last week’s session at SQL Saturday Vancouver, BC on August 26th:

Slide1Slide2Slide3Slide4Slide5Slide6Slide7Slide8Slide9Slide10Slide11Slide12Slide13Slide14Slide15Slide16Slide17

Implementing Row-Level Security in Power BI

The best method to implement row-level security in a published Power BI model or SSAS Tabular model consumed from the Power BI service will depend to some degree on your data structure and requirements.  The method I demonstrate here is one of the most flexible approaches and one that I commonly use in my projects.

Can’t We Just Get Along? Making SSRS, Power BI and Excel Play Well Together

Please join me and other 2017 PASS Summit speakers for 24 Hours of PASS: Summit Preview on July 19th and 20th.  24HOP is a series of 60 minute on-line sessions presented back-to-back for 24 hours, from the same professionals who will deliver preconference and main conference sessions during the Summit the first week of November this year.  These online sessions are free of charge and normally attended by thousands of individuals to gain insight and knowledge about the topics.  24 Hours of PASS features free educational webinars delivered over 24 hours. Topics covered in this edition include Performance Tuning, SQL Server 2017, Linux, DevOps, Azure, PowerShell, SSRS, Power BI and much more. Browse all sessions. These webinars provide a sneak peek at some of the best practices, expert tips and demos you’ll find at this year’s PASS Summit in Seattle.

image

My session this year is titled: Can’t We Just Get Along? Making SSRS, Power BI and Excel Play Well Together and begins at 7:00 PM Pacific Time.  Make sure to double-check the 24HOP schedule to confirm when you register for this and any other sessions.

Session description:

Choosing the right reporting tool can be a challenge but can different reporting tools be blended to create a unified solution?  Learn the strengths and limits of each; for Self-service, IT-driven and Business-Driven BI, reporting and analytics.  We’ll review an actual solution to see how these tools are being used together to meet the needs of users and technical professionals in different roles in a large organization.

In this 60 minute session, I will preview some of the material covered in the full-day preconference: Modern Reporting with SQL Server 2016 and 2017 Reporting Services full day pre-conference session on October 31st in Seattle.

Modern Reporting with SQL Server 2016 and 2017 Reporting Services full day pre-conference session

I am thrilled to be presenting a full-day preconference session before the PASS Summit, on October 31st.  Please join me for a deep tour of the new capabilities and BI platform integrations for SQL Server Reporting Services 2016 & 2017.  The session will also review the essential skills and tasks to setup and configure the report server and web portal, report design and modern report solution planning.

Here’s an overview of the session:

The 2016 product release was a big upgrade that, once again, brought SSRS into center stage as a market-leading reporting and dashboard design tool for operational and BI reporting.  The big news was that SSRS reports truly run in all modern web browsers and work on mobile devices and all desktops in a new modern web portal.  The introduction of Mobile Reports, formerly known as Datazen, gives mobile device users interactive, tactile and actionable dashboard style reporting on all the popular mobile smartphones and tablets.  The 2017 product brings even more goodness with the integration of Power BI Report Server, on-premises Power BI report integration in a cloud-free platform.  Join Paul for a full-day pre-conference session where you will explore new capabilities and learn to integrate modern reporting into your data platform.

This session covers:

  • What’s new in Reporting Services 2017 (and 2016)?
  • Reporting Services and Power BI Report Server Architecture
  • Parameter improvements
  • Overview of report solution design general best practices
  • Integrating and managing Power BI on-premises
  • Integrating SSRS with the Power BI service
  • Graphical report design
  • Visualization choices
  • Chart visual types and new visual options
  • Indicators and sparklines
  • Creating dashboard reports
  • Advanced and analytic reporting
  • Advanced design techniques
  • Conditional visibility & drill-down
  • Pagination & flow control
  • Error management
  • Drill-through navigation
  • Query design beyond the basics
  • Reporting with SQL Server and Analysis Services
  • Using the DAX and MDX query editors
  • Analytical and Business intelligence reporting
  • Managing report projects and consolidating reports
  • Upgrading from prior versions of SSRS
  • Framing and integrating reports the easy way
  • Introducing Mobile Reports
  • Multi-device mobile reporting, business cases & scenarios
  • Visual control categories & behavior
  • Mobile reports and parameters
  • Filtering an navigating mobile reports
  • Building mobile report solutions
  • Mobile drillthrough – targeting mobile reports and paginated reports
  • Content management
  • Managing the web portal
  • SSRS and Power BI Report Server administration
  • Custom branding
  • Instance caching
  • Server management and configuration

Register for the PASS Summit and preconference here.  Also, look out for a sneak peek of this and many other sessions in the 24 Hours Of PASS which will run continuously through July 19th and 20th.  Watch the PASS Learning Channel for announcements.

Session Recordings: Advanced Data Transformation Techniques, and Power BI Options for the Enterprise

Absolutely amazing….  The Microsoft Data Insights Summit was a series of one great learning experience after another.  My greatest impressions of the summit were that the Power BI community is coming together as a cohesive group of data professionals.  I think Power BI and Microsoft data platform have a solid story now as a solution rather than just a product and it was apparent that business people and technologies are taking it seriously.

In my post before the Data Insight Summit, I shared an overview of the two sessions we were preparing to deliver.  It took the organizers only one day to post all the session recordings from the entire conference for public viewing.  Let me say that again… every session, in its entirety, is available to watch for free online.  Here are my two sessions.

I’m posting this just two days after the summit and the Advanced Data Transformation Techniques session recording has been viewed well over 1,000 times!  Thank you for the tremendous interest in these topics.  I teamed up with Brian Grant to demonstrate several real-world techniques for transforming data with Power Query.

In Power BI Options and Approaches, I showed how Power BI can be used as a first-class presentation tool to analyze and visualize enterprise data in on-premises databases such as SQL Server and Analysis Services.

 

From the Keynote at the Microsoft Data Insights Summit

In sitting up front in the Data Insights Summit waiting for the keynote to begin.  It’s Monday, June 12, 2017.  I’ll update this post with announcements.  We’ve been told to expect some important news and announcement today.  Internet connectivity is pretty spotty so you may see some updates later in the keynote session.

I’m sitting in the Press section with fellow bloggers, who are also sending updates from the Summit keynote.  Check out their notes as well. 

Reza Rad:  http://radacad.com 

Marco Russo: http://sqlbi.com

Matt Allington: http://exceleratorbi.com.au/exceleratorbiblog

James Phillips on stage: “we need to get a bigger room… we’re over capacity. please don’t tell the fire marshal  …just kidding.  No, really… I’m just kidding”

IMG_1178 (002)

Power BI has more data connectors than any other BI product in the industry.

Custom visuals has been a big game changer.  Surprisingly, many are company and industry-specific.  Developers don;t have to use “regular” visuals when they have unique needs.

Power BI is “Planet Scale” …deployed in every geo data center we have on the globe.

Certified & HIPPA-compliant.  The numbers reflect that Microsoft is now the clear leader in the Data Analytics industry.

IMG_1181 (002)

Sirui demonstrating Power BI Premium, which enables you to have “Special Powers”
Premium allows schedule refresh up to 48 time per day.

James: “Actionable BI”  in Power BI and PowerApps  …the loop of amazingness.

Amir Netz: New features being released today:
– EM1, embedded content starts at $625/month
– SharePoint Online embedding
– Teams… select a workspace & reports to embed into a Teams discussion
– Visio Diagram visual control…  shapes are mapped to fields in the model and can be conditionally colored.

IMG_1185 (002)
– PowerApp visual provides real-time write-back embedded in a Power BI report

Import custom visuals directly from the Store in PBI Desktop

Data bars with conditional formatting – in tables…. super cool!

WE HAVE DRILL-THROUGH from page to page!!!!

Bookmarks… “remembers” the context of the page filters and slicers.  New Bookmarks pane allows named bookmarks to be saved.

Spotlight… creates “pop-up” experience for a visual

Show/hide…  different visuals related to bookmarks

Navigation buttons:  need I say more? 

“What-if Parameters” – works like a disconnected table.  Interactive values are reference-able in DAX formulas.

Explain the Increase: like Quick Insights, uses AI algorithm to visualize a data change.

Q&A on the phone app:  UX is like a text message conversation

IMG_1186 (002)

The new feature summary and wrap-up:

IMG_1189

Microsoft Data Insights Summit Live Streaming & Free Sessions

image

(6/15/17 update: view these recorded sessions & notes from the opening keynote address)

If you are fortunate enough to be attending the Microsoft Data Insights Summit in Seattle along with about 2,000 others on June 12 & 13, I’ll look forward to seeing you there.  This will be a great community event and a chance to meet, network and learn from well-known speakers, authors and product team leaders responsible for the best data analytics tools in the industry.  The summit has been sold-out for a few weeks.  If you can’t make it to Seattle to be there in person, you can still attend the conference!  I don’t just mean that you can watch a few cherry-picked sessions and keynote addresses…  You can watch every session for free.  Selected sessions, which do include the keynote addresses, will be streamed lived during the conference but all of the sessions are recorded and will be available within two weeks after the last sessions wrap-up on June 13.  This an awesome learning experience.  I will post links and more details here about the recorded sessions after they become available.

If you are going to the summit, please attend my two sessions:

Monday, June 12, at 1:00 PM
Power BI Solution Options and Approaches
Power BI is a terrific self-service data analysis tool but it is also an enterprise-class reporting and dashboard tool for serious business data stored in a data center. Which options are best and most cost-effective as you plan your solutions? Learn how to build serious, secure, and scalable business solutions with on-premises data sources, SQL server, analysis services and other business systems. You’ll also see how you can architect solutions with security and user-access control utilizing groups and content packs.
(BI Pro / 200 LEVEL / Power BI, SQL Server Analysis Services, SQL Server Reporting Services / Administration and Governance)

Tuesday, June 13, at 10:30 AM
Advanced Data Transformation Techniques
Learn how to solve challenging data import and cleansing problems with Power Query and M functions. This session covers advanced data transformations and shaping techniques for unique data formats and structures. See how to transpose and pivot data, and work with structured data sources like XML & JSON. We will explore the “M” Data Mashup language, create and use query functions and parameters to create durable & manageable automated data refresh processes.
Co-presented with Brian Grant from CSG Pro
(BI Pro / 300 LEVEL / Excel, Power BI / Data Prep and Modeling)

Here’s a sneak peek at the material from both sessions.  You’ll be able to download the slide decks when the sessions are available to view online.

image

In “Power BI Solution Options“, I’ll introduce different ways Power BI can be used with enterprise data & on-premises data sources.  Using Power BI to create self-service data models is a great capability.  In the right hands and used in the right way, it can be a valuable business asset.  Used incorrectly and in the hands of inexperienced users, good data can product bad results.  How can you prevent a train wreck?

image

Power BI, used as a client tool for managed and governed data sources, whether managed on-premises or in managed cloud services, can provide a solution with the best of both worlds; control and governance over enterprise data and flexibility for reporting and analysis.

image

The Power BI platform provides all the components needed to manage analytical data; including queries for data transformation and shaping, data modeling, calculations and visualizations.

image

In enterprise-scale solutions, Power BI is a great data presentation tool but may just be the tip of the iceberg.  Below the surface, we may use SQL Server Analysis Services to manage models and calculations, and data transformation tools – such as SSIS -for data acquisition and transformations.  Formal solutions may incorporate an enterprise data warehouse, data marts and master data management.

image

image

Categorically, there are four options to use Power BI with enterprise data.  These include:

  • Using scheduled refresh with a conventional embedded data model, a cache of data is stored in the Power BI model and refreshed at regular intervals.
  • Direct Connect uses an on-premises or cloud-based Analysis Services data model.  Larger data volumes are managed and secured within the model and calculations are stored within a centrally-managed repository.  This option often provides the best balance between IT governance and reporting flexibility.
  • DirectQuery allows data to remain in SQL Server or another supported on-premises database platform.  This is a good option for solution teams having strong relational database skills who don’t need complex calculations or the flexibility of a tabular or multidimensional model.
  • Developers and solution architects can stream data directly to Power BI dashboard tiles through a REST API.  Streaming can be performed from other data streaming data sources and using the streaming service available through Power BI Premium.

image

image

image

I will demonstrate using Direct Connect and DirectQuery using SSAS and SQL Server on-premises through the On-premises Gateway.

 

In the session titled “Advanced Data Transformation Techniques”, I’ve teamed-up with Power Query guru, Brian Grant.  I love watching Brian teach people how to use these tools, which he knows back-and-forth and always has interesting analogies and object lessons to make learning M and Power Query easier.

image

These are the topics we are covering in our 50 session.  It will be lightening fast and nearly all live demos!

image

First the basics… real quick.  When you use menu options in the Power BI Query Editor, the design tool actually generates steps as M script.

image

One of the most powerful features of the Power Query (officially called “Get Data” feature) is the ability to preview data at the completion of any step in a query.  Essentially, you have the ability to rewind and play-forward each of the query steps and see the data as it will exist after each transformation step.  This rapid design preview experience allows you to effectively build queries in a highly-interactive fashion.

image

Here are three common examples of data that would need to be transformed prior to consumption in a tabular data model.

image

We show you how to transform structured data in a JSON document .

image

How can you pivot Data?  Get it… Pivot Data!  Corny, I know.  There are actually several different ways that data can be transposed, pivoted or unpivoted.  In short, transposing means that all rows become columns and all columns become rows.  …easy.   To Pivot data, we must establish an anchor value of some kind (like the push pin in the corner of the Lt. Commander Data picture ) that will bind like-rows together so other rows can be rotated and transformed into columns.  We’ll show you a practical example by extending the analogy.

imageTo

Error handling is a reality in most any data project and there are several approaches that may be appropriate depending on your data quality requirements.  We share a simple flow chart for deciding how to handle and manage erroneous data.

image

The bottom line is that we must either deal with the erroneous data or not.  If he erroneous rows can be transformed and corrected, life is bliss and we ca move on.  In a financial accounting system where every record must e accounted for, efforts would be made to find and correct all te errors.  If a sampling of rows aren’t worthy to pass the transformation test, and loosing them would not significantly affect the statistical outcome, that may be an acceptable loss.  In that case, removing the noise caused by the erroneous records would be the better option.

image

We will explore the “M” or Data Mashup formula language and demonstrate using some useful examples.

image

 

image

 

image

 

image