Everyone is really excited about PASS Summit being right around the corner and as usually happens, the folks at the Portland Oregon user group are putting on a SQL Saturday the weekend before.
I love getting to explore the city of Portland and eat food that came out of a converted camper 
or back yard shed, I love catching up with so many friends that make it out to the city that weekend, but this year I have something else to love.
My friend Mike Lawell ( b | t ) and I have teamed up to share some great SQL PowerShell content designed around the new features that have been coming out in the monthly releases of SSMS 2016. We’ll be doing this on Friday the 21st and there are two other great choices to pick from as well if you’re in town that day.
I hope to see you in Portland and if you’re a fan of all the SQL PowerShell goodness that has happened this year make sure to find me by Saturday morning for some cool SQL PowerShell stickers I have with me!
October 2016 SQL Mastery Sessions
An Oregon SQL Special Event
A Full-day of SQL Training offered the day before Oregon SQLSaturday 2016
Friday, October 21, 2016, 8:00 AM until 4:30 PM
University of Phoenix; 13221 SW 68th Pkwy, Tigard, OR 97223
Registration and Location Details
SQL PowerShell – Episode IV: A New Hope
Aaron Nelson | Mike Lawell
T-SQL For Performance and Accuracy
Vern Rabe
Paul Turley, Rachel Dyer, Brian Grant
SQL PowerShell – Episode IV: A New Hope
Aaron Nelson
25 New SQL PowerShell cmdlets have already been delivered and more are on their way every month. 2016 has unleashed a new era in what can be done with data by leveraging PowerShell. PowerShell is for every Data Professional and this session is built from my own experience as a DBA, Database Developer, and now B/I Developer.
The day will start with a primer on the basics of PowerShell and it’s ecosystem. You will learn things like how to work with data, import it into SQL Server extremely quickly, back up your database schema, compare that schema with another database. We will go through techniques to scale your scripts across multiple instances as well as how to store the results where you want. Even if you’re not a not a B/I Developer you may have needed to deploy an SSRS report or two, you will learn easy ways to leverage PowerShell to deploy lots of reports to lots of machines.
While we’re at it, we’ll go through new features the community has asked Microsoft build for SQL PowerShell, how you can add your voice to that conversation, and how to build something to get around an issue until Microsoft gets around to building their own solution.
Finally, we will go through error handling and other things to make your scripts more durable.
All attendees will receive all of the code that is demoed, as well as handouts about the language and how to get things done..
Aaron Nelson ( blog | twitter ) is a Consultant with SolidQ with over 15 years of experience in Architecture, Business Intelligence, Development, and Performance Tuning of SQL Server. He was recently awarded a 6th Microsoft MVP for SQL Server (Data Platform). Aaron holds certifications for MCITP: Business Intelligence Developer, Database Administrator, & Database Developer.
Aaron leads the PowerShell Virtual Chapters of PASS, and volunteers for the local PASS Chapter AtlantaMDF, and helps organize SQL Saturday events in Atlanta.
This year, the PowerShell VC of PASS set out to “Make SQL PowerShell Great Finally” and it is well on its way. Along with PowerShell MVP Chrissy LeMaire, Aaron helps organize what the community is asking for in the new SqlServer PowerShell module on the community Trello board (sqlps.io/vote). Thanks to these efforts and the countless contributions from the SQL community around the world, we have already received 25 new PowerShell cmdlets and have been told to expect even more new cmdlets every month!
Mike Lawell is a 20 year veteran of SQL Server, working with all versions since 6.5. He specializes in performance tuning, infrastructure, and high availability. He has been a consultant for SolidQ since 2015 specializing in health checks, performance optimization, & security and compliance.
He is also a certified Scuba instructor with a long bucket list of dive destinations. He resides in Atlanta, GA and you can catch him at many of the SQLSaturdays in the SE region of the United States.
T-SQL For Performance And Accuracy
Vern Rabe
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 is just about everything.
After the internals 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
- OUTER APPLY
- 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.
Vern Rabe (LinkedIn | Twitter) an independent SQL Server consultant and contract trainer in Portland, OR. He has attained MCSE, MCITP (both Administration and Development), and MCT certifications, among others. Vern has been working with databases for 23 years, and has worked with SQL Server since version 4.21a. He provides broad technical SQL Server skills gained from the mixture of academic and practical experiences acquired from his classroom instructing and varied consulting contracts.
Mastering Power BI Solutions
Paul Turley, Rachel Dyer, Brian Grant
In this second annual installment of the Power BI Hands-on Workshop, you will master the “power” of Power BI. Learn to use self-service and enterprise-scale Power BI capabilities; gain valuable skills to integrate, wrangle, shape and visualize data for analysis. Beginning and intermediate level users will learn to address data and reporting challenges with advanced design techniques. After several successful workshops last year, Paul has invited two respected experts in their respective disciplines to co-deliver this workshop and to go deeper with the maturing Power BI platform.
Bring your laptop with Power BI Desktop installed; 64-bit Windows & 8 GB of RAM (4 GB min) is recommended. A Power BI subscription is recommended but not required.
Topics & Skills:
Data wrangling and transformations:
- Source data from a variety of platforms & formats
- Connect to live and on-premises data using DirectQuery & SSAS direct connect
- Manage & schedule data refresh
- Query design best practices & survival skills
- Advanced query scripting techniques using “M”/Power Query formulas
Modelling and shaping data:
- Data modelling basics & best practices
- Modelling approaches with flat, normalized, star & snowflake schemas
- DAX calculation basics & fundamentals
- Need-to-know and good-to-know DAX functions & skills
- Understanding challenging DAX concepts; row & filter context, relationship cardinality & filter flow
- Advanced DAX calculations & business applications
Visualizing and analyzing business data:
- Standard report design principles & patterns
- Self-service end-user coaching
- Proper applications of filters & slicers
- Applying report visuals with cross filtering to answer business questions
- Report & dashboard design showcase
Advanced concepts and solutions:
- Working with on-prem, hybrid & published solutions
- Managing published models, reports & dashboards in the Power BI service
- Working with workspaces, content packs & security roles
- Using gateways to manage live & on-premises data sources
- Integrating Power BI with SSRS, Excel & SSAS
- Using R for specialized reporting, statistical & predictive analytics
- Using custom visuals for advanced & specialized reporting & analytics
Embedding reports & dashboards into sites & web solutions
Paul Turley (Blog | LinkedIn | Twitter) is an independent BI consultant, owner of Intelligent Business LLC, and a Microsoft Data Platform MVP. He consults, writes, speaks, teaches & blogs about business intelligence and reporting solutions. He works with companies around the world to visualize and deliver critical information to make informed business decisions. Paul is a Director of the Oregon SQL PASS chapter & user group, the lead author of Professional SQL Server 2016 Reporting Services and other titles from Wrox & Microsoft Press.
Rachel Dyer (LinkedIn) has been working with Data Warehouse related projects for almost 15 years, primarily in the role of consultant. In the last two years she has specialized in Power BI with particular attention to DAX and the modeling aspects of the platform. Her passion is for understanding the reasonably tricky parts, avoiding the really tricky parts, and helping others understand what’s cool about columns and rows.
Brian Grant (LinkedIn) Is an Analytics Consultant with CSG Pro, Brian has several years of experience training people in various data tools such as Power Query, and Excel both via workshops and online screencasts. He’s happiest when solving complex DAX problems and breaking down difficult concepts for people in ways that are easy to understand.
The post October 2016 SQL Mastery Sessions first appeared on SQLvariations: SQL Server, a little PowerShell, maybe some Power BI.
