Applied Data Science for BI Professionals and DBAs
Data plays an ever-increasing role in today’s society and provides a wealth of possibilities for you and the organization you work for. As a BI professional and/or DBA you are probably used to working with data every day, but what if business requirements can no longer be met through traditional solutions? In those cases, more-and-more organizations look to data science to provide a solution.
But how and where do you start with things like exploratory data analysis or machine learning?
Thankfully as a BI professional or DBA you have a head start when it comes to working with data, and through this full day workshop you will learn the additional skills you need to take your first steps in the area of data science!
What you will learn during this workshop:
• Understand the basic concepts of data science processes and why it’s different than traditional approaches;
• Get more familiar with statistical terms and techniques and learn why they are important;
• Making a start with programming in the R language;
• How to use R and Azure Machine Learning to build your first machine learning models;
• Operationalizing your models through various methods like
in-database R using SQL Server or through the Azure Machine Learning webservices.GrillrostThu 08:30 - 17:00
Indexanalyse und Performancesteigerung
Nicht immer ist die Konfiguration des Microsoft SQL Server die Ursache für eine schlecht performende Applikation. Sehr häufig sind es Fehler im Design der Datenbanken, Indexe oder eine Mischung aus allen Komponenten.
In diesem Workshop lernen Sie die unterschiedlichen Indextypen kennen, die in einem OLTP-System verwendet werden können. Neben den Grundlagen der Indexierung werden DMV vorgestellt, die eine Analyse von Indexen ermöglichen.
Ausgestattet mit den Grundlagen der Indexierung und den eingesetzten Werkzeugen wird in vielen detaillierten Beispielen gezeigt, wie Indexengpässe analysiert, bewertet und gelöst werden.AnzünderThu 08:30 - 17:00
Designing Azure Infrastructure for Data Platform Projects
In this daylong session, we’ll review all the various infrastructure components that make up the Microsoft Azure platform. When it comes to moving SQL Server systems into the Azure platform having a solid understanding of the Azure infrastructure will make migrations successful and making support solutions much easier.
Designing your Azure infrastructure properly from the beginning is extremely important. An improperly designed and configured infrastructure will provide performance problems, manageability problems, and can be difficult to resolve without downtime.
As Azure scales around the world many more companies, no matter where they are located, will be begin moving services from on-premises data centers into the Azure Cloud, and a solid foundation is key to successful migrations.SpießThu 08:30 - 17:00
Analysis Services DNA
Have you ever wanted an overview of your cubes and their dimensions, attributes and measures?
This session will show you how you can use Analysis Services DMVs to extract information about your cubes and collect it for later use. We will then see how you can report on top of it thus documenting your SSAS cubes. We will then hear about different scenarios where this data might be useful.
By attending this session, you will learn how to extract, transform and load the data into a database using SSIS. You will learn how you can create reports that show you the relevant information for different scenarios and how my company is making use of these information for different purposes. Some of the use cases for this data can be aligning dimensions across cubes or creating a bus matrix for documentation purposes.
At the end of the session the attendees leave with all the code needed to start on their own as well as the knowledge on how to extend and further the solution for their own company or clients.HandschuhFri 09:00 - 10:00
Introduction to Machine Learning
Learn how to build an Azure Machine Learning model, how to use, integrate and consume the model within other applications, and learn the basic principles and statistics concepts available in the different ML algorithms. If you want help choosing between a 'neural net' or a 'two class boosted decision tree', this session will reveal all!SpießFri 09:00 - 10:00
Mission SQL Migration - Aus Blech wird VM
Mehr und mehr physikalischen SQL Server-Installationen droht das aus. Aber bei weitem nicht alle ziehen in die Cloud.
Anhand eines realen Projekts sehen wir uns an, wie mehrere physikalische Server in virtuelle Maschinen umgezogen worden sind inkl. der dafür wichtigen und häufig vernachlässigten Konfiguration der VMs selbst.AnzünderFri 09:00 - 10:00
Hinter den Kulissen von Statistiken
Sie kennen die Situation, dass eine Abfrage gestern noch schnell und zufriedenstellend gearbeitet hat und heute will sie nicht mehr so richtig? Was machen Sie in einer solchen Situation:
• Ich starte den Server neu (hat ja sonst auch immer geklappt)
• Ich lösche den Prozedur-Cache (hat mir mein DBA erzählt)
• Ich hole mir erst einmal einen Kaffee und überlege, was ich in dieser Session gelernt habe
Microsoft SQL Server benötigt Statistiken für ideale Ausführungspläne. Wenn Statistiken nicht aktuell sind, kann Microsoft SQL Server unter Umständen Ausführungspläne erstellen, die eine Abfrage um ein Vielfaches langsamer laufen lassen. Neben dem grundlegenden Verständnis für Statistiken werden in dieser Session Besonderheiten gezeigt, die nur einem kleinen Kreis von Experten bekannt sind.ZangeFri 09:00 - 10:00
Laying the Foundation for GDPR
GDPR is coming, no matter where you are if you are handling data on European data subjects. Laying a solid foundation of data security practices is vital to avoid the potential fines and damage to reputation that being non-compliant can bring.
Practicing good data hygiene is vital to meeting compliance requirements, whether it is GDPR, PCI-DSS, HIPAA or other standards. The fundamentals around data identification, classification, and management are universal. Together we will look at some of the key areas that you can address to speed up your readiness for meeting GDPR requirements. Including what data is covered, principals for gaining consent, data access requests as well as other key recommended practices.
By the end of this session you will be able to start the groundwork on getting your organization in shape for its journey to compliance.If you want to avoid the big fines, up to EUR 20 million or 4% of global turnover whichever is higher, it is important to act early.GrillrostFri 09:00 - 10:00
Let’s build SSIS packages with Biml – live!
You have heard about Biml? Maybe even seen a session about it before but still have doubts, how easy you can make something useful out of it? In this session, we’ll use Biml to build and populate a staging area including the corresponding SSIS packages. But there won’t be any pre-compiled demos! Everything is happening live! Starting with a blank staging database, we’re going to end up with a complete solution over the course of this session, to prove that it is not just easy to adapt existing Biml code but also to start from scratch and still be successful very quick!
Let’s see, how that goes…ZangeFri 10:15 - 11:15
Moving advanced analytics to your SQL Server databases
Traditionally advanced analytical solutions, like machine learning, require you to bring your relational data to the machine learning model. Your model would then perform a prediction and return the results. While the process described above is reliable, it involves moving your data between the database where it is stored and the location where your model resides. This also means an increase in the complexity of your analytical solutions. For instance, how do you trigger the scoring of new data as soon as it enters the database? Or, how can you design this process for real-time scoring?
With the release of SQL Server 2016 Microsoft integrated a solution to the questions above, in-database analytics, allowing you to bring the analytics to your data instead of the other way around. Through in-database analytics we can design, train and score models directly from SQL Server without moving data out and back into the database. This creates a huge advantage, especially when working with real-time predictions, but how do you implement in-database analytics in your environment?
In this session we are going to explore the various methods available inside SQL Server 2016 & 2017 to perform in-database analytics. From building and storing our models directly inside SQL Server, to performing real-time scoring on data as soon as the data is stored inside a table.
After this session you will be able to understand the advantages and disadvantages of the various in-database analytics methods and you will be ready to start building your first in-database models!HandschuhFri 10:15 - 11:15
dbachecks - a new module from the dbatools team
dbachecks is a framework created by and for SQL Server pros who need to validate their environments. It is a free, open source module that allows data pros to crowdsource our checklists using PowerShell's open source testing framework, Pester.
Basically, we all share similar checklists and mostly just the server names and RPO/RTO/etc change.
We offer over 50 built-in checks.
Imagine being able to easily ensure that:
- Backups are being performed within your policy
- Identity columns are not about to max out
- Servers have access to backup paths
- Database integrity checks are being performed and corruption does not exist
- Disk space is not about to run out
- All enabled jobs have succeeded
- The build you are running is still supported
- Dedicated Administrator Connection is setup
- Your instances haven't generated any SQL Memory Dumps
- SQL Servers are compliant with DISA, CIS, and more
Do you already have some Pester checks in place? No problem, we can run your tests too.
And to top it off, we make it easy to email yourself or view the results of your tests in a gorgeous Power BI Dashboard.
Join us to see how easy (and beautiful) it can be validate all of your SQL Server environments!GrillrostFri 10:15 - 11:15
Recursive CTEs - cursing again and again?
Did you ever have to solve hierarchical puzzles in T-SQL, e.g. breaking down organisational charts?
My personal mission was a BOM explosion. No, not BOMB explosions, only mostly harmless bills of material to calculate production costs - a simple parent-child hierarchy, yet with unknown number of levels. This could be not easily solved with an iterative approach, but I heard about this thing called recursive CTEs and wanted to give it a try.
Yes, it involved cursing, because recursive CTEs confronted me with challenges regarding debugging the dreaded circular references and also performance issues.
Let's have a look at what we can do to end the cursing and start enjoying the elegant beauty of recursion.AnzünderFri 10:15 - 11:15
An End-to-End Approach to App and DB Security
Understanding how to reduce the attack surface area of applications and SQL Server environments is imperative in today's world of constant system attacks from inside and outside threats. Including securely accessing a database, properly encrypting data, using SSL/TLS and certificates throughout the system, guarding against common front-end attacks like SQL Injection (SQLi) and Cross Site Scripting (XSS), etc. This session will include both T-SQL and .Net code to give you an overview of how everything works together.SpießFri 10:15 - 11:15
Soft Skills for Success
We developers spend a lot of time developing our computer / programming / data skills. These skills are only part of what developers need to be successful. In this session I would like to talk about some of the soft skills that I have used when working with colleagues, customers and even my family.AnzünderFri 11:30 - 12:30
Einstieg in Azure SQL Databases und Powershell Automation
Einstieg in das Thema SQL Datenbanken in der Microsoft Cloud, von A wie Allgemeines, nach B wie Backup, dann über P wie Powershell nach R wie Restore.
Ich gebe einen Überblick, welche Möglichkeiten es gibt eine Azure SQL Database zur Verfügung zu stellen, wie höhere Anforderungen realisiert werden können, ebenso gehe ich auf das Thema Kostenersparnis ein.
Wir werden uns verschiedene Deployments anschauen, auf das Thema Backup und auf die unterschiedlichen betrieblichen Leistungsanforderungen eingehen.ZangeFri 11:30 - 12:30
"Self-Healing" Query Execution in SQL 2017
In this session we’ll look at two of the big performance improvements introduced with SQL 2017: Adaptive Query Processing and Automatic Tuning. We will discuss the mechanics of each feature, highlight any implementation considerations, and, of course, have a look at the features in action. Scripts will be made available for your own experimentation.SpießFri 11:30 - 12:30
Relationship Goals: The SQL Server Graph Database
Relational databases have their strengths. Ironically data relationships are not one of them. Graph databases excel in this department using nodes and edges. They are optimized to find and view relationships using graph theory.
One of the best new features of SQL Server 2017 is the Graph Database! It brings us the best of both worlds in one easy platform! Come learn about the history of graph databases, how they work and why you should be using it!GrillrostFri 11:30 - 12:30
SQL Server 2016 Database Administration for the non-DBA
Are you a developer or a systems admin and you've just been handed a SQL Server database and you've got no idea what to do with it? I've got some of the answers here in this session for you. During this session, we will cover a variety of topics including backup and restore, recovery models, database maintenance, compression, data corruption, database compatibility levels and indexing.
While this session won't teach you everything you need to know, it will give you some insights into the SQL Server database engine and give you the ability to better know what to look for.HandschuhFri 11:30 - 12:30
50 ways to show your data
„Ein Bild sagt mehr als tausend Worte“ - wie wahr, auch und gerade bei der Datenanalyse. Visualisierung ist der schnelle, einfache Weg, das Gesamtbild in Deinen Daten zu erkennen, und gerade das R-Ökosystem hat in dieser Hinsicht einiges zu bieten.
Es mögen nicht genau 50 sein, aber in dieser Session zeige ich viele ansprechende Visualisierungen, die das ggplot2-Package und seine Freunde ermöglichen. Wir starten jenseits der üblichen Balken-, Linien- und Streudiagramme.
Stattdessen kommen Diagramme auf den Bildschirm, von denen Du schon immer wissen wolltest „Wie macht man das?“. Wir werden Wasserfalldiagramme, Violinen, Joyplots, Randhistogramme, Karten und mehr sehen… und Ihr bekommt den Code, um alles nachzubauen.ZangeFri 13:15 - 14:15
Cosmos DB - NoSQL schlägt zurück (Eine Überblick über die dunkle Seite Ihrer Daten)
Es war einmal vor langer Zeit in einer weit, weit entfernten Datenbank...
SQL war für lange Zeit die einzige Option Applikationsdaten zu speichern. Es gab immer wieder rebellische Aktivitäten, das SQL Imperium zu stürzen, was eine neue Hoffnung verbreitete. Aber alle anderen Möglichkeiten Daten zu speichern, entpuppten sich nur als eine dunkle Bedrohung.
Nun, nachdem Erwachen der Cosmos DB startet die Rache von NoSQL.
Während der Session schauen wir uns Azure Cosmos DB im Detail an, was Sie mit Cosmos DB erreichen können und wie Cosmos DB in einer galaktischen Umgebung aus Daten und Applikationen genutzt werden kann.
Besuchen Sie meinen Vortrag und finden Sie richtige Lösung für Ihre Applikation.
Mögen die Daten mit Ihnen sein.HandschuhFri 13:15 - 14:15
DevOps for the DBA
Far too many people responsible for production data management systems are reluctant to embrace DevOps. The concepts behind DevOps can appear to be contrary to many of the established best practices for securing, maintaining and operating a reliable database. However, there is nothing inherent to a well-designed DevOps process that would preclude ensuring that the information stored within your data management system is completely protected. This session will examine the various methods and approaches available to the data professional to both embrace a DevOps approach to building, deploying, maintaining and managing their databases and protect those databases just as well as they ever have been. We will explore practices and plans that can be pursued using a variety of tooling and processes to provide DevOps methodologies to the systems under your control. You can embrace DevOps and protect your data.SpießFri 13:15 - 14:15
The Force Awakens - Azure SQL Server for the on-prem DBA
Have you ever been asked what the cloud means for your database environment or wondered if Azure is a viable platform for a new system? If so, look no further!
This session begins with an overview of Azure and where SQL Server fits in among the other services of the cloud. It follows with key differences between SQL Server on-prem, SQL Server on a VM, Azure SQL Server and Azure SQL Server Managed Instance, as well as discuss some of the pros, cons, challenges and benefits of putting a database in the cloud in the first place.
If you are eager to explore the database offerings in Azure, gain a basic understanding of which toys to use when and develop a plan for harnessing the power of the cloud, then this session will provide the tools you need to step into a new world.GrillrostFri 13:15 - 14:15
Introduction to Containers
Containers have been around for a while now but are becoming increasingly popular and with Microsoft’s release of Windows Server 2016, Docker is now their effective container standard. In this demo heavy session, we will run through what containers are, how to get started with Docker and “spinning up” SQL Server environments with minimal fuss and how you can adapt these or indeed your own VM images to create your own bespoke SQL Server development and testing environments.AnzünderFri 13:15 - 14:15
How to Use Parameters Like a Pro and Boost Performance
Parameters are a fundamental part of T-SQL programming, whether they are used in stored procedures, in dynamic statements or in ad-hoc queries. Although widely used, most people aren’t aware of the crucial influence they have on query performance. In fact, wrong use of parameters is one of the common reasons for poor application performance.
Does your query sometimes run fast and sometimes slow – even when nothing’s changed? Did it happen to you that a stored procedure, which had always been running for less than a second, suddenly started to run for more than 5 seconds consistently – even when nothing had changed?
In this session we will learn about plan caching and how the query optimizer handles parameters. We will talk about the pros and cons of parameter sniffing (don’t worry if you don’t know what that means) as well as about simple vs. forced parameterization. But most important – we will learn how to identify performance problems caused by poor parameter handling, and we will also learn many techniques for solving these problems and boosting your application performance.GrillrostFri 14:30 - 15:30
The PowerShell Standards Agency – Write better code for Everbody
There’s the quick way or there’s the right way. In this session we will look at good practices and standards to follow when writing PowerShell to make it easier for you and others to trust and reuse your code
By the end of this session you’ll have a guide to being a better PowerShell citizen, following best practices and sharing your code with others.
Become a member of the PowerShell Standards Agency* (Not a real thing) and write better code for EverybodyAnzünderFri 14:30 - 15:30
Responding to Extended Events in near real-time
Extended Events provide deep insight into SQL Server's behavior and allow us to gather information not available by other means. However, compared to other technologies such as SQL Trace and Event Notifications, a way to react to the events as soon as they happen seems to be lacking. In this session we will see how the Extended Events streaming API can be used to process events in a near real-time fashion. We will demonstrate how this technology enables new possibilities to solve real world problems, such as capturing and notifying deadlocks or blocking sessions.SpießFri 14:30 - 15:30
Champions League Final Battle - Tableau vs. Power BI
If you look into the Gardner Quadrant you will see two major players in Self-Service BI: Tableau and Power BI. Of course there is a lot in the press, but hey, here are two guys one Microsoft MVP and a Tableau Expert and these to will hold a final of these massive competitors battling on a real world exercise. If you are interested to see the differences in playing skills, tackling, pressing and how the goal keepers handle penalties. have a look. After that session you are prepared and know whether a Tableau or Power BI come in and says: I can do this in my tool better... and we can tell you: It will be fun as well to see the battle.HandschuhFri 14:30 - 15:30
ETL with Powershell
Lately I've seen a lot of ETL processes built in Python, even on the windows / SQL Server platform where SSIS would be the most powerful and mature ETL environment.
But there are many cases where a full blown ETL stack (SSIS with BIML for instance) would be over the top or just not someones forte.
But what about Powershell? Would it be able to do the job as easy as writing some Pyton, and as fast and robust as SSIS?
Let's find out!ZangeFri 14:30 - 15:30
Migrating your Database to Azure SQL DB as you never seen it
Azure SQL DB Managed Instances brings a lot of benefits for migrating databases to the cloud and taking advantage of a scalable and reliable Database platform. In this session I am going to walk you through the various aspects of Managed Instances and how to migrate it, but also I am going to show you how it was done in the past. Afterwards you can compare and find your way to Azure SQL DatabaseGrillrostFri 15:45 - 16:45
PBIRS - Power BI Reports Server On Prem - A real life story
Erfahrungen über die Integrations eines Power BI Berichtsservers in die Unternehmenssystemlandschaft
• Installation und Konfiguration
• Securtiy, Datenschutz
• Tools, Autoren, VersionsverwaltungAnzünderFri 15:45 - 16:45
SQL Containers in the Cloud! An intro to running Docker in Azure
As containers are becoming more and more prevalent, this session provides an introduction to the different options of running containers in Azure.
I'll cover the following different options for running Docker in Azure:
Running Docker on standalone VMs
Docker for Azure (Swarm)
Azure Container Instances
Azure Container Services
This session is aimed at SQL Server DBAs and Developers who have some experience with Docker (Docker for Windows) and want to know the different options that are available in Azure.
Each topic will be backed up with demos (live and recorded, depending on the time needed) which will show how simple it is to get up and running with these technologies.SpießFri 15:45 - 16:45
Biml Tips and Tricks: Not just for SSIS packages!
"Wait, what? Biml is not just for generating SSIS packages?"
Absolutely not! Come and see how you can use Biml (Business Intelligence Markup Language) to save time and speed up other Data Warehouse development tasks. You can generate complex T-SQL statements with Biml instead of using dynamic SQL, create test data and populate static dimensions, and even compare tables and views across multiple servers and databases.
Don't Repeat Yourself, start automating those boring, manual tasks today!HandschuhFri 15:45 - 16:45
Wird Tabular Mode langsam erwachsen?
Wer sich mit Analysis Services Tabular Mode bereits in den Versionen 2012 und 2014 befasst hat, musste sich mit einigen Kinderkrankheiten und fehlender Funktionalität herumschlagen. Speziell für erklärte Anhänger der multidimensionalen Welt war die neue Option nicht viel mehr als ein unausgereiftes Spielzeug.
Doch mit SQL Server 2016 und 2017 hat sich dieser Eindruck massiv geändert.
Was sind die wichtigsten Veränderungen in den letzten Versionen? In welchen Bereichen sind noch Wünsche offen? Und welche Faktoren spielen heute noch eine Rolle bei der Wahl zwischen Tabular Mode und Multidimensional?ZangeFri 15:45 - 16:45
SQL Server Problem Bingo!
What's more fun then a Bingo? SQL Server and a Bingo, during this session we will be handling a variety of SQL Server problems you might face in your data platform environment. These problems are put into a bingo machine & will be handled as they get chosen by destiny! This is an action packed demofull session containing all of the most common SQL server problems as seen in the field. Come to this session and you might win a fabulous price!GrillrostFri 17:00 - 18:00
Let the Cognitive Services to help you to find your favourite cocktail
Who does not know the problem, you sit in the bar and just don't know which cocktail to order?
The Cogntive services offer here with face, emotion and recommendation three APIs that can help you. How do you best combine these services to get a suggestion for your cocktail?HandschuhFri 17:00 - 18:00
Upgrading to SQL Server 2017
Upgrading to the latest version of SQL Server is often seen as a comprehensive and difficult project. Management often fails to see the benefit for migrating to the latest version and your end users aren't interested in all of the extra testing. As a DBA you need to come up with a plan that earns both management and end-user support.
In this session, we will cover all the information you need to collect and consider before, during, and after upgrading to SQL Server 2016. We will discuss licensing, upgrade approaches, and build out your own upgrade checklist.
If you feel stuck on an older version of SQL Server, attend this session to understand the features and benefits of SQL Server 2016 that will justify your upgrade project. Come and learn about the tools and methods that will make your upgrade project be successful.
You will learn:
Upgrade tasks and options
Post-upgrade tasksSpießFri 17:00 - 18:00
Deploy your databases to Azure using VSTS and DACPAC
In this session we will create a complete CI/CD setup from scratch!
From source control using SSDT to deployments in Azure using VSTS and DACPAC. We will cover it all!
Join this session if you want to SEE how you do this and where the common pitfalls are.AnzünderFri 17:00 - 18:00
Improve your Database Performance in Seven Simple Steps
You wrote the code, you tested it, it works, and it’s fast. So you deploy. And then those pesky users insist on entering not hundreds, not thousands, but millions of rows – and suddenly, you have performance problems.
What to do? Blaming SQL Server is a good start, but won’t solve the problems. You can of course hire a database consultant to make all your performance problems (and all your money) disappear - but why not first take a look yourself?
This session will show you seven simple things, that might alleviate most of your database related performance problems. Use these tricks at your workplace, and you can be the hero of the department!ZangeFri 17:00 - 18:00