Planning my PASS Summit 2018 schedule

Summit is just around the corner and its time to start thinking about some of the sessions you will be attending. In the past, I have winged-it but this often resulted in me missing session that I really wanted to attend or attending too many of the same type of session. As I will also be attending  SQL Saturday Portland and some of the speakers will also be speaking I also have the opportunity to see some of the sessions there. Below are some of the sessions that I plan to attend this year.

Day One

Day 1 Keynote

PASS Summit always begins with a keynote, in addition to providing a good roadmap of the features to come over the last few years, we regularly get to hear the first announcements of new and upcoming features in SQL Server. With SQL Server 2019 on the horizon maybe there will be more exciting news (Holding thumbs).

Data types do matter – Angela Henry

I am very excited about this talk as it will be the first PASS Summit presentation for my friend Angela Henry. Not only is Sumit a great time to learn but also to support #SQLFamily and I look forward to seeing her do her thing.

I’ve been working with SQL Server for a long time now and kind of just assume that I know the basics, it will be great to get a bit of a refresher and hopefully fill in some of the missing gaps that I have.

My Power BI Report is Slow: What Should I Do? – Marco Russo

Performance tuning in DAX is something that I have been doing more recently of so have some experience with the topic but who can turn down the opportunity to learn from the master. What I’m most looking to take away from this talk is the Approach that Marco takes to identifying the biggest bottlenecks and patterns that he uses to address them.

Storytelling for Machine Learning and Advanced Analytics – Jen Underwood

Jen has worked for Microsoft and many other industry leaders. She has a fantastic grasp not only on the Microsofts BI tools but industry trends in general, I look forward to her thoughts on this.

Speaker Idol: Round One

As I am taking part in this competition this year it is only right to spy on support my fellow contestants. I look forward to watching although I’m not sure if watching the others present will make me more or less nervous.

Day Two

Day 2 will be a little different for me, taking part in this year’s Speaker Idol competition and speaking during the last session of day 2 I will not be attending any afternoon sessions as I will need to prepare for my talk.

Inside Columnstore Indexes – Bob Ward & Ryan Stonecipher

This will be my 4th PASS Summit and I have never been to a Bob Ward session, they are legendary for melting your brain. Unlike some of his other talks, columnstores are something that I am familiar with and share some common implementation patterns that underpin vertipaq which is the engine behind Power BI. Hopefully, I will be able to keep up.

Test Driven Development in SQL Server (How to Deploy Code Safer) – Hamish Watson

Devops is something that I am trying to understand more while integrating it more into my daily activities. On top of that, I understand the speaker like to hand out candy so who could say no to that.

Speaker Idol: Round Two

I will be presenting during this session so if you don’t find me here that means that I have messed up somewhere.

Day Three

Day three I’ll be a bit of a mixed bag, If I make it through the first round then I will again be skipping sessions to ensure that I have enough time to prepare.

Using Azure Databricks to Develop Scalable Data Solutions – Ginger Grant

I got to meet Ginger earlier this year and enjoyed talking to her. She will be presenting on a topic that will play a greater role in the data platform ecosystem in years to come so need to pay attention to more and I look forward to learning.

Building Enterprise-Grade BI Models with Microsoft Power BI – Christian Wade

For a long time, I have been an analysis services fan and most of my work revolves around it in some way, it looks like Microsoft plan to make Power BI the centre of the analytics experience so understanding this may be very important

Deep Dive in DAX Evaluation Context – Alberto Ferrari

Although I understand DAX context evaluation reasonably well this is a must attend talk for me, just like Marco Russo’s session, this is a must-attend talk for me.

Speaker Idol: Final Round

This will e the final session of the summit, I hope to be speaking during this session as well but even if I don’t make it through I will make sure I will be the crowd supporting the other contestants.

 

 

 

 

 

 

 

PASS Summit 2018

I’m excited to be able to go to my 4th PASS Summit having attended in 2012, 2014 and 2016 (a little bit of a trend there). PASS summit is always the highlight of the Microsoft Data Platform community (more than just SQL nowadays), and I look forward to reconnecting with old friends and meeting new ones. Flying from South Africa to Seattle is always a bit of an effort from both a time and cost perspective, so I like to bring as many people as possible with me especially people who have not had the opportunity before.  This year I joined by two first-timers, James McGillivray (T) and Charl Heinemann (T) along with some repeat attendees like as JP Voogt (T) and Jody Roberts (T). While I have always enjoyed my trips to PASS Summit, this year is going to be a little more interesting.

