55232-A: Writing Analytical Queries for Business Intelligence Training Course

Module 1: Introduction to TSQL for Business Intelligence

This module discusses the differences between writing analytical queries and transactional DML (Data Manipulation Language) queries. It also describes the typical architecture of a business intelligence environment, emphasizing the role of SELECT queries in retrieving data for analysis from relational databases. The module introduces the sample database to be used throughout the course and begins a presentation on the SELECT query.

Lessons

• Two Approaches to SQL Programming for Government
• TSQL Data Retrieval in an Analytics Environment for Government
• The Database Engine for Government Operations
• SQL Server Management Studio and the CarDeal Sample Database for Government
• Identifying Variables in Tables for Government Analysis
• SQL as a Declarative Language for Government Use
• Introduction to the SELECT Query for Government Applications

Lab : Lab 1

• Create a database diagram for government use
• Create and execute basic SELECT queries for government data retrieval

After completing this module, students will be able to:

• Describe the purpose of analytical queries in government operations
• Explain the function of TSQL data retrieval in an analytics and business intelligence environment for government
• Describe the primary functions of the database engine in a government context
• Discuss TSQL as a declarative language suitable for government applications
• Identify variables of interest in database tables for government analysis
• Write basic SELECT queries for government data retrieval

Module 2: Turning Table Columns into Variables for Analysis: SELECT List Expressions, WHERE, and ORDER BY

This module covers the identification and relationship between levels of measurement and column data types. It continues the discussion on the SELECT query and introduces the WHERE and ORDER BY clauses to refine and organize query results.

Lessons

• Turning Columns into Variables for Analysis in Government
• Column Expressions, Data Types, and Built-in Functions for Government Use
• Column Aliases for Clarity in Government Queries
• Data Type Conversions for Government Data Integrity
• Built-in Scalar Functions for Government Applications
• Table Aliases for Simplifying Government Queries
• The WHERE Clause for Filtering Government Data
• ORDER BY for Organizing Government Query Results

Lab : Lab 2 - Write queries using:

• Column and table aliases for government data clarity
• DISTINCT to eliminate duplicates in government datasets
• WHERE to filter government data
• ORDER BY to organize government query results
• Built-in functions for government data manipulation
• Explicit and implicit data type conversion for government data integrity

After completing this module, students will be able to:

• Implement column expressions in SELECT queries for government analysis
• Use column and table aliases effectively in government queries
• Describe data types and implement data type conversions for government data integrity
• Utilize built-in functions in government TSQL queries
• Apply WHERE and ORDER BY clauses to refine and organize government query results

Module 3: Combining Columns from Multiple Tables into a Single Dataset: The JOIN Operators

Module 3 discusses creating single datasets for analysis by combining results from multiple database tables using JOIN operations. This is particularly relevant for government data integration and analysis.

Lessons

• Primary Keys, Foreign Keys, and Joins in Government Databases
• Understanding Joins, Part 1: CROSS JOIN and the Full Cartesian Product for Government Data
• Understanding Joins, Part 2: The INNER JOIN for Government Data Integration
• Understanding Joins, Part 3: The OUTER JOINS for Comprehensive Government Analysis
• Understanding Joins, Part 4: Joining More Than Two Tables in Government Databases
• Understanding Joins, Part 5: Combining INNER and OUTER JOINs for Complex Government Queries
• Combining JOIN Operations with WHERE and ORDER BY for Refined Government Data Analysis

Lab : Lab 3 - Write SELECT queries using:

• Inner join to integrate data from multiple government tables
• Left, right, and full join to handle incomplete government datasets
• Joins of more than two tables for complex government data integration
• Join operators in combination with WHERE and ORDER BY for refined government queries

After completing this module, students will be able to:

• Discuss and describe the conceptual flow of JOIN operations in government databases
• Implement INNER and OUTER JOIN operations on two or more tables for government data integration
• Explain the order of operations in SELECT clauses for government queries
• Combine JOIN operations with WHERE and ORDER BY to refine government query results

Module 4: Creating an Appropriate Aggregation Level Using GROUP BY

This module covers the aggregation of quantitative column values across grouping factors for the purpose of group-wise comparisons and/or changing the granularity of a dataset, which is essential for government data analysis.

Lessons

• Identifying Required Aggregation Level and Granularity in Government Data
• Aggregate Functions for Summarizing Government Data
• GROUP BY for Group-Wise Analysis in Government Databases
• HAVING for Filtering Aggregated Government Data
• Order of Operations in SELECT Queries for Government Data Analysis

