Paul Turley's SQL Server BI Blog

sharing my experiences with the Microsoft data platform, SQL Server BI, Data Modeling, SSAS Design, Power Pivot, Power BI, SSRS Advanced Design, Power BI, Dashboards & Visualization since 2009

Menu

Skip to content
  • Home
  • My Books
  • Doing Power BI The Right Way – for Enterprise Reporting
  • Paginated Report Recipes eBook
    • 01: Alternate Row Table “Green Bar” Report
    • 02: Alternate Row Shading in Matrix (with Column Groups)
    • 03-Reusable Report Template
    • 04-Drill-through from Power BI to Paginated Report On-premises
    • 05-Parsing Multi-Value Parameters
    • 06-Sending Customized Paginated Reports to Multiple Recipients
    • 07-Creating a Calendar Report
    • 08-Horizontal Table Report
    • 09-Customizing Gauges with Images
    • 10-Histogram Chart
    • 11-Dynamic Chart Sizing
    • 12-Drill-Through for a Multi-Level Matrix Report
    • 13-Column Chart with Target Line
    • 14-Creating a Checkbox List to Show Existing Records
    • 15-Creating Sparklines
    • 16-Drill-Through Report Link Breadcrumbs
    • 17-Heatmaps: Using Color to Highlight Larger Amounts of Data
    • 18-Spatial Data – Visualizing the Geometry Data Type
  • Best Practice Resources: Blogs & Books from the Experts
  • Presentations
  • Video Tutorials
  • COVID-19 Daily Updates Report
  • Visualizations
  • About/Bio
  • Paul’s Bio
  • Note to SPAMers

Category Archives: SQL Syndication

T-SQL for Performance and Accuracy – Vern Rabe: A preconference essential for anyone using SQL Server

January 11, 2018 by Paul Turley

1

SQL Saturday Preconference: February 9th, 2018 in Redmond, WA

Do you think you write T-SQL queries that perform well?  Think again.  I learn something important – and I mean career and life-altering important – when I attend one of Vern’s sessions on T-SQL optimization.  He taught me SQL Server a long time ago and I recommend this all-day session to anyone who needs these skills.

It seems like there’s always a query or a stored procedure that takes way too long to execute. Don’t be quick to blame it on a missing index, or out of date statistics, or insufficient memory. Often a slow performing query can be drastically improved with a rewrite. This preconference will teach you how to write T-SQL with two goals in mind: Getting the results you expect (accuracy), and quickly (performance).

In this preconference we will start with some SQL Server internals, where you will learn how the optimizer determines the execution plan, which join algorithms should be used in which situations, and some of the right and wrong choices that the optimizer can make. We’ll discuss how statistics are key to the optimizer’s choices, how the plan cache is used (for good or bad), how to minimize blocking, and how the ubiquitous transaction log is a performance factor in just about everything.

After we discuss the internals of query execution, you will learn how to use various tools to identify poorly performing queries and the reasons for their poor performance. We’ll dig deep into the execution plan, identifying some details of how the optimizer chose to execute a query. We’ll learn about tell-tale warnings, estimated vs. actual plans, and parallel execution threads. We’ll also learn where the graphical execution plan can mislead you.

Next, we’ll get into specific of T-SQL queries, where we’ll discuss many of the query writing mistakes that can lead the optimizer down a bad path, and most importantly, how to rewrite those queries to give the optimizer the appropriate information to make good decisions. All while making sure the results are exactly what they should be.

Some of the topics covered:

  • Graphical Execution Plans
  • Statistics
  • Cardinality estimator
  • Plan cache
  • Parallelism
  • SARGABILITY
  • Implicit conversion
  • UDFs
  • NOLOCK

All attendees will receive a USB flash drive with a copy of the PowerPoint presentation and all of the demonstration SQL scripts. Although not necessary for the class, if you bring a laptop configured with a USB port and SQL Server 2016, you can execute the scripts along with the presentation.

https://www.eventbrite.com/e/t-sql-for-performance-and-accuracy-tickets-41172854115?aff=es2

Share this:

  • Click to email this to a friend (Opens in new window)
  • Click to print (Opens in new window)
  • Click to share on Twitter (Opens in new window)
  • Click to share on LinkedIn (Opens in new window)
  • Click to share on Facebook (Opens in new window)
  • Click to share on Reddit (Opens in new window)

Like this:

Like Loading...
Posted in Microsoft BI Platform, SQL Server, SQL Syndication, SQLServerPedia, T-SQL. Tagged Redmond SQL Saturday, SQL Server, SQL Server Optimization, Vern Rabe

Interview with Guy In A Cube’s Adam Saxton and Patrick Leblanc

December 19, 2017 by Paul Turley

0

Since starting the Guy In A Cube series over three years ago, Adam Saxton has become the front man for Microsoft Business Intelligence feature announcements and Power BI how-to tutorials.  Joined by Patrick Leblanc last year, the Guy In a Cube series features over 200 short video tutorials with at least two new clips added every week.  The Guy In A Cube YouTube channel currently has over 13,000 followers.

I always look forward to spending time with both of these guys at community events.  I first met Adam several years ago when he was a Senior Support Escalation Engineer for Microsoft who helped with a number of tough BI server setups.  Patrick did senior level consulting work for customers in the field before joining Microsoft as Data Platform Solutions Architect.  Adam and Patrick are natural born entertainers.  With their deep knowledge and expertise with database technology, security and the entire BI, analytics and reporting stack; they offer one of the top online resources for learning and keeping up with the rapidly-expanding Microsoft BI platform.

At conferences and events, I rarely see Adam without a camera in his hand and so was a real treat to get them in front of my camera for this interview.  We chatted about how they got started and how they continue to use their series to highlight new and important features, and to provide guidance to support the Microsoft BI community.

In this interview during the 2017 PASS Summit, we continue to explore the topic of this series of blog posts on Microsoft BI solutions for the enterprise.  Patrick and Adam share their guidance about managing large-scale solutions, version control and multi-developer projects using Power BI, Analysis Services and SQL Server.

Share this:

  • Click to email this to a friend (Opens in new window)
  • Click to print (Opens in new window)
  • Click to share on Twitter (Opens in new window)
  • Click to share on LinkedIn (Opens in new window)
  • Click to share on Facebook (Opens in new window)
  • Click to share on Reddit (Opens in new window)

Like this:

Like Loading...
Posted in BI Industry, BI Projects, Business Analytics, Microsoft BI Platform, MVP Community, PASS, Power BI, SQL Server, SQL Syndication, SQLServerPedia, SSAS Administration, SSAS Design, SSRS Administration, SSRS Design, Tabular Models.

Enterprise Analysis Services Development: Interview with Alberto Ferrari and Marco Russo

December 2, 2017 by Paul Turley

1

 

Between sessions at the PASS Summit, I had the privilege of interviewing Alberto Ferrari and Marco Russo; SSAS Maestros and BI industry thought leaders. Alberto and Marco are revered as expert trainers and practitioners of SSAS and Power BI solutions. They are also well known for evangelizing best practices using the DAX calculation and query language. We spoke about some of the challenges and learnings they have gained from years of large-scale project implementations.

Both SSAS Tabular and Power BI Desktop share the same characteristic in that the semantic data model definition is described in a single file. Compared with older multidimensional model projects, the single file approach simplifies much of the development effort but it can be challenging to coordinate projects with multiple developers. They shared tool recommendations to assist with project integration, version management and development. Marco and Alberto recommend a number of community supported add-ins and other freely available software to enhance Visual Studio and Power BI Desktop.

I sincerely appreciate these two gentlemen taking the time to share their insights in this short interview, and their many contributions over the years that continue to improve Microsoft’s industry-leading Business Intelligence platform and the community that supports it.  A comprehensive library of resources, training, tools and articles are available at: SQLBI.com.