Microsoft Data platform MVP

This will be the first time I attend PASS Summit as a Microsoft MVP, while there are no extra perc’s to being an MVP at the event it’s always nice to add one more ribbon to my attendee badge and reconnect with some of the many people I met at the MVP Summit.

Speaker Idol

This year I have been selected to take part in the Speaker Idols competition. The Speaker Idols competition consists of several 5-minute lightning talks over four rounds, the first three rounds are made up of four contestants (12 in total) who each present to a panel of judges, a winner is chosen from each group who then go onto the final round held on Friday afternoon. I will be presenting in the second group also made up of Leslie Andrews (T), Janusz Rokicki (T) and Paresh Motiwala(T) all of whom I have never met before so I look forward to getting to know each of them as well as the judges.

Power BI World Tour

I have also been selected to present a session at the Power BI World tour taking place in Bellevue from 29 to 30 October 2018. I will be speaking about Data flows in power BI which at the moment is still in private preview but will likely be made public by then. This is a two-day event dedicated to Power BI and will also take place in the building where most of the Power BI team work out of so it is sure to be a great event.

SQL Saturday Oregon

I will also be attending SQL Saturday Oregon for the second time. I will also be attending with James and JP and we look forward to getting in some hiking while we are there and SQL Saturday Oregon would not be the same without #SQLTrain, without a doubt the best way to travel to PASS Summit that starts the next day.

October and November are going to be great, and I Hope that I will see you all there.

 

 

Creating a custom date table using PowerQuery

A common requirement in many BI environments including PowerBI is a date table. Date tables are a great way to add extra depth to you period\time sensitive reporting and are essential when using the time intelligence features of DAX.

There are a couple of options you have when creating a date-table such as importing the table from another data source such as a data warehouse or you can create the date-table using the CALENDAR function in the data model. In this post, we will look at how a date-table can be created using PowerQuery (also known as M).

