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

clip_image004_thumb

Read the rest of this entry »

VN:F [1.9.22_1171]
Rating: 9.9/10 (8 votes cast)
VN:F [1.9.22_1171]
Rating: +3 (from 3 votes)

Demystifying T-SQL Subqueries–Part I

When I teach SQL Server Development/T-SQL courses, subqueries is usually one of those topics that students find confusing. Usually it’s a head scratcher, although there are some who just get it.

So let’s try to demystify subqueries.

Very simply put, a subquery is just a query within a query. It’s a SELECT inside another SELECT.

Now let’s break it down. Let’s start with understanding what a query gives you first.

Understanding results of regular queries

Before we can understand subqueries, we need to understand first the different results we get from regular queries. A regular query always results in a dataset. It gives you the following variations of results:

Result

Description

Sample

A

Scalar Value

subquery that returns scalar value

B

Single Column
or Column List
or Single List of Values

subquery that returns a list (single column) of values

C

Table

subquery that returns a table

Read the rest of this entry »

VN:F [1.9.22_1171]
Rating: 8.6/10 (8 votes cast)
VN:F [1.9.22_1171]
Rating: 0 (from 2 votes)

Downloading Vancouver Weather Data Using PowerShell

I’m trying to get some data I can play around with Tableau, fortunately information for Canadian climate data is available in CSV and XML from http://www.climate.weatheroffice.gc.ca/. I thought I’d go fetch the Vancouver daily weather data since 1953 using PowerShell.

Here’s a few PowerShell snippets for anyone interested. I haven’t cleaned these up yet, but should be pretty functional.

Script to Fetch Data

$yearfrom = 1953
$yearto = 2012
$folder = "C:\Temp\Weather Files"
for ($i = $yearfrom; $i -le $yearto; $i++)
{
   $filename = "weather_$($i).csv"
   $weatherfile = Join-Path $folder $filename
   $url = "http://www.climate.weatheroffice.gc.ca/climateData/bulkdata_e.html?Prov=BC&StationID=889&Year=$($i)&Month=1&Day=1&timeframe=2&format=csv"
   $webclient = New-Object System.Net.WebClient
   $webclient.UseDefaultCredentials = $true
   $webclient.DownloadFile($url, $weatherfile)
}

Script to Remove Extra Lines

$path = "C:\Temp\Weather Files\*.*"
$newfolder = "C:\Temp\Weather Files\Clean"
 
Get-ChildItem -Path $path -Include "*.csv" |
Foreach {
   #need to delete lines 1-25, leave only data
   $file = $_
   $newfilename =  "clean_$($file.Name)"   
   $newfile = Join-Path $newfolder $newfilename
   $filecontents = Get-Content $file.FullName   
   $filecontents[25..($filecontents.length-1)] | Out-File $newfile -Encoding ascii
}

Script to Merge Files

$csvfolder = "C:\Temp\Weather Files\Clean"
$mergefolder = "C:\Temp\Weather Files\Merge"
$mergedfilename = "merged_all_weather.csv"
$header = @"
"Date/Time","Year","Month","Day","Data Quality","Max Temp","Max Temp Flag","Min Temp","Min Temp Flag","Mean Temp","Mean Temp Flag","Heat Deg Days","Heat Deg Days Flag","Cool Deg Days","Cool Deg Days Flag","Total Rain (mm)","Total Rain Flag","Total Snow (cm)","Total Snow Flag","Total Precip (mm)","Total Precip Flag","Snow on Grnd (cm)","Snow on Grnd Flag","Dir of Max Gust (10s deg)","Dir of Max Gust Flag","Spd of Max Gust (km/h)","Spd of Max Gust Flag"
"@
 
#put headers in final merged file
$header | Out-File $fullmergedfilename
$fullmergedfilename = Join-Path $mergefolder $mergedfilename
 
Get-ChildItem -Path $csvfolder | 
Get-Content |
Out-File $fullmergedfilename -Append
 
#open up windows explorer, let's check
explorer $mergefolder
VN:F [1.9.22_1171]
Rating: 10.0/10 (5 votes cast)
VN:F [1.9.22_1171]
Rating: 0 (from 0 votes)

