Pre-Conference Workshop Full Day Trainings (12th of Feb 2018)

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/.

Azure Infrastructure

Pre-Conference Full Day Workshop

Level: 200 (Intermediate)

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 out around the work many more companies, no matter where they are located, will be begin moving services from data centers into the Azure and a solid foundation is key to successful migrations.

Denny Cherry

Denny Cherry

Denny Cherry is the owner and principal consultant for Denny Cherry & Associates Consulting and has over a decade of experience working with platforms such as Microsoft SQL Server, Hyper-V, vSphere and Enterprise Storage solutions. Denny’s areas of technical expertise include system architecture, performance tuning, security, replication and troubleshooting. Denny currently holds several of the Microsoft Certifications related to SQL Server for versions 2000 through 2014 including the Microsoft Certified Master as well as being a Microsoft MVP for several years.  Denny has written several books and dozens of technical articles on SQL Server management and how SQL Server integrates with various other technologies.

Design & Implement SQL Server HA/DR Hybrid Solutions with Microsoft Azure

Pre-Conference Full Day Workshop

Level: 300 (Advanced)

In this full-day workshop, we will be focusing on designing & implementing cloud data platform solutions with the Microsoft Data Platform by using SQL Server on-premises, hybrid and cloud data platform solutions. We will look at how to provide hybrid high availability and disaster recovery (HA/DR) solutions to our SQL Server databases. The following topics will be covered

– Design & implement a hybrid SQL Server HA/DR solution using the Microsoft Azure Infrastructure-as-a-Service (IaaS) offerings

– Design & implement a business continuity solutions using the Microsoft Azure SQL Database Platform-as-a-Service (PaaS) offerings

– How to avoid the networking pitfalls of a hybrid HA/DR solution

– Exploring the new SQL Server 2016 features that enable hybrid HA/DR solutions

At the end of the workshop, you will be confident enough to design & implement a HA/DR solution for your SQL Server databases that leverages Microsoft Azure without all the marketing fluff.

Edwin Sarmiento

Edwin Sarmiento

Edwin M Sarmiento is a Microsoft Data Platform MVP and Microsoft Certified Master from Ottawa, Canada specializing in high availability, disaster recovery and system infrastructures running on the Microsoft server technology stack.  He is very passionate about technology but has interests in music, professional and organizational development, leadership and management matters when not working with databases.   He lives up to his primary mission statement – “To help people and organizations grow and develop their full potential.

Demystifying the DAX Language

Pre-Conference Full Day Workshop

Level: 200 (Intermediate)

DAX is the data modelling language of Power Pivot, Power BI and SSAS Tabular Edition. It is reasonably easy to get started with DAX, particularly if you come from a SQL background, however sooner or later most people hit a wall where they get stuck and can’t move forward. Have you ever typed a DAX formula, pressed enter and the crossed your fingers hoping you will get the right answer? If this sounds like you, then chances are there are some deeper concepts that you don’t fully understand, and these gaps are stopping you from moving forward.

In this full day pre-con session, Matt Allington (Author of the book Learn to Write DAX) will clearly explain how the DAX language really works under the hood. At the end of the session you will fully understand filter propagation, initial filter context and evaluation contexts. The session will use real world examples to explain the concepts. This is not a beginner’s course – you should already know the basics of DAX before attending.

Matt Allington

Matt Allington

Matt Allington is a career data professional that has spent more than 30 years leveraging data to drive success. In 2014 Matt left the corporate world to start his own company (Excelerator BI) and now works full time to help others to use Microsoft BI products (Power BI, Power Pivot for Excel, Power Query for Excel). Matt is Microsoft MVP based in Sydney Australia, however has a global reach through his blog at http://xbi.com.au/blog

36 Scripts: Performance Tricks Jedi Master Wants You to Know

Pre-Conference Full Day Workshop

Level: 300 (Advanced)

“Train yourself to let go of everything you fear to lose.” – Yoda

