Technology: SQL Server
Software Assurance Value:
This three-day instructor led course is about writing TSQL queries for the purpose of database reporting, analysis, and business intelligence. Specifically, this course presents TSQL within the context of data analysis – in other words, making meaning from the data rather than transaction-oriented data-tier application development.
The course starts with a brief discussion of levels of measurement and quantitative research methodology, and integrates these concepts into each TSQL topic presented. The goal is to provide a consistent, direct, and purposeful learning path for RDBMS data retrieval for use in analytical tools such as SQL Server Reporting Services, PowerBI, R, and Excel.
After completing this course, students will be able to:
This course is intended for information workers and data science professionals who seek to use database reporting and analysis tools such as Microsoft SQL Server Reporting Services, Excel, Power BI, R, SAS and other business intelligence tools, and wish to use TSQL queries to efficiently retrieve data sets from Microsoft SQL Server relational databases for use with these tools.
Module 1: Introduction to TSQL for Business Intelligence
This module discusses writing analytical queries vs. transactional DML queries, and describes the typical architecture of a business intelligence environment. It discusses the role of SELECT queries in retrieving data for analysis from relational databases. It introduces the sample database to be used in the course, and begins a presentation of the SELECT query.
Lab : Lab 1
After completing this module, students will be able to:
Module 2: Turning Table Columns into Variables for Analysis: SELECT List Expressions, WHERE, and ORDER BY
This module covers the identification of and relationship between levels of measurement and column data types. It continues a discussion of the SELECT query and adds the WHERE and ORDER BY clauses.
Lab : Lab 2- Write queries using:
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.
Lab : Lab 3- Write SELECT queries using:
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.
Lab : Lab 4- Write queries using:
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.
Lab : Lab 5- Write queries using:
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 in which these techniques are useful for producing datasets for analysis. Finally, it describes the database security issues involved, and techniques for creating and using these database objects while maintaining current permission sets on source data.
Lab : Lab 6
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 the client tool, in addition to exporting datasets to text files which can then be accessed by the client tool.
Lab : Lab 7