The company I work for is evaluating tools for BI/Visualization, and Tableau is one of the front runners that we are considering.

I’ve been fortunate to be able to attend this week’s Advanced Tableau training in Vancouver. Our trainer is one of the Tableau Jedis, Interworks’ Director of Business Intelligence (BI) Dan Murray, and boy does he know how to impress.

At first, because of budget constraints, I was contemplating on whether I should just watch the videos – since Tableau has graciously published a number of them allowing anyone to learn the product. But I’ve always thought that there’s always something to learn from in class training – especially if you get an awesome instructor. And I am fortunate – and very thankful – I did get an awesome instructor. I cannot believe how much he packed in two (2) days of training. Don’t get me wrong, my brain was full after two days – but even after the class ended I just wanted to keep on going and try out more Tableau stuff.

I won’t go through each topic that we covered, but for anyone interested, the curriculum for the Advanced Tableau training is posted in the Tableau site. Upcoming training sessions from Interworks are posted in the Interworks site.

Anyway, just wanted to share some of the tidbits I have learned:

Tableau Specific

  • Extracts allow you to work with your data really fast.
    • True story – in v6 an 80M record Tableau extract took > 30 mins to load. In v7, it took ~ 1 second.
  • The color of the pill matters. BLUE means discrete. GREEN means continuous.

  • Tableau can do inner, left, right join, or union, although Tableau usually doesn’t recommend unions, they don’t guarantee performance
  • Tableau works great with relational data, even flat files. Within the same connection, you can do JOINs on your tables, but within different connections you can BLEND. JOINs happen at the server (or source), BLENDs happen locally.

    Tableau JOIN

    Tableau BLEND

  • Ctrl + Dragging a pill allows you to copy the pill and repurpose
  • Actions are almost always faster than parameters
  • Quick filters are great, but use sparingly. When dashboarding, real estate is expensive (sounds like the Vancouver real estate market)
  • When adding reference lines, invoke reference line from axis you want to reference from
  • Annotations can be risky – might fly around when your data changes
  • Tableau tooltips look awesome by default.

Data Exploration and Visualization

  • Easy is hard. There’s a lot of work in making something easy.
  • Don’t be afraid to explore your data. Sometimes, you know which questions to ask, but sometimes you don’t. In either case, don’t be afraid to experiment, explore. You might be surprised at what you can discover.
  • Dan differentiates data into 3 Types:
    • Type 1 – Data you know (normal BI) – ex sales, profit etc
    • Type 2 – Data that comes from Type 1, is the blip, is the explanation to the question.
    • Type 3 – Data you needed to know that you didn’t know you needed to know; Real data discovery; Can usually be explored with scatter plots; This is where jaw dropping starts
  • Scatter plots are a great way to explore data.
  • If you’re serious about visualization, you’ll read Stephen Few’s books  and watch Hans Rosling TED talks. Also check out Deiter Rams and Iain McGilchrist‘s The Divided Brain on Youtube
  • When dashboarding – always ask yourself the question – what story am I trying to tell?

I am pretty excited to start working with Tableau. I plan to do more Tableau-related posts and tutorials in the future (of course incorporating all the tricks I’ve learned from this training session).

I work with SSRS a lot, and I can see how SSRS and Tableau can be great complements.

As a last bit, here’s my super awesome dashboard – done in 40 minutes and uploaded to Tableau public! (with lots of help from Tableau Jedi Dan Murray)

Go ahead, click on the image to play with it. Go ahead, I know you want to. :)

VN:F [1.9.22_1171]
Rating: 10.0/10 (4 votes cast)
VN:F [1.9.22_1171]
Rating: +2 (from 2 votes)

Get SQL Server 2012 SSRS Report Rendering Extensions Using PowerShell

There are new rendering extensions supported in SSRS 2012. Welcome extensions are the OPENXML rendering formats, to be used with Word 2007/2010 (.docx) and Excel 2007/2010 (.xlsx).