At the heart of any date table, we require a column of distinct, continuous dates for a given date range.  Fortunately, PowerQuery has a function List.Dates that can create such a table with a few quirks\features. Firstly this function returns a list and not a table, to solve this we can use the Table.FromList function to convert the list to a table. Secondly unlike the CALENDAR function in DAX which takes a start and end date as parameters the List.Dates function instead takes a start date, count of periods that need to be created and finally a duration. this duration can be in days, hours, minutes or seconds, in our case, we would want to use days. Unless you know the exact number of days that you need in your date table it may be easier to calculate the number of days using the Duration.Days function which will return the number of days between two dates. The code sample below can be used to create a list of dates between the StartDate and EndDate.

    StartDate = #date(2017,1,1),
    EndDate = #date(2017,12,31),
    NumberOfDays = Duration.Days(Duration.From(EndDate-StartDate)),
    Source = List.Dates(StartDate,NumberOfDays ,#duration(1,0,0,0))

The next step is to convert this list into a table using the Table.FromList function, which also allows us to provide a better column name for our date column, this column also need to be set to a date data type.

    TableFromList = Table.FromList(Source,Splitter.SplitByNothing(),{"Date"}),
    ChangeType = Table.TransformColumnTypes(TableFromList,{{"Date", type date}})

This simple table with the correct relationships in your data model will allow you to use the powerful time intelligence functions that DAX supports, however, we want to create our own custom date attributes that allow for more natural slicing, we will do this by adding additional columns using the Table.AddColumn function along with the Date family of functions.

    Year = Table.AddColumn(ChangeType ,"Year",each Date.Year([Date]),type number),
    MonthOfYearNumber = Table.AddColumn(Year ,"Month of Year Number",each Date.Month([Date]),type number),
    MonthOfYearName = Table.AddColumn(MonthOfYearNumber ,"Month of year",each Date.ToText([Date],"MMMM"),type text),
    DayOfMonth = Table.AddColumn(MonthOfYearName ,"Day of Month",each Date.Day([Date]),type number),
    DayOfWeekNumber = Table.AddColumn(DayOfMonth,"Day of Week number",each Date.DayOfWeek([Date]),type text),
    DayOfWeekName = Table.AddColumn(DayOfWeekNumber ,"Day of week",each Date.ToText([Date],"dddd")),
    PeriodNumber = Table.AddColumn(DayOfWeekName,"Period Number",each Date.ToText([Date],"yyyyMM"), type number),
    Period = Table.AddColumn(PeriodNumber ,"Period",each Date.ToText([Date],"MMM") & "-" & Date.ToText([Date],"yy"))
  • Feel free to add and remove any columns that you would find useful.

The last thing that we may consider doing to make this code more useful is to convert the code block into a function, this will allow us to create multiple date-tables if required but more importantly, it will make using a dynamic start and end date much easier. The final code block would look like this.


let CreateDateTable = (StartDate as date,EndDate as date) as table =>
    let
        NumberOfDays = Duration.Days(Duration.From(EndDate-StartDate)),
        Source = List.Dates(StartDate,NumberOfDays ,#duration(1,0,0,0)),
        TableFromList = Table.FromList(Source,Splitter.SplitByNothing(),{"Date"}),
        ChangeType = Table.TransformColumnTypes(TableFromList,{{"Date", type date}}),
        Year = Table.AddColumn(ChangeType ,"Year",each Date.Year([Date]),type number),
        MonthOfYearNumber = Table.AddColumn(Year ,"Month of Year Number",each Date.Month([Date]),type number),
        MonthOfYearName = Table.AddColumn(MonthOfYearNumber ,"Month of year",each Date.ToText([Date],"MMMM"),type text),
        DayOfMonth = Table.AddColumn(MonthOfYearName ,"Day of Month",each Date.Day([Date]),type number),
        DayOfWeekNumber = Table.AddColumn(DayOfMonth,"Day of Week number",each Date.DayOfWeek([Date]),type text),
        DayOfWeekName = Table.AddColumn(DayOfWeekNumber ,"Day of week",each Date.ToText([Date],"dddd")),
        PeriodNumber = Table.AddColumn(DayOfWeekName,"Period Number",each Date.ToText([Date],"yyyyMM"), type number),
        Period = Table.AddColumn(PeriodNumber ,"Period",each Date.ToText([Date],"MMM") & "-" & Date.ToText([Date],"yy"))
    in
        Period
in
    CreateDateTable

 

If you have never created a blank query before then you can use the following steps to implement this code.

From the Query Editor select Get data –> Other –> Blank Query

BlankQuery.PNG

This creates  a new query, select the advanced editor from the Home ribbon

BlankQuery2.png

Using the Advanced editor replace the default code with final code sample from above

BlankQuery3.png

Invoke the function by selecting it from the list of queries (I have renamed the Query to make it more usable), capture the start and end dates using the calendar controls then click on the [invoke] button.

BlankQuery4.png

You will now have a custom date table that you are able to use in your data model.

BlankQuery5.png

I’m speaking: PASS Business Analytics Marathon

PASS_17_BAMarathon_Banner_440x220.jpg

I will be presenting a webinar on 7 June for the PASS Business Analytics marathon sponsored by Quest.

During my talk, I will introduce attendees to streaming data and how it different from how we as data professionals have traditionally dealt with data. We will then look at how Azure Stream Analytics can be used to provide rich real-time reporting.

We will conclude the talk with a practical demonstration of creating a streaming analytics Job in Azure and use PowerBI to visualize the results.

Presentation code:
You can download the code I used to create the events here

Importing data from web pages using Power BI

Sometimes the data we need for our data models can only found on web pages. The challenge with web pages is that they are often poorly structured and contain a mix of content types such as tables, lists and images, this makes importing data challenging so is often omitted in most ETL tools. Fortunately importing data from websites is made pretty easy in Power BI. In this post, we will import a list of countries along with its populationRead More »

Importing data into Power BI desktop using R

Power Bi is a great tool for data discovery with new functionality being made available all the time. However, this functionality is still limited when compared to a mature platform like R and you may come across scenarios where R is a better choice. It is for just such cases that Power BI now supports R script as a data source. Three scenarios come to mind where this might want to do this:

Read More »