This demo-oriented (story telling narrative) a day-long session will answer the questions which DBA and Developers often wonder about Performance Tuning and Query Optimization. There are some problems which have straight forward answers, but there are some problems which are often invisible or needs attention without even a problem statement. This day-long session will be divided in these three major components

1) Identifying and resolve common performance tuning patterns and anti-patters

2) Proactively resolving optimization bottlenecks and issues

3) Plan ahead for unexpected performance issue while upgrading to the latest version of SQL Server 2014 and 2016

Stay alert for pop quizzes.

Pinal	 Dave

Pinal Dave

Pinal Dave is a SQL Server Performance Tuning Expert and an independent consultant. He has authored 11 SQL Server database books, 21 Pluralsight courses and has written over 4000 articles on the database technology on his blog at a https://blog.sqlauthority.com. Along with 16+ years of hands on experience he holds a Masters of Science degree and a number of database certifications.

Agile Data warehouse design with BIML

Pre-Conference Full Day Workshop

Level: 200 (Intermediate)

-Have you ever needed to develop hundreds of similar SSIS packages?
-Have you ever been in a situation where you need to refactor some code in all this packages because of a design change to the underlying data?
-Do you want to adopt a more agile approach towards data warehouse design?
If you answer yes to any of these questions, then this full day of training is for you.
You will need to bring your own laptop for this full day of hands on training, where we will start by introducing BIML (Business Intelligence Markup Language), showing the syntax, the tools. We will then move on from BIML to BIMLScript and guide you to building a reusable, extensible metadata driven solutions (built on MDS, a simple database or even Excel) for generating the SSIS packages used for your ETL process.
Underway we’ll have a look at how to implement common data warehouses patterns in BIML such as early arriving facts, slowly changing dimensions and change data capture.

What you will leave this session with is the foundation for a BIML Script framework, that will allow you to tear-down and recreate your ETL process in minutes, instead of having to recode each package by hand.
Prerequisites:
A working knowledge of SSIS and ETL concepts
A working laptop with Visual Studio installed (even the Express edition)
Basic knowledge of C# programming

Regis Baccaro

Regis Baccaro

Régis is a Senior Consultant working with databases and BI since 1999. He specializes in mentoring, performance tuning and scalable architecture of datawarehouses and bi-solutions. His background and work experience mixes technology and business, providing him a unique edge to contribute to datawarehouse and database projects, and effectively interacting with technical and business decision makers. Régis is a frequent speakers at SQL Server conferences around the globe and is a board member of the Danish SQL Server User Group and the founder of the SQL Saturday Denmark community event. He is also Recipient of an MVP award for Data platform for four years in a row.

Data Lake Store / Analytics with Python, R and Cognitive services

Pre-Conference Full Day Workshop

Level: 200 (Intermediate)

Moving your data analysis to Azure, will make you scale in so many ways.

In this workshop we take a look and a deep dive into Azure Data Lake, a new feature on Azure that will enable almost anyone to work with BIG DATA.

It combines C# and SQL to a language that is way more intuitive than what we are used to. Azure Data Lake Store, the new repository for data of various origins – we can collect, store and share data from this lake as we see fit.

Azure Data Lake Analytics, a new way to scale and use your analytics on AZURE and BIG Data, it introduces U-SQL a new language combined of C# and T-SQL to make the task of analyzing BIG Data easier and more comprehendible.

Azure Data Lake Tools for Visual Studio, provide an integrated development environment that spans the Azure Data Lake, dramatically simplifying authoring, debugging and optimization for processing and analytics at any scale. After this session, you will have an understanding of the new feature, and will hopefully be inspired to use it either in a Proof Of Concept or in a Production scenario.
The newest addition to Data Lake Analytics is execution of R, Python and Cognitive Services, parts that this session will focus on

