Writing Analytical Queries for Business Intelligence (MCC-55232)


Course Number: MCC-55232

Duration: 3 days (19.5 hours)

Format: Live, hands-on

BI Training Overview

This Writing Analytical Queries for Business Intelligence training, Microsoft Community Course 55232, teaches students how to write T-SQL queries for the purpose of database reporting, analysis, and business intelligence. This BI course provides a consistent, direct, and purposeful learning path for RDBMS data retrieval for use in analytical tools such as SQL Server Reporting Services, Power BI, R, and Excel.

Location and Pricing

Accelebrate offers instructor-led enterprise training for groups of 3 or more online or at your site. Most Accelebrate classes can be flexibly scheduled for your group, including delivery in half-day segments across a week or set of weeks. To receive a customized proposal and price quote for private corporate training on-site or online, please contact us.

In addition, some courses are available as live, online classes for individuals. See a schedule of online courses.

Objectives

  • 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 T-SQL SELECT queries to produce ready-to-use data sets for analysis in tools such as Power BI, SQL Server Reporting Services, Excel, R, SAS, SPSS, and others
  • Create stored procedures, views, and functions to modularize data retrieval code

Prerequisites

Students should have:

  • Context knowledge of data analysis and business intelligence scenarios. For example, an understanding of a work-related business intelligence project or need
  • Basic knowledge of the Windows operating system and its core functionality, including file system navigation
  • Basic understanding of the purpose of relational database management systems such as SQL Server

Outline

Expand All | Collapse All

Introduction to TSQL for Business Intelligence
  • Two Approaches to SQL Programming
  • T-SQL 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
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
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
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
Subqueries, Derived Tables and Common Table Expressions
  • Non-correlated and correlated subqueries
  • Derived tables
  • Common table expressions
Encapsulating Data Retrieval Logic
  • Views
  • Table-valued functions
  • Stored procedures
  • Creating objects for read-access users
  • Creating database accounts for analytical client tools
Getting Your Dataset to the Client
  • Connecting to SQL Server and Submitting Queries from Client Tools
  • Connecting and running SELECT queries from:
  • Excel
  • Power BI
  • RStudio
  • Exporting datasets to files using
  • Results pane from SSMS
  • The bcp utility
  • The Import/Export Wizard
Conclusion

Training Materials:

All BI training students receive Microsoft official courseware.

Software Requirements:

Attendees will not need to install any software on their computer for this class. The class will be conducted in a remote environment that Accelebrate will provide; students will only need a local computer with a web browser with a stable Internet connection. Any recent version of Internet Explorer, Mozilla Firefox, or Google Chrome will be fine.

When you contact us about purchasing this class, we will provide a live demo of the online lab environment so that you may explore the web browser interface in more detail.



Learn faster

Our live, instructor-led lectures are far more effective than pre-recorded classes

Satisfaction guarantee

If your team is not 100% satisfied with your training, we do what's necessary to make it right

Learn online from anywhere

Whether you are at home or in the office, we make learning interactive and engaging

Multiple Payment Options

We accept check, ACH/EFT, major credit cards, and most purchase orders



Recent Training Locations

Alabama

Birmingham

Huntsville

Montgomery

Alaska

Anchorage

Arizona

Phoenix

Tucson

Arkansas

Fayetteville

Little Rock

California

Los Angeles

Oakland

Orange County

Sacramento

San Diego

San Francisco

San Jose

Colorado

Boulder

Colorado Springs

Denver

Connecticut

Hartford

DC

Washington

Florida

Fort Lauderdale

Jacksonville

Miami

Orlando

Tampa

Georgia

Atlanta

Augusta

Savannah

Hawaii

Honolulu

Idaho

Boise

Illinois

Chicago

Indiana

Indianapolis

Iowa

Cedar Rapids

Des Moines

Kansas

Wichita

Kentucky

Lexington

Louisville

Louisiana

New Orleans

Maine

Portland

Maryland

Annapolis

Baltimore

Frederick

Hagerstown

Massachusetts

Boston

Cambridge

Springfield

Michigan

Ann Arbor

Detroit

Grand Rapids

Minnesota

Minneapolis

Saint Paul

Mississippi

Jackson

Missouri

Kansas City

St. Louis

Nebraska

Lincoln

Omaha

Nevada

Las Vegas

Reno

New Jersey

Princeton

New Mexico

Albuquerque

New York

Albany

Buffalo

New York City

White Plains

North Carolina

Charlotte

Durham

Raleigh

Ohio

Akron

Canton

Cincinnati

Cleveland

Columbus

Dayton

Oklahoma

Oklahoma City

Tulsa

Oregon

Portland

Pennsylvania

Philadelphia

Pittsburgh

Rhode Island

Providence

South Carolina

Charleston

Columbia

Greenville

Tennessee

Knoxville

Memphis

Nashville

Texas

Austin

Dallas

El Paso

Houston

San Antonio

Utah

Salt Lake City

Virginia

Alexandria

Arlington

Norfolk

Richmond

Washington

Seattle

Tacoma

West Virginia

Charleston

Wisconsin

Madison

Milwaukee

Alberta

Calgary

Edmonton

British Columbia

Vancouver

Manitoba

Winnipeg

Nova Scotia

Halifax

Ontario

Ottawa

Toronto

Quebec

Montreal

Puerto Rico

San Juan


© 2013-2021 Accelebrate, Inc. All rights reserved. All trademarks are owned by their respective owners.
This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.