Nr: 386
SQLSaturday #386 - Albany 2015
SessionID: 25505
Surviving parsing XML with T-SQL
Event Date: 25-07-2015 - Session time: 08:30:00 - Track: DBA - Database Development
Speaker: Steve Simon
Title: Surviving parsing XML with T-SQL
Abstract:
In this hands on presentation we shall be looking at varied techniques to materialize our data (from XML format) in an efficient and effective manner. We shall begin by tackling the subject within Management Studio and then progress to SQL Server Integration Services and SQL Server Reporting Services. The presentation is based upon true case scenarios and will show many of the pitfalls or gotcha’s of which to be wary.
SessionID: 28148
Getting Started with MDX
Event Date: 25-07-2015 - Session time: 08:30:00 - Track: Enterprise BI
Speaker: William E Pearson III
Title: Getting Started with MDX
Abstract:
In this session, led by Microsoft BI Architect and SQL Server MVP Bill Pearson, we will concentrate largely upon crafting simple MDX expressions and queries whose purposes, for the most part, are to return a set of data. We will overview the structure of a cube, using as a basis the sample Adventure Works cube that is available to anyone installing SQL Server Analysis Services 2008 R2. We will then outline the components of simple MDX syntax, and get started writing basic expressions and queries. We will expose basic member functions, introduce filters (or “slicers”), and begin exploring core MDX functionality, including calculated members, and named sets. This session applies equally to Analysis Services 2008, 2008R2 and 2012.
SessionID: 35800
101 Stupid Things Your Colleagues Do When Setting Up A SQL Server
Event Date: 25-07-2015 - Session time: 08:30:00 - Track: DBA - Administration
Speaker: Matt Slocum
Title: 101 Stupid Things Your Colleagues Do When Setting Up A SQL Server
Abstract:
SessionID: 37450
Scratching the surface: An overview of SSRS and building your first BI Report
Event Date: 25-07-2015 - Session time: 08:30:00 - Track: Self-Service BI
Speaker: Chris Seferlis
Title: Scratching the surface: An overview of SSRS and building your first BI Report
Abstract:
SessionID: 37780
PowerShell for Absolute Beginnners
Event Date: 25-07-2015 - Session time: 08:30:00 - Track: IT/Server Administration
Speaker: Michael Wells
Title: PowerShell for Absolute Beginnners
Abstract:
Have you wanted to learn PowerShell, but didn't know where to start? Have you been scared off by all the pipes and curly braces, or maybe you're just not sure what PowerShell can do for you? This session will teach you the very basics of PowerShell and give you the fundamentals you need to start writing your own scripts and functions. You'll also get a list of resources to continue learning and take your skills to the next level.
SessionID: 37786
An Introduction to Big Data, Hadoop and Microsoft Azure HDInsight.
Event Date: 25-07-2015 - Session time: 08:30:00 - Track: Big Data
Speaker: Adriano da Silva
Title: An Introduction to Big Data, Hadoop and Microsoft Azure HDInsight.
Abstract:
This presentation provides a high level overview of basic Big Data and Hadoop fundamentals and concepts. It is an ideal opportunity for those considering or learning Hadoop to see Big Data in Action with examples using Microsoft Azure HDInsight, which is a 100% Apache Hadoop-based service in the cloud provided by Microsoft. We will also cover Big Data exploration and data visualization demonstrations using Microsoft products; such as PowerQuery for Excel.
SessionID: 37936
Monitoring SQL Server using Extended Events
Event Date: 25-07-2015 - Session time: 08:30:00 - Track: DBA - Administration
Speaker: Hilary Cotter
Title: Monitoring SQL Server using Extended Events
Abstract:
SessionID: 37942
All things non technical
Event Date: 25-07-2015 - Session time: 08:30:00 - Track: Professional/Personal Development
Speaker: Susan Lundberg
Title: All things non technical
Abstract:
This session will address all things non technical.
Job Market
How to look for a new job while employed
What are employers looking for
Do I stay or do I go
Networking
Social Media pros and cons
Keeping up
SessionID: 25493
DAX and the tabular model
Event Date: 25-07-2015 - Session time: 10:00:00 - Track: Enterprise BI
Speaker: Steve Simon
Title: DAX and the tabular model
Abstract:
With the advent of the tabular model in SQL Server 2012, another analytical option has opened to the BI Specialist. We shall be looking at what critical factors should be analyzed prior to opting for the model and that done, how to extract data from a tabular model using Data Analysis Expressions (DAX).
We shall discuss many of the often used functions, discuss their 'overloads' (utilizing several practical examples) and see how to turn the extracted data into valuable information that may be used by decision makers to help the enterprise achieve its corporate goals.
SessionID: 28146
Introducing Forensic Analytics with PowerPivot for Excel
Event Date: 25-07-2015 - Session time: 10:00:00 - Track: Self-Service BI
Speaker: William E Pearson III
Title: Introducing Forensic Analytics with PowerPivot for Excel
Abstract:
Despite the wealth of analytical technologies available today, 65% of fraud at public companies is detected through tips, management review, or simple accidents. Forensic Analytics combine human intuition and leading-edge technologies, and can provide superior detection methods to significantly improve the detection and investigation of illegal or unethical activities.
In this session, we explore Forensic Analytics: a set of techniques we can use to uncover irregularities in financial data. We also gain an understanding of goals of Forensic Analytics: to enable us to find out how — and why — these irregularities exist, and to find out their source(s) — especially when fraudulent activity is suspected. We discuss guiding principles that are key to isolating data anomalies, and outline a standard, repeatable process to Forensic Analytics. Throughout the session, we view examples of how we can employ PowerPivot for Excel to drive Forensic Analytics via various tests and reviews.
SessionID: 35799
What Every DBA Needs To Know About Storage For SQL Server
Event Date: 25-07-2015 - Session time: 10:00:00 - Track: DBA - Administration
Speaker: Matt Slocum
Title: What Every DBA Needs To Know About Storage For SQL Server
Abstract:
Do you have issues communicating your needs to your Storage/SAN Administrator? Do they seem to speak a different language when it comes to determining and providing your SQL Server storage needs? In this session we'll review storage technologies so that you can communicate effectively with your Storage/SAN Administrator as well as methods of monitoring your storage to ensure that your needs are met.
In this session we will discuss RAID types, Local/Direct Attached Storage vs SAN Storage, and Flash Storage (including how it fits into multiple storage models). We'll also review methods for monitoring and testing your storage performance. With the information gathered in this session, you'll be able to have thorough, informed discussions with your Storage Administrator as well as being able to provide solid metrics to confirm storage performance.
SessionID: 36282
Tracking Index Usage in SQL 2012 and SQL 2014
Event Date: 25-07-2015 - Session time: 10:00:00 - Track: DBA - Administration
Speaker: Ken Schwartz
Title: Tracking Index Usage in SQL 2012 and SQL 2014
Abstract:
An important function in the role of DBA is one of Index Maintenance. This includes not only evaluating what indexes we should have, but also what indexes we do have, but don't really need. Since database usage patterns change over time, indexes that were needed once might not be needed now.
But I'm not really going to talk much about that. :)
In order to evaluate index usage, we need to have some way of gathering and storing index usage information over time. Unfortunately, many of the standard solutions are falling short. Why? You better come to the presentation and find out.
SessionID: 36785
.NET for Data Professionals
Event Date: 25-07-2015 - Session time: 10:00:00 - Track: .NET
Speaker: Robert Biddle
Title: .NET for Data Professionals
Abstract:
No .NET experience is needed for this session. Basic understanding of programming concepts such as if/then logic and loops is suggested.
SessionID: 37891
Architecting Availability Groups
Event Date: 25-07-2015 - Session time: 10:00:00 - Track: DBA - Administration
Speaker: Derik Hammer
Title: Architecting Availability Groups
Abstract:
You are a DBA familiar with some basic Windows clustering and database mirroring concepts. You are considering Always-On Availability Groups and realize that the simplistic setup wizard is a bit, “too easy.” This session will cover the pros and cons of four typical configurations of Always-On Availability Groups including their marketable features such as read-only routing and backup off-loading. I will demonstrate how to use the Availability Group Listener correctly and why you should be using Windows Server 2012 or above, along with some of my own personal lessons learned.
SessionID: 37927
So you think you know SSIS? What about Scripting?
Event Date: 25-07-2015 - Session time: 10:00:00 - Track: DBA - Database Development
Speaker: Bryan Cafferky
Title: So you think you know SSIS? What about Scripting?
Abstract:
If you don’t know how to extend the power of SSIS through the Script Task and Script Component, you don’t know SSIS. Empower your SSIS skills by learning how to add .Net scripts to your packages using C# or VB.Net code to access the full power of Windows and its object models. In this presentation Bryan will walk through the basics of SSIS scripting building to more complex examples using real world cases. Learn how to load any external flat file by taking direct control, how to leverage the power of the SQL Server Object Model (SMO), ActiveX Data Objects (ADO.Net), and use the power of Regular Expressions for advanced string parsing. You will leave with the basics you need to start scripting and a number of useful scripts you can add to your packages.
SessionID: 11035
Advanced Programming in PowerShell
Event Date: 25-07-2015 - Session time: 12:30:00 - Track: DBA - Database Development
Speaker: Bryan Cafferky
Title: Advanced Programming in PowerShell
Abstract:
PowerShell is the hottest new Microsoft technology and you need to know it. Assuming a basic knowledge of PowerShell, this session will focus on advanced topics like creating modules, advanced functions, creating custom PowerShell objects, creating a GUI for your scripts, application automation and much more. Many code examples will be examined to show you how to apply the concepts. This is the presentation to attend to step up your game.
SessionID: 16752
Advanced Transactional Replication
Event Date: 25-07-2015 - Session time: 12:30:00 - Track: DBA - Administration
Speaker: John Flannery
Title: Advanced Transactional Replication
Abstract:
We will cover several advanced topics in Transactional Replication including how to make a ddl change to one table in the publication without reinitializing the publication; how to make a ddl change to a table without having to snapshot; creating replicated tables on file systems other than Primary – including partitioned file systems; and bidirectional replication. And hint – we will be leaving the wizard in Redmond!! Bring a copy of SQL 2014 Developers Edition with AdventureWorks2014 if you want to play along
SessionID: 20991
SQL Server Compression - Getting More Out of Less
Event Date: 25-07-2015 - Session time: 12:30:00 - Track: DBA - Optimization/Tuning
Speaker: Michael Nowill
Title: SQL Server Compression - Getting More Out of Less
Abstract:
SessionID: 26119
Automated Installing and Configuration of SQL2014/SQL2012 AlwaysOn Across Multiple Datacenters
Event Date: 25-07-2015 - Session time: 12:30:00 - Track: DBA - Administration
Speaker: Thomas Grohser
Title: Automated Installing and Configuration of SQL2014/SQL2012 AlwaysOn Across Multiple Datacenters
Abstract:
The whole process is presented as a life demo
SessionID: 37349
Whacha just say? Talking technology to non-technical people
Event Date: 25-07-2015 - Session time: 12:30:00 - Track: Professional/Personal Development
Speaker: Ray Kim
Title: Whacha just say? Talking technology to non-technical people
Abstract:
Many tech professionals are faced with the challenge of explaining technical concepts to people who don't understand technology. And only a few are able to do it well. This session provides some strategies and suggestions to go about bridging the technological knowledge gap. With time and practice, a technical person can serve as an effective interpreter of technical concepts. This is an interactive session; audience participation is expected and encouraged!
SessionID: 37714
Bulletproof SSIS - A new process control framework
Event Date: 25-07-2015 - Session time: 12:30:00 - Track: Enterprise BI
Speaker: Daniel Bowlin
Title: Bulletproof SSIS - A new process control framework
Abstract:
Have you ever had a package fail and wish it would recover automatically? Have you ever encountered a source or network issue and loaded bad or incomplete data into production? Do you have jobs that fail or load bad data because an upstream dependency ran long? Do you need faster loads to stay within your load window?
Some of the common challenges facing SSIS developers and DBAs are scaling up and/or out to increase throughput, load balancing, recoverability and restartability, basic process auditing, and multiple job dependency checking. This presentation reviews all of the elements to building a process control framework using the work pile design pattern that can help resolve many of these issues. Finally we will conclude with a demonstration of a basic process control framework outlining table design, control packages, load packages, dependency checks, audit controls, and very basic load balancing. Downloads include: slide deck, table scripts, SSIS packages.
SessionID: 37748
Software testing automation myths and CI
Event Date: 25-07-2015 - Session time: 12:30:00 - Track: QA Automation
Speaker: Vijaya Kokkili
Title: Software testing automation myths and CI
Abstract:
Even though functional testing has been around for several years people still make assumptions which lead to major blunders. I am sure many of software engineers have heard a question, 'why manually test, when it can be replaced by automation tests?'. This assumption leads to major blunders in test automation. We will go through few of test automation assumptions and how many assume automation is a silver bullet. We will talk about how to achieve efficient and valuable test automation.
We will also go through, how to incorporate Continuous Integration into testing automation to achieve Continuous Delivery.
SessionID: 13876
Dynamic SQL: Writing Efficient Queries on the Fly
Event Date: 25-07-2015 - Session time: 14:00:00 - Track: DBA - Database Development
Speaker: Edward Pollack
Title: Dynamic SQL: Writing Efficient Queries on the Fly
Abstract:
Explore the many uses for dynamic SQL as well as best practices for writing, testing, debugging, and maintaining it, with the bonus of helping to improve the sanity of your coworkers (and your own) in the process!
SessionID: 36588
Optimizing Protected Indexes
Event Date: 25-07-2015 - Session time: 14:00:00 - Track: DBA - Administration
Speaker: Chris Bell
Title: Optimizing Protected Indexes
Abstract:
As a SQL Server DBA I see my role having 2 main focuses. First is to protect the data and second is to ensure performance is maximized. More often than not companies forget about data security when they focus on performance tuning. In this presentation I will mention a few cases where encryption could have saved a lot of hassle. I will then demo a method that can be used to "index" commonly searched on encrypted values (ex: SSNs, Credit Card, etc) helping reduce the impact on the performance of a query.
SessionID: 37705
Debugging For Fun and Profit
Event Date: 25-07-2015 - Session time: 14:00:00 - Track: .NET
Speaker: Michael Phipps
Title: Debugging For Fun and Profit
Abstract:
Bugs. Ugh. Who likes 'em? NOBODY.
But they are a fact of life. Everyone makes mistakes. Tracking them down is part of our lives as developers. This session will fill your tool kit with methods, ideas and techniques for finding and crushing bugs.
This presentation is (mostly) not .Net and Visual Studio specific and would be a great fit for developers who work in any language.
SessionID: 37715
Unleashing SSIS with expressions, configurable properties, and parameters
Event Date: 25-07-2015 - Session time: 14:00:00 - Track: Enterprise BI
Speaker: Daniel Bowlin
Title: Unleashing SSIS with expressions, configurable properties, and parameters
Abstract:
Have you struggled to build SSIS packages? You are starting to get the hang of it, but many things more than basic data movement remain a frustrating challenge. The SSIS expression language is an incredible source of power within SSIS but a profound challenge to many. Another area of power and flexibility in SSIS is its very broadly scoped configurability. This presentation will walk through the basics of the SSIS expression language including the whys and wherefores of a few expression peculiarities. Then we will review how you can use those expressions to configure so many of the properties in SSIS that you might barely recognize what your package can do when you are finished. Finally we will review input parameters and how to further influence the behavior of your packages at run time. Download includes slide deck, expression samples, and some packages.
SessionID: 37798
Location Location Location Preparing and Presenting Geographic Data
Event Date: 25-07-2015 - Session time: 14:00:00 - Track: DBA - Database Development
Speaker: David Dye
Title: Location Location Location Preparing and Presenting Geographic Data
Abstract:
This session will begin with the tasks necessary to model a dimensional data structure to maintain and present geographic data. We will discuss various requirements that can affect a data model such as granularity, surrogate keys, slowly changing dimensions. Real life examples and solutions will provided to give better context to each step in the process.
After the data structure has been created we will focus on how to best “clean” and validate the geographic data. We will focus on using SSIS to obtain latitude and longitude and then how this can be converted SQL Server spatial types.
Finally we will cover presenting this data using SSRS and Power Map and how to create dynamic and aesthetically pleasing graphical reports. This portion of the session concentrates on presenting reports using the geographic data type that answer specific questions such as:
Events that occurred at a specific address, zip code, and even block range and events within a mile radius.
SessionID: 37960
Stunning Dashboards using Datazen
Event Date: 25-07-2015 - Session time: 14:00:00 - Track: Enterprise BI
Speaker: Sunil Kadimdiwan
Title: Stunning Dashboards using Datazen
Abstract:
In this session I will demonstrate the power and ease of using this technology
SessionID: 38023
Accounting Basics for Database Developers
Event Date: 25-07-2015 - Session time: 14:00:00 - Track: Professional/Personal Development
Speaker: Mark Frawley
Title: Accounting Basics for Database Developers
Abstract:
Many database developers sooner or later need to consume from or feed data to an accounting application such as the
General Ledger. Not being trained as bookkeepers, we may find bookkeeping conventions confusing at best and mystical at
worst. When is a credit negative, and a debit positive ? The subject also has a reputation for being dull.
But not really understanding the concepts and semantics seriously impedes unit testing, effective communication with the business, and excessive dependence on business analysts as intermediaries. We can do better ! This session will focus on demystifying the concepts and terms of basic accounting, facilitating further study if you are so inclined and enabling you to understand the requirements and be proactive in testing.
The session will conclude with a brief demo showing how SQL Server's Analysis Services directly supports some bookkeeping concepts.
SessionID: 38225
Event Date: 25-07-2015 - Session time: 14:00:00 - Track: Big Data
Speaker: Jonthan McKinney
Abstract:
SessionID: 36586
Hacking Expos#233; - Using SSL to Secure SQL Server Connections
Event Date: 25-07-2015 - Session time: 15:30:00 - Track: DBA - Administration
Speaker: Chris Bell
Title: Hacking Expos#233; - Using SSL to Secure SQL Server Connections
Abstract:
You know all the ways to protect your database when it is at rest, but what about when someone connects and starts running some queries? What if they connect and don't do anything? Just how exposed is that data?
We will assume the role of a hacker and, by using a simple technique, we will sniff packets on a network to reveal what data is being sent. You may be shocked!
We will then secure our database connections with a simple self-signed SSL certificate.
Once secured, we will resume the role of the hacker and look inside the packets to see what has changed.
*Warning - Do not try these demos at work without proper permissions as actual hacking techniques are used.
SessionID: 37179
Introduction to SQL Server Automation with PowerShell
Event Date: 25-07-2015 - Session time: 15:30:00 - Track: DBA - Administration
Speaker: Chris Sommer
Title: Introduction to SQL Server Automation with PowerShell
Abstract:
DBA’s are constantly faced with challenges in maintaining a healthy SQL Server environment. Monitoring our database servers, deploying database code changes and performing database restores are just a few examples of a day in the life of a DBA. As workloads increase, performing these tasks manually introduces more opportunity for human error and time starts to become your bottleneck. Your only option if you want to stay above water is to become more efficient at what you do. Enter, automation.
Using PowerShell with the SQLPS module is a very potent combination for DBA’s looking to automate their database environment. This presentation will give you an overview of the PowerShell language itself, and then it will dive a bit deeper into the SQLPS module for SQL Server. Be it large or small, there are plenty of opportunities for leveraging PowerShell and SQLPS to help you automate your environment.
The sooner you start to learn the better, because PowerShell is here to stay.
SessionID: 37892
A Primer on the Basics of NoSQL Databases
Event Date: 25-07-2015 - Session time: 15:30:00 - Track: DBA - Database Development
Speaker: Jason Cortes
Title: A Primer on the Basics of NoSQL Databases
Abstract:
Think CouchDB is for DBA's who work from home? Or never heard about the wonderful job MongoDB does at deleting data?! Well now is your chance to get your feet wet and dive into MEAN development. In this presentation you'll get a tour of the MEAN Stack, a crash course in some of the NoSQL flavors- CouchDB, MongoDB, Cassandra, and Redis... and also see what Microsoft is doing with this new trend. Then we'll wrap up with a demonstration of working with Hadoop on Amazon's Web Services Cloud.
SessionID: 37902
Advanced Troubleshooting Your SQL Connections
Event Date: 25-07-2015 - Session time: 15:30:00 - Track: DBA - Administration
Speaker: Mike Hays
Title: Advanced Troubleshooting Your SQL Connections
Abstract:
Often in supporting SQL Server, we are asked to troubleshoot why a user or application cannot connect. It becomes more challenging when we find nothing is wrong with the way SQL Server is running. In this session we review common mistakes to the configuration of network settings. We’ll also show how to troubleshoot the SQL Server Browser. We’ll cover how to test for firewall issues. Finally we’ll discuss why SQL Server is blamed for dropping its connections.
SessionID: 37962
How you can build an interactive visual explore screens in Excel
Event Date: 25-07-2015 - Session time: 15:30:00 - Track: Self-Service BI
Speaker: Joe Rourke
Title: How you can build an interactive visual explore screens in Excel
Abstract:
SessionID: 38832
Recovery and Backup for Beginners
Event Date: 25-07-2015 - Session time: 15:30:00 - Track: IT/Server Administration
Speaker: Mike Hillwig
Title: Recovery and Backup for Beginners
Abstract:
We all know that backups are important. But are we building a backup strategy that can recover in a manner that meets business needs? All too often, we plan a backup strategy without a recovery strategy.
In this session, veteran DBA Mike Hillwig will start with understanding the recovery strategy and work toward implementing the right backups to meet that recovery strategy. He'll also set the record straight around some very common myths around database recovery and backups.