We will look at the following topics:

  • Data Lake Store
  • Data Lake Analytics
  • Python
  • R
  • Cognitive Services
  • Job Scheduling
  • HDinsight
  • Best Practices for storing your data in Data Lake
  • Where Data Lake Store and Analytics fit into a Modern Data Warehouse

You will leave this workshop with a solid understanding of Data Analysis on Azure using the newest tools for that. A library of scripts that you easily can extend to use in your daily work.

Prerequisites:
A working laptop with Visual Studio installed (even the Express edition)
Basic knowledge of C# programming
Basic knowledge of T-sql
Basic Knowledge of Data
And an Azure Account, either MSDN or FREE Trial

Kenneth Nielsen

Kenneth Nielsen

Kenneth M. Nielsen currently work at Microsoft as Data Insights Architect, focusing on data services on Azure. Previously worked as managing consultant and teamlead for the company Rehfeld Partners in Denmark. He has worked at various consulting firms and worked on many small/large/very large bi installations in Denmark over the last 12 years. He really likes to advice the customers to take the right decisions, but also maintains a high technical knowledge, so he can act at both architect and developer.

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.

Analytics and Machine Learning with Power BI and R

Pre-Conference Full Day Workshop

Level: 300 (Advanced)

You keep hearing about the machine learning and R recently. R become a language for data analysis and machine learning that makes the need for learning it more clearly. Moreover, Microsoft introduces R server as a comprehensive platform for using machine learning and R functionality inside the data analysis tools such as SQL Server 2016 and Power BI.

This course contains of four main modules: how to do machine learning inside Power Query, how to draw complex data visualization inside Power BI report using R scripts, how to create custom visual using R scripts and finally how to set up and use some of the available Custom visual in Power BI office store.

First Module “Advance Visualization with R” (1.5 hours)

Some R visual that we do not have in Power BI and we able to draw them with the help of writing R scripts. Chart like “column with Chart, “Facet chart with different sub chart”,” Slope Chart”, Polar charts, normal distribution, correlation analysis and so forth.

In this module, people get familiar with the below items;

  1. How to set up power BI to write R scripts inside R visual editor
  2. Get familiar with ggplot2 package and its related charts like geom_bar, geom_jitter, geom_point and so on
  3. How to works with aes() function to show different variables in a charts, facet_wrap(), ggplot(),geom_vline(), geom_text() and so on.
  4. How to debug the written r scripts inside Power BI with R studio
  5. How to draw a Facet chart to show 5 different variables in one chart
  6. How to draw a slope chart to show the changes of a variable between two different time, branch etc.

Second Module, Create custom Visual with R scripts (1 hour)

In the most of the reports, there is a need to draw a visual that may be not built in by Power BI standard visual or power BI office store. Also, sometimes, we want to just create a custom visual as a black box that people able to import them and use them without seeing the code behind them (because security reason, easy to use, avoided unwanted change)

There is a possibility to draw a custom R visual using R scripts. In this module, I will show how to create a custom visual by following some steps

  1. Get familiar with plotly package in R
  2. How to install power bi visualization tools inside the command prompt
  3. How to create a template folder via command Pbiviz in command prompt
  4. How to change the R scripts, pbiviz.json files to create your own visual
  5. How to publish the visualization, create it as a pbiviz files and imported it to Power BI

Third Module, Machine learning inside Power query using R scripts (3 hours)

In this module, I will talk about the main concepts of machine learning and, I will introduce some of the algorithms with the concept behind them, how to have them in power BI using R scripts inside the power query editor, how to enhance them and how to visual them.

  1. What is overfitting and underfitting in machine learning
  2. Algorithm for prediction of a group, KNN (k nearest neighbor)
  3. The main concepts and statistic behind KNN
  4. How to find the best K value (number of the nearest neighbor)
  5. How to evaluate a classification algorithm (example KNN) talking about the accuracy measure like “accuracy”, “Recall”, “Precision” and so on.
  6. Algorithm for clustering, K-mean clustering
  7. how k-mean cluster works (concepts behind it)
  8. how to find the best number of cluster in k-mean algorithm
  9. how to analysis a clustering problem using power bi visual
  10. What is market basket analysis, the main concepts of Associative Rules
  11. How to do market basket analysis inside the power BI and R transformation in Power query.
  12. How to use some R custom visual to show the market basket analysis
  13. Algorithm for predicting a value, deep learning using Neural Network (NN)
  14. Th main concepts of NN
  15. How to have it inside power BI, how to draw a network in report
  16. How to identify the number of hidden nodes
  17. Forecasting (Time series) the main concepts behind it, What Exponential Smoothing, its difference with Arima
  18. How to show them inside power BI and using R codes

