Pre-Conference Workshop Full Day Trainings (18th of Feb 2019)

Deep Dive into Power Query with Custom Data Connectors

Pre-Conference Full Day Workshop

Level: 300 (Advanced)

Want to learn more about the M language? Come join Matt Masson from the Power Query team for a full day training session covering advanced M and the creation of Custom Data Connectors using the Power Query SDK (https://aka.ms/DataConnectors). This talk will start with an overview of the M language and the Power Query experience, and then guide you through the creation of a new data connector following a hands-on-lab approach with the TripPin connector sample. Students should bring their own laptops with Visual Studio 2015 or 2017 (Community/Pro/Ent – not VS Code), and the latest version of Power BI Desktop, so they can follow along with the exercises.

Matt Masson

Matt Masson

Matt Masson is a Principal Program Manager at Microsoft who focuses on data integration challenges. Matt has worked on multiple products in this space, including Power BI, Power Query, Azure Data Factory, SQL Server Integration Services (SSIS), Data Quality Services (DQS), and Master Data Services (MDS). Most recently, he has been focused on Power BI’s data connectivity experience and the Power Query SDK (https://aka.ms/DataConnectors). He has authored two books – SSIS Design Patterns (Apress) and SQL Server 2012 Integration Services (MS Press), and is a frequent presenter at Microsoft conferences. You can find his blog at https://www.mattmasson.com/.

Practical R for everyone

Pre-Conference Full Day Workshop

Level: 100 (Basic)

THIS SESSION IS PRACTICAL SO BRING A LAPTOP / TABLET!
This day takes you through some of the fundamentals of R and gets you hands on with some useful R for ETL, producing reports, and doing data science. Starting with some simple data manipulation, we’ll quickly progress to tools that can make an immediate impact in your day job.
For ETL, we’ll look at ways to import and export data, transform it, and apply data quality checks along the way.
For producing reports we’ll see how we can build web reports, PDFs, and interactive dashboards.
For data science, we’ll look at how R supports you along the process.
By the end of the day, you’ll know some R (including some advanced stuff!) that you can add to your toolbelt for free.
Modules/Topics Include:
1.            Data Manipulation Fundamentals
2.            R for ETL
3.            R for Reporting
4.            R for Data Science
Key Takeaways for The Audience
1.            Basic R skills in analysing tabular data
2.            A good understanding of what is involved in using R for ETL
3.            Capable of building basic reports in R
4.            Able to support data scientists or start coding data science
Demos:
1.            Tidyverse data pipelines
2.            Data Manipulation fundamentals
3.            Importing and exporting data
4.            Data transformation and quality checking
5.            Using rmarkdown
6.            Building flexdashboards
7.            Data preparation for data science
8.            Sampling
9.            Constructing models
10.          Evaluating models
11.          Publishing models in SQL Server
Attendee Pre-requisite (if any)
1.            A device which can access a browser and typing is comfortable on
Steph Locke

Steph Locke

Steph is the founder of a consultancy in the UK. Her talks, blog posts, conferences, and business all have one thing in common – they help people get started with data science. Steph holds the Microsoft MVP award for her community contributions. In her spare time, Steph plays board games with her husband and takes copious pictures of her doggos.

Speeding Up SQL Server Queries

Pre-Conference Full Day Workshop

Level: 300 (Advanced)

Speeding Up SQL Server Queries Pre-Con is designed for application developers and programmers who write T-SQL queries and develop applications that use SQL Server at the back-end to application databases. The full-day class is primarily meant for database developers who have relevant experience with SQL Server and only needs practical examples and techniques of speeding up queries that can be applied in applications & production right away. In this full-day training, participants will learn about the internals of query execution, internals of data storage & retrieval and see crisp, real-world examples of tuning query performance.

Module 1: SQL Server Wait Types & Statistics

Query Lifecycle

Connections -> Sessions -> Requests -> Tasks -> Workers -> Threads

Common Wait Types in SQL Server, identification & diagnosis

Module 2: The Holy Trinity – CPU, Memory, IO

Identifying, troubleshooting & tuning workload patterns that cause excessive CPU

Identifying, troubleshooting & tuning workload patterns that cause excessive Memory Usage

Identifying, troubleshooting & tuning workload patterns that cause excessive IO

Module 3: Query Execution & Query Plan Analysis

Statistics & Cardinality Estimation

Reading & Analysing Execution Plans

Important Iterators

Module 4: Query Tuning

Rewriting Transact-SQL

Optimizing Stored Procedures

Solving Parameter Sniffing Issues

Parametrization Best Practices

Tips with Procedural Code

Module 5: Index Tuning

Demystifying Common Myths with Indexes

Indexing Strategies

Index Fragmentation

Real-World Index Tuning Examples

Module 6: Dealing with Deadlocks

SQL Server Concurrency, Isolation Levels, Locking Granularity & Hierarchy

Common Deadlock Types

Deadlock Best Practices

Practical Deadlock Troubleshooting & Diagnosis

Pre-Con Takeaways:

Practical, Real-World Examples

All Demo Scripts

Loads of learning that can be applied into production right-away

Additional Notes:

Most training programs, theoretical or practical, lac real-world examples. In this pre-con, an attendee will learn real world query tuning skills with SQL Server. The emphasis is on real-world & practical skills.

Pre-Con will be fast paced. 100% demo-oriented. Attendees are requested to pay full attention to the instructor and the demos.

Laptops & mobile phones off. All demo codes & labs will be provided to practice later.

Amit Bansal

Amit Bansal

Amit R S Bansal is a SQL Server Specialist at SQLMaestros (brand of eDominer Systems). He leads the SQL and BI practice with a much focused team providing consulting, training and content development services to more than 160+ SQL customers globally. He frequently speaks at international conferences including MS Ignite, SQLBits, SSGAS/DPS, PASS Summit & SQL Saturday events. Amit is also honored with Microsoft Regional Director status, MVP award and Microsoft Certified Master of SQL Server credential. Amit has been working with SQL Server since 1999 and has been part of countless mission critical SQL deployments. Performance Tuning in SQL Server is his core area of expertise. To learn more about Amit, visit http://sqlmaestros.com/amit-bansal/

Master your data for Power BI & Excel

Pre-Conference Full Day Workshop

Level: 100 (Basic)

Are you tired of piecing together data provided in text files, Excel files, web pages and even the body of emails to build the business intelligence solutions you need? Until now this has been a painful process with a great deal of cut and paste effort, and updates have been tedious and error prone. That stops today!

In this course, you’ll learn how Power Query can clean up, reshape and combine your data with ease – no matter where it comes from. Converting ASCII files into tables, combining multiple text files in one shot and even un-pivoting data is not only simple, but an investment in the future refresh-able with a single click when next needed.

If you need to learn one skill today, how to work with Power Query is it.  While the course will show you how to “Get & Transform” data in Power BI, these skills are also completely portable to Excel today.

Please bring a laptop running the latest version of Power BI Desktop so that you can get hands on with cleaning up dirty data..

Ken Puls

Ken Puls

"Ken Puls, FCPA, FCMA, is a Chartered Professional Accountant, blogger, author and trainer with over 20 years of business and financial modelling experience. His passion lies in exploring tools to turn data into information, and teaching others how to benefit from them. Ken has held the Microsoft MVP distinction since 2006 in both the Excel and Data Platform categories. He is also one of about 4% of CPA’s to be recognized as a Fellow of his accounting body. Learn more about Ken at https://ca.linkedin.com/in/kenpuls."

Practical SQL Server Performance Analysis

Pre-Conference Full Day Workshop

Level: 300 (Advanced)

This full-day Precon will teach you techniques for general troubleshooting and performance analysis from start-up to advanced techniques.
A Highlight is the possibility to connect live to the Demo Server via your local SSMS to analyze prepared scenarios on your own. This is probably the first ever PreCon with a continuously running workload for analysis by all attendees!

We analyze a choice from the following scenarios:

  • Investigating Memory Problems
  • Identifying Plan Regression
  • Analysis of Indexes & Statistics
  • Detecting I/O Latency problems
  • Investigating Tempdb Performance
  • Locking & Blocking Analysis, Concurrency problems

Techniques & Tools shown:

  • SQL Server Architecture & Internals
  • Monitoring Methodology, Tools & Techniques:
  • Dynamic Management Views (DMVs)
  • Wait Statistics
  • Extended Events for Workload & Query-Analysis
  • Query Store
  • Overview on Third-Party tools (subject to discussion with the organizers)
Andreas Volter

Andreas Volter

"Andreas Wolter has been specialized in SQL Server for 18 years. He is not only a Microsoft Certified Master (MCM) but also one of 2 Microsoft Certified Solutions Masters Data Platform (MCSM) in the Asia Pacific Region and has been awarded with the MVP several times. Together with his company Sarpedon Quality Lab, he is focusing on development and optimization of high performing OLTP Systems and big Data Warehouse Architectures. "

Power BI for Developers and Programmers

Pre-Conference Full Day Workshop

Level: 300 (Advanced)

This course targets programmers who want to leverage Power BI in their applications, or want to extend Power BI features with custom visuals. It’s an advanced course where you will learn what Power BI platform has to offer developers including:

  • Embedding Power BI content into web applications
  • Power BI REST API and using it into custom .NET applications
  • Streaming data concepts; pushing data into real-time dashboards.
  • Introduction to Custom Visuals; creating new visuals

Prerequisites:
Familiarity with Power BI content creation (i.e., modeling, report building, publishing to service)
Familiarity with .Net, PowerShell and JavaScript basics are highly desirable
Power BI Pro account (60 day free trial is sufficient)

Sergei Gundorov

Sergei Gundorov

The presenter, Sergei Gundorov, currently works as a Program Manager on the Power BI team at Microsoft. He has Master’s Degree in Business Administration. Sergei published numerous articles over the course of the last 26 years through multiple channels on various subjects that range from accounting and economics to in depth technical articles on MSDN (Microsoft Developer Network). Sergei’s most recent contribution was to the Security Whitepaper for Power BI published in May of 2017. He presented his most valuable technological advancements to worldwide developer audiences at TechEd, TechReady, and Office/SharePoint Developer conferences. He participated and contributed to numerous webcasts that focused on various technologies that he is considered to be an expert on. He is an inventor and filed several patent applications. Sergei runs popular technical “XLGeek’s Space” blog where his work is praised very highly by the readers and his apps users.

AI and Cognitive Services in a Day

Pre-Conference Full Day Workshop

Level: 300 (Advanced)

In this one-day classroom training, I will focus on AI-oriented services such as Azure Bot Services, Azure Search, and Cognitive Services. The main aim of this pre-con is to teach different ways by which one can create smart applications by using some predefined tools to writing some codes in R or Python. A brief description about the Machine Learning concepts will be provided. Audience will learn about how to set up the cognitive services, they will also get familiar with the main services such as face recognition, text analytics and so forth. They will learn how to set up a Bot service and how to use text analytics or other services like Bing search on it. Moreover, they will learn how to set up Azure search to enhance the searching data in their application and reduce complexity. In addition, if they want to develop their own machine learning models using R or Python, they can develop the code inside .Net framework. Finally, if they already developed a model in Azure ML, they will learn how to use the related API inside the .Net framework to create a smart application.

Leila Etaati

Leila Etaati

Leila is a Artificial Intelligence Microsoft MVP, PhD, Trainer, and BI consultant. She is world well-known speaker in Machine Learning and Analytics topics, and spoke in world’s best international conferences in Data Platform topics, such as; Microsoft USA Ignite, Microsoft data Insight Summit, Microsoft NZ ignite, SQL PASS Summits, PASS Rally, SQL Nexus, SQL Saturdays and so on. She has more than 10 years’ experience in Data Mining and Analytics. She writes blog posts in RADACAD and also publishes YouTube videos in our channel. She also is an invited lecturer in universities such as University of Auckland, and Unitec, and some other universities. She worked in many industries including banking financial, power and utility, manufacturing, tourism, and so on.

Definitive Power BI Architecture

Pre-Conference Full Day Workshop

Level: 300 (Advanced)

This training is designed for data architect or administrator, who is designing the architecture of leveraging Power BI in a solution. Someone who wants to understand how all components of Power BI are sitting beside each other to build the whole solution. This training is designed for understanding the strategy of using Power BI rather than the development of it.

In this training, you will learn about architecting the strategy of a Power BI solution from end to end. This is not training about development (Modules 1 to 3 already covered that). You do not need to attend previous modules to attend this course. This course is designed separately from those. However, knowing some of the basics of Power BI is helpful.

You will learn about Power BI Service, and different types of connections in Power BI and choose the right type of connection for your solution. You will also learn all different ways of sharing a Power BI Solution and the pros and cons of each. The course continues with a detailed discussion about row-level security. You will learn how gateway configuration and set up will be in the whole package. You will also learn about integrating Power BI with other tools, and some architecture blueprints to follow when you are designing a Power BI solution.

At the end of this training, you will be able to design the architecture of Power BI for your requirement; you can choose the right way of sharing and design the gateway configuration as well as the communication of Power BI with other tools and services. You will be able to answer any questions that come to your mind about a solution architecture in Power BI world and make the right decision to choose the right strategy of Power BI usage in your organization.

The training includes but not limited to topics below:

 

1: Power BI Service

Power BI service or website plays an important role in designing a Power BI solution. Power BI website is the hosting environment for Power BI reports, dashboards and datasets. In this section, you will learn about these objects and their configuration. You will also learn about the different types of connections in Power BI, and the position of the gateway in the solution, configuring and installing it.

  • Power BI Components
  • Power BI Service Content; Dashboard, Report, and Dataset
  • Dashboard Features in the service
  • What is Gateway?
  • Two types of Gateway; On-premises, Personal
  • Configuration and Installation of Gateway
  • Import Data and Schedule Refresh
  • DirectQuery Connection
  • Live Connection
  • Composite Model

 

2: Sharing

There are several ways of sharing Power BI reports and dashboards. Each of the methods has pros and cons and should be used in specific scenarios. In this section, you will learn scenarios to use each of these methods for sharing, and the sharing of architecture, and a comparison between all these methods at the end.

  • Basic Dashboard or Report Sharing
  • Workspaces in Power BI; Dev Environment
  • Power BI Apps; User Environment
  • Publish to Web
  • Embed in SharePoint Online; Office 365 Integrated Sharing
  • Power BI Embedded; Embedding content into another application
  • Comparison of all Sharing methods

 

3: Security

Sharing is about giving users access to the entire content; security is about giving them access to part of it. There are different ways of implementing security (which is called row-level security in Power BI). Statics row-level security is a good option when roles are limited items. The next level is to define a dynamic row-level security using DAX functions. Dynamic row-level security comes as different patterns which will be discussed here through examples.

  • Row Level Security
  • Dynamic Row Level Security
  • Patterns for Dynamic Row Level Security
  • Manager Level Access
  • Organization Chart Access as Dynamic Row Level Security
  • Profiles and Users; Dynamic Row Level Security
  • Dynamic Row Level Security using SQL Server Analysis Service Live Connection

 

4: Administration and Licensing

As a Power BI administrator, you need to have a careful eye on some of the metrics, and control some of the settings across your organization Power BI tenant. In this section, you will learn about Power BI administrator configuration options and options which are critical to controlling. You will also learn about all licensing options for Power BI and will have a clear view of what would be the best licensing option for you.

  • Power BI Administrator Configuration
  • Usage Metrics
  • Customizing Usage Metrics
  • Power BI Licensing Guide
  • Power BI Premium
  • Power BI Embedded Licensing

 

5: Integration

Power BI can integrate with other tools and services such as Reporting Services and Excel. In this section, you will learn about all integration options for Power BI. This section will also give you a full understanding of Power BI Report Server; the on-premises usage of Power BI. You will learn Power BI REST API capabilities (not at a developer level, more in architecture level), and you will learn about real-time streaming options available with Power BI.

  • PowerPoint Integration with Power BI
  • Power BI and Excel; More than just an Integration
  • Power BI Report Server: Power BI on-premises
  • SQL Server Reporting Services Integration with Power BI
  • Real-time Streaming with Power BI, and Azure Stream Analytics
  • Power BI REST API

 

6: Architecture

The last part of the training focuses on architecture blueprints for Power BI. In addition to architecture best practices for sharing, self-service, enterprise-level architecture, you will learn about a tool that can help in Power BI solution designed by RADACAD; Power BI Helper.

  • Architecture for Enterprise Scale
  • Architecture for Sharing
  • Architecture for Self-Service
  • A tool that helps: Power BI Helper
Reza Rad

Reza Rad

Reza Rad is author, speaker, trainer and consultant. He is the world well-known name in the Microsoft BI field. Reza Rad is invited speaker in world’s best and biggest SQL Server and BI conferences such as PASS Summits, PASS Rallys, SQLBits, TechEds, Ignites, Microsoft Data Insight Summit, and so on. He is author of books on this topic, and he has more than 15 years’ experience in the Microsoft BI technologies. 10 years of his experience focused on training and consulting to the largest BI teams in the world. He is also Microsoft Certified Trainer for years. He is Microsoft SQL Server MVP (Most Valuable Professional) focused on BI and Data Analysis, He has been awarded MVP from Microsoft because of his dedication and expertise in Microsoft BI technologies. He is author of Power BI online book; from Rookie to Rock Star; http://radacad.com/online-book-power-bi-from-rookie-to-rockstar