To list the rendering extensions, you can use the ReportViewer from the Reporting Services Redistributable Package.
You can download from here
http://www.microsoft.com/en-us/download/details.aspx?id=6442

 
[System.Reflection.Assembly]::Load("Microsoft.ReportViewer.WinForms, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91") | Out-Null
 
$ReportServer = "http://KERRIGAN:80/ReportServer"
$rv = New-Object Microsoft.Reporting.WinForms.ReportViewer
$rv.ProcessingMode = "Remote"
$rv.ServerReport.ReportServerUrl = $ReportServer
$rv.ServerReport.ListRenderingExtensions()

This should list all the currently supported extensions. In SSRS 2012 they are listed below. Note that both Word 2003 and Excel 2003 are technically still supported, but by default they are hidden from the export dropdown. The Excel and Word that we see in the export dropdown is the EXCELOPENXML and WORDOPENXML formats, which corresponds to the new .xlsx and .docx extensions.

Name LocalizedName Visible
XML XML file with report data True
NULL NullRenderer False
CSV CSV (comma delimited) True
ATOM Data Feed False
PDF PDF True
RGDI Remote GDI+ file False
HTML4.0
MHTML MHTML (web archive) True
EXCEL Excel 2003 False
EXCELOPENXML Excel True
RPL RPL Renderer True
IMAGE TIFF File True
WORD Word 2003 False
WORDOPENXML Word True
VN:F [1.9.22_1171]
Rating: 9.8/10 (8 votes cast)
VN:F [1.9.22_1171]
Rating: +2 (from 2 votes)

PowerShell Deep Dives / SQL Server PowerShell Cookbook

This has truly been a busy and exciting year for me. I’ve been blessed to have not one, but two books, in the works.

The PowerShell Deep Dives project is a community-written book on 300 level PowerShell topics. With topics ranging from administration, scripting, to PowerShell Platforms, there is definitely something for everyone. This book will be published by Manning, and all royalties will go to charity. Simply said, all the authors in this book will be contributing their time for charity.

I am very happy and humbled to say my abstract for a chapter for the PowerShell Deep Dives book has been accepted. To say that I will be part of this project is both an honor and a pleasure.

In no particular order, here are the authors of the PowerShell Deep Dives book.If I missed anyone, or if there are incorrect blogs/twitter handles, please let me know.

Author Chapter Topic Blog Twitter
Ed Wilson PowerShell Administration Script Center @ScriptingGuys
Don Jones PowerShell Administration PowerShell with a Purpose @concentrateddon
Richard Siddaway PowerShell Administration, PowerShell Scripting http://richardspowershellblog.wordpress.com/ @RSiddaway
Sean Kearney PowerShell Administration http://www.powershell.ca/ @energizedtech
Arnaud Petitjean PowerShell Administration http://www.powershell-scripting.com @apetitjean
Boe Prox PowerShell Administration http://learn-powershell.net/author/boeprox/ @proxb
Bartek Bielawski PowerShell Administration, PowerShell Scripting http://becomelotr.wordpress.com/author/bartekbielawski/ @bielawb
Mike Robbins PowerShell Administration http://mikefrobbins.com/ @mikefrobbins
Vadims Podans PowerShell Administration http://en-us.sysadmins.lv/ -
Ashley McGlone PowerShell Administration http://blogs.technet.com/b/ashleymcglone/ @goateepfe
James O’Neill PowerShell Scripting http://jamesone111.wordpress.com/ -
Jeffery Hicks PowerShell Scripting, PowerShell Platforms http://jdhitsolutions.com/blog/ @JeffHicks
Jonathan Medd PowerShell Scripting http://www.jonathanmedd.net/ @jonathanmedd
Jeff Wouters PowerShell Scripting http://jeffwouters.nl/ @JeffWouters
Adam Driscoll PowerShell Scripting http://csharpening.net/ @adamdriscoll
Richard Macdonald PowerShell Scripting http://blogs.msdn.com/b/richmac/ @_richmac
Kirk Munro PowerShell Scripting http://poshoholic.com/ @Poshoholic
Matthew Reynolds PowerShell Scripting - -
Tome Tanasovski PowerShell Scripting,PowerShell for Developers http://powertoe.wordpress.com/ @toenuff
Trevor Sullivan PowerShell for Developers http://trevorsullivan.net/category/powershell/ @pcgeek86
Josh Gavant PowerShell for Developers http://blogs.msdn.com/b/besidethepoint/ @joshugav
Karl Prosser PowerShell for Developers http://karlprosser.com/coder/ @karlprosser
Arvind Shyamsundar PowerShell for Developers http://blogs.msdn.com/b/arvindsh/ @arvisam
Jim Christopher PowerShell for Developers http://www.beefycode.com @beefarino
Grzegorz Gaêzowski PowerShell for Developers http://gsgalezowski.pl @gsgalezowski
Ben Miller PowerShell Platforms http://sqlblog.com/blogs/ben_miller/default.aspx @dbaduck
Donabel Santos PowerShell Platforms http://www.sqlmusings.com @sqlbelle
Robert C Cain PowerShell Platforms http://arcanecode.com/ @arcanecode
Chris Bellée PowerShell Platforms Hey Scripting Guys contributions -
Jason Helmick PowerShell Platforms http://www.jasonhelmick.com/ @theJasonHelmick
Jen Stirrup PowerShell Platforms http://www.jenstirrup.com/ @jenstirrup
Aaron Nelson PowerShell Platforms http://sqlvariant.com/ @sqlvariant
Doug Finke PowerShell Platforms http://www.dougfinke.com/ @dfinke