Fourth module, Available R custom visual in office store for Power BI (0.5 hours)

In this module, I will talk about the available machine learning and advance analytics charts inside Power BI such as clustering, time series, and decision tree, how to use them and how to set up the parameters for them.

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.

Ultimate Architecture, Security, Sharing and Governance for Power BI Solution

Pre-Conference Full Day Workshop

Level: 300 (Advanced)

In this course, you will learn everything about Power BI Service, Architecture, Security, Sharing and Governance. You will learn the difference of dashboard and Report, and setting up a gateway, The difference of personal and on-premises gateway. You will learn all tips and tricks of configuration and installation and performance of gateway to connect Power BI on cloud report to on-premises data source and schedule it to refresh. You will also learn about DirectQuery and LiveQuery and difference between these two method. You will learn what is situations that you need to use DirectQuery or LiveQuery or even Import Data. You will learn about architecture for a solution with Power BI considering integration options of Power BI with other applications. You will also learn all tips about sharing and security with best practices. This course will be delivered in lecture style plus hands on lab experiments.

Agenda;
5.1: Gateways

  • What is Gateway?
  • Two types of Gateway
  • Personal Gateway; an example
  • On-premises Gateway; not only for Power BI

5.2: On-premises Gateway in Details

  • Requirements for on-premises gateway
  • Architecture Flow diagram of the gateway
  • Installation and Configuration considerations
  • Install and Configure Gateway example
  • Data Source Definition in Power BI Service
  • Schedule Refresh

5.3: Gateway Performance

  • Performance Considerations
  • Limitations of Gateway
  • Gateway for SSAS Live data source

5.4: DirectQuery

  • Step beyond 1/10GB Limitation in Power BI
  • Limitations of DirectQuery
  • Dashboard and Report considerations for DirectQuery
  • Performance Considerations

5.5: Live Query

  • Using SSAS Live data source
  • Difference of DirectQuery and Live Query
  • Enterprise Scale architecture
  • UPN Mapping
  • Architecture of Gateway and SSAS Live Connection

5.6: Power BI Service

  • Dashboard vs Report
  • Dashboard Tiles
  • Dashboard Alerts
  • Quick Insights
  • Usage Metrics
  • Data Classification
  • Power Q&A
  • Tips and Tricks for Power Q&A

5.7: Sharing

  • Dashboard Sharing
  • Work Spaces in Power BI
  • Power BI App
  • Publish to Web
  • Comparison of all Sharing methods
  • Sharing Architecture for enterprise

5.8: Administration

  • Admin Portal
  • Set Power BI Administrator
  • Tenant Settings
  • Audit Log

5.9: Row Level Security

  • Row Level Security in Power BI; regardless of Data Source
  • Row Level Security in SSAS Live Connection
  • Dynamic Row Level Security

5.10: Integration

  • Analyze in Excel
  • Power BI Publisher for Excel
  • Reporting Services and Power BI
  • Power BI Report Server
  • Power BI and Power Point

5.11: Real-time

  • REST API of Power BI
  • Real-time Tiles
  • Azure Stream Analytics

5.12: Architecture

  • Architecture for Enterprise Scale
  • Architecture for Reporting
  • Architecture for Self-Service
  • Licensing
  • Power BI Free
  • Power BI Pro
  • Power BI Premium
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