Writing Analytical Queries for Business Intelligence Courseware (55232AC)

This three-day course begins with an introduction to TSQL for Business Intelligence. The focus is on analytical queries as opposed to transactional DML queries, providing students an understanding of the architecture of a business intelligence environment and the role of SELECT queries in data retrieval. It also imparts knowledge about identifying variables in tables and viewing SQL as a declarative language.

Following the introductory module, the course shifts towards turning table columns into variables for analysis, elaborating on column expressions, data types, and built-in functions. It furthers the exploration of SELECT queries by incorporating WHERE and ORDER BY clauses. Students learn to handle data types and conversions, use built-in functions, and implement column and table aliases.

The next phase of the course teaches students how to combine columns from multiple tables into a single dataset using JOIN. It progressively covers various types of joins, starting with CROSS JOIN, then delving into INNER JOIN and OUTER JOINS, and finally exploring multiple table joins and combinations of INNER and OUTER JOINS. An important aspect of this module is to understand the interplay of JOIN operations with WHERE and ORDER BY clauses.

The course then addresses the concept of creating appropriate aggregation levels using GROUP BY. This module helps students identify the required aggregation level and granularity. It delves into the use of aggregate functions, GROUP BY and HAVING clauses, and the order of operations in SELECT queries.

Subsequently, the course moves to cover subqueries, derived tables, and common table expressions, giving students the ability to create intermediate result sets. It covers both non-correlated and correlated subqueries, explaining their use and importance in creating sophisticated queries.

The second-to-last module encapsulates data retrieval logic, discussing the use of views, table-valued functions, and stored procedures. It elaborates on database security issues, the creation of database objects, and maintaining permission sets on source data.

The final module of the course addresses techniques for making datasets produced by SELECT queries available to analytical client tools like SQL Server Reporting Services, PowerBI, Excel, and R. It explores running queries directly from client tools, as well as exporting datasets to text files.