Master Your Data for Power BI – SQLSaturday Vancouver Pre-Con Session

One of the local Sponsors to SQLSaturday #635 – Vancouver, BC is Excelguru.

Ken, founder of Excelguru, will be delivering a “Pre-Conference” session on Power BI!  Here are some of the details, and a link to further details and a registration page.

Friday, August 25, 2017

The sad reality is that not all data we need for our reports is stored in nicely curated databases. While some of the data may be, the reality is that we also need to piece together data provided in text files, Excel files, web pages and even the body of emails in order to build the complete business intelligence solutions we need. Until now this has been a painful process with a great deal of cutting and pasting into Excel files. Updates have been tedious and error prone, if the solution isn’t abandoned outright. That stops today.

This course is about Data. It’s about getting, transforming, cleaning and reshaping your data. It’s about turning awkward data into shiny golden tables that are perfect for analysis.

You’ll learn how Power BI can clean up, reshape and combine your data with ease – no matter where it comes from. And the best part? While we’ll be focusing on these techniques in Power BI desktop, they are also 100% portable to Excel 2010 or higher via the Power Query add-in.

In this hands-on workshop, Ken will cover:

  • A quick review of Power BI report basis
  • Importing basic data
  • Appending and merging tables
  • Pivoting, un-pivoting, and transposing data
  • Conditional logic, and
  • Other techniques such as structuring and grouping queries, and understanding query folding and the formula firewall

Register Today and Save:

As an exclusive offer for the SQL Saturday Vancouver mailing list, register before 6:00pm on Friday, August 18, 2017 and save 10% on the registration price! Simply enter discount code SQLSAT at check-out to redeem this offer.

Full course details and registration at













SQLSaturday #635 – Vancouver, BC coming August 26th

This Summer, Vancouver data professionals welcome the 5th iteration of the global phenomenon that is SQLSaturday!

Critical Facts


what?   SQLSaturday #635 – Vancouver 2017

where?  UBC Robson Square Downtown Campus – Vancouver, BC

when?   Saturday, August 26th, 2017 (All day! – Schedule coming soon)

who?    500 attendees (it’s our goal!) – professionals who work with data

why?   For the love of data!

how much?  it’s FREE!  – Purchase of a boxed lunch is optional ($15) after registration is complete, you will be directed to PayPal to purchase the lunch, if you want it.  

How do I find out more? Go to… 


Our goal this year is to break the 500 attendee ceiling.  How do we do that?  We open up more tracks and invite other technical communities to talk on data focused topics.  The Azure community will have a track to talk on the current offerings that the Microsoft cloud offers in way of Data Platform capabilities.  The Office 365 / SharePoint Community will also be invited to submit sessions on unique offering in the data space that their community can share.  With MySQL and PostgreSQL now being offered in Azure, we may still see more presentations from OpenSource communities.  In addition to “R” the statistical analysis language that is a key language of the Data Scientist community, we can also expect to see presentations on Python as both “R” and Python now able to be called from T-SQL Stored Procedures.




A History of the first 4 SQLSaturday’s held in Vancouver, BC

Vancouver was the first SQLSaturday outside the USA back in 2011 with SQLSaturday #65. It was an event held the weekend before the Microsoft MVP Summit in Bellevue Washington, so the event was infused with speakers from around the Globe. The event took place at a swanky hotel in the heart of downtown Vancouver. The event was a smashing success. Michael Lato was the main organizer, but Rich Baumet and I did a lot to make the event successful as Michael was on the other side of the country.

Rich Heigis wrote a blog that can be found here.  Aaron Bertrand wrote his blog post here.  Michael Lato blogged multiple times here. Donabel Santos also wrote a blogpost here.  