During the interview we talked about several different tools that they adamantly endorse for any SSAS developer to have at their disposal. From my own experience, in rough order of priority, I recommend :

  • BIDS Helper / BI Developer Extensions – This Visual Studio add-in is an important tool for anyone developing Business Intelligence solutions in any version of Visual Studio or SQL Server. It includes several essential features and capabilities for SSIS, SSRS and SSAS projects. It has been a community-developed and community-supported tool for many years and is considered by most experienced BI developers to be a core extension of the Visual Studio SSDT (formerly called “BIDS”) add-in.
    Community developed tools like this help to spur future enhancements to the officially-supported capabilities in later Microsoft development tooling.  I suggest that you carefully read the documentation for SSAS Tabular features in this tool because some of the options only apply to certain versions of SSAS. Some of the BIDS Helper features for SSAS 2012 and 2014 have since been added to the officially-supported designer for SSAS 2016 and 2017. BIDS Helper was initially maintained on the Microsoft CodePlex site (here: http://bidshelper.codeplex.com/) and has since been moved to GitHub here: https://bideveloperextensions.github.io/. It can now be installed in the Visual Studio Tools > Extensions and Updates menu by search for “BI Developer Extensions” in the Online section.
  • DAX Studio – This is a stand-alone application and the most comprehensive DAX, SSAS and Power BI calculation script and query editing and performance tool available. It is an open source project managed and maintained by a collaboration of several community MVPs and industry leaders (coordinators are Darren Gosbell & Marco Russo). The latest version and codebase have recently been moved from CodePlex to http://daxstudio.org.
  • Tabular Editor – An open source, stand-alone SSAS Tabular model design tool that runs outside of Visual Studio. It is distributed through a GitHub repo at: https://tabulareditor.github.io. The source code is maintained at: https://github.com/otykier/TabularEditor. It is described as a lightweight editor for SSAS Tabular Models built in .NET/WinForms and utilizes the Analysis Management Objects (AMO) library.
  • DAX Editor – an extension to SSDT that makes it easier to edit DAX measures in a Tabular project. It includes: DAX Syntax Highlighting, DAX Formatting and Textual representation of measures defined in a Tabular project
    Extracting/importing measures from/to a Tabular model file (.BIM). This add-in is developed and maintained by SQLBI. It can be found in the Visual Studio > Tools > Extensions and Updates menu or downloaded from SQLBI at: https://www.sqlbi.com/tools/dax-editor.
  • BISM Normalizer – An add-in for Visual Studio/SSDT that can be installed from the Visual Studio Tools > Extensions and Updates menu by search for “BISM Normalizer”. This is an essential tool if you need to compare multiple SSAS Tabular projects and databases (including workspace databases) and then manage the steps to selectively merge additions and differences between objects. I have found this useful for integrating parallel development work on the same SSAS Tabular project but I will warn you that if used routinely to merge multiple changes to a single project, it can be a complex undertaking and not something I recommend on a daily basis.
    BISM Normalizer is developed and maintained by Christian Wade, formerly an independent consultant and now a Senior Program Manager on the SSAS product team. Although Christian works for Microsoft, this is still considered a community-supported, third-party tool not officially supported by Microsoft. It is, however, the only tool designed specifically to perform schema comparisons and object merging in SSAS.

In our discussion, we also refer to these tools and resources:

  • VertiPaq Analyzer – An older tool (actually an Excel Power Pivot workbook) created by Kasper de Jonge, Program Manager from the Power BI/SSAS product team, that imports SSAS DMV query results and reports object size, object data compression rates and optimization statistics. It has since been updated and enhanced. This is a convenient way to get information quickly about your SSAS or Power BI model. The same information can be obtained, with a little more effort, by running DMV queries in DAX Studio or SSMS. Links to the original and updated versions are available on the SQL BI site at: https://www.sqlbi.com/articles/data-model-size-with-vertipaq-analyzer/.
  • DAX Formatter – An online tool for optimizing and reformatting DAX expressions, available from SQLBI, at: http://www.daxformatter.com/
  • Tabular Modeling in Microsoft SQL Server Analysis Services – is a Microsoft Press book written by Alberto and Marco that applies mainly to SSAS Tabular 2016 and 2017 projects. This is an absolutely invaluable reference and tutorial on SSAS design best practices and design techniques. Previous books from our Italian friends include DAX Patterns and The Definitive Guide to DAX; all excellent learning resources that I have in my book library.

Shortly after this conversation, Marco and Alberto delivered two sessions at the PASS Summit conference about DAX best practices and performance optimizations. A little later, Marco sat with Guy In A Cube hosts Adam Saxton and Patrick Leblanc to chat about these sessions. You can find that interview here. I also had a chance to catch-up with Adam and Patrick in an earlier interview during the conference, which I will share in a later blog post.

Share this:

  • Click to email this to a friend (Opens in new window)
  • Click to print (Opens in new window)
  • Click to share on Twitter (Opens in new window)
  • Click to share on LinkedIn (Opens in new window)
  • Click to share on Facebook (Opens in new window)
  • Click to share on Reddit (Opens in new window)

Like this:

Like Loading...
Posted in BI Industry, BI Projects, DAX, Microsoft BI Platform, Power BI, SQL Syndication, SQLServerPedia, SSAS Administration, SSAS Design, Tabular Models. Tagged Albert Ferrari, ENterprise SSAS, Marco RUsso, PASS Summit 2017, SSAS Tools BI Development Tools

Power BI Hands-On Workshops in April, 2017

March 2, 2017 by Paul Turley

1

During the month of April, I will be delivering three full-day Power BI hands-On workshops.  Each of these events will be the Friday preceding these SQL Saturday events.  Seating is limited and many of these workshops tend to book-up.  Follow the links to register.

Huntington Beach, Orange County, CA;  March 31
SQL Saturday: April 1

Madison, WI;  April 7
SQL Saturday: April 8

Redmond, WA;   April 14
SQL Saturday: April 15

The format will be the same for each event.  These are intermediate-level workshops.  If you’re new to Power BI, just a little self-study should get you ready to optimize your learning experience.

Event Description:

In this Power BI hands-on Workshop, we will quickly review the essentials and learn some advanced techniques to transform, model and analyze business information with Power BI Desktop. Techniques and best practices presented are from several prior workshops and years of field experience. Learn to use self-service and enterprise-scale Power BI capabilities; gain valuable skills to integrate, wrangle, shape and visualize data for analysis.

If you’re just getting started, please pick-up an intro book or use online resources to familiarize yourself with Power BI Desktop so you can take your skills to an intermediate level in the workshop. If you have the basics, be prepared to take your skills to the next level; learn to address data and reporting challenges with advanced design techniques. At the conclusion of the workshop, you will have a complete solution built from real business data, shaped, cleansed & modeled; with a dashboard and interactive report visuals ready for analysis.

Topics & skills include: Power Query/M, modeling and calculations/DAX, standard & custom visuals, R visuals. Data sources: CSV, JSON, SQL Server; query folding, scheduled refresh & DirectQuery.

Attendees should have at least intro-level experience w/Power BI Desktop or Power Pivot. Bring laptop w/latest Power BI Desktop ver. installed (PowerBI.com), 64-bit Windows & 4 GB of RAM (8 GB recommended), 2 GB free storage. Power BI subscription recommended, not required.

Share this:

  • Click to email this to a friend (Opens in new window)
  • Click to print (Opens in new window)
  • Click to share on Twitter (Opens in new window)
  • Click to share on LinkedIn (Opens in new window)
  • Click to share on Facebook (Opens in new window)
  • Click to share on Reddit (Opens in new window)

Like this:

Like Loading...
Posted in BI Industry, Business Analytics, Data Visualization, Microsoft BI Platform, MVP Community, Power BI, Self-service BI, SQL Saturday, SQL Server Pro Magazine, SQL Syndication, SQLServerPedia.

Gartner Magic Quadrant for BI 2017 Top 3 Rankings

February 17, 2017 by Paul Turley

2

This is a different spin on the top three vendor rankings visualized in Power BI.  According to the placement of the chart elements in the “Visionary/Leader” quadrant, Microsoft leads Tableau by a little over 36% in the “Vision Completeness “ score and Tableau beat out Microsoft on the “Ability to Execute” scale by just 1.09%.  I’ve always wondered just how fine Gartner’s ranking scale is.  Disclaimer – I’m simply repurposing the chart image as I explain below and don’t guarantee the accuracy of these results. Continue reading →

Share this:

  • Click to email this to a friend (Opens in new window)
  • Click to print (Opens in new window)
  • Click to share on Twitter (Opens in new window)
  • Click to share on LinkedIn (Opens in new window)
  • Click to share on Facebook (Opens in new window)
  • Click to share on Reddit (Opens in new window)

Like this:

Like Loading...
Posted in Gartner, Microsoft BI Platform, Power BI, Self-service BI, SQL Server Pro Magazine, SQL Syndication, SQLServerPedia.

Professional SSRS 2016 Book Preview Posts

January 22, 2017 by Paul Turley

16

This page is a table of contents for for several new and forthcoming posts.  I’m posting a series of excerpts for my Wrox Press book: Professional SQL Server 2016 Reporting Services and Mobile Reports.  Each of the posts is a condensed version of the material covered in a corresponding chapter from the book.  Although I would love for you to buy the book to get the full edition of each topic, each post will contain valuable information that I hope will be informative and educational on it’s own; whether you buy the book or not.  I’ll update this index with topics and links as I continue to add each post.  Until then, some of these will serve as placeholders for future posts.

image

Please post comments or contact me through my blog if you have questions or feedback, or if you are interested in training and consulting related to this material. – Paul

Continue reading →

Share this:

  • Click to email this to a friend (Opens in new window)
  • Click to print (Opens in new window)
  • Click to share on Twitter (Opens in new window)
  • Click to share on LinkedIn (Opens in new window)
  • Click to share on Facebook (Opens in new window)
  • Click to share on Reddit (Opens in new window)

Like this:

Like Loading...
Posted in BI Projects, Business Analytics, Microsoft BI Platform, SQL Server, SQL Server Pro Magazine, SQL Syndication, SQLServerPedia, SSRS Administration, SSRS Design.

Publish Power BI Reports to SSRS On-premise Preview Now Available

January 17, 2017 by Paul Turley

7

This just in from the Reporting Services product team:

“Power BI reports in SQL Server Reporting Services: January 2017 Technical Preview now available”  This feature addition will allow Power BI reports to be published to a local SQL Server Reporting Services server, entirely-on-premises without using the Power BI cloud service.

The January 2017 Technical Preview can be downloaded from: https://www.microsoft.com/en-us/download/details.aspx?id=54610

I literally just received this update from Microsoft and plan to download and start working with the preview.  I’ll post updates with my experience.

This is not an update for SQL Server 2016 but it is actually the next version of SQL Server which is due out in a few months.

Please check back for updates!

More information on the SQL Server Reporting Services Team Blog

Share this:

  • Click to email this to a friend (Opens in new window)
  • Click to print (Opens in new window)
  • Click to share on Twitter (Opens in new window)
  • Click to share on LinkedIn (Opens in new window)
  • Click to share on Facebook (Opens in new window)
  • Click to share on Reddit (Opens in new window)

Like this:

Like Loading...
Posted in BI Industry, BI Projects, Data Visualization, Microsoft BI Platform, Self-service BI, SolidQ, SQL Server, SQL Server Pro Magazine, SQL Syndication, SQLServerPedia, SSRS Administration.

Power BI on-Premises Production Targeted for Mid 2017

December 16, 2016 by Paul Turley

3

NEWS FLASH: Power BI reports can be deployed to SQL Server Reporting Services web portal.  The production-ready release is targeted for mid 2017.  This is much sooner than most folks in the community were anticipating.  An installable technical preview is targeted for January of 2017.  This announcement was just made on the SQL Server Reporting Services Team Blog.

From the announcement:

Which Power BI capabilities do you plan to add to SSRS?

We’re focusing our efforts on adding Power BI reports to SSRS and on supporting the features Power BI Desktop offers for use within these reports, including a variety of data connectors and visualizations. Beyond the current Technical Preview, we plan to add support for

  • 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)

