55232 Writing Analytical Queries for Business Intelligence

Introduction:

This course is about writing TSQL queries for the purpose of database reporting, analysis, and business intelligence.

Objectives:

After completing this course, students will be able to:
– Identify independent and dependent variables and measurement levels in their own analytical work scenarios.
– Identify variables of interest in relational database tables.
– Choose a data aggregation level and data set design appropriate for the intended analysis and tool.
– Use TSQL SELECT queries to produce ready-to-use data sets for analysis in tools such as PowerBI, SQL Server Reporting Services, Excel, R, SAS, SPSS, and others.
– Create stored procedures, views, and functions to modularize data retrieval code.

Course Outline:

1 – INTRODUCTION TO TSQL FOR BUSINESS INTELLIGENCE

  • Two Approaches to SQL Programming
  • TSQL Data Retrieval in an Analytics / Business Intelligence Environment
  • The Database Engine
  • SQL Server Management Studio and the CarDeal Sample Database
  • Identifying Variables in Tables
  • SQL is a Declarative Language
  • Introduction to the SELECT Query
  • Lab 1: Introduction to TSQL for Business Intelligence

2 – TURNING TABLE COLUMNS INTO VARIABLES FOR ANALYSIS: SELECT LIST EXPRESSIONS, WHERE, AND ORDER BY

  • Turning Columns into Variables for Analysis
  • Column Expressions, Data Types, and Built-in Functions
  • Column aliases
  • Data type conversions
  • Built-in Scalar Functions
  • Table Aliases
  • The WHERE clause
  • ORDER BY
  • Lab 1: Write queries

3 – COMBINING COLUMNS FROM MULTIPLE TABLES INTO A SINGLE DATASET: THE JOIN OPERATORS

  • Primary Keys, Foreign Keys, and Joins
  • Understanding Joins, Part 1: CROSS JOIN and the Full Cartesian Product
  • Understanding Joins, Part 2: The INNER JOIN
  • Understanding Joins, Part 3: The OUTER JOINS
  • Understanding Joins, Part 4: Joining more than two tables
  • Understanding Joins, Part 5: Combining INNER and OUTER JOINs
  • Combining JOIN Operations with WHERE and ORDER BY
  • Lab 1: Write SELECT queries

4 – CREATING AN APPROPRIATE AGGREGATION LEVEL USING GROUP BY

  • Identifying required aggregation level and granularity
  • Aggregate Functions
  • GROUP BY
  • HAVING
  • Order of operations in SELECT queries
  • Lab 1: Write queries

5 – SUBQUERIES, DERIVED TABLES AND COMMON TABLE EXPRESSIONS

  • Non-correlated and correlated subqueries
  • Derived tables
  • Common table expressions
  • Lab 1: Write queries

6 – ENCAPSULATING DATA RETRIEVAL LOGIC

  • Views
  • Table-valued functions
  • Stored procedures
  • Creating objects for read-access users
  • Creating database accounts for analytical client tools
  • Lab 1: Encapsulating Data Retrieval Logic

7 – GETTING YOUR DATASET TO THE CLIENT

  • Connecting to SQL Server and Submitting Queries from Client Tools
  • Connecting and running SELECT queries from:
  • Excel
  • PowerBI
  • RStudio
  • Exporting datasets to files using
  • Results pane from SSMS
  • The bcp utility
  • The Import/Export Wizard
  • Lab 1: Getting Your Dataset to the Client

Enroll in this course

$1,785.00

Need Help Finding The Right Training Solution?

Our training advisors are here for you.

USD United States (US) dollar