Vancouver’s 2nd SQLSaturday (#114) found a new suburban home in BCIT’s Burnaby’s Campus.  A more modest sized budget and with fewer attendees, the event was once again orchestrated by Michael Lato, and I popped by the day before the event to help out with the onsite event execution.  Not being on the 


Autumn is here – Let’s Connect, Share & Learn

There are a number of exceptional events taking place close to home (Vancouver, BC, Canada) over the next few weeks… here are just 5 Data-centric events to excite the senses!

  1. New Era of Data Warehouse Design – with ColumnStore technology  (Free event)
  2. Operationalize Power BI  (Free event)
  3. PASS Summit 2016  (discount code provided!)
  4. Columnstore & Memory-Optimized Workshop  (discount code provided!)
  5. Vancouver SQL Camp  (Free event)


September 28th (Wednesday Evening!)

Vancouver BI PASS Chapter presents (Free event):

“New Era of Data Warehouse Design – with Columnstore technology”

by Sepand Gojgini, Lead Business Intelligent Developer DATATEAM services

Having a robust data warehouse and analytical platform is a great competitive advantage, however, many organizations lack one due to complexity and cost associated with developing and maintaining one. In this session, we will share a, proven, new data warehousing approach that can help you take advantage of advancements in Microsoft SQL Server.

for FULL details:
to RSVP:


October 13th (Yup another Thursday Evening!)

Vancouver Power BI User Group Meeting & Vancouver BI PASS Chapter presents (Free event):

“Operationalizing Power BI for the Enterprise”

by Scott Stauffer, Independent Data Consultant

“How do I take my Power BI Solution and pass it over the wall to IT to manage? … How do we take what we’ve developed and make it more “-able”? … Come join us as we look at some solutions that we think might help transition your desktop solution to the enterprise.

for FULL details
Meetup Page:
to RSVP:


October 25 – 28th (Starts on the 24th if you want to take in the preconference sessions)

PASS Summit 2016
at the Washington State Convention Center

4,000 of my closest friends get together each year to Connect, Share & Learn!

2 days of half and full day preconference sessions & 3 days of general sessions

Seattle, WA

Save $150 with the Chapter Discount Code: LC15CJQ9

Full details & to register:


October 31st – November 2nd (Monday – Wednesday)

ColumnStore & Memory-Optimized Workshop
in Redmond, WA (Microsoft Campus)

This 3-day workshop put on by Niko Neugebauer (Data Platform MVP), of OH2data, will be just what your boss ordered to help improve your database performance!

Discover the two of the most important & revolutionary technologies for Data Warehousing, Operational Analytics and OLTP – the Columnstore Index and In-Memory Tables!

– Niko’s Columnstore blog series includes over 90 posts over the last 3 years!

Save $200 with the Chapter Discount Code: VANCANSQL16

Full details & to register:


November 4th (Friday Afternoon)

Vancouver SQL Camp

This Free event is hosted by the DevTeach Community & Microsoft – Pizza & 4 technical SQL sessions by Rich, Scott, Florian and Jean-René on a Friday afternoon

for FULL details:
to register:,26,1,46

SQL Server 2016 General Availability and Community Launch event in Vancouver

Grab and Evaluation Edition or FREE Developer's Edition of SQL Server 2016

Grab and Evaluation Edition or FREE Developer’s Edition of SQL Server 2016

Well, on June 1st, Microsoft delivered a new version of its “Enterprise Class” Database, SQL Server.  Why should you upgrade?  There are so many reasons that I can think of, but likely you’ll be interested because…
SQL Server Developer’s Edition is now FREE! … so why not take it for a spin…
Need resources to get you started… Yup, we can help there too…

tl;dr? – Make your way to the end to link to the resources 😉


Why not join 1 of the Vancouver PASS Chapters? - Click the Logo above and fill select the appropriate region info.

Why not join 1 of the Vancouver PASS Chapters? – Click the Logo above and fill select the appropriate region info.

Vancouver hosted the LARGEST Community Launch event in Canada, thanks to the support of Microsoft, PASS and Swim Recruiting.

swim recruiting

swim recruiting

Our sponsors threw some cash our way to feed the attendees with some snacks, coffee and boxed lunches.  The Swim Team was helping out with registration and talking to you about career opportunities in the Vancouver area.  PASS dropped off a load of DVDs “Top 10 Sessions of PASS Summit 2015.”  These recorded sessions give you an idea of what you simply DO NOT want to miss out on… Are you sold on the PASS Summit?  Go to Vancouver BI Chapter site and grab the discount code to save $150 on registration for the PASS Summit 2016 coming to Seattle in the fall.

Microsoft was at the SQL 2016 Community Launch Event too, represented by Andrew Boudreau.  Andrew played double duty delivering 2 sessions – You Rock, Andrew!  Andrew started the show off sharing the pieces of SQL Server that he is PASSionate about – Azure Data Warehouse and Polybase to name two.  I followed up with “Visualization & Reporting Options on the Microsoft Data Platform” talking about what’s new with SSRS and a few things on Power BI too… Andrew, graciously agreed to fill the lunch slot and filled in the gaps that I left out on Power BI, in addition to launching into some Power BI demos to delight the audience.

After Lunch, Rich Baumet jumped into DBA enhancements and new features.  [On a side note Rich is once again doing the Ride to Conquer Cancer.  Why not make an investment in a Cancer free future… Click here to help him meet his fundraising goal!]

After Rich, Todd McDermid jumped into share some In-Memory topics, along with some T-SQL features, Always Encrypted, and even a few SSIS pieces, because we asked him to…

A great day was had by all… It is tough to know exactly how many people showed, as some people only came for half a day and others didn’t register, they just slid in, but best estimates based on check-in is 125 – 130 attendees.  Not bad for a community driven event.  For the next SQL Launch, though… Microsoft Canada needs to step it up and have a bigger Launch event here in Vancouver.

The Resources?

Right, you probably want the slide decks…

Todd’s slide decks and demo files are available here:

My slide decks, in addition to Andrews, can be found here:!AgxiYutcvpJCgrwxStCiDrdz4R6fRg

Here is a fancy poster to print out and hang in your office!

SQL Server 2016 Technical Overview poster

SQL Server 2016 Technical Overview poster


Call to Sponsors – SQL Server 2016 Community Launch Event – Vancouver, BC

Okay, this is pertaining my LAST blog post, “SQL Server 2016 Community Launch Event – Vancouver, B.C.”  a.k.a. The Event

As Vancouver is one of the TOP 10 most expensive cities to live, it’s reasonable to expect that it is also one of the TOP 10 most expensive cities to host an event… Community event, or not…

That is why we are reaching out to YOU, our partners in making the  SQL Server community awesome.

We are offering sponsorship opportunities at 3 levels:

Disclosure monies collected will go towards Event, revenue after expenses doesn’t make it to my pocket but goes towards food and beverage for the local Vancouver SQL PASS Chapters meetings.


Bronze Level $375 (a.k.a. – I want to help the community!

show me a little love, but don’t make me stand behind a booth all day…)

  • Thank you at beginning and at the end of the day on stage
  • Logo on Slide Deck / Eventbrite Site
  • Logo on Vancouver PASS OR PASS BI Chapter site for 6 months


Silver Level $750 + Bizcard draw prize (a.k.a. – I’m a Marketing Geek!

I need a little separation from all the excitement of SQL in the form of a table as a platform for my Marketing Magic! [okay, seriously, those SQL people are a little strange, the table is my protection])

all the benefits of the Bronze level in addition to…

  • a table for your marketing stuff – let me know if you don’t have a table cloth it seems most companies do now-a-days. (Yes, we have power cables and chairs too)
  • Logo on Vancouver PASS AND PASS BI Chapter site for 6 months
  • 1 mailing – sent by us, on your behalf; before or after The Event
  • 30-second elevator pitch on stage at the beginning of the event inviting attendees to come talk with you at your booth.
  • you do your business card draw on stage and another short elevator pitch opportunity


Gold Sponsor $2000 (a.k.a. Lunch is one me!)

(Come hear us Roar, we are an MS Partner with Data Platform competency; OR, we sell 3rd part software that makes the Microsoft Data Platform sing; OR, we sell Enterprise hardware that makes SQL Server hum along [Singing or Humming – it’s all good…])

You want the top spot?  First come, first served for this prize sponsorship level; but move fast, there is only one spot!

So again, you get all the benefits of the Bronze & Silver levels in addition to…

  • You have us for the lunch hour – we’re all yours.

UPDATE – April 3rd:  

We have room C300, the theatre, that now comfortably seats 175 people.  We had 100-110 attendees for the launch in 2014, so with a bigger venue, and more hype, we expect 125 – 150 attendees.

There is only 1 Gold Sponsorship opportunity as we only have the 1 room, but you would have the attention of the entire audience as they chow down on their boxed lunches.

Silver and Bronze offerings are not really limited, but practically, we only have enough space for 5-6 booths comfortably

SQL Server 2016 Community Launch Event in Vancouver, BC

What?  Microsoft SQL Server 2016 Community Launch Event
Where?  UBC Robson Square Downtown Campus (800 Robson St, Vancouver, BC V6Z 3B7)
When?  Thursday, May 19th, 2016  (9 AM – 5 PM)

We will have Microsoft Employees, Data Platform MVPs and Seasoned SQL Professionals talking about and demonstrating the new features of SQL Server 2016.  Of course, we don’t know the ACTUAL release date of the product at this point, but CR1 (Release Candidate 1) is out, so RTM (Release to Manufacturer) shouldn’t be TOO far behind?  Never the less, the community will unite and converge under Robson Street once again, to Connet, Share, Learn on May 19th.

More details to come… Drop the date into your Calendar, Eventbrite page to follow…

No, this is NOT an April Fools joke…


Scott’s Hierarchy of Presentation Needs

As I roll up my sleeves, make some espresso [insert 12 other OCD / procrastination habits], In preparations for another SQLSaturday, I think once again about how I have needs.  I’m not talking about the one’s that Abraham Maslov provided me – those are a given.  I have certain needs as a presenter in order to be successful.  Now this isn’t a paper that is to be published in psychology journal to be pondered upon and dissected  for decades, but simply a draft to be added to and perhaps referenced from time to time when people consider the physical surrounding when they speak.  I haven’t done my due diligence to see if this topic has been suitably covered in other blogs or books, and at this point I’m not sure that I am locked into precedence of layers or completeness.  In fact, please comment with additional recommendations and opinions.

So here we are… remember, it’s just a draft!


  • Audio
  • Visual
  • Connectivity
  • Electricity

Technical Issues… Please Standby – Image to come shortly… 🙁



When influencing, educating, sharing knowledge, it’s important to be heard!  Now anyone who knows me well knows that I have a rather loud voice at times.  Perhaps, I never learned about my, “Inside Voice,” in elementary school, or I am slowly losing my hearing and speak louder to compensate, or because I took music & theatre in school and the ability to project my voice has become an ability that has improved over the years [I’d like to think the latter, but other’s debate heavily over the former two possibilities.]  Where am I going with this?  Oh, yes… “…it’s important to be heard!”  When you are speaking in certain venues, you have Lapel or podium mics, in other venues, the acoustics are similar to that of an amphitheatre and then sometimes, you simply have to project your voice.  It isn’t critical that I know the Audio characteristics of the venue, however, it certainly does help to know ahead of time so that I can properly prepare.


So there is me… I have to be dressed appropriately, PJ’s, slippers… uhm, suit, tie, polished shoes…  But then there is this thing called PowerPoint, and although I’d like to believe that my audience just came to see me… {C’mon, of course, they soooo did…} they likely came to see a technical demo, a psychedelic Data Viz, or an architectural tech diagram.  So it would help if I had the correct connectors for my computer.  Also, of importance is to be privy to the supported resolutions and aspect ratios, but, more importantly, the native resolution of the projection system.  Certainly knowing the size of the screen and room is great to know, it isn’t a major concern in rooms designed for such activities.  When you first start developing your slides… this is the best time to choose your slide format 4:3, 16:10, 16:10.  Changing this afterwards can be a major pain, but Scott Hanselman posted this excellent blog on how to quickly change the aspect ratio without stretched images.  We aren’t talking just about PowerPoint presentations.  If you have visual designers like Visual Studio that have a number of regions, shrinking things down to a low resolution can make it rather cumbersome to navigate. Knowing this beforehand a presenter can practice at this resolution, and make needed changes to the regions that are displayed by the tool by default.  In turn, the audience won’t be distracted by a panicked, disoriented presenter.


Notebooks, small batteries, no power outlets at the podiums/tables.  ‘nuf said…


With so many service offerings in the cloud today, and demo environments living on someone else’s servers, connectivity is king.  Ideally, I’d like to be able to access the internet, connect to a VPN, use Remote Desktop in Azure via an Ethernet Adapter, but know, in reality, this is not always going to be an option.  I understand companies have security concerns so they lock down ports on their firewalls, provided limited bandwidth connections, have a spotty or aged WiFi infrastructure in place or simply don’t provide ANY of these options. Now sure, I have some fairly beefy machines that I can bring along, but I am getting old, and to carry a lot of extra tech in my backpack can do a number on my back.  Let’s not mention that the TSA people raise eyebrows when you have a LOT of hardware for a weekend trip.  Another option is to record videos with Camtasia, and likely a recommended backup in the event that things don’t quite go as planned.



Okay, now it’s your turn!  Did I miss anything here?  Do you have a layer to insert into the Presentation Hierarchy of Needs?  Add your comments below!

SQLSaturday makes a 4th Stop in Vancouver, BC

…In less than 2 weeks! – Register Now

SQLSaturday #407


We’re BAAAaaack!

Well after almost 2 1/2 years we decided to do it one more time! (For historical purposes you can check-out my last post on the topic here.)

“What’s a SQLSaturday?”

A little unsure what a SQL Saturday is… well you can read the original description on the wiki written by the founder, or you can read other people’s thoughts, here and here… And please do have a look, BUT my interpretation is this:

A free, one day conference style event organized by volunteers, financially supported by sponsors, and attended by data professionals. These events are happening all around the world with much the same model. PASSionate people sharing their knowledge and networking with their peeps.

Wondering if this means you? If you aren’t currently a data professional, that’s okay, this is an excellent way to make a start, meet some people in the field and take in some free educational sessions. Wondering what Big Data, the Internet of Things (IoT), or Data Scientist means? Wondering what people are talking about when they say Self-Serve BI or Visual Analytics? Yeah… we can help you out there too. 😉

“Gimme a little background…”

Vancouver had the first SQL Saturday outside the U.S. (#65) in 2011 and the last SQL Saturday saw presenters from around the world sharing their knowledge. At that event, SQLSaturday #198, we had about 425 attendees and we took over the BCIT Burnaby Campus.

“… and this year?”

This year we are Downtown Vancouver at the UBC Robson Square Campus. Our Schedule has a choice of 5 sessions for every timeslot – how will you ever choose? Take a friend or business colleague and divide and conquer- share the experience when you get back to the office!

We will have educational sessions and during the lunch hour we will have vendor focused sessions so that our generous sponsors have a chance to talk about the products and services that they sell while you enjoy your lunch. This year we also have a Women in Technology (WIT) panel session hosted by Cindy Gross, from Microsoft Corp.

So how do you register for this exciting day of Data fun and exploration? It’s just a few clicks away…

Register Now

“Free? There’s no such thing as a free lunch is there?”

Well, in this case, no, the lunch isn’t free. If you do register, please consider the $15 lunch. It is a boxed lunch with deli sandwich, cookie and your choice of a drink. All food in made onsite in the Industrial – food safe Kitchen to ensure freshness. Taking advantage of this option means you don’t miss out on education and networking opportunities while you try and hunt down offsite food choices.

We, the organizers don’t draw a red cent (or a shiny nickel for that matter). We volunteer or time, and it is a break-even event. Sponsors cover the fees for the facilities, WiFi, the A/V equipment, Insurance, and the Speaker’s Dinner… And NO, the speakers don’t get paid anything for their time either, so please, if you come, thank them for taking the time to share their incredible knowledge with you. While you are at it also thank the sponsors, without them this event just wouldn’t happen either.

Not sure this is your thing?

Not really that into data? Do you IT or Data Savvy friends a favour and pass this link along so that they have an opportunity to attend.

Again, this is less than two weeks away, register now to reserve your spot. I look forward to seeing you at the event!


Register Now



Data Mashups Recipes using Power Query

Potatoes, cream and butter not required!

Tomorrow evening I will be delivering a presentation to the Vancouver PASS Chapter the Vancouver BI PASS Chapter and invitations have gone out to various other local UGs as I will be covering a few different areas.

Power Query is a pretty slick tool and I will add details here, but they will also be in the Slide Deck that I will include later in this post at a link to my SlideShare account.

This page will have some links to other important places that you might want to explore, such as OpenDataBC.  OpenDataBC has a MeetUp group that just ramped up in November for Vancouver.  It’s sister Meetup in Victoria has been meeting for quite a while, but when one of the key organizers moved to Vancouver for a new role, she decided to make things happen here too.  Thanks for the hard work Gillian!

Looking to learn more about the recent updates?  Check out the PowerBI MSDN blog, and see post like this:

I LOVE how Miguel Llopis does a detailed blog post with screen shots, but also takes the time to also showcase each new feature to the realease in a short screencast – Microsoft, take note, people DO like this – Please encourages this… give that man a raise!

Looking to Download the Power Query Add-in… Likely the best place to go for the December update is:

Power Query release cadence – monthly sprints!  I LOVE it – something new to touch EVERY MONTH…

What will I be doing in the session?

  1. Showing you how to get the tools
  2. Showing you around the tool
  3. Doing some fun stuff with public data
  4. Scraping data from websites
  5. Creating a Do… WHILE construct to iterate over a website.

Afterwards, BELLINIS!  The Cactus Club is just around the corner from our Venue, so why wouldn’t we


Day 1 of Dimensional Modeling in Depth and Loving it!

Wow! The end of an amazing Day #1 – Dimensional Modeling in Depth.

I frequently espouse the benefits of benefits of following the Kimball approach to clients. Sometimes they listen and send their people to the Kimball University Training, and sometimes they simply buy them the book and say, “Make it so, Number One!” [Think Captain Picard from Star Trek the Next Generation]. Now was the time to put my money where my mouth was. If I believe in it, I need to drink my own Kool-Aid. Ya know, it’s kinda tasty! Apparently, with the number of the people in the room, others like the taste too… 72 attendees! WHAT? Yes, and 44 are from the state of Texas – there are also other from Canada too. Overall, I was a little shocked, but delighted to see the room and all the people.

They course isn’t simply a re-hash of the book, there are different conversations, and with 72 people, we get a lot of great questions. I wouldn’t necessarily ask some of them, but I’ve been intrigued by some of the answers and conversation that comes from them the comments and questions.

Margy is a Rock Star. Truly. She downplays here role saying that people are waiting to see and hear Ralph speak, but honestly, I’m getting more than my money’s worth from Margy. It’s just so damn sad that they are retiring. We have to entrap all of them and keep them out of retirement. Really, we can make it happen… Okay, I’m just getting tired and punchy… time to hit the hay so that I’m refreshed for Day 2.

Oh, and I almost forgot – I will also be attending the North Texas SQL Server User Group on Thursday, because, “Hey, it’s in the same building!” – How cool is that.  Who will I be seeing present?  None other than the infamous Mindy Curnutt!  She will be speaking on “Tracing and Extended Events – 3 Methods to end the Madness” – It’s like I won the lottery TWICE in one week!

P.S. – The POST process failed last night in WordPress and fell off the Rocker… Luckily, I saved it in Word and could re-post today!