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
Following the Power BI World Tour, Seattle event on Oct 30, please join me for a full-day of deep learning. That’s right… it’s on Oct 31st so put on your Wonder Woman or Captain America costume and get ready to exercise your super powers with Power Query and Power BI! You will learn to master Power Query extensively from Beginner to Advanced. The other session taught at the same time by Brian Grant is “Power BI: Enhance Your Data Model with DAX” but ya gotta pick one. You can learn more about the Power BI World Tour and the Academy by following these events on Twitter and LinkedIn using the links at the bottom of this post, or search these hashtags:
#PowerBIUG | #PowerBI | @pbiusergroup | #PowerBIUGAcademy | #PBIWorldTour
The foundations of a Business Intelligence solution are data transformations, data wrangling, data cleansing and ETL. A well-crafted Power BI project rests on Power Query and the queries that define the data model, calculations and report visuals. This full-day session will teach you how to lay the foundation for a Power BI solution with simple and advanced Power Query techniques.
Learn from Paul Turley, ten-year Microsoft Data Platform MVP and veteran BI Solution Architect. You will learn best practice design patterns, tricks, shortcuts and proven techniques to improve your skills and add immediate value to your projects. Power Query is everywhere – and growing.
The skills and techniques taught in this workshop apply to Power BI Desktop, the “Get Data” feature in Excel 2016+, SQL Server Analysis Services 2017+ (SSAS), Azure Analysis Services (AAS) and Data Flows in the Power BI Common Data Service (CDS). You will learn through exercises and instructor-led hands-on demos. Bring your laptop with the latest version of Power BI Desktop installed. The rest will be provided. We will cover material from basics through advanced. Each exercise is separate so you can absorb only what you need to learn, based on your prior experience, needs and skill level.
Power Query Basics
Quick tour of the Power Query interface & essentials
Creating and managing queries
Adding and editing steps
Recovery and project management
Essential best practices
Managing data sources
Working with folder paths, web URIs & database connections
Referencing & Duplicating queries
Consolidating queries, building base queries & dependency chains
Loading queries into data model tables
Basic error handling & debugging
Data Sources & Structures
Flat CSV files
Irregular text files (headings & totals)
JSON (simple)
JSON (complex, with nested & ragged hierarchies)
SQL server
Excel (single sheet/table, multiple sheets/tables)
Folders & file collections
Web pages a page tables
Web APIs & web service endpoints
Essential Query Techniques
Managing data types
Applying correct naming conventions
Working with Date & Time values
Splitting & formatting columns
De-duplicating & grouping
Pivot, Unpivot & Transpose
Custom columns & expression basics
Extracting tables from a data sources to supporting essential modeling for Power BI report design:
Fact tables
Dimension tables
Bridge tables
Slicer & calculation-driver tables
Role-playing dimensions
Advanced Power Query Techniques
Working with M: The Data Mashup language
M function essentials
Prioritized learning (what’s most important)
Using & managing parameters
Using the #shared object for internal documentation, examples & code syntax
Understanding M objects (values, tables, lists & records)
Number, Date, Time & Text manipulation M functions
Create a Date lookup/dimension table using M & Power Query
Create a Time series lookup/dimension table using M & Power Query
Why do I need a Date dimension in Power BI?
Standard date parts & hierarchies
Columns to support time-intelligence calculations
Working with fiscal & special-purpose calendars (e.g. 4-4-5, ISO)
Working with query functions
Parameterized queries, API endpoints & user-defined functions
Putting it Together
Examples:
Queries to support data model construction
Queries used to support report visuals
Deploy a report, configure the on-premises gateway
Use query parameters to schedule refresh in a deployed Power BI solution
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.
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.
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: 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: 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.
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.
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 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)
Step 2: run a command to download and install the rstools library (see line 1):
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 …
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:
Progress is displayed while the files are uploaded:
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.
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 “:
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):
I’m pleased to announce that I will be presenting two more Dashboard-In-A-Day training sessions in October. These free, full day training sessions are co-sponsored by Microsoft and SolidQ. I’ve had a blast teaching Power BI in lots of other locations. What’s not to like? It’s Power BI and it’s free. That’s a pretty good combination.
If you’re in the area, please plan to take and day and get some great training. Here are the dates and registration links:
October 11th Richmond Microsoft Office in Glen Allen, VA
DIAD is designed to accelerate your Power BI experience with a comprehensive training program in a single day. A trained Power BI specialist partner will host you for the entire day – all you have to do is bring your Windows-based notebook and we will supply the rest – even lunch!* After registering, you will receive an email confirmation that includes a link to download the training materials you’ll need for the day. With DIAD you get practical hands-on experience with SolidQ who specializes in the art and science of bringing data to life in a full-day of instructor lead report building. You will learn how to connect to, import & transform data from a variety of sources, build real data models, as well as author and publish Business Intelligence reports. You will learn how to customize and share your creations for collaboration with other groups securely inside your organization including mobile device sharing.
…now that I have your attention…
there aren’t any yet.
We (along with Aaron Nelson, Data Platform MVP & Chrissy LeMaire, PowerShell MVP) are working with the SQL Server product teams to recommend the first set of CmdLets that we would like to see added to the PowerShell libraries. Please help us by posting comments with your suggestions. What are the most important SSRS-related tasks that you would like to automate using PS? Give us your top five or so.
When asked to work on this recommendation, my first thought was to just duplicate the commands that are current exposed by the RE.exe utility. That might be a good starting point but this effort should be more deliberate than just copying what was evolved through the past product versions.
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:
“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:
This is Paul’s brain…
…this is Paul’s brain after writing a technical book:
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.
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.
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.
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.
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.
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.
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.
“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.
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.
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.
I’m thrilled to have been selected to deliver a session at the 2016 PASS Global Summit titled “Reporting Services 2016: The Force Awakens”. I’ve been speaking at SQL Saturdays and have done some training on SSRS 2016 this year but this new session has yet to be finished SO…. please let me know what you want to learn about Reporting Services in October. Please leave a comment in this post and let me know what’s most important and appealing to you. I’ll do my best to work the most popular topics into my session.
Here are some ideas:
What’s new in the web portal and how is it different then Report Manager
How is HTML 5 rendering different from previous versions
What browsers and devices are supported by SSRS 2016
What can I do (and can’t do) with mobile reports
How to use and create KPIs
How to create navigation paths with KPIs, mobile reports and paginated reports
How does SSRS integrate with Power BI
How does mobile reporting work and look with different types of reports and visuals
How have paginated report visuals changed and improved
Is there anything new or different about SSRS setup or configuration
Is programming with SSRS any different than before
I just finished posting a 4-part series demonstrating how to use Power BI with on-premises data. The running time for all four parts is about 44 minutes. I cover using the Power BI Enterprise Gateway to connect published Power BI reports to on-premises data sources:
SQL Server database connected with DirectQuery
Large SQL Server database with clustered column store index connected with DirectQuery
SSAS multidimensional/cube on-prem with direct connect
SSAS Tabular with large data volume & multiple partitions on-prem with direct connect
The four-part series is on my YouTube channel using the following links:
Part 1
Part 2
Part 3
Part 4
Adam Saxton also gives a nice walkthrough of using SQL Server DirectQuery with Power BI that may provide an additional perspective and information about securing and sharing the solution.
I’d also like to give a plug for a terrific Power BI architecture diagram poster created by Dustin Ryan. I love the simplicity of this comprehensive graphic. You can download the PDF file from his blog.
So, you just installed SQL Server 2016 and you’re pretty sure you chose the options to install the client tools and all the components you needed. You look for SSMS and, what the heck? it’s not there. OMG, what have they done?!
SSMS is Gone!
Seriously, SQL Server Management Studio is nowhere to be found. It’s not on any of the menus. You search and can’t find it. You even start looking for the executables in case setup got screwed-up ad didn’t create the application shortcuts.
..so is SSDT. Gone… nowhere to be found!
SQL Server Data Tools is also missing. Holy crap, what’s going on? Is SSMS no longer supported? Do I have to pay for a copy of Toad or start using SQLCMD? Do we have to install a retail copy of Visual Studio on our server? WHAT DO WE DO?
First of all, stop freaking out, take your blood pressure medicine and hold your horses. SSMS and SSDT are still supported in SQL Server 2016 but they’re just not installed with the SQL Server media anymore. If you go back to the Installation Center and look down a little on the Installation page, you’ll see two new links.
These take you to the web – and, yes, you need to either get to the Internet from your server or download them from another PC to a network share or portable media. The reason these tools are installed separately now is so Microsoft can update them more frequently and you don’t need to wait for the next product release to get the latest version. In the new age of “evergreen products”, a lot of the Microsoft product teams are using the new download-on-demand approach as the primary means to distribute client, design and development tools that tend to be updated frequently. If your organization has download restrictions then just host a copy of the sanctioned version on a network share or make it part of your installation package. For most admins, database and BI solution architects with elevated privileges, you’ll typically want to use the latest version to get bug fixes and optimizations.
Following these links will take you to a page with a few simple choices that will be updated as new versions are released.
“SSDT” = (“SSDT-BI” = “BIDS” + “Data Tools”)
This isn’t confusing. SQL Server Data Tools for SQL Server 2016 now encompasses both database project template for Visual Studio as well as all the Business Intelligence project templates. In former iterations, these tools were previously known by about four different names. Whether you need to develop database projects, SSRS, SSIS, SSAS, or all of the above; you will need SSDT. If you have any edition of Visual Studio 2015 installed, the templates will be added to the IDE. If you do not have Visual Studio 2015 installed, the VS 2015 IDE shell will be installed for you – just like BIDS was in the past.
The promise is that when future versions of Visual Studio are released, you’ll just download an updated SSDT package and everything will be peachy. You’ll also be able to use older versions of companion tools for a longer period of time. In theory, this should mean that we won’t have to play the “what version of what goes with what” game any longer. If you’ve been around Microsoft products for a while, you’ll remember when SQL Server 2012 went with Visual Studio 2010 which went with Office 2013 – or something like that.
This is a reminder to compete the annual Business Intelligence product survey conducted by BARC Research. I’ve been participating in the survey for several years (as far as I know, as long as it has been running). As a participant, I receive a detailed copy of the results which are informative and insightful.
The survey takes 10-15 minutes and you can get started here.
Last year the BI Survey evaluated 35 products, with 3,267 survey responders. BARC conducts a credible survey and not a SPAM trolling exercise. To see the infographic with a summary of last years’ results, click the image below:
Power BI reports can now be embedded into a pubic-facing website! Today, Faisal Mohamood, Group Product Manager on the Power BI team announced that Power BI reports can now be published to the web for all the world to see – woo hoo! I’ve worked with the feature and it’s quite easy to implement. Details are here. To exhibit this new capability, Microsoft is hosting a Best Report competition which I entered with the SolidQ Airline Performance Tracker.
Please join me on February 3rd at 10 Pacific Time for this presentation brought to you by Microsoft and SolidQ
If you’re still on SQL Server 2005, there are some important things to be aware of. First and foremost is that you are missing out on innumerable capabilities in more recent versions of the SQL Server platform, in the relational engine and the BI stack. Secondly, Microsoft will no longer support SQL Server 2005 after mid April of this year. Why is this any more significant than previous versions? There has never been such a large body of customers on an outdated database platform nearing the end of its useful life. This is not a marketing ploy to upsell customers but simply the normal retirement cycle of a ten-year-old, outdated product. Yet, for some organizations managing critical systems, it’s a disaster waiting to happen.
Considering performance and security alone, the differences between SQL Server 2005 and recent versions are quantum. We frequently see cases where performance improvements on a typical workload improve by multiples of 2, 10 and more, making the ROI math pretty simple. Improvements to the core BI feature set (Reporting Services, Analysis Services and Integration Services) are also vast, with improved reliability, ease of design and usability. These are some of the topics I’ll discuss in this free presentation.
An upgrade of your SQL Server Business Intelligence solution brings with it the challenge of understanding how to apply new features to your BI workload, but it also opens up tremendous opportunity for business impact. In this session we’ll talk about business benefits to upgrading your BI solution, costs to upgrade now vs. the costs of waiting, and current trends in corporate upgrade strategies. For those impacted by the end of extended support for SQL Server 2005 on 04/12/2016, we will discuss enhancements to the SQL Server platform and examples of how to take advantage of the latest features. We’ll touch on workload-specific upgrade benefits to modernizing your SSAS, SSRS, and SSIS environments, then wrap up with how upgrade can improve users’ reporting experience using the latest in mobile BI.
![clip_image002[7]](https://i0.wp.com/sqlserverbi.blog/wp-content/uploads/2016/07/clip_image0027.jpg "clip_image002[7]")
![clip_image004[9]](https://i2.wp.com/sqlserverbi.blog/wp-content/uploads/2016/07/clip_image0049.png "clip_image004[9]")
