Course Overview
It is a course provided by Microsoft and offered by CounselTrain Technologies. This course focuses on teaching participants how to design and implement analytical queries for business intelligence solutions using SQL Server. It covers the use of various analytical functions, such as ranking, windowing, and aggregation, to extract useful insights from data and to support decision-making processes.
The course is designed for database professionals who have experience working with SQL Server and want to learn more about writing analytical queries for business intelligence applications.
Here is a brief overview of the topics covered in the course:
– Introduction to analytical queries and business intelligence
– Using ranking functions to sort and filter data
– Using windowing functions to analyze data subsets
– Using aggregate functions to summarize data
– Using PIVOT and UNPIVOT to transform data
– Using GROUPING SETS and CUBE to generate cross-tabular reports
– Using T-SQL to query data in a hierarchical structure
– Using the APPLY operator to join data sets
– Using Common Table Expressions (CTEs) to simplify complex queries
– Using the OVER clause to partition and order data
The course also includes hands-on labs and exercises to practice the concepts taught in the course. At the end of the course, participants should be able to design and implement analytical queries for business intelligence solutions using SQL Server and should have a better understanding of how to extract insights from data to support decision-making processes.
Learning Options
Target audiences
- The target audience for the Writing Analytical Queries for Business Intelligence training (Course 55232-A) is data analysts, data scientists, and business intelligence professionals
- This course is designed to help those who need to develop and understand the kinds of analytics queries required to gather business-critical insights
- It is also suitable for individuals with a basic familiarity with SQL and/or an understanding of database system architecture, schema design, and file structures
- This course is designed to help participants understand the fundamentals of analytical query writing, decision making, and optimization in order to analyze data and make informed business decisions
Schedule Dates
Curriculum
-
Module 1: Introduction to TSQL for Business Intelligence
-
Module 2: Turning Table Columns into Variables for Analysis: SELECT List Expressions, WHERE, and ORDER BY
-
Module 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 3- Write SELECT queries using:
-
Module 4: Creating an Appropriate Aggregation Level Using GROUP BY
-
Module 5: Matrix Reports
-
Module 6: Charts
-
Module 7: Printing and Exporting Reports
- Basic knowledge of relational databases and SQL
- Familiarity with the Microsoft Windows operating system and its core functionality
- Familiarity with data warehousing concepts and multidimensional data models
Additionally, participants should have experience working with SQL Server and creating basic Transact-SQL (T-SQL) queries. It is also recommended that participants have completed the following courses or have equivalent knowledge:
- 20761A: Querying Data with Transact-SQL
- 20762A: Developing SQL Databases
- 20767A: Implementing a SQL Data Warehouse
Having these prerequisites and prior knowledge will help participants better understand the concepts presented in the course and get the most out of the training.
