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
What is the key to advancing your career in the Microsoft data platform? Here is some advice from some of the most successful people in the industry…
Every year we have some big community events that bring together community leaders from all over. These are international user group and community leaders who write books and speak at conferences. we had our local Oregon SQL Saturday “SQL Train” which is a chartered train – some coaches that one of our speaker’s chartered to bring all of our speakers and attendees up to the PASS summit after Oregon SQL Saturday, and then the big PASS summit (the big conference up in Seattle). I had a chance to sit down with a number of our speakers, community leaders and attendees and just ask questions about what brought them there, and advice that they would give people in the industry about how to get the most value out of that experience …and this is what they said:
Well-run monthly user group meetings and big annual events local events like SQL Saturday don’t just happen by themselves. It takes a lot of planning, a lot of volunteers and a lot of coordination to make these events successful. Part of that effort are the annual leadership planning meetings that we have during the week of PASS summit. Here are some short clips from those meetings where several hundred local leaders from all over the world got together to share ideas, to exchange notes and to coordinate to be able to make these events successful. Leaders cross-pollinate, exchange ideas and they work together to make this a great community. Why? …because somebody did that for us when we were getting started and we want to give back to the community. So, get involved; join the leadership committees at your local user groups, volunteer at SQL Saturday. Volunteer to do a short speaking engagement. Just get up and talk to some of your peers. Get started by volunteering in the community so that you can be part of the ongoing great community we have around the Microsoft data platform.
A service provider or vendor might want to publish multiple copies of a report that should connect to different database servers or databases. In a true multitenant service solution, we would have a singe database with row-level user mapping tables that filter data by the logged in user. True multitenant solutions require quite a lot of planning and development work to implement. In smaller-scale or interim solutions, copies of a report can be deployed to different workspaces and then queries can be parameterized to use different database connections.
In this post, I’ll demonstrate deploying and configuring such a solution where the server name and database name have been parameterized and setup to use the on-premises gateway to connect and refresh data. I’ll also setup scheduled refresh. The full video walk-through is below but I’ll do a quick review to set the stage.
This is the Power Query Editor in Power BI Desktop. I have two parameters that are used to specify the ServerName and DatabaseName for each SQL Server query:
Once deployed to a workspace in the service, the gateway must be configured with a data source for every possible server name and database combination. In my example, I can connect to my local server using the NetBIOS name, IP address or LocalHost. These all are acceptable methods but a data source must be added to the gateway configuration for each so the that the connection strings match exactly. Remember that the connection is from the on-prem gateway to the database server so names like LocalHost or an internal IP address will work just fine. In my example, I’m using the IP address of my local loopback adaptor on the local machine to connect to a local instance of SQL Server over the TCP connection.
In the workspace, the dataset is bound to the gateway. Click the ellipsis and choose Settings.
To bind the gateway to the dataset, click to select the radio button next to the gateway. This flips the switch titled “Use a data gateway”. Apply the setting and then you can refresh the data or schedule refresh.
Finally, the parameters can be updated right here in the dataset settings.
This is a post about a post about a post. Thanks to those of you who are entering comments in the original May 12 post titled SQL, M or DAX? This is a popular topic. And thanks to Adam Saxton for mentioning this post in his Guy in A Cube Weekly Roundup.
This is a HUUUUGE topic and I can tell that I’ve struck a chord with many BI practitioners by bringing it up. Please post your comments and share your ideas. I’m particularly interested in hearing your challenging questions and your thoughts about the pros-and-cons of some less-obvious choices about whether to implement transformations & calculations in SQL, M or DAX.
This week, I have had engaging conversations on this topic while working on a Power BI consulting project for a large municipal court system. As a consultant, I’ve had three weeks of experience with their data and business environment. The internal staff have spent decades negotiating the intricacies and layers upon layers of business process so of course, I want to learn from their experience but I also want to cautiously pursue opportunities to think outside the box. That’s why they hired me.
Tell me if this situation resonates with you… Working with a SQL Server database developer who is really good with T-SQL but fairly new to Power BI & tabular modeling, we’re building a data model and reports sourced from a line-of-business application’s SQL Server database. They’ve been writing reports using some pretty complicated SQL queries embedded in SSRS paginated reports. Every time a user wants a new report, a request is sent to the IT group. A developer picks up the request, writes some gnarly T-SQL query with pre-calculated columns and business rules. Complex reports might take days or weeks of development time. I needed to update a dimension table in the data model and needed a calculated column to differentiate case types. Turns out that it wasn’t a simple addition and his response was “I’ll just send you the SQL for that…you can just paste it”. The dilemma here is that all the complicated business rules had already been resolved using layers of T-SQL common table expressions (CTEs), nested subqueries and CASE statements. It was very well-written SQL and it would take considerable effort to re-engineer the logic into a dimensional tabular model to support general-use reporting. After beginning to nod-off while reading through the layers of SQL script, my initial reaction was to just paste the code and be done with it. After all, someone had already solved this problem, right?
The trade-off by using the existing T-SQL code is that the calculations and business rules are applied at a fixed level of granularity and within a certain business context. The query would need to be rewritten to answer different business questions. If we take the “black box” approach and paste the working and tested SQL script into the Power Query table definition, chances are that we won’t be able to explain the query logic in a few months, after we’ve moved on and forgotten this business problem. If you are trying to create a general-purpose data model to answer yet-to-be-defined questions, then you need to use design patterns that allow developers and users to navigate the model at different levels of grain across different dimension tables, and in different filtering contexts. This isn’t always the right answer but in this case, I am recommending that we do as little data merging, joining and manipulation as possible in the underlying source queries. But, the table mapping between source and data model are not one-to-one. In some cases, two or three source tables are combined using SQL joins, into a flattened and simplified lookup table – containing only the necessary, friendly-named columns and keys, and no unnecessary clutter like CreatedDateTime, ModifiedDateTime and CreatedByUser columns. Use custom columns in M/Power Query to transform the row-level calculated values and DAX measures to perform calculations in aggregate and within filter/slicing/grouping context.
I’d love to hear your thoughts and ideas on this topic.
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.
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.
While attending PASS Summit 2017 in Seattle, I had a chance to catch-up of several friends and industry experts, and I will be sharing these interviews in a series of posts. On the 1st of November, Pinal Dave completed his 4200th post on SqlAuthority.com – a landmark by blogging once per day for eleven years. Pinal shares this success story and humbly discusses his secrets about being the world-renound authority that we all know, along with some advice about tuning SQL Server.
Thank you, Pinal, for your time and all you do for the PASS community.
Please join me and other 2017 PASS Summit speakers for 24 Hours of PASS: Summit Preview on July 19th and 20th. 24HOP is a series of 60 minute on-line sessions presented back-to-back for 24 hours, from the same professionals who will deliver preconference and main conference sessions during the Summit the first week of November this year. These online sessions are free of charge and normally attended by thousands of individuals to gain insight and knowledge about the topics. 24 Hours of PASS features free educational webinars delivered over 24 hours. Topics covered in this edition include Performance Tuning, SQL Server 2017, Linux, DevOps, Azure, PowerShell, SSRS, Power BI and much more. Browse all sessions. These webinars provide a sneak peek at some of the best practices, expert tips and demos you’ll find at this year’s PASS Summit in Seattle. Continue reading →
I am thrilled to be presenting a full-day preconference session before the PASS Summit, on October 31st. Please join me for a deep tour of the new capabilities and BI platform integrations for SQL Server Reporting Services 2016 & 2017. The session will also review the essential skills and tasks to setup and configure the report server and web portal, report design and modern report solution planning. Continue reading →
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.
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
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.
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.
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.
What am I most excited about as I prepare for the PASS Summit here in Seattle this week? A lot of things. Preparing for my session, which will be on Thursday at 1:30, by far the most popular and interesting topics are about integration and tool choice. Today’s public announcement on the SSRS product team blog about on-premises Power BI integration with Reporting Services is really big news. It’s great to see two of the technologies I love working together. Whether in the cloud or on-premises, Power BI and Reporting Services can be used together.
Thursday’s session is titled “Reporting Services 2016, the Force Awakens”. The consensus among industry users is that Reporting Services was stalled and left alone for far too long. Technology and industry trends have moved forward over the past five years or so but Microsoft have invested in other tools and products, aside from Reporting Services. The SSRS server architecture is capable but the rendering and delivery components needed to be modernized.
The good news is that the modernization effort has been underway for quite some time. Resources that were directed to other investments have been recommitted, not only to the Reporting Services product, but to integration across the product stack. It’s really hard to build a reporting platform that addresses every need. I think Microsoft have a history of creating false expectations by putting experimental and incomplete features out in the industry that weren’t on the committed roadmap. I know better than to believe that the pattern won’t ever be repeated but I firmly believe that there is a well-defined reporting and BI roadmap and product teams are making rapid progress against it. It is once again an exciting time to invest in the Microsoft analytics, reporting and BI tool set.
I’ll publish the entire deck after the 2016 Summit. Until then, here are some selected slides…
Comparing the progress of new features added to SSRS over the release cycles, the product really slept from 2010 until 2016.
Microsoft’s reporting & analytic platform has shifted significant as they moved from products to services, and from servers to the cloud. The dust is settling on this major refocusing effort. Capabilities that were introduced in Azure, Cortana Analytics and Power BI are now becoming available to “the box”, on-premises or in hybrid cloud scenarios. We can expect more announcements on this as the roadmap is played-out and gaps are filled-in.
Reporting Services is a central player in the on-prem delivery of the BI, analytics and reporting tools offered by Microsoft. These include Excel, Paginated reports created with SSDT/report designer or Report Builder, Mobile Reports and Power BI Desktop.
