sqlbelle’s data adventures (Tableau/SQL Server tutorials on YouTube)

It’s been a while since I blogged (life got busy!), and am quite surprised to see that some of the posts I had are still getting traction. I guess some issues still persist – perhaps in slightly different forms, but the way we solve or tackle them remain the same.

Excited to share many things since I last blogged, but one of the biggest ones happened in the last couple of months. With the pandemic I had to switch to teaching exclusively online, and really re-think how I deliver my classes.

I’ve decided to try a new avenue to teach – YouTube! I’ve started my channel – sqlbelle’s data adventures – and hoping this can provide value and/or inspiration to those trying to learn data and technologies like Tableau and SQL Server. It’s been fun, but definitely requires commitment.

Here are some of my videos to date (YouTube: sqlbelle’s data adventures):

Initial focus has been with Tableau, but definitely want to add a lot of SQL Server (and MySQL) in this library.

If you’re not familiar with Tableau yet, you can start with this video to have a general idea how to create charts! This is just the tip of the iceberg.

This shows quick tutorials on creating charts like bar chart, line chart, area chart, scatter plot, pie chart, maps, text tables, heat map, highlight tables and many others.

Getting maximum consecutive years in T-SQL using Common Table Expressions (CTE)

This is an expansion (and slight variation) of Joe’s answer in StackOverflow regarding the thread Find the maximum consecutive years for each ID’s in a table (Oracle SQL) using SQL Server. I’ve tested this in SQL Server 2014, but should work from SQL Server 2005 onwards where the ranking functions are supported.

The problem Joe solved is not uncommon – i.e. how to get the consecutive years of something (consecutive year sales, consecutive years in school, consecutive years volunteering etc), and his solution is pretty clever.

Let’s assume you are working on a sales database. You have multiple clients who could be purchasing from your store several times a year, or a couple of times every five years. You may want to know what’s the maximum consecutive years they’ve purchased from you. Why? Perhaps in a marketing campaign, you may want to give your loyal customers (purchased in 5 or more consecutive years) a special discount.

Your data may look like the following screenshot. Notice that in this example, Client 00001, 00002 and 00003 purchased only once. Client 00004 purchased several times, and it looks like there were purchases in consecutive years.

Download sample T-SQL script to create and populate the SAMPLESALES table

Let’s take it step by step to understand both the problem and solution better. It will be easy to visually identify which clients have purchased consecutive years if we first display all the unique years that client has purchased:

SELECT 
    DISTINCT
    CLIENTID,
    CLIENTNAME,
    YEAR(REVENUEDATE) REVENUEYEAR
FROM
    SAMPLESALES

Here’s the result:

Here we can see Client 00004 purchased in 2004 and 2005 (2 consecutive years), but did not purchase in 2006. Starting 2007, Client 0004 started purchasing again every year until 2012 (6 consecutive years).

More reasons why I teach

This is my fuel. This is why I teach. Sure, I won’t please everyone. Not everyone will appreciate or will be happy with the way I teach, but even if there’s one person in the class who thinks the time they spent in my class was worth it – that’s all the reason I need.

(All names removed from cards below)

This is what I got today from a student:

Automate SSRS Report Generation using PowerShell

This blog post is tested on SQL Server 2014 and PowerShell V5.

PowerShell has become more feature-rich in every version. We are now looking at PowerShell V5 (currently can be downloaded as part of Windows Management Framework v5 April 2015 Preview).

The support landscape with SQL Server hasn’t changed much. There isn’t a drastic increase in SQL Server cmdlets. However, the language and feature improvements in PowerShell in general improve how we can work with SQL Server.

One area where we can use PowerShell is with SQL Server Reporting Services (SSRS). I blogged about this a while back, but it’s time to revisit and expand on how we can use PowerShell to automate report generation.

In this blog post I will focus on generating PDF reports via scripting. Let’s tackle this piece by piece first, and we’ll put everything in a nice little script at the end of the post.

Windows 10 and Visual Studio 2015 Jumpstart and Resources

Get Started. Design. Develop.Publish

Windows 10 and Visual Studio 2015 have been released.

Microsoft claims Windows 10 as the best OS yet and Visual Studio 2015 has been enriched with ability to work on desktop, mobile (Windows, Android, and iOS), web and cloud applications and services. (It’s pretty sweet that there is a free Visual Studio Community version too!)

Here are some resources you’ll need to get started:

• Download all the developer tools (Visual Studio 2015), learn more about the features, and access the documentation
• Find out what’s new for developers in Windows 10
• Get started with Windows 10 using Microsoft Virtual Academy (MVA) courses
• Learn about the Top 10 Windows 10 features from Channel9 

Once you have the tools and gears in place, have fun!

Discovering SSRS Report Parameters using PowerShell

We can use PowerShell to discover many SSRS properties, including the different parameters available in a report. This blog post is based on SQL Server 2014 and PowerShell V5.

Here is an example:

SSRS parameters in SSDT Preview

This particular report utilizes different variations of parameters. To review, here are the available data types for SSRS report parameters:

SSRS Parameter Data Types

In the example above, Customer ID is an integer. Order Date From and To are dates. Online Flag is a boolean. Min Total Due is a float. Status is a multi-value text.

Conversations with Impact – the importance of stories