Lab : Lab 4 - Write queries using:

• Aggregate functions to summarize government data
• Aggregate function with HAVING to filter aggregated government data
• Aggregate function with GROUP BY and HAVING for detailed government analysis
• Aggregate function with GROUP BY, HAVING, WHERE, and ORDER BY for comprehensive government queries

After completing this module, students will be able to:

• Describe the row granularity of result sets in government data analysis
• Discuss and implement aggregate functions to achieve required row granularity in government datasets
• Use GROUP BY to calculate aggregate values for groups in government data
• Use HAVING to filter records in the result set by aggregate value in government queries
• Combine GROUP BY and HAVING with WHERE and ORDER BY for comprehensive government data analysis

Module 5: Subqueries, Derived Tables, and Common Table Expressions

This module covers the use of subqueries, derived tables, and common table expressions in SELECT queries as techniques for creating intermediate result sets, which are crucial for advanced government data analysis.

Lessons

• Non-correlated and Correlated Subqueries for Government Data Analysis
• Derived Tables for Intermediate Results in Government Queries
• Common Table Expressions for Complex Government Data Analysis

Lab : Lab 5 - Write queries using:

• Non-correlated subqueries for government data retrieval
• Correlated subqueries for detailed government analysis
• Derived tables for intermediate results in government queries
• Common table expressions for complex government data analysis
• Subqueries, derived tables, and common table expressions combined with other TSQL topics for advanced government data retrieval

After completing this module, students will be able to:

• Describe and discuss the rationale for creating intermediate result sets within SELECT queries in government applications
• Implement non-correlated and correlated subqueries for government data analysis
• Implement derived tables for intermediate results in government queries
• Implement common table expressions for complex government data analysis
• Create intermediate to advanced TSQL queries to retrieve result sets for government analysis

Module 6: Encapsulating Data Retrieval Logic

This module discusses the encapsulation of data retrieval logic in views, table-valued functions, and stored procedures. It also describes scenarios where these techniques are useful for producing datasets for analysis in a government context. The module covers database security issues involved and techniques for creating and using these objects while maintaining current permission sets on source data.

Lessons

• Views for Simplifying Government Data Retrieval
• Table-Valued Functions for Dynamic Government Queries
• Stored Procedures for Complex Government Operations
• Creating Objects for Read-Access Users in Government Databases
• Creating Database Accounts for Analytical Client Tools in Government

Lab : Lab 6

• Create a SQL login for government use
• Create a database user and assign required permissions for government data access
• Create a database schema for views, functions, and stored procedures in government databases
• Create a view for government data retrieval
• Create a table-valued function for dynamic government queries
• Create a stored procedure for complex government operations
• Allow a user with read-only access to use views, table-valued functions, and stored procedures in government databases

After completing this module, students will be able to:

• Identify scenarios where views, table-valued functions, and stored procedures simplify data retrieval for government applications
• Compare and contrast views, table-valued functions, and stored procedures in a government context
• Create views, table-valued functions, and stored procedures for government data analysis
• Describe the security requirements for creating database objects in government databases
• Implement views, table-valued functions, and stored procedures for users with read-only access to source data in a government context

Module 7: Getting Your Dataset to the Client

This module covers common techniques for making datasets produced by SELECT queries available to analytical client tools such as SQL Server Reporting Services, PowerBI, Excel, and R. It discusses running queries directly from these client tools and exporting datasets to text files that can be accessed by the client tools.

Lessons

• Connecting to SQL Server and Submitting Queries from Client Tools for Government
• Connecting and Running SELECT Queries from:
• Excel for Government Data Analysis
• PowerBI for Government Data Visualization
• RStudio for Government Data Science
• Exporting Datasets to Files Using:
• The Results Pane from SQL Server Management Studio (SSMS) for Government
• The bcp Utility for Efficient Data Export in Government
• The Import/Export Wizard for Government Data Transfer

Lab : Lab 7

• Retrieving the results of a view in Excel for government data analysis
• Running an ad-hoc SELECT query from Excel for government data retrieval
• Running an ad-hoc query from PowerBI for government data visualization
• Running an ad-hoc query from RStudio for government data science
• Using the Import/Export Wizard to write the results of a query to a text file for government data transfer

After completing this module, students will be able to:

• Describe the properties of database connection strings in government applications
• Run queries from and return results to Excel, PowerBI, and RStudio for government data analysis
• Export query results to external text files using the SSMS results pane, the bcp utility, and the Import/Export Wizard for government data transfer