Writing Analytical Queries for Business Intelligence (55232)

Course Overview

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

Prerequisites

Before attending this course, students must have:

• Context knowledge of data analysis and business intelligence scenarios.  For example, an understanding of a work-related business intelligence project or need.
• Basic knowledge of the Windows operating system and its core functionality, including file system navigation.
• Basic understanding of the purpose of relational database management systems such as SQL Server.