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
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 →
Finally, the parameters can be updated right here in the dataset settings.
