October 2017 SQL Mastery Sessions

Friday, October 27, 2017

A FULL day of training offered the day before Oregon SQL Saturday
8:00 AM until 5:00 PM
Microsoft Portland Office; 1414 NW Northrup St, Portland, OR 97209

Advanced Analytics with Power BI and R
Leila Etaati

Securing the Data Platform: Crafting a Strategy That Works
Ed Leighton-Dick

Mastering Technical Presentations
Arnie Rowland

Advanced Analytics with Power BI and R
Leila Etaati

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 editor2- get familiar with ggplot2 package and its related charts like geom_bar, geom_jitter, geom_point and so on
  2. how to works with aes() function to show different variables in a charts, facet_wrap(), ggplot(),geom_vline(), geom_text() and so on.
  3. How to debug the written r scripts inside Power BI with R studio
  4. How to draw a Facet chart to show 5 different variables in one chart

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.

Register NowRegister for this session

Biography: 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 the RadaCad 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.

Securing the Data Platform: Crafting a Strategy That Works
Ed Leighton-Dick

Your database servers are insecure, and you know it.  Unfortunately, so does the person upstairs who just wants to take a peek.  That executive who wants a copy of the database to build some reports on the plane knows it. So does the call center rep who’s trying to provide great service to the customer on the phone. And the hacker who’s attacking your website? Yeah, they know it, too.  We can no longer rely solely on the same old security practices to protect our data.  We need to secure the data at its source.

In this full-day course, Microsoft MVP Ed Leighton-Dick will lead participants in a tour of security for the Microsoft data platform, particularly SQL Server. The complex security needs of SQL Server itself will be presented as a series of layers to give participants a framework for planning and implementation after the completion of the course. A significant portion of this discussion will focus on ensuring that all participants, regardless of past experience, gain a common, thorough understanding of the critical authentication and authorization structures of the data platform.  Security of other pieces of the Microsoft data platform will also be covered. By the end of the day, participants will gain familiarity with tools and techniques they can use to protect their systems from common threats.

This course is intended for database administrators and others responsible for maintaining the security of database systems.

Topics to be addressed in this course include the following:

  • Authentication and authorization structures
  • Separation of duties
  • Securing server-to-server communications
  • Encryption
  • Cloud security
  • Common threats
  • Defenses
  • Monitoring and auditing the security infrastructure
  • New security features included in SQL Server 2016

Upon completion of this course, attendees will understand:

  • Current threats to their SQL Server environment
  • How to implement SQL Server’s built-in security features to defend against threats
  • How to monitor the security framework they have implemented

Attendees with 1-3 years of experience administering SQL Server databases in some capacity will benefit the most from this course

A copy of the reference slides and demonstrations will be provided to attendees for reference and experimentation in their environments. Please contact Ed with any questions: ed.leighton-dick@kingfisherdata.com 

Register NowRegister for this session

Biography: Ed is a Microsoft Data Platform MVP and SQL Server performance and architecture specialist with over twenty-five years of experience in development, database administration, and system administration.  He is the founder and principal consultant for Kingfisher Technologies, a consultancy focused on SQL Server performance, reliability, and security.  Ed volunteers his time with PASS, including roles as Midwest Regional Mentor, Chapter Leader of I-380 PASS SQL Server User Group, and organizer of SQLSaturday Iowa City.  He can often be found teaching sessions at local, regional, and national events, including user groups, SQLSaturday, Iowa Code Camp, 24 Hours of PASS, and PASS Summit.  Outside of work, he is a devoted husband, father of two beautiful girls, and beginning runner. 

Ed can be reached through his blog, edleightondick.com, and on Twitter at @eleightondick

Mastering Technical Presentations
Arnie Rowland

Your work may require that you make presentation to various Teams, Executives, Customers, perhaps even the public. Do you cringe at the possibility? Do you leave wishing that it had come across a little better? Do you get performance jitters -‘butterflies in your stomach’? In order to succeed in today’s technology fields, you know that you are going to have to master presenting. You know that you need to become comfortable and fluid with your presenting skills. You’re reading this, so you know!

During this full-day experiential workshop, you will be given information about techniques, style, tips and tricks, and a ton of resources. AND throughout the day, you will be guided in short presentation experiences to allow you to ‘test your wings’. There will be coaching, mentoring, positive observational critique, and lots of trial and error. You will learn from observation, from presenting, and from understanding how to offer critique. One thing is certain, you will leave at the end of the day with a greatly enhanced sense of becoming a confident technology presenter.

We will cover:

  • How to ‘know’ and gauge the audience.
    • What should you expect that the audience already knows?
    • Why is the audience likely to agree or disagree with you?
    • How to ‘set the hook’ before you even start.
    • Why (and how) so many Presenters fail to ‘reel them in’?
  • How to prepare your presentation
    • What are the 3-Ts?
    • Carefully chosen words can be worth a 1000 pictures
    • Why less can be much more
    • Why you should love AND hate Powerpoint.
    • Why you should be wary of technical jargon overload.
    • Should you include humor in this age of ‘political correctness’.
    • How much practice is enough?
  • What’s your body communicating?
    • Are you presenting yourself in ways that sabotage your message?
    • Are you open to, and inviting information exchange?
    • Are you just in a hurry to get it over with?
    • Don’t apologize, in words OR body language.
    • Is the audience getting a chance to really, really, see you at your best?
  • Are you getting the audience’s full attention?
    • Using humor, stories, analogies, even ‘facts’ to ‘get the floor’.
    • Are you clear, speaking to be understood?
    • How do you keep from overloading the audiences attention span?
    • How to change directions mid-stream to meet the audience
  • What is your purpose for presenting?
    • You are NOT just informing, but seeking something -WHAT?
    • Do you need the audience to do something?
  • How do you process your experience after the presentation?
    • You CAN be your own worst critic.
    • How to get helpful feedback.
    • How do you know what to do differently next time.
    • How to learn watching others

Register NowRegister for this session

Biography: During his career as an outspoken architect and application developer with very large databases, Arnie [ Blog | LinkedIn | Twitter ] realized that most issues between Developer and DBA comes from incomplete communication and a misunderstanding of each other’s primary responsibilities. As a frequent Trainer and Mentor for Developers and Database Administrators, he has nurtured a knack for helping to ‘bridge the gap’. Arnie is a rare mix of expert developer and gifted database administrator, bringing both sets of skills to better communicate with development teams to create a working solution.

 Arnie is a frequent presenter at SQLSaturdays, Code Camps, the PASS Community Summit, TechEd, Collaborate, and many other conferences. He is a Certified Technical Trainer, and was a Microsoft Certified Trainer for over a decade. Arnie has served as adjunct faculty at both University and Community College. He is a long serving Microsoft SQL Server MVP, a Subject Matter Expert (SME) working on SQL Server training courses for over 20 years, including involvement as a SME with the development of Microsoft SQL Server Certification Exams. , and has served as a technical editor for several publishers, including multiple SQL Server titles in the Microsoft Official Curriculum. Clients include Fortune 500 and Multi-National companies, Federal and State agencies, Foreign governments, nationally recognized training facilities, and local enterprises -both public and private.


Arnie is a Senior Moderator on the MSDN and TechNet SQL Server related Forums, and is a founding member of the TechNet Wiki Governing Council. Arnie is founder, and current president of the Oregon SQL User group, co-Founder of the Portland Code Camp, and has been the lead facilitator for SQLSaturday Oregon events.


In addition to being a pilot and flight instructor, Arnie holds post-graduate degrees , is an avid bicyclist and sailor, and plays racquetball competitively.