Given our focus on Power BI reports, we have no current plans to add other Power BI features (such as “dashboards,” Q&A, Quick Insights, and others) to SSRS.

What can we expect in the next Technical Preview of Power BI reports in SSRS?

With the current Technical Preview, we used a pre-configured Azure VM to offer you a preview that’s quick and easy to try. Our focus for the next Technical Preview is on a version you can download and install on your own VM or server, a necessary next step toward a production-ready version. Aside from this aspect, the functionality will be similar to the current Technical Preview’s.

When will we have this next Technical Preview?

We’re targeting January 2017 to release this next Technical Preview.

What’s the release vehicle for a production-ready version?

We plan to release the production-ready version in the next SQL Server release wave. We won’t be releasing it in a Service Pack, Cumulative Update, or other form of update for SSRS 2016.

When will we have a production-ready version?

We’re targeting availability in mid-2017.

Can I deploy SSRS 2016 today and migrate to SSRS with Power BI reports when it’s available?

Yes, we aim to make it easy to migrate to SSRS with Power BI reports from SSRS 2016 and previous versions.

The complete blog post can be found here: https://blogs.msdn.microsoft.com/sqlrsteamblog/2016/12/16/power-bi-reports-in-sql-server-reporting-services-feedback-on-the-technical-preview/

Share this:

  • Click to email this to a friend (Opens in new window)
  • Click to print (Opens in new window)
  • Click to share on Twitter (Opens in new window)
  • Click to share on LinkedIn (Opens in new window)
  • Click to share on Facebook (Opens in new window)
  • Click to share on Reddit (Opens in new window)

Like this:

Like Loading...
Posted in BI Industry, Business Analytics, Microsoft BI Platform, Power BI, Self-service BI, SolidQ, SQL Server, SQL Syndication, SQLServerPedia, SSAS Administration, SSRS Administration, SSRS Design.

Analysis Services and Power BI: Time-travel, Space-warping and Teleportation

December 10, 2016 by Paul Turley

1

As I visit businesses, consulting clients and training classes, to teach data reporting and analytics; there is a recurring conversation.  It is one that I have had for eighteen years.  The audiences change and the technology implementations are a little different over time, but the essential conversation is still the same.

This happened again last week as I was trying to explain the unique characteristics of Multidimensional and Tabular storage to a client.  I’m developing a training course where I needed to explain the concepts once again – and then it hit me!  …these unique data storage and analytic technologies do what we dream about in science fiction stories and achieve capabilities we imagine existing in the distant future.  Channel surfacing on television this week, I watched an episode of Timeless, a Star Trek rerun and a Star Wars movie – where time-travel, space-warp travel and teleportation were commonplace realities.  Although fictional as they were portrayed, I think these concepts are very real in our technology landscape.  Please indulge me as I explain.

We live in a Linear world.  We reside in a place, work in a place, store and move stuff from place to place.  Centuries ago, if man needed to move something (perhaps just himself) to a distant place, he would walk, ride a horse or take a sailboat.  In weeks or months, he would arrive in another place.  Today, we get in a car, navigate the streets, perhaps highways and Interstates and then arrive in a different place within minutes or hours.  For longer trips, we board a large metal tube, sit very close to several complete strangers as we climb up into the sky; some period of time goes by and then we somehow arrive in a distant place along with our stuff.  At 35,000 feet where the air is very thin, a jet airplane can travel many times faster; leaving an altitude where certain physical laws restrict speed, only to re-enter that atmosphere after it has arrived.  To someone from the distant past, this experience would seem fictional.

Image result for jet climping

image: nasa.gov

On a daily basis, I sit or stand in front of my computer monitors, and see and speak with people in distant places.  We have live conversations about the weather in their time zone or hemisphere.  Through some strange but now common process, technology converts our speech, images, directions, documents, effort and thoughts into 8-bit network packets and electrons that travel almost instantly through space; leaving the earth for a short period, to be transported and reassembled somewhere else.

Years ago, when I wanted new stuff, I would drive to the store.  If they didn’t have what I wanted or if it was too expensive, I would spend the day driving to different stores.  Today, I “go” to the store on my computer and the very next day, my stuff arrives on my doorstep.  We are continually inventing ways to bend space and teleport things within the changing confines of reality.

Data storage is much like the real world.  We store terabytes and petabytes (numbers we can’t even explain) in a small space.  But, to navigate through the linear storage structure of relational databases and files, the data might as well be thousands of miles or kilometers apart.  In order to perform time-variance and statistical calculations, program code must access a group of records in one location to aggregate them, and then find a related group of records – perhaps millions of rows apart – to perform another aggregation.  The query might need to perform this operation dozens, hundreds or thousands of times; before it can calculate the final results.  One of the best examples is a time-series calculation where a query must “visit” records for each time period to perform an aggregation.  The effort is compounded when the goal is to compare aggregate values and variances  for parallel and relative periods (e.g. “one year ago”, “three years ago”, etc.) and then perform forecasting or regressions.

image

image: ctdn.com

In relational storage, the query engine must “drive” from place to place, navigating the streets (file groups, files, pages and rows) to find the records.  In an analytic structure (like an Analysis Services cube, or SSAS Tabular model or Power Pivot/Power BI in-memory model), the query engine performs a calculation operation and then teleports to a new location to find a different group of related records. 

image: space.com

In a multidimensional cube, the storage processing code quite literally folds space to create the cube structure and pre-calculate some of the aggregate values.  Rather than leaving all the data in a linear table, it actually moves data segments close together, reshaping the data structure (thus, the term “cube”).  This approach required a great deal of complexity in both the software and the resulting data structure.  Advances in computer hardware and software techniques helped simplify the approach.  The in-memory tabular model exists in linear, yet fragments of compressed space – where the actual distance between adjacent records is much closer than it appears to be.  When the query process is done looking up a group of records it warps to the next group of related records using a memory address.  Instead of traveling through space, it steps out of it, like the jet airplane leaving the atmosphere, to re-enter in a different location.  It takes much less time to run queries because it is not moving through all the rows of linear tables.

Image result for VertiPaq Compression

image: microsoftpressstore.com, Ferrari, Russo

By using unique and innovative data storage methods (to reduce storage space and access data faster without moving through data), we can, in effect; perform time-travel, warp space and teleportation.

Share this:

  • Click to email this to a friend (Opens in new window)
  • Click to print (Opens in new window)
  • Click to share on Twitter (Opens in new window)
  • Click to share on LinkedIn (Opens in new window)
  • Click to share on Facebook (Opens in new window)
  • Click to share on Reddit (Opens in new window)

Like this:

Like Loading...
Posted in Articles, BI Industry, BI Projects, Microsoft BI Platform, MVP Community, Power BI, PowerPivot, Self-service BI, SolidQ, SQL Server, SQL Server Pro Magazine, SQL Syndication, SQLServerPedia, SSAS Design, Tabular Models.

Migrating reports between SSRS report servers simplified with new PowerShell commands

December 3, 2016 by Paul Turley

8

I recently needed to replicate all of the reports on one report server to a different server.  I started downloading each report one at a time to a file system folder, moving them to the other machine and then uploading each RDL file.  Needless to say, that’s a slow and painful process.  Since a new set of PowerShell command lets (Cmdets)  were introduced for Reporting Service just a couple of weeks ago, I decided to give them a try and share the results.

