55232 Writing Analytical Queries for Business Intelligence
- 3 Days Course
- Language: English
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
$2,800.00