My other “baby” is SQL Server PowerShell Cookbook – a book that I have been writing for Packt for the last little while. This cookbook style book has a lot of PowerShell snippets specifically for SQL Server, and covers topics from monitoring SQL Server to using PowerShell with the SQL Server Business Intelligence (BI) stack.

VN:F [1.9.22_1171]
Rating: 9.8/10 (5 votes cast)
VN:F [1.9.22_1171]
Rating: +1 (from 1 vote)

Microsoft Private Cloud Sweepstakes – ends September 6 2012

Learn About The Microsoft Private Cloud to Win a Trip for Two to Mexico!

Microsoft has released new and exciting products that will change the way IT Pros utilize Virtualization and Microsoft Private Cloud solutions.   Two products which are a part of these great changes are the newly released System Center 2012 and the soon to be released Windows Server 2012.  Both of these solutions were designed to make virtualization and extending to the private cloud simpler and much more efficient.

With these new changes to Infrastructure and the IT world, it’s a great time to learn about these new solutions and keep yourself and your organization ahead of the curve in terms of where technology is headed.  In fact, Microsoft has even added an incentive to learn about their Private Cloud solutions through the Skyrocket Sweepstakes!

Entering is easy!  All you have to do is register, and then download a free TechNet evaluation like Windows Server 2012 RC or System Center 2012 to get started.  Every applicable evaluation you download gives you an entry into the sweepstakes! And the best part is the more evaluations you download, the better your chances.  And what’s the prize you may ask? Oh, just a 7 day, 8 night trip for two to Cozumel, Mexico!

The contest ends September 6th so don’t wait!  Register now!

 On Twitter? 

Try #Microsoft’s free #TechNet #Evaluations for a chance to win a trip to Mexico! http://bit.ly/MZCnjQ #SkyrocketSweepstakes

VN:F [1.9.22_1171]
Rating: 10.0/10 (2 votes cast)
VN:F [1.9.22_1171]
Rating: 0 (from 0 votes)

Thanks to everyone who attended PowerShell PowerHour!

I just wanted to say thanks to everyone who attended Idera’s PowerShell Power Hour. I’ve had a great time doing the presentation, and I hope you enjoyed it. Thank you for all the positive and constructive feedback! :)

The webcast is archived by Idera here:
http://www.idera.com/Events/RegisterWC.aspx?EventID=416

The scripts and slides can be downloaded from my site:
http://www.sqlmusings.com/presentations-and-webcasts/

VN:F [1.9.22_1171]
Rating: 10.0/10 (3 votes cast)
VN:F [1.9.22_1171]
Rating: +1 (from 3 votes)
`