Aaron Nelson, long-time Microsoft Data Platform MVP and PowerShell fanatic, has literally been hounding me for the past few weeks to take some time and look at a new PowerShell library and scripts originally developed by Parth Shah from the SSRS product team.  Jaime Tarquino also played a big role in automating the build process.  It’s the personal commitment of folks at Microsoft like Parth and Jaime that really make a product like SSRS shine.

Aaron can be pretty persistent and I admit it’s ironic that I’ve been too busy finishing up the final editing work on the Wrox SSRS 2016 book and the new edX SSRS course to look at these RS PowerShell CmdLets but I’m glad that I did because they’re an absolute gold mine.

Back in July Aaron asked me to post some questions about what PowerShell support members of the Reporting Services community would like to see.  That post is here.  We got some good feedback to help the product team prioritize their efforts in this area.  Aaron and and Chrissy LeMaire  worked closely with the Reporting Services team to improve the commands before they were recently announced, and the team cited their help in this announcement: https://blogs.msdn.microsoft.com/sqlrsteamblog/2016/11/07/community-contributions-to-the-powershell-scripts-for-reporting-services/

Back to my project…  I have developed several demo and lab exercise reports for the new edX SSRS course:  Analyzing and Visualizing Data with SQL Server Reporting Services.  The development environment is an Azure virtual machine that I recently didn’t have access to while travelling and needed to move all the reports to a local VM to continue my work.  I needed to make sure that current versions of these dozens of reports are on both servers.  Using a couple of new PowerShell commands, this was quite easy.  Here’s how it worked:

Step 1: I open the PowerShell ISE console on the server where I need to archive the reports (shown with the script I’ll run in the next few steps)

image

Step 2: run a command to download and install the rstools library (see line 1):

