Microsoft Big Data Hackathon is happening in Vancouver, April 18-19, 2015

Microsoft Big Data Hackathon April2015

The Microsoft Big Data Hackathon is happening in Vancouver. (Register here)

April 18-19, 2015 (Saturday and Sunday)

Simon Fraser University – Morris J Wosk Centre for Dialogue (Map)
580 W Hastings St
Vancouver British Columbia V6B 1L6

You will need to register for the event. Register here.
To participate, you will need to be part of a 3-5 member team.

Continue reading

Data Sets for BI/Analytics/Visualization Projects

Just wanted to compile a list of datasets, or sources of datasets, that can be used for BI/Analytics/Visualization projects and explorations (not limited or specific to any tools). These come in different formats, and some may need to be cleaned up. Please do read the restrictions and EULAs that come with each of the links/sources.

This is a work in progress. I  am still updating this post, and will most likely re-arrange or re-categorize the links as I stumble across other data sets.

If you have any that you can share, I would love to add those to this list (and mention you shared it!) – please leave a comment below and I will add them to the list!

Last Updated : February 01, 2015

Canadian Data

US Data

Continue reading

Microsoft Second Shot through May 31, 2015

Microsoft Second Shot is back for 2015! If you’re planning to upgrade your Microsoft certifications (MCSE, MCSA, MCSD), you might want to take advantage of this insurance. You will be eligible for a free retake (if you don’t pass the first time) if you take your exam between January 5, 2015 and May 31, 2015.

Check all the details here:

By the way, Microsoft has switched to Pearson as a testing provider. You may find that testing centres could be scarce in some locations at first, hopefully that picks up soon!


Getting Grand Total and Subtotals using SQL Server Window Functions

Did you know that you can use SQL Server’s window function in an aggregate, and partition by NULL to get a grand total?

Let me illustrate. In the example below I use my trusty ol’ pubs database. (Hey, it’s still a great database for simple examples. Great for people starting to learn SQL too).

Let’s get some basic numbers first so we know what we are expecting to get.

Using a basic query that gets some grand total, we get 493. This the total for the whole table.



Continue reading

Writing SQL statements is easy ….

I teach a number of courses on databases, including introductory relational databases which includes introductory SQL, as well as Microsoft’s Querying SQL Server courses.

When students first learn how to write SQL, the tendency is to get too focused on getting the syntax right, and making sure the query executes. Don’t get me wrong. That’s really important especially when you’re just beginning your database journey. I have been guilty of this, and still am sometimes guilty of this.

However, blame it on age or experience, my focus has shifted from worrying about the syntax to understanding the problem at hand. I no longer jump to “coding” right away. I still worry about the syntax, but I leave the worrying for later.

Continue reading

PowerShell Deep Dives

I contributed a chapter in Manning’s PowerShell Deep Dives book. All proceeds for this book go to Save the Children foundation.

powershell deep dives  save the children

Here is a quote of the description of the book from the Manning website:

PowerShell Deep Dives is a trove of essential techniques and practical guidance. It is rich with insights from experts who won them through years of experience. The book’s 28 chapters, grouped in four parts (Administration, Scripting, Development, and Platforms), were hand-picked by four section editors: Jeffery Hicks, Richard Siddaway, Oisín Grehan, and Aleksandar Nikolić.

Whether you’re just getting started with PowerShell or you already use it daily, you’ll find yourself returning to this book over and over.

powershell deep dives authors

Demystifying T-SQL Subqueries–Part II

In the last post (Demystifying T-SQL Subqueries – Part I) we looked at how to use scalar subqueries. Let’s continue our subquery adventure.

As with Part I, the following T-SQL query samples are using the Chinook database.

Subqueries which return a single list of values

Result Description Sample
B Single Column
or Column List
or Single List of Values

Continue reading