I feel quite lucky to be working where I work. One of the things I like about working at a university is having access to a number of learning opportunities, be it credit or not-for-credit courses, or even 1 or 2 hour sessions on anything. I love learning so it’s a field day for me when I get to attend these courses or events.

Conversations with Impact

by Gareth Williams. Cuba – Havana Public Artwork. https://www.flickr.com/photos/gareth1953/15377856386/

One session I recently attended was “Conversations With Impact”. Ms. Joanna Piros,a senior consultant with Counterpoint Communications and also faculty at UBC Sauder School of Business’s Executive Education program, was the one who delivered the one hour session.

At the beginning of the session, Ms. Piros emphasized three (3) key statements about conversations:
1. All communication is strategic
2. You must frame others before you frame yourself (I didn’t know what media framing meant before I attended this session)
3. You must tell stories

This definitely struck a chord with me because one of the courses I teach, Visual Analytics with Tableau, also revolves around telling stories with data.

Upcoming SQL Server Events – SQL Saturday #407 Vancouver and 24HOP

A couple of upcoming SQL Server events:

24HOP

24HOP GOC 2015 (or 24 hours of PASS Growing Our Community 2015) is happening June 24-25, 2015. This event is a 24 hour event, with a session happening every hour delivered by SQL Server and Business Intelligence (BI) experts from around the world. The list of sessions are posted 24HOP 2015.

Application & Database Development

• Common Table Expressions – Advanced
• Database Design: Solving Problems Before they Start!
• Every Byte Counts: Why Your Data Type Choices Matter
• Introduction to Microsoft Azure DocumentDB
• Offline Database Development and Unit Testing with SSDT
• Protecting Your Data with Encryption

BI Information Delivery

• Introduction to the Power BI Designer
• Understanding and Visualizing Data Using R in SQL Server

BI Platform Architecture, Development & Administration

• Don’t Repeat Yourself – An Introduction to Agile SSIS Development
• Improving Data Analytics with Azure Machine Learning
• Optimizing your data warehouse for OLAP Processing
• Rethink your ETL with BIML
• SSAS Design and Performance Best Practices
• Cloud Application Development & Deployment
• Azure IOT: A Practical Introduction to Stream Analytics

Enterprise Database Administration & Deployment

• Common SQL Server Mistakes and How to Avoid Them
• Curious Cases of Availability Groups
• Data Pages, Allocation Units, IAM chains… Oh My!
• Overlord – tracking changes, real-time, in your environment
• SQL Server Agent: The life preserver for the drowning DBA
• The Art and the Science of Designing a Mission Critical SQL Server Solution
• The Data Loading Performance Presentation
• Trend Analysis of SQL Error Logs: Seeing Beyond the Error
• Turbo-Charged Transaction Logs

Professional Development

• Making the Leap from Developer to DBA

SQLSaturday #407 in Vancouver, BC

SQLSaturday #407 in Vancouver, BC is happening on June 27, 2015 at UBC Robson Square, 800 Robson Street, Vancouver. SQLSaturdays are one day free training events for SQL Server professionals. SQL Server professionals have developed a pretty tight knit community over the years, and this is one way of sharing information, learnings, experiences and best practices on the product. This event is also volunteer-driven – the organizers and presenters volunteer their time and effort to help foster the #SQLCommunity.

Sessions delivered range from SQL Server Administration, Development, Business Intelligence Application, Business Intelligence Delivery, and Professional Development.

Register now!

SQLSaturday #407

Check out the schedules and sessions here: http://www.sqlsaturday.com/407/Sessions/Schedule.aspx

SQLXML: How to get related role ad report from a Blackbaud Task (TASKPECXML)

When working with Blackbaud Enterprise CRM, you may create tasks that point to a report. Here’s how to extract the related report and roles from a task:

;WITH XMLNAMESPACES ('http://www.w3.org/2001/XMLSchema-instance' AS xsi,
                     'http://www.w3.org/2001/XMLSchema' AS xsd,
             'bb_appfx_commontypes' AS common,
             'bb_appfx_pagedefinition' AS pagedef,
              DEFAULT 'bb_appfx_task'), 
taskcte AS 
(
SELECT  
   TC.NAME TASKNAME,
   SR.NAME ROLENAME, 
   R.SYSTEMROLEID, 
   TC.TASKSPECXML,
   TC.TASKSPECXML.value('(//TaskSpec/common:ShowReport/@ReportID)[1]', 'varchar(100)') REPORTID_FROMTASKSPEC,
   TC.TASKSPECXML.value('(//TaskSpec/common:ShowPage/@PageID)[1]', 'varchar(100)') PAGEID 
FROM
    V_SECURITY_SYSTEMROLEASSIGNMENT_USER_TASK R
    INNER JOIN SYSTEMROLE SR
    ON R.SYSTEMROLEID = SR.ID
    INNER JOIN TASKCATALOG TC
    ON R.TASKID = TC.ID
)

SELECT * 
FROM taskcte

Girls do science – and @Microsoft #Recruiting2027 awaits

Just read this article from Business Insider entitled This viral video from Microsoft on how girls feel about science is fantastic.

It’s awesome. Loved it. Spoiler alert – at the end of the video, the girls get a card from Microsoft that encourages them to keep on doing what they do:

Thanks @Microsoft for encouraging girls to do science.