Invoke-Expression (Invoke-WebRequest https://aka.ms/rstools)

Step 3: Line 5 downloads all the reports in the “DAT214x Course” folder on my Azure VM report server to a local file system folder:

Out-RsFolderContent …

image

Step 4: On the destination server, I also run the command to install the rstools library…

Step 5: After copying the files to a local folder (or you could use a UNC path to access a shared folder), run Write-RsFolderContent to upload all the reports to the same destination folder:

image

Progress is displayed while the files are uploaded:

image

With shared data sources in the same places, all of the files are uploaded and working:

This saved me hours of time and provided peace of mind, knowing that I could easily automate large migrations in the future.

image

Here is the complete list of commands.  You can get help, execution details and optional parameter information by prefixing the command with the word “help “:

Backup-RsEncryptionKey
Get-RsCatalogItems
Get-RsDataSource
Get-RsItemReferences
Grant-AccessOnCatalogItem
Grant-AccessToRs
New-RsConfigurationSettingObject
New-RsDataSource
New-RsWebServiceProxy
Out-RsCatalogItem
Out-RsFolderContent
Register-PowerBI
Remove-RsCatalogItem
Restore-RSEncryptionKey
Revoke-AccessOnCatalogItem
Revoke-AccessToRs
Set-RsDataSet
Set-RsDataSource
Set-RsDataSourcePassword
Set-RsEmailSettingsAsBasicAuth
Set-RsEmailSettingsAsNoAuth
Set-RsEmailSettingsAsNTLMAuth
Set-RsSharedDataSource
Write-RsCatalogItem
Write-RsFolderContent

Some additional notes (from Aaron)…

You can download these commands into your PowerShell environment very easily by running this simple command:

Invoke-Expression (Invoke-WebRequest https://aka.ms/rstools)

You need to be running PowerShell as Administrator in order to do this.

My favorite command among them is this one which will upload an entire folder worth of Reports & Datasets to an SSRS server for you.  Here’s an example using Jamie Thomson’s SSIS Reporting Pack (attached):

Write-RsFolderContent -ReportServerUri ‘http://localhost/ReportServer_SQL2016’ -Path ‘C:\SSIS Reporting Pack\Reports’ -Destination /SSISReporting

You can get the full list of commands by running this:

Get-Command -Module ReportingServicesTools

If you have access to multiple report servers, you only need to run PowerShell commands on one machine.

Folder recursion is supported if you have nested folders.

I think you’ll find these commands can help speed up your Development Lifecycle.

Thanks again to Parth, Jaime, Aaron, Chrissy, Riccardo and the rest of the product team for making this possible!

clip_image002

Share this:

  • Click to email this to a friend (Opens in new window)
  • Click to print (Opens in new window)
  • Click to share on Twitter (Opens in new window)
  • Click to share on LinkedIn (Opens in new window)
  • Click to share on Facebook (Opens in new window)
  • Click to share on Reddit (Opens in new window)

Like this:

Like Loading...
Posted in BI Projects, MVP Community, PowerShell, SolidQ, SQL Server Pro Magazine, SQL Syndication, SQLServerPedia, SSRS Administration.

Power BI On Premises Integration with Reporting Services Preview

November 18, 2016 by Paul Turley

2

Since the Reporting Services product team announced last year that it was their intention to provide an on premises deployment option for Power BI reports, there has been tremendous interest.  Despite their determination to provide clarity around their efforts , there has still been some speculation about how and when we’ll see this capability.  I don’t want to jump on the bandwagon and add to the rumors or debate the details.  The fact is that product leadership have been more than forthright about their progress and must work through the usual rigor of packaging and supporting a major feature addition when it is ready for release.

At the PASS Summit in Seattle a few weeks ago, Riccardo Muti and Chris Finlan announced that the team planned to release a new preview of the enhancements to SQL Server 2016 Reporting Services that could support Power BI reports hosted within the SSRS web portal.  Today, they are delivering the goods with an Azure virtual machine that we can use to preview these features.  Riccardo was very clear that the VM would be a very early preview and that the actual released product wouldn’t be announced until it was ready.  The question on the minds of several community leaders is whether these features will be available in some kind of update for SQL Server 2016 or whether it will be in the next version of SQL Server.  I checked with Riccardo Muti, Group Program Manager for SSRS and he says “We’re releasing Power BI reports in ‘SSRS vNext’ as part of the SQL Server vNext wave, but don’t assume ‘vNext’ must mean a couple of years.“ …and, “it’ll be here in 2017“.  Now we have a definitive answer.

Dan English posted about this today in an article titled On-Premise Power BI–A Microsoft Story.  I encourage you to read his entire post and understand his thoughts on the matter.  At the PASS Summit session, I had the impression that there would be an interim update for SQL Server 2016 but after reading a lengthy email thread today, that doesn’t seem to be the case.  My conclusion is that plans can change and there has not been on official announcement on the release plan.

The official announcement was posted today on the Reporting Services product team blog by Chris Finlan, titled Technical Preview of Power BI reports in SQL Server Reporting Services now available.  Chris told me about this plan a few weeks head of the announcement and said that they weighed their options carefully before they decided to go down this route.  The trade-off was that this decision would likely raise unanswered questions and speculation out in the community.  In the end, they decided it was best to err on the side of being open and transparent with their customers.

From Chris’ post on the team blog:

As we brainstormed creative ways to let people try this functionality as early as possible, we had three very specific goals we wanted to achieve:

  • Provide access to the new functionality publicly as early as possible while ensuring the end-user experience was something you’d find valuable
  • Create a self-contained experience and environment that allowed users of any skill level an easy way to get started
  • In no way disrupt or delay the initial preview of a downloadable and installable version

By using the Azure Marketplace to distribute this early technical preview, we feel we have not only met those goals, but also established a repeatable way to distribute content in the future.  For users who would prefer to run this technical preview on an on-premises server, you are welcome to provision a virtual machine and then download the image as a .vhd file and use Hyper-V functionality to do so.

Read the entire post for more details and to learn how to obtain the preview.

Share this:

  • Click to email this to a friend (Opens in new window)
  • Click to print (Opens in new window)
  • Click to share on Twitter (Opens in new window)
  • Click to share on LinkedIn (Opens in new window)
  • Click to share on Facebook (Opens in new window)
  • Click to share on Reddit (Opens in new window)

Like this:

Like Loading...
Posted in BI Industry, Microsoft BI Platform, MVP Community, Power BI, Self-service BI, SQL Server, SQL Server Pro Magazine, SQL Syndication, SQLServerPedia, SSRS Administration.

SQL Server 2016 SP1 is Now Available

November 16, 2016 by Paul Turley

0

In the Business Intelligence and reporting space, SQL Server 2016 was a big step forward.  Aside from significant relational engine improvements, Analysis Services Tabular matured considerably.  Components of Reporting Services were completely re-architected and several new features were added, which include modern HTML5 rendering for all browsers, the new web portal, mobile reports, KPIs, Power BI integration, native browser printing, parameter layout control and PowerPoint export.  With new features, there are always loose ends to tie up – and that’s what service packs are for.  I am aware of a few enhancements in SP1 to the Reporting Services web portal, and general improvements to the SSRS platform.

As of today, SQL Server 2016 Service Pack 1 is generally available for all product editions.  The service pack can be downloaded from here.  For the official announcement, read this post on the SQL Server Blog from Microsoft.  From the announcement…

The capabilities in SQL Server 2016 SP1 which are now being make available to Standard edition and Express edition for the first time include:

  • Faster transaction performance from In-memory OLTP, faster query performance from In-memory ColumnStore, and the ability to combine the two for real-time Hybrid Transactional and Analytical Processing, also known as Operational Analytics;
  • Data warehousing or data mart performance features such as partitioning, compression, change data capture, database snapshot, and the ability to query across structured and unstructured data with a single node of PolyBase; and
  • The innovative security feature Always Encrypted for encryption at rest and in motion, as well as fine-grained auditing which captures more detailed audit information for your compliance reporting needs. In addition, row-level security and dynamic data masking are being made available to Express edition for the first time.

Share this:

  • Click to email this to a friend (Opens in new window)
  • Click to print (Opens in new window)
  • Click to share on Twitter (Opens in new window)
  • Click to share on LinkedIn (Opens in new window)
  • Click to share on Facebook (Opens in new window)
  • Click to share on Reddit (Opens in new window)

Like this:

Like Loading...
Posted in BI Industry, Microsoft BI Platform, SQL Server, SQL Syndication, SQLServerPedia.

Business Analytics Day Jan 11, 2017 Chicago

November 14, 2016 by Paul Turley

1

I hope you can join me at the Chicago Microsoft Technology Center for the PASS Business Analytics Day, on Wednesday, January 11, 2017.

PASS_BAD_20170111

The focus of this hands-on workshop is building business solutions with Power BI Desktop and the Power BI service.  We will cover both self-service and enterprise scenarios, with data from files and on-premises data sources; focusing on core skills using Power Query “M”, modeling techniques, DAX measures and visualization design.  At the end, you will have a complete solution built from real business data, shaped, cleansed and modeled; with a dashboard and interactive report visuals ready for analysis.

You will also learn about different options to deploy and integrate Power BI into your local reporting servers and secure web application through embedding and web publishing.  You will see how Power BI works with R to add forecasting, predictions and advanced statistical analytics.  See how custom visuals are used to extend visual analysis and how Power BI may be used for live streaming, DirectQuery to on-prem database systems and direct connect with Analysis Services to architect large-scale business data solutions.

Register to attend the Mastering Power BI Solutions session at PASS Business Analytics Day.  Make sure you bring your laptop with Power BI Desktop already installed before the event.  All the materials will be provided at the beginning of the workshop.

The Chicago Microsoft Technology Center is located downtown at 200 East Randolph Street, Suite 200, Chicago, IL 60601.  Arrive early.  The workshop will begin at 8:30 and runs until 4:30 PM.  You can only attend one session during the Business Analytics Day event.

Share this:

  • Click to email this to a friend (Opens in new window)
  • Click to print (Opens in new window)
  • Click to share on Twitter (Opens in new window)
  • Click to share on LinkedIn (Opens in new window)
  • Click to share on Facebook (Opens in new window)
  • Click to share on Reddit (Opens in new window)

Like this:

Like Loading...
Posted in BI Industry, Power BI, Self-service BI, SQL Server Pro Magazine, SQL Syndication, SQLServerPedia.

Happy Birthday Power BI

July 24, 2016 by Paul Turley

1

Today marks the first anniversary of Power BI Desktop and the Power BI Service.  The effort to achieve this milestone and the on-going commitment of the teams responsible for this product is nothing short of phenomenal.  Earlier this week, I reached out to a few friends to join me in sharing this Happy Birthday wish and thanks to the people behind this amazing, and ever-improving product.  Adam Saxton assembled this video montage and is credited for the impressive production work.

The product released last year on this date was a major overhaul of the earlier tools, with some features based on mature technologies that have been improving for several years.  Since then, the pace of development, new capabilities and feature enhancements has been mind-blowing.  The Power BI service is enhanced weekly and the desktop tool is updated every month, while the platform remains stable and reliable.

To the Microsoft Power BI product team:
From those of us in the community – partners, trainers, consultants, MVP Program members, user group leaders, developers, integrators and service providers – thank and congratulate you.

Here are some out takes and clips I received after Adam finished the production:

 

Share this:

  • Click to email this to a friend (Opens in new window)
  • Click to print (Opens in new window)
  • Click to share on Twitter (Opens in new window)
  • Click to share on LinkedIn (Opens in new window)
  • Click to share on Facebook (Opens in new window)
  • Click to share on Reddit (Opens in new window)

Like this:

Like Loading...
Posted in BI Industry, Business Analytics, Microsoft BI Platform, Power BI, Power View, Self-service BI, SolidQ, SQL Server Pro Magazine, SQL Syndication. Tagged Happy Birthday Power BI

SSRS 2016 Book Nearly Done… My Brain is now Jell-O

July 19, 2016 by Paul Turley

6

(first excerpt from the upcoming book)

“Professional SQL Server 2016 Reporting Services and Mobile Reports” from Wrox Press is content complete!  The book is well into the editing stage– still, another grueling phase of the publishing process, but there is light at the end of the proverbial tunnel.  What is it like to write an 800+ page technical book?  I’ve been trying to come up with the best metaphor to describe a year of persistent weekend-and-evening content development – building labs, demonstrations and exercises; performing research and and hounding my friends on the product team.  A big thanks thanks to Chris Finlan, Riccardo Muti and Robert Bruckner on the SSRS product team for their continued help and support.

I’ve admittedly been a bit quiet on the community front for a while I’ve been heads-down on this book project but I hope to start redirecting more energy to community content.

Intense book writing can be a life-sucker:

Image result for pricess bride torture

This is Paul’s brain…
…this is Paul’s brain after writing a technical book:

Image result for brain jello

Don’t get me wrong – I enjoy book writing.  I’ve spent a lot of my career writing technical books and courseware because I enjoy the challenge.  Writing helps me see things from different perspectives and at different levels of depth and difficulty for different audiences.

Give my brain a couple of weeks to heal and I’ll channel some of that energy into blogging and producing more community-facing training content.

So, what’s in the book?  This is the Fifth Edition of the Wrox Professional SQL Server Reporting Services series which I’ve been leading and co-authoring since 2003.  Amazon still has a working title listed but this is normal.  As a next-edition, some of the 2012 edition evolved forward but most of the book is all new material.  Four chapters are dedicated to the new Mobile Report features introduced in SSRS 2016.  Before someone asks… when will the book be out?  I don’t know exactly; there’s still work to be done.  Soon.  How’s that?

Over the next few weeks, I’ll post excerpts from the book to give you a taste of what’s coming and provide some valuable information about SSRS 2016.

Here’s a short except from Chapter 2:
What’s New in SQL Server 2016 Reporting Services?

Reporting Services was released as an add-in tool for SQL Server 2000 back in early 2004. At the time, the feature set was light by comparison to the product today but the foundational architecture was hasn’t changed significantly. Essential features included basic charts, sub-reports data regions and single-value textboxes.

clip_image002

The second release in 2005 added a self-service report authoring tool called Report Builder (later named Report Builder 1.0) paired with a semantic modelling tool in the designer. The original modelling and ad hoc report tool has since been deprecated but it inspired more capable replacement technologies like Power Pivot and the later generations of Report Builder. Not to be confused with the original Report Builder tool, Report Builder 2.0 and 3.0 produce report definition files compatible with the report project tools that are integrated with Visual Studio; originally called Business Intelligence Development Studio (or “BIDS”).

Report Builder and Designer Enhancements

The report design experience for standard “paginated” reports hasn’t really changed much over the past few product versions but there have been incremental improvements. Report Builder is restyled to conform to Office 2016 standards. The installation process for Report Builder changes to an “Evergreen” app. This means that Microsoft maintains updates for frequent download, rather than the old “ClickOnce” installation from your on-prem server. Similar to prior versions, users can elect to install Report Builder from the Report Portal menu.  Report Builder is updated with a modern look-and-feel, simple and sleek, Changes are mostly cosmetic while the fundamental features are the same.

The Visual Studio integrated Report Designer is now part of SQL Server Data Tools (SSDT), a downloadable add-in for Visual Studio. Although the tool set hasn’t changed significantly, there are some subtle changes to the way SSDT is installed and the way updates are delivered. First of all, confusion about the name “SSDT” is dispelled because the former “SSDT” (the previous version add-in for database projects) and “SSDT for BI” (the previous version add-in for SSIS, SSAS and SSRS projects) are now a combined package, simply called SSDT. Secondly, you can simply download and install a version of SSDT that will work with the current version of Visual Studio or a few versions back. The SSDT add-in will be updated frequently and you will have the option to install updates on-demand from within Visual Studio.

Modern Browser Rendering

One of the most significant product improvements in 2016 may be one of the least apparent under casual observation. The entire HTML rendering engine has been completely overhauled across the platform. The report portal, used to navigate and manage report content, and the actual report content are rendered to modern HTML 5 standards, which are supported by all modern web browsers. The shift to modern HTML output means that web content produced by Reporting Services is consistently consumable on any device, regardless of the operating system or web browser, so long as it supports modern standards. The benefits are readily apparent when reports simply work on smartphones of any type, on tablets and laptop and desktop machines; regardless of the brand or operating system.

Parameter Layout Control

You will have improved control over parameter formatting and placement.  Since the inception of Reporting Services about twelve years ago, parameters have always been arbitrarily arranged in a narrow bar at the top of the browser window, from left-to-right, and then top-down.  The report designer has a grid to manage the placement of parameters, in the parameter bar, in any configuration, within definable rows and columns.

image

Mobile Reports

The addition of mobile dashboards to the SSRS platform is based on the Datazen product acquisition from ComponentArt in 2015. Mobile reports are primarily designed to enable data interactivity in dashboard-style reports created by a mobile report developer. Managing this expectation is important because this tool is significantly different than conventional Reporting Services.

Mobile reports can be viewed in the browser but are optimized for phone and tablet devices through native, installed applications running on all the major mobile OS platforms.   They are not a replacement for high-fidelity paginated reports created with Reporting Services, or self-service analytics in Power BI; they serve an entirely different purpose.

clip_image002

clip_image004

At first, the mobile dashboard experience may seem to be a simple drop-in of the Datazen product; but, it is apparent that some integration with the SSRS architecture has already taken place, and more adaptations are likely on the horizon. The first notable difference is the Datazen server is entirely replaced by the SQL Server report server, and queries are now managed as SSRS shared datasets.

KPIs

New Key Performance Indicators (KPIs) integrated with the new Report Portal are also based on the Datazen product acquisition.  These KPI visuals are created and managed entirely within the portal.  In additional to the standard traffic light style comparison of actual vs target values, KPIs can include a trend line or segment chart. The KPI is driven by data from one or more shared datasets that were created in the SSDT report designer. For ease and simplicity, any value of the KPI can be entered manually through the design page.

clip_image002[7]

Although they are visualized in the Report Portal, KPIs are delivered to mobile devices through the Power BI mobile applications on every popular device platform.

Native Printing Control

The previous printing capability in SSRS replied on an ActiveX control which was only supported on Windows desktops and in certain web browsers. Even in tightly controlled Windows server environments, system administrators would rescind ActiveX support and disallow report printing from the server. The modern printing solution uses the PDF renderer to printable output and then the Adobe document viewer to perform the actual printing

PowerPoint Rendering

Users have had the option to export and render report content to Excel for several SSRS versions. Output to Word was added in SQL Server 2008 and then both of these rendering options were improved and updated in the 2008 R2 version. Now a third Office application format will be supported with the introduction of PowerPoint document rendering.

Most report items and data regions are converted to individual image objects in the resulting PowerPoint sides with one side generated per report page. Additional slides are created based on the report content size and layout. Textboxes are created for titles and report text, which support some report actions and textbox properties.

Integrated and Improved Report Portal

A new Report Portal web interface is introduced to replace the Report Manager. Like Report Manager, the portal is an ASP.NET web application used to access, run and manage reports in the web browser. The new portal has a look-and-feel we are accustomed to seeing in other modern apps from Microsoft these days; with responsive design for constancy on different device form factors.  Report Portal will be the home for mobile reports, KPIs and paginated reports – the new name for RDL reports authored with Reporting Services.  In the future, we may see support for additional content types.

clip_image003

Report Portal supports in all modern web browser by emitting responsive HTML5 with adaptations for mobile devices and screen orientations.

New Charts and Visual Enhancements

With the addition of two new chart types, visualization improvements are inched forward in Reporting Services.  The new Sunburst and Treemap charts apply multi-level field groups visualized in both color and visual boundaries.

clip_image004[9]

New and updated report visuals are likely to be an area of focus for future Reporting Services enhancements, given the success of self-service BI tools like Power BI. The design interface is identical to existing chart types and the only real difference is that groups of rows are visualized in these unique formats. The Sunburst chart is also capable of consuming unbalanced hierarchies with slices generated for different levels only where data points exist.

Standardized, Modern Browser Rendering

At first, you may not notice significant changes from previous versions, but the HTML renderer has been completely overhauled and updated.  Now, reports are rendered to HTML 5 standards whereas, they should consistently maintain the same appearance and behavior in all modern browsers that support the HTML 5 standard such as Microsoft Edge, IE 11, and newer versions of Google Chrome, Safari and Firefox.  This change is a welcome improvement which should clear up many problems with inconsistent and quirky report layouts while using different web browsers and devices.  By the same token, the change means there is no specific backward-compatibility for outdated browsers; consequently, reports that may have worked (or partially worked) in an old version of Internet Explorer may no longer work until the user upgrades.

Power BI Dashboard Pinning

For organizations who have invested in the Power BI cloud service, the Power BI integration feature allows users to pin graphical SSRS report visuals to their online dashboards.  In order to use this feature, an administrator must register the report server with an existing Power BI subscription, and a report user must have access to the Power BI subscription.

Reporting Services Configuration Manager  includes a new page to manage Power BI Integration.  This is where you register the report server instance with the Power BI subscription.  When a report with “pinnable” items (such as images, charts and gauges) is viewed in Report Portal, the Power BI icon is displayed on the toolbar.

clip_image006

“pinnable” items are highlighted in Report Portal. When a visual is selected, you are prompted to select a Power BI dashboard and the refresh frequency.  This schedules an Agent job on the report server to push updated visuals to the dashboard at the selected frequency.

clip_image007

The Select frequency of updates option utilizes the SSRS subscription architecture by scheduling a SQL Server Agent job on the database server with the report server catalog. The Agent job re-queries the report data and then a report server component refreshes the Power BI dashboard tile with an updated report visual.

Pinned report visuals appear on the dashboard alongside the Power BI report and Excel visuals.  Clicking on one of these visuals will drill-through to the report back on the on-premises report server.  This gives users a seamless navigation experience between cloud-hosted Power BI content and selected report visual elements on your own report server.

clip_image009

The integrated Power BI experience is a big step forward in providing a completely integrated IT-hosted and self-service reporting, BI and analytics solution.

Share this:

  • Click to email this to a friend (Opens in new window)
  • Click to print (Opens in new window)
  • Click to share on Twitter (Opens in new window)
  • Click to share on LinkedIn (Opens in new window)
  • Click to share on Facebook (Opens in new window)
  • Click to share on Reddit (Opens in new window)

Like this:

Like Loading...
Posted in BI Industry, DataZen, MVP Community, SolidQ, SQL Server, SQL Server Pro Magazine, SQL Syndication, SQLServerPedia, SSRS Administration, SSRS Design. Tagged Datazen, Mobile Reports, Reporting Services 2016, SSRS 2016

Post navigation

← Older posts

Blog Stats

  • 1,773,055 hits

Email Subscription

Enter your email address to subscribe to this blog and receive notifications of new posts by email.

Join 5,757 other subscribers

Recent Posts

  • Use Paginated Reports to Create a Gantt Chart
  • Doing Power BI The Right Way – for Enterprise Reporting
  • Doing Power BI the Right Way: 8. Delivery options
  • When to Use Paginated Reports vs Interactive Power BI reports
  • Hybrid Tables, Incremental Refresh and Table Partitioning in Power BI
  • Are We There Yet? …Composite Models Using DirectQuery Over Power BI Datasets
  • Microsoft Data Community Interviews and Advice about Working Remotely
  • Learn Databricks, ADF & Paginated Reports at the PASS Community Summit
  • Guy In A Cube Power BI Livestream
  • Power BI Live Stream this Saturday

Category Cloud

BI Industry BI Projects Business Analytics Microsoft BI Platform MVP Community PASS Power BI PowerPivot Power View Self-service BI SolidQ SQL Server SQLServerPedia SQL Server Pro Magazine SQL Syndication SSAS Administration SSAS Design SSRS Administration SSRS Design Tabular Models

Archives

  • April 2022 (1)
  • February 2022 (3)
  • December 2021 (3)
  • November 2021 (1)
  • October 2021 (3)
  • July 2021 (3)
  • June 2021 (1)
  • April 2021 (1)
  • March 2021 (2)
  • February 2021 (3)
  • January 2021 (3)
  • December 2020 (4)
  • November 2020 (1)
  • October 2020 (2)
  • September 2020 (1)
  • August 2020 (1)
  • July 2020 (4)
  • May 2020 (1)
  • April 2020 (3)
  • March 2020 (3)
  • February 2020 (1)
  • January 2020 (1)
  • December 2019 (2)
  • November 2019 (5)
  • October 2019 (1)
  • September 2019 (1)
  • August 2019 (2)
  • July 2019 (5)
  • May 2019 (1)
  • April 2019 (3)
  • March 2019 (1)
  • February 2019 (2)
  • December 2018 (3)
  • November 2018 (1)
  • October 2018 (1)
  • September 2018 (3)
  • July 2018 (5)
  • May 2018 (2)
  • April 2018 (2)
  • March 2018 (3)
  • February 2018 (3)
  • January 2018 (3)
  • December 2017 (3)
  • November 2017 (4)
  • October 2017 (1)
  • September 2017 (2)
  • August 2017 (1)
  • July 2017 (1)
  • June 2017 (4)
  • April 2017 (8)
  • March 2017 (1)
  • February 2017 (2)
  • January 2017 (8)
  • December 2016 (4)
  • November 2016 (3)
  • October 2016 (2)
  • September 2016 (1)
  • July 2016 (3)
  • June 2016 (3)
  • May 2016 (3)
  • March 2016 (6)
  • February 2016 (5)
  • January 2016 (2)
  • December 2015 (4)
  • November 2015 (3)
  • October 2015 (2)
  • September 2015 (2)
  • August 2015 (3)
  • July 2015 (6)
  • June 2015 (1)
  • May 2015 (5)
  • April 2015 (4)
  • March 2015 (1)
  • February 2015 (2)
  • January 2015 (4)
  • December 2014 (3)
  • November 2014 (1)
  • October 2014 (4)
  • September 2014 (1)
  • August 2014 (2)
  • July 2014 (5)
  • June 2014 (4)
  • May 2014 (2)
  • April 2014 (6)
  • March 2014 (3)
  • February 2014 (7)
  • January 2014 (5)
  • December 2013 (2)
  • November 2013 (1)
  • October 2013 (1)
  • September 2013 (2)
  • July 2013 (4)
  • June 2013 (5)
  • April 2013 (1)
  • March 2013 (4)
  • February 2013 (3)
  • January 2013 (1)
  • December 2012 (4)
  • November 2012 (4)
  • October 2012 (3)
  • September 2012 (3)
  • August 2012 (2)
  • July 2012 (2)
  • June 2012 (2)
  • May 2012 (3)
  • March 2012 (2)
  • February 2012 (3)
  • December 2011 (1)
  • November 2011 (3)
  • October 2011 (11)
  • September 2011 (7)
  • August 2011 (4)
  • July 2011 (2)
  • June 2011 (4)
  • May 2011 (5)
  • April 2011 (5)
  • March 2011 (4)
  • February 2011 (2)
  • January 2011 (4)
  • December 2010 (4)
  • November 2010 (4)
  • October 2010 (1)
  • September 2010 (1)
  • August 2010 (2)
  • June 2010 (1)
  • May 2010 (2)
  • April 2010 (1)
  • March 2010 (19)
  • December 2009 (1)
  • June 2009 (1)

Tag Cloud

" & Workspace and Database Recovery Techniques Aaron Nelson Ad-hoc reporting Add columns Add controls Albert Ferrari Alternate row colors Analysis Services Operations Guide Apple Are There Rules for Tabular Model Design? Article Assemblies Azure Azure Reporting Azure SQL Database BARC Survey best practices BI BI Center of Excellence BI COE BI Conference Bill Gates Birds-of-a-Feather BI Roles and Team Composition BISM BI Survey 10 Blogging Breakcrumb links Browser settings Build career Business Intelligence Business Intelligence for Visual Studio 2012 Business scorecard Can I Use Reporting Services with Tabular & PowerPivot Models? Checkbox in report Checkbox list Check mark Chris Webb Cloud computing Column chart Community Conditional formatting Conference presentation Conference review Conference session Conference Session Topics Cortana Power BI Integration Custom code Custom coding reports Custom Functions Dashboard design Dashboard standards Database Lifecycle Management Data Modeling 101 for Tabular Models Data Quality Services Dataset filter nulls Datazen Datazen control selection Date parameters DAX DAX: Essential Concepts DAX: Some of the Most Interesting Functions DAX: Some of the Most Useful Functions DAX functions DAX reference DAX syntax Demo scenario Denali CTP3 DevTeach DLM Do I Write MDX or DAX Queries to Report on Tabular Data? Do We Need to Have SharePoint to Use Tabular Models? Drill-down Drill-through Drillthrough Dynamic column visibility Dynamics CRM Dynamics reporting Embedded formatting ENterprise SSAS Errors Estimating BI European PASS Filter by user Formula Firewall Funnel charts Garner Magic Quadrant Microsoft BI Getting Started with DAX Calculations Global Summit Live Feeds Greenbar report Grocery shopping demo Hans Rosling Happy Birthday Power BI Hide columns Hitachi Consulting How Do You Design a Tabular Model for a Large Volume of Data? How Do You Secure a Tabular Model? How to Deploy and Manage a Tabular Model SSAS Database How to Promote a Business-created PowerPivot Model to an IT-managed SSAS Tabular Model HTML text integrated mode Interview Interviews Isn’t a Tabular Model Just Another Name for a Cube? James Phillips Julie Koesmarno King of Spain KPI indicator Licensing Login prompt Manually starting subscription Map Visualization Marco RUsso Master-detail report Master Data Management MDM MDX datasets MDX queries Microsoft Architecture Journal Microsoft humour Microsoft MVP Microsoft news Mobile Reporting Mobile Reports MVP community MVP Deep Dives 2 MVPs support the community MVP Summit navigation Nested tables Null filter Olivier Matrat Olympia WA Oracle vs Microsoft in the movies Oregon SQL Saturday Parameter controls Parameterize Parameters PASS 2012 PASS BAC Blog Feed PASS community leaders PASS Conference PASS Global Summit 2012 PASS Keynotes PASS Summit PASS Summit 2017 PASS Summit 2018 PASS Summit Announcements Paul te Braak PDF image distortion dithering fonts PerformancePoint Pinal Dave Poll About Product Usage Poll Results Pop-up window; Java script Portland OR Power BI Administration Power BI Best Visuals Contest Power BI DAX Power BI Partner Showcase Power BI Premium Power BI Pro Power BI Training Power BI World Tour Power Pivot PowerPivot Power Pivot DAX Power Query Power Query Training Power View Power View multidimensional cubes Preparing Data for a Tabular Model Project Phoenix Recipes Redmond SQL Saturday Reed Jacobson Remove columns Repeating list Report controls report dependencies Report deployment Reporting Services 2016 Reporting Services Training Report navigation Report parameters Report recipe book Reports for MDX Return specific row Rob Collie DAX Book Robert Bruckner Scheduled Refresh Scripting Tabular Model Measures Self-service reporting Seth Bauer SharePoint SharePoint 2012 SharePoint integration Simplifying and Automating Tabular Model Design Tasks SolidQ SolidQ Journal Solid Quality Mentors Spatial queries; happy holidays; Merry Christmas SQLAuthority SQLCAT SQL Saturday SQL Saturday 446 SQL Saturday Portland Oregon SQL Server SQL Server 2012 Upgrade Guide SQL Server community SQL Server Data Tools – Business Intelligence for Visual Studio 2012 SQL Server Denali SQL Server Denali; Self-service reporting SQL Server Denali CTP3 SQL Server MVP SQL Server Optimization SQL Server Pro Magazine SQL Teach SSAS SSAS Performance Logger SSAS Tabular SSAS Tools BI Development Tools SSDT BI SSRS 2016 SSRS dynamic columns SSRS PowerShell SSRS version control standards Start subscription Steve Jobs StreamInsight Strip line style Subscription Survival Tips for Using the Tabular Model Design Environment Tabular DAX Tabular Model & " Tabular Model Common Errors and Remedies Tabular Model Design Tabular Model Design Checklist Tabular Modeling Tabular models Tabular report design TechEd TechEd 2011 Sessions TechSmith Snagit Pro themes Threshold line Top values Training clsses Unconference User-related report content User authentication User prompted to login Using DAX to Solve real-World Business Scenarios Vancouver BC Vern Rabe Visualisation Visualization Visual Report Design Volunteers Weather and Climate Web.Contents Web API What About Multidimensional – Will Tabular Replace It? What are the Naming Conventions for Tabular Model Objects? What Do You Teach Non-technical Business Users About PowerPivot and Tabular Models? What’s the Best Business User Tool for Browsing & Analyzing Business Data with Tabular Models? What’s the Best IT Tool for Reporting on Tabular Models? What’s the Difference Between Calculated Columns & Measures? What’s the Difference Between PowerPivot and Tabular Models? Why Tabular? Wrox book
RSS
RSS Feed
RSS
RSS Feed
Note to SPAMers

Email Subscription

Enter your email address to subscribe to this blog and receive notifications of new posts by email.

Join 5,757 other subscribers

Recent Posts

  • Use Paginated Reports to Create a Gantt Chart
  • Doing Power BI The Right Way – for Enterprise Reporting
  • Doing Power BI the Right Way: 8. Delivery options
  • When to Use Paginated Reports vs Interactive Power BI reports
  • Hybrid Tables, Incremental Refresh and Table Partitioning in Power BI
  • Are We There Yet? …Composite Models Using DirectQuery Over Power BI Datasets
  • Microsoft Data Community Interviews and Advice about Working Remotely
  • Learn Databricks, ADF & Paginated Reports at the PASS Community Summit
  • Guy In A Cube Power BI Livestream
  • Power BI Live Stream this Saturday

Category Cloud

BI Industry BI Projects Business Analytics Microsoft BI Platform MVP Community PASS Power BI PowerPivot Power View Self-service BI SolidQ SQL Server SQLServerPedia SQL Server Pro Magazine SQL Syndication SSAS Administration SSAS Design SSRS Administration SSRS Design Tabular Models

Archives

  • April 2022 (1)
  • February 2022 (3)
  • December 2021 (3)
  • November 2021 (1)
  • October 2021 (3)
  • July 2021 (3)
  • June 2021 (1)
  • April 2021 (1)
  • March 2021 (2)
  • February 2021 (3)
  • January 2021 (3)
  • December 2020 (4)
  • November 2020 (1)
  • October 2020 (2)
  • September 2020 (1)
  • August 2020 (1)
  • July 2020 (4)
  • May 2020 (1)
  • April 2020 (3)
  • March 2020 (3)
  • February 2020 (1)
  • January 2020 (1)
  • December 2019 (2)
  • November 2019 (5)
  • October 2019 (1)
  • September 2019 (1)
  • August 2019 (2)
  • July 2019 (5)
  • May 2019 (1)
  • April 2019 (3)
  • March 2019 (1)
  • February 2019 (2)
  • December 2018 (3)
  • November 2018 (1)
  • October 2018 (1)
  • September 2018 (3)
  • July 2018 (5)
  • May 2018 (2)
  • April 2018 (2)
  • March 2018 (3)
  • February 2018 (3)
  • January 2018 (3)
  • December 2017 (3)
  • November 2017 (4)
  • October 2017 (1)
  • September 2017 (2)
  • August 2017 (1)
  • July 2017 (1)
  • June 2017 (4)
  • April 2017 (8)
  • March 2017 (1)
  • February 2017 (2)
  • January 2017 (8)
  • December 2016 (4)
  • November 2016 (3)
  • October 2016 (2)
  • September 2016 (1)
  • July 2016 (3)
  • June 2016 (3)
  • May 2016 (3)
  • March 2016 (6)
  • February 2016 (5)
  • January 2016 (2)
  • December 2015 (4)
  • November 2015 (3)
  • October 2015 (2)
  • September 2015 (2)
  • August 2015 (3)
  • July 2015 (6)
  • June 2015 (1)
  • May 2015 (5)
  • April 2015 (4)
  • March 2015 (1)
  • February 2015 (2)
  • January 2015 (4)
  • December 2014 (3)
  • November 2014 (1)
  • October 2014 (4)
  • September 2014 (1)
  • August 2014 (2)
  • July 2014 (5)
  • June 2014 (4)
  • May 2014 (2)
  • April 2014 (6)
  • March 2014 (3)
  • February 2014 (7)
  • January 2014 (5)
  • December 2013 (2)
  • November 2013 (1)
  • October 2013 (1)
  • September 2013 (2)
  • July 2013 (4)
  • June 2013 (5)
  • April 2013 (1)
  • March 2013 (4)
  • February 2013 (3)
  • January 2013 (1)
  • December 2012 (4)
  • November 2012 (4)
  • October 2012 (3)
  • September 2012 (3)
  • August 2012 (2)
  • July 2012 (2)
  • June 2012 (2)
  • May 2012 (3)
  • March 2012 (2)
  • February 2012 (3)
  • December 2011 (1)
  • November 2011 (3)
  • October 2011 (11)
  • September 2011 (7)
  • August 2011 (4)
  • July 2011 (2)
  • June 2011 (4)
  • May 2011 (5)
  • April 2011 (5)
  • March 2011 (4)
  • February 2011 (2)
  • January 2011 (4)
  • December 2010 (4)
  • November 2010 (4)
  • October 2010 (1)
  • September 2010 (1)
  • August 2010 (2)
  • June 2010 (1)
  • May 2010 (2)
  • April 2010 (1)
  • March 2010 (19)
  • December 2009 (1)
  • June 2009 (1)

Tag Cloud

" & Workspace and Database Recovery Techniques Aaron Nelson Ad-hoc reporting Add columns Add controls Albert Ferrari Alternate row colors Analysis Services Operations Guide Apple Are There Rules for Tabular Model Design? Article Assemblies Azure Azure Reporting Azure SQL Database BARC Survey best practices BI BI Center of Excellence BI COE BI Conference Bill Gates Birds-of-a-Feather BI Roles and Team Composition BISM BI Survey 10 Blogging Breakcrumb links Browser settings Build career Business Intelligence Business Intelligence for Visual Studio 2012 Business scorecard Can I Use Reporting Services with Tabular & PowerPivot Models? Checkbox in report Checkbox list Check mark Chris Webb Cloud computing Column chart Community Conditional formatting Conference presentation Conference review Conference session Conference Session Topics Cortana Power BI Integration Custom code Custom coding reports Custom Functions Dashboard design Dashboard standards Database Lifecycle Management Data Modeling 101 for Tabular Models Data Quality Services Dataset filter nulls Datazen Datazen control selection Date parameters DAX DAX: Essential Concepts DAX: Some of the Most Interesting Functions DAX: Some of the Most Useful Functions DAX functions DAX reference DAX syntax Demo scenario Denali CTP3 DevTeach DLM Do I Write MDX or DAX Queries to Report on Tabular Data? Do We Need to Have SharePoint to Use Tabular Models? Drill-down Drill-through Drillthrough Dynamic column visibility Dynamics CRM Dynamics reporting Embedded formatting ENterprise SSAS Errors Estimating BI European PASS Filter by user Formula Firewall Funnel charts Garner Magic Quadrant Microsoft BI Getting Started with DAX Calculations Global Summit Live Feeds Greenbar report Grocery shopping demo Hans Rosling Happy Birthday Power BI Hide columns Hitachi Consulting How Do You Design a Tabular Model for a Large Volume of Data? How Do You Secure a Tabular Model? How to Deploy and Manage a Tabular Model SSAS Database How to Promote a Business-created PowerPivot Model to an IT-managed SSAS Tabular Model HTML text integrated mode Interview Interviews Isn’t a Tabular Model Just Another Name for a Cube? James Phillips Julie Koesmarno King of Spain KPI indicator Licensing Login prompt Manually starting subscription Map Visualization Marco RUsso Master-detail report Master Data Management MDM MDX datasets MDX queries Microsoft Architecture Journal Microsoft humour Microsoft MVP Microsoft news Mobile Reporting Mobile Reports MVP community MVP Deep Dives 2 MVPs support the community MVP Summit navigation Nested tables Null filter Olivier Matrat Olympia WA Oracle vs Microsoft in the movies Oregon SQL Saturday Parameter controls Parameterize Parameters PASS 2012 PASS BAC Blog Feed PASS community leaders PASS Conference PASS Global Summit 2012 PASS Keynotes PASS Summit PASS Summit 2017 PASS Summit 2018 PASS Summit Announcements Paul te Braak PDF image distortion dithering fonts PerformancePoint Pinal Dave Poll About Product Usage Poll Results Pop-up window; Java script Portland OR Power BI Administration Power BI Best Visuals Contest Power BI DAX Power BI Partner Showcase Power BI Premium Power BI Pro Power BI Training Power BI World Tour Power Pivot PowerPivot Power Pivot DAX Power Query Power Query Training Power View Power View multidimensional cubes Preparing Data for a Tabular Model Project Phoenix Recipes Redmond SQL Saturday Reed Jacobson Remove columns Repeating list Report controls report dependencies Report deployment Reporting Services 2016 Reporting Services Training Report navigation Report parameters Report recipe book Reports for MDX Return specific row Rob Collie DAX Book Robert Bruckner Scheduled Refresh Scripting Tabular Model Measures Self-service reporting Seth Bauer SharePoint SharePoint 2012 SharePoint integration Simplifying and Automating Tabular Model Design Tasks SolidQ SolidQ Journal Solid Quality Mentors Spatial queries; happy holidays; Merry Christmas SQLAuthority SQLCAT SQL Saturday SQL Saturday 446 SQL Saturday Portland Oregon SQL Server SQL Server 2012 Upgrade Guide SQL Server community SQL Server Data Tools – Business Intelligence for Visual Studio 2012 SQL Server Denali SQL Server Denali; Self-service reporting SQL Server Denali CTP3 SQL Server MVP SQL Server Optimization SQL Server Pro Magazine SQL Teach SSAS SSAS Performance Logger SSAS Tabular SSAS Tools BI Development Tools SSDT BI SSRS 2016 SSRS dynamic columns SSRS PowerShell SSRS version control standards Start subscription Steve Jobs StreamInsight Strip line style Subscription Survival Tips for Using the Tabular Model Design Environment Tabular DAX Tabular Model & " Tabular Model Common Errors and Remedies Tabular Model Design Tabular Model Design Checklist Tabular Modeling Tabular models Tabular report design TechEd TechEd 2011 Sessions TechSmith Snagit Pro themes Threshold line Top values Training clsses Unconference User-related report content User authentication User prompted to login Using DAX to Solve real-World Business Scenarios Vancouver BC Vern Rabe Visualisation Visualization Visual Report Design Volunteers Weather and Climate Web.Contents Web API What About Multidimensional – Will Tabular Replace It? What are the Naming Conventions for Tabular Model Objects? What Do You Teach Non-technical Business Users About PowerPivot and Tabular Models? What’s the Best Business User Tool for Browsing & Analyzing Business Data with Tabular Models? What’s the Best IT Tool for Reporting on Tabular Models? What’s the Difference Between Calculated Columns & Measures? What’s the Difference Between PowerPivot and Tabular Models? Why Tabular? Wrox book
Powered by WordPress.com.
Paul Turley's SQL Server BI Blog
Proudly powered by WordPress Theme: Sunspot.
 

Loading Comments...
 

    loading Cancel
    Post was not sent - check your email addresses!
    Email check failed, please try again
    Sorry, your blog cannot share posts by email.
    %d bloggers like this: