Nr: 720
SQLSaturday #720 - Albuquerque 2018
SessionID: 72054
Faster SSIS
Event Date: 05-05-2018 - Session time: 08:30:00 - Track: BI Platform Architecture, Development Administration
Speaker: Andy Leonard
Title: Faster SSIS
Abstract:
Ever wonder why SSIS runs so slow? Watch SSIS author Andy Leonard as he runs test loads using sample and real-world data and shows you how to tune SQL Server 2016 Integration Services (SSIS 2016) packages.
We'll start by experimenting with SSIS design patterns to improve performance loading AdventureWorks data. We will implement different change detection patterns and compare execution performance for each. Then, we'll explain a Data Flow Task's bottleneck when loading binary large objects - or Blobs.
Finally, we'll demonstrate a design pattern that uses a Script Component in a Data Flow to boost load performance to MySql, whether on-premises or in the cloud.
SessionID: 72069
Top 5 SQL Server Mistakes
Event Date: 05-05-2018 - Session time: 08:30:00 - Track: Enterprise Database Administration Deployment
Speaker: Kathi Kellenberger
Title: Top 5 SQL Server Mistakes
Abstract:
SessionID: 73674
Event Date: 05-05-2018 - Session time: 08:30:00 - Track: Database Application Development
Speaker: Jay Robinson
Abstract:
SessionID: 74660
Implementing a Cloud Analytics Solution: Options, costs and more
Event Date: 05-05-2018 - Session time: 08:30:00 - Track: Cloud Analytics
Speaker: Marc Beacom
Title: Implementing a Cloud Analytics Solution: Options, costs and more
Abstract:
SessionID: 77259
An Introduction to SQL Server Management Studio
Event Date: 05-05-2018 - Session time: 08:30:00 - Track: Introduction to SQL Server
Speaker: Adrian Mee
Title: An Introduction to SQL Server Management Studio
Abstract:
Almost all of us use SQL Server Management Studio to interact with SQL Server. But, did you know it's chock full of tricks and handy little shortcuts. In this session I will demonstrate my favorite tricks and shortcuts that I've picked up over the years that have helped make it easier and more fun to use.
SessionID: 78295
Querying Temporal Tables
Event Date: 05-05-2018 - Session time: 08:30:00 - Track: Database Application Development
Speaker: Ron Beatty
Title: Querying Temporal Tables
Abstract:
System-versioned temporal tables, first introduced with SQL Server 2016, make it possible to not only query current data, but to also query the data as it was at a particular point in time. First, a temporal table is a type of table where the current version of the data is stored in the temporal table while previous versions of the data are stored in an associated “history” table. Together, the temporal table and its related history table store a full history of the data as it changes over time. Second, the new FOR SYSTEM_TIME clause is available in SELECT … FROM constructs for querying temporal tables at either a precise moment in time or during a specific period of time with one of four sub-clauses: AS OF, FROM TO, BETWEEN AND, and CONTAINED IN (,). In this presentation, the schema of a temporal table and its corresponding history table will be explored to gain an understanding of how SQL Server stores temporal data. This will be followed with queries demonstrating how and when to us
SessionID: 73211
3 Lessons Learned in Successful Consulting
Event Date: 05-05-2018 - Session time: 09:45:00 - Track: Professional Development
Speaker: Mohammad Darab
Title: 3 Lessons Learned in Successful Consulting
Abstract:
I would like to share the 3 lessons I learned that helped me become a much better consultant. These lessons were crucial in helping me make that "mind shift" from thinking like a regular "9 to 5 DBA" to thinking like a consultant. The goal of this session is to help those who are interested in becoming a consultant. Hopefully these 3 lessons will help make that transition successful as it did for me.
SessionID: 73684
Killing Cursors
Event Date: 05-05-2018 - Session time: 09:45:00 - Track: Database Application Development
Speaker: Cyndi Johnson
Title: Killing Cursors
Abstract:
SessionID: 73836
Introduction to R
Event Date: 05-05-2018 - Session time: 09:45:00 - Track: Analytics and Visualization
Speaker: Jeff Renz
Title: Introduction to R
Abstract:
SessionID: 74179
SQL Server Data Encryption 101
Event Date: 05-05-2018 - Session time: 09:45:00 - Track: Enterprise Database Administration Deployment
Speaker: John Morehouse
Title: SQL Server Data Encryption 101
Abstract:
It seems more lately than not, there are daily stories of a data breach somewhere. Data security is one of the most critical things a data professional should be aware of. Now, there are several ways of implementing data security in today's world. SQL Server comes with several native data encryption methods that you can use. In this session, we will explore the basics of data encryption, how to implement some of them, some gotchas as well as some of the newer features of data encryption in newer versions.
SessionID: 78054
Dynamic Data Driven Subscriptions in SSRS Without Enterprise Edition
Event Date: 05-05-2018 - Session time: 09:45:00 - Track: BI Information Delivery
Speaker: Dave Justice
Title: Dynamic Data Driven Subscriptions in SSRS Without Enterprise Edition
Abstract:
Data Driven reports are a powerful Reporting Services feature, but they are only available in the Enterprise Edition. In this session we're going to dig into the ReportServer database and look at some tables that will allow us to dynamically interact with subscription records and use them to control report output.
We will also leverage an SSRS interface to set and update parameters to the subscription tables.
By using these techniques we can simulate the power of Data Driven Subscriptions without having to have Enterprise Edition. These techniques can also be used to dynamically configure actual Data Driven Subscriptions and kick them off without access to the SQL Agent engine. Anyone wanting to extend their SSRS capabilities in creating report automation should attend this session.
SessionID: 70966
Accelerating Development and Testing Using Data Virtualization
Event Date: 05-05-2018 - Session time: 11:00:00 - Track: Enterprise Database Administration Deployment
Speaker: Tim Gorman
Title: Accelerating Development and Testing Using Data Virtualization
Abstract:
Constraining everything in development and testing is database size. Storage is a bottleneck, and while faster SSD/Flash is available, it is still too expensive due to the size of many databases, especially in DEV and TEST. More to the point, it takes a lot of time to push terabytes around, and time itself is expensive. The old joke about "good, fast, and cheap -- pick any two" is so true with data.
Providing full databases for each developer or tester on each task of each project seems unrealistic, when each copy might require terabytes of storage, so for decades everyone has limited themselves to working in shared dev/test environments that are refreshed only every few months, where conflicts occur, quality suffers, and things move slowly.
Come learn why data virtualization is the solution to a problem everyone knows.
Good, fast, and cheap -- have all three with data virtualization. Be able to clone data quickly, and remove the biggest constraint to development and testing.
SessionID: 73007
The Power of Data: The Complete Beginners Data Warehouse Kit
Event Date: 05-05-2018 - Session time: 11:00:00 - Track: BI Platform Architecture, Development Administration
Speaker: Leslie Andrews
Title: The Power of Data: The Complete Beginners Data Warehouse Kit
Abstract:
SessionID: 74155
Why is my DBA So Grumpy ... When I Use NOLOCK and Other Query Hints.
Event Date: 05-05-2018 - Session time: 11:00:00 - Track: Database Application Development
Speaker: Rick Lowe
Title: Why is my DBA So Grumpy ... When I Use NOLOCK and Other Query Hints.
Abstract:
Most of us have heard that using the NOLOCK hint is not recommended. But why is this? What is it about NOLOCK that sends most DBAs into a tizzy? And how about other hints such as ROWLOCK and RECOMPILE - are they OK to use? Come hear a discussion on why exactly query hints cause problems when used excessively, and watch some demos of NOLOCK craziness in action.
SessionID: 77060
Querying Semistructured Data in Azure Data Lake with USQL
Event Date: 05-05-2018 - Session time: 11:00:00 - Track: Cloud Analytics
Speaker: Russel Loski
Title: Querying Semistructured Data in Azure Data Lake with USQL
Abstract:
Data is exploding across enterprises. Much of it is semi-structured junk. Or is it junk? What are you going to do with it until you can find out? Microsoft’s Azure Data Lake is a cloud storage and analytical service for parking a variety of data. When you are ready, you can query that semi-structured data using an ANSI SQL language called U-SQL. In this session I will demonstrate the similarities and differences between U-SQL and T-SQL. I will demonstrate how easy it is to build a query against 21 GB of CSV files. Such queries can help you determine whether you have a gold mine in your data or a bunch of garbage before investing in a full data warehouse build.
SessionID: 77256
Database Project Makes Deployment Consistent
Event Date: 05-05-2018 - Session time: 11:00:00 - Track: Database Application Development
Speaker: Thomas Norman
Title: Database Project Makes Deployment Consistent
Abstract:
The deployment process is frustrating; an object is missing, an object is deployed in the wrong order or an incorrect version. Deploying to multiple environments like Dev, QA, UAT, and Prod is a challenge whether on-premise or in the cloud. Source controlling database objects is paramount. Team development is required. Database code will only be deployed when changed. Proper security deployment will be repeatable per environment. Together, let us discover how using Database Project will help us with consistent releases. Deployments can cause you trouble but we will share a reliable deployment and version control process. Let’s see how Database Project can help us accomplish this goal.
SessionID: 70928
Hi, my name is Powershell, let's be friends! An Intro to PoSH
Event Date: 05-05-2018 - Session time: 13:00:00 - Track: Enterprise Database Administration Deployment
Speaker: Amy Herold
Title: Hi, my name is Powershell, let's be friends! An Intro to PoSH
Abstract:
Powershell wants to be your friend - your very powerful friend. In this session we will go over some Powershell basics that will help get you started as well as some known "gotchas" when working in Powershell. This is a beginner session - no Powershell experience necessary. WARNING: You may get warm-fuzzies about Powershell by attending this session.
SessionID: 70943
Code Like a Snake Charmer - Intro to Python!
Event Date: 05-05-2018 - Session time: 13:00:00 - Track: Analytics and Visualization
Speaker: Jamey Johnston
Title: Code Like a Snake Charmer - Intro to Python!
Abstract:
The session will be an introduction to the Python language including:
1.) Installing and Configuring Python
2.) Access and Manipulate Data
3.) Install and Manage Packages
4.) Create and Use Objects/Variables
5.) Control Flows and Functions
6.) Managing the Python installation in MSSQL
Attend this session to learn how to use Python to take your data analytics to the next level. We will use Python, SQL Server and the Anaconda distribution of Python to learn the basics of Python and how it is integrated in the Microsoft stack! We will walk through a simple deployment of a machine learning model to see how it all works together and learns some basic data science fundamentals.
No prior statistics knowledge needed.
SessionID: 71337
Critical Path SQL
Event Date: 05-05-2018 - Session time: 13:00:00 - Track: Database Application Development
Speaker: Thane Schaffer
Title: Critical Path SQL
Abstract:
Critical path SQL will focus on recursive common table expressions (CTE) on problem solving in finding minimum and maximums for common business problems like minimum/maximum manufacturing costs and lead times in BOM's (bill of materials) and other hierarchical data structures where a tree traversal is needed as well as non-hierarchal data structure examining CTE usage with the traveling salesman problem to examine a desired minimum.
SessionID: 73562
An Introduction to Power BI
Event Date: 05-05-2018 - Session time: 13:00:00 - Track: Introduction to SQL Server
Speaker: Martin Schoombee
Title: An Introduction to Power BI
Abstract:
This session will explore every aspect of Power BI…from the technology used behind the scenes and the tenant/service in Azure, to data refresh options and development best practices. We’ll cover everything you need to know (and more) before you embark on your first Power BI project.
SessionID: 77081
Event Date: 05-05-2018 - Session time: 13:00:00 - Track: Professional Development
Speaker: Je'Anna Lanza-Abbott
Abstract:
Because little in business or life gets done without negotiation, this session has the potential to deliver significant upside in your professional and personal outcomes. In this session, you will be introduced to basic negotiation concepts and tactics, followed by a focus on important aspects of the negotiation process—obstacles to effective communication at the bargaining table, the maximization of multiple interests, and preparation tactics for your next negotiation. We will put it all together by examining a special type of negotiation: YOUR SALARY!
Participants will:
• Learn strategies for negotiation analysis and preparation
• Understand different strategic choices and interpersonal skills necessary for success
• Know what information is needed to negotiate effective outcomes
• Experiment with new techniques and test their effectiveness in a safe environment
SessionID: 77212
VLDB Tips and Migration Tactics
Event Date: 05-05-2018 - Session time: 13:00:00 - Track: Enterprise Database Administration Deployment
Speaker: Todd Kleinhans
Title: VLDB Tips and Migration Tactics
Abstract:
Are your databases growing? Most likely they are but what do you have to do differently when they reach VLDB (>1TB) in size? I currently manage and have recently migrated terabyte sized databases (~30TB) to SQL Server 2016. In this session, people will learn how they need to change their thinking when it comes to bread and butter DBA tasks like daily and weekly maintenance, patching, restores and monitoring. I will also share many lessons learned and strategies from migrating from an older version of SQL Server to a newer version like SQL Server 2016. Afterwards people will have a greater sense of confidence that they too can manage and migrate VLDB(s).
SessionID: 70952
Make Power BI Your Own with the Power BI APIs
Event Date: 05-05-2018 - Session time: 14:15:00 - Track: BI Platform Architecture, Development Administration
Speaker: Steve Wake
Title: Make Power BI Your Own with the Power BI APIs
Abstract:
Power BI makes accessing data from various sources and building advanced visualizations for analytics easy to do. Behind the scenes Power BI is built on standard technologies and APIs that allow it to be customized in multiple ways to fit in with your environment the way you need it to. In this session we will walk though samples and demos that show how you can extend Power BI with custom visualizations, access custom data sources, embed various Power BI elements in your applications, and be able to push your data into a Power BI report/dashboard. Each of these items take advantage of the Power BI APIs in different ways and we will discuss each and provide the code samples for you to take back and get a head start. This session will be showing code to access the Power BI REST API using C# and NodeJS along with some M for accessing and manipulating custom data sources.
SessionID: 70982
SQL Server Take Over: Establishing a Baseline
Event Date: 05-05-2018 - Session time: 14:15:00 - Track: Enterprise Database Administration Deployment
Speaker: Lowry Kozlowski
Title: SQL Server Take Over: Establishing a Baseline
Abstract:
SQL Server Take Over: Establishing a Baseline
Performance assessment and tuning for the beginner DBA
Have you ever inherited a group of servers with not a lot of documentation to accompany them? Do you have a lot of undocumented or new servers in your environment and you want to establish some baseline configuration setting prior to making changes? You need up to date information on the system very quickly.
The server is running slowly... or is it? Slow compared to what? Before you being digging into performance issues, there are some baseline metrics you should have established. Only after the information has been gathered, can a plan of attack be developed to help resolve issues with performance. Attendees in this session will learn how to quickly gather information, review the results and get an action plan on the next step in performance tuning.
SessionID: 72769
Challenges Opportunities in Creating Accessible Power BI Reports
Event Date: 05-05-2018 - Session time: 14:15:00 - Track: BI Information Delivery
Speaker: Meagan Longoria
Title: Challenges Opportunities in Creating Accessible Power BI Reports
Abstract:
Accessibility is catering for your whole audience, including those with disabilities. According to the US Census Bureau, 19 percent of the of the population had a disability in 2010. Do you know if your reports are designed in an inclusive way such that everyone can use them? This can be especially important if you are making reports for government entities, educational institutions, or the general public. In this session, we'll discuss accessibility standards and regulations of which you may want to be aware, how users with disabilities can consume Power BI reports, and guidance and pitfalls when designing inclusive reports.
SessionID: 74178
An Introduction to Azure
Event Date: 05-05-2018 - Session time: 14:15:00 - Track: Introduction to SQL Server
Speaker: John Morehouse
Title: An Introduction to Azure
Abstract:
It's not a question on whether or not the landscape for the common DBA is changing. Without a doubt, it is. Azure offers up a new world of possibilities for DBA's and we should all strive to learn it. In this session we'll cover some basic knowledge and terminology of Azure as well as how easy it is to incorporate Azure into your environment. We will stand up a new Azure virtual machine as well as a setup SQL DB. You will see how easy it is to accomplish this. This new found knowledge will help propel your career into the new landscape.
SessionID: 77280
The Anatomy of Select
Event Date: 05-05-2018 - Session time: 14:15:00 - Track: Database Application Development
Speaker: Jason Brimhall
Title: The Anatomy of Select
Abstract:
When was the last time you sat down to evaluate a SELECT statement? What actually goes into the processing of that teeny little word? There might be a fair bit more to this simple term than just fetching data from the database. This session covers the simplicity of the select. Then we take it a step further to dive into some of the complexities tied directly to this particular statement.
This session will help the attendee better understand the Select statement. With a more solid understanding of this statement, the attendee can return and implement some of this new knowledge to help improve processing and query response times in their own environments.
SessionID: 77322
SQL Server Meets Big Data in the Cloud: Weather Edition
Event Date: 05-05-2018 - Session time: 14:15:00 - Track: Cloud Analytics
Speaker: Rowland Gosling
Title: SQL Server Meets Big Data in the Cloud: Weather Edition
Abstract:
From government agencies and private networks, weather data moves through data platforms and becomes observations and forecasts. We’ll take a look at gathering, transforming and storing weather data using Microsoft Azure, SQL Server, Hadoop and a variety of other technologies (Kafka, Spark, Python, Postgres).
SessionID: 70935
Power BI, from a DBA's Perspective
Event Date: 05-05-2018 - Session time: 15:45:00 - Track: Enterprise Database Administration Deployment
Speaker: TJay Belt
Title: Power BI, from a DBA's Perspective
Abstract:
Microsoft’s Power BI is the new hotness. In this session, you will get your hands dirty and dive into PowerBI and create an actual report, with actual data. You will get to learn from examples from what a DBA has done to implement Power BI in his shop and make his data accessible.
As a DBA, we often need to collect and display data in a friendly and interactive fashion. We will play with some PowerShell to collect data from several systems, store this data in an Azure DB in the Cloud, then point Power BI at this data to display and interact with said data. You will learn a little about PowerShell, Azure SQL DB, Power BI, and how to make these work for you.
SessionID: 71232
Source Control and Database Projects - Just Do It
Event Date: 05-05-2018 - Session time: 15:45:00 - Track: Database Application Development
Speaker: Leslie Weed
Title: Source Control and Database Projects - Just Do It
Abstract:
If you aren't using database projects you should be and if you aren't using any source control well..... NOW IS THE TIME. We'll review basic functionality and purpose of both and walk through how they work together. Let's talk about the type of foundation pieces you need to pay attention to and how to organize this into team and agile work. Lots of tips and tricks to remove the often experienced headaches that developers have experienced with implementing both of these items.
SessionID: 72067
Introduction to T-SQL Windowing Functions
Event Date: 05-05-2018 - Session time: 15:45:00 - Track: Database Application Development
Speaker: Kathi Kellenberger
Title: Introduction to T-SQL Windowing Functions
Abstract:
T-SQL windowing functions can simplify many complex queries. They are the best thing that has happened to the T-SQL language in years, but many database professionals and developers haven’t heard about them.
They were initially introduced with SQL Server 2005. In 2012, Microsoft added several new functions that let you do things like pull values from different rows without a performance penalty.
Attend this session to learn how T-SQL windowing functions apply to many real world examples.
SessionID: 77179
The Ultimate Maintenance Plan
Event Date: 05-05-2018 - Session time: 15:45:00 - Track: Enterprise Database Administration Deployment
Speaker: Edward Roepe
Title: The Ultimate Maintenance Plan
Abstract:
Is your SQL Server running slower and slower? Do you prefer a proactive rather than a reactive approach to managing SQL Server? Do you worry about being able to recover in an emergency? If you answer yes to any of the above this session is for you.
We will start by exploring some of the maintenance options that are available. We will then explore the popular utility program by Ola Hallengren and how to implement. Next, we will explore the other areas of SQL Server that need to be maintained like history tables in MSDB. Finally, we will explore the different options when maintenance windows are tight.
At the end of this session you will be able to: (1) Choose a maintenance program for your SQL Servers; (2) Program and schedule the maintenance programs; (3) Ensure that the SQL Server is running at optimum performance each day.
SessionID: 80010
Python in SQL Server 2017
Event Date: 05-05-2018 - Session time: 15:45:00 - Track: Analytics and Visualization
Speaker: Chris Hyde
Title: Python in SQL Server 2017
Abstract:
SQL Server 2017 has finally arrived, bringing with it a host of exciting new features. Of particular interest to BI practitioners and data scientists is the integration of the Python programming language into the SQL ecosystem. This integration opens up a number of possibilities for in-database analytics and intelligent database applications, so let's get started!
In this introductory session we'll briefly introduce the Python language and then look at how to install and administer Machine Learning Services. We'll start to demonstrate the power of Python by showing you how to create and run simple scripts from within T-SQL. We'll then discuss some of the powerful open source packages that are available to aid your data analyses. Finally, we'll go through some examples of how to use Python to integrate data analytical methods in real-world situations, and wow your customers with the power of analytics!