Data Warehouse Developer-SQL Server/ETL/SSIS/SSAS/SSRS + AI

Download and install Visual Studio 2022 Community edition, select the data storage and processing workload, exclude the Azure data lake option, and verify the installation from the start menu.

Launch Visual Studio 2022 from the start menu, sign in with a Microsoft account if available, and set a dark or blue theme.

Install SQL Server Data Tools template extensions in Visual Studio to enable integration services, reporting services, and analysis services projects, then verify by creating new projects.

Understand how a data warehouse, a central repository, centralizes data from multiple sources, enabling integrated analysis to answer business questions about customers, products, and locations.

Compare database and data warehouse architectures to highlight OLTP versus OLAP, and how normalization versus denormalization affects write performance and analytical query speed.

Differentiate between a data warehouse and an enterprise data warehouse, showing when a solution serves a single department versus the entire organization.

Assess hardware requirements for a data warehouse by evaluating network bandwidth, memory, processor, disk capacity, and plan for drip feeding ETL with memory caching during 24-hour operations.

Open SQL Server Management Studio and set advanced options to 1 to enable the SQL Server agent extended stored procedures, then refresh to reveal the agent immediately.

Check and adjust database settings in SQL Server Management Studio to prepare a data warehouse, focusing on recovery model, separating logs from data, and tempdb sizing and performance.

Explore the ftdw sizing tool, an Excel spreadsheet from Microsoft that estimates data warehouse hardware needs and lets you input users, queries, and data volume.

Learn the two main data warehouse tables: fact and dimension, and how they enable filtering, grouping, and time- and location-based analysis for business reports.

Explore the physical design of a data warehouse with a snowflake technique and learn to implement views in SQL Server to simplify joining geography and sales territory dimension tables.

Create a view to aggregate fact internet sales with sum totals, grouping by order date, customer key, and currency key, and index with schema binding for faster pre-aggregation.

Explore designing dimension tables with star and snowflake techniques, shown in Adventureworks diagrams. Star centers a fact table with surrounding dimensions for performance; snowflake adds related dimensions with greater complexity.

Extract, transform, and load data from multiple sources via a staging area, transforming and cleansing it before loading into a destination data store or warehouse.

Explore sql server integration services (ssis), a Microsoft platform for enterprise data integration and transformation, enabling extract, transform, and load from Xml, flat files, and relational data.

Design a data warehouse with an ETL process using SSIS. Extract data from multiple sources, transform it, and load it while adapting to data changes.

Start by verifying SQL Server services are running, then create a new Integration Services project in Visual Studio, configure the project and solution names, and rename the default package.

Explore a new data source with SSIS data profiling to assess column length distribution and null ratios, connecting to AdventureWorks and profiling the dim customer columns.

Use the data profile viewer to run a data profiling task, inspect column length distribution, and assess null rates and the sports column type for data warehouse design.

Master control flow in SQL Server Integration Services by building a for loop with a variable initialized to zero and an x < 4 condition that increments each iteration.

Explore SSIS control flow by using for loop and for each loop containers, collection options, and expression tasks with precedence constraints to branch based on a variable X.

Create an SSIS data flow that extracts from a flat file and loads into a SQL Server destination, using blue arrows for good data and truncating the target table.

Implement a data flow by truncating a table, resolving Unicode issues, and converting data types from a flat file source to an Ole DB destination using data conversion.

Open a reseller ssis package in visual studio and examine data flow components (flat file source, data conversion, ole db destination) while using breakpoints to pause and inspect variables.

Enable the data viewer on the data flow arrow to inspect rows as they move from data conversion to destination, pausing to observe total rows and buffers.

Learn how to configure SSIS package event logging to text files or SQL Server tables, including error and execution-status tracking for real-time troubleshooting.

Handle errors in an SSIS package by redirecting bad rows to a separate destination, so the ETL process continues. Use a flat file source and a SQL Server destination.

Learn incremental ETL by splitting extract, transform, and load into smaller packages. Use per data source or per step packaging to ease maintenance and isolate issues with a staging database.

Extract modified data using three techniques: a date-time column, change data capture, and change tracking in SQL Server; CDC stores before/after rows, change tracking stores only keys.

Explore extracting modified data by enabling change data capture and change tracking, both at the database level and for specific tables, using SQL Server Management Studio and stored procedures.

Illustrate extracting modified data via change data capture (CDC) on the Adventureworks 2014 database, focusing on the Dim Account table and its dim_account_change table to track before and after changes.

Enable change tracking at the database and table levels using SQL Server Management Studio or a script, and test changes on the dim account table to verify tracking.

Explore loading modified data in dimension tables, distinguishing static from dynamic data and applying slowly changing dimensions, including type 1, type 2, and historical tracking.

Compare type 1, type 2, and type 3 slowly changing dimensions for loading modified data, and learn how type 3 uses current and original value columns with an effective date.

Explore slowly changing dimensions, including type zero, four, five, six, and seven, and compare them with type one, two, and three for managing current and historical data.

Create an integration services catalog for SQL Server 2012 to deploy a project with packages, secured by an encryption key. Configure retention and server logging levels, recommending performance for production.

Deploy and validate a SQL Server Integration Services package by building the reseller package, deploying via the wizard to a subfolder in the SSIS catalog, and reviewing execution reports.

Learn to execute an ETL package with SQL Server Agent by creating a job, adding SSIS package steps, scheduling at 2 a.m. daily, and configuring notifications and error logs.

Configure advanced options in the integration services catalog using properties, single user mode, and a stored procedure to set the encryption algorithm, then switch back to multi user mode.

Install SQL data quality services, set up the data quality server and client, and create the main, projects, and staging databases with the DCS installer.

Learn how data quality services cleanses data from multiple sources by building a data quality project, selecting a knowledge base, and validating and approving suggested corrections.

Learn to find duplicates with data quality services by creating a knowledge base, defining a matching policy, mapping fields, and publishing rules to SQL Server data quality services.

Create a new data quality project using the customer match knowledge base to find duplicates, review matches with a minimum score of 80, and export results or survivorship data.

Apply data quality services in an ssis data flow to cleanse a csv. Use a knowledge base for state mapping and observe auto-suggestions and new values.

Explore how business intelligence turns raw data into meaningful insights to measure business health, support decisions, and use data warehouses with SQL Server Reporting Services and SQL Server Analysis Services.

Create a new SQL Server Reporting Services project in Visual Studio 2019 using the Reporting Server Project Wizard. Configure the project name, solution name, and location.

Create a view from a fact table and three dimension tables, design an SSRS report in SQL Server Data Tools with customer-based grouping, showing order quantity and total product cost.

Learn to design and customize SQL Server Reporting Services reports for a data warehouse, add columns and totals, apply grouping by state, and preview formatted, professional results.

Create a new SSAS project in Visual Studio 2019 by selecting the analysis template from the startup screen, then configure the project name, solution, and location.

Create a Microsoft SQL Server Analysis Services data cube using a data source, data source view, and fact internet sales plus dimension tables; deploy, publish, and process the cube.

Explore SSAS cubes to analyze data, browse measures and dimensions, and view grand totals and subtotals by customer and promotion, improving reporting performance on data warehouse.

Explore basic database concepts, including tables, rows and columns, records, and objects, and learn how schemas, primary keys, and foreign keys shape relational databases and RDBMS like SQL Server.

Execute create, read, update, and delete operations on databases and tables. Build a Python front-end to interact with a SQL Server database.

Explore SQL Server data types, including string and unicode (char, varchar, text, nchar, nvarchar, ntext), binary, numeric (bit, int, decimal), and date and time types (date, datetime, time, datetimeoffset).

Learn how to create a new database in T-SQL by writing the create database statement, naming the database (unique, up to 128 characters), and executing the query.

Create a new table using T-SQL by selecting the database, defining columns (product id as primary key, product name, price, product description), and executing the create statement.

Learn how to insert data into a table using T-SQL by specifying the database, table, and column list, then provide corresponding values to insert multiple records.

Learn to read data from a table using T-SQL, including selecting specific or all columns, applying a where clause, and understanding the result set.

Create views as virtual tables based on the result set of an SQL statement; they can use one or more tables and stay up to date on every query.

Create and test a basic transact-sql stored procedure with an input money parameter, a begin-end batch, and conditional flow to run against a view.

Back up a SQL Server database using t-sql with a full backup (including transaction logs) to an external USB drive, illustrating the command and file naming.

Learn how to delete records in a database using t-sql, including a where clause to delete a single record whose product id equals four and avoid removing all rows.

Learn how to truncate a database table in SQL Server, removing all rows while keeping the table structure intact. Verify data with a before-and-after select.

Use the drop database statement to delete a database, noting all information and tables are lost, and ensure you are not connected to it by selecting master first.

Learn how to restore a database from a backup using the restore database statement, specify the database name and backup file path from a USB drive, and verify the restore.

Sign up for ChatGPT with Google or other options, enter your date of birth to verify age, and explore free, plus, and pro plans while navigating the ChatGPT interface.

Learn to generate an ETL script with ChatGPT for an SSIS package, connecting to AdventureWorks 2022, standardizing phone numbers, and loading into a staging table with error handling and logging.

Create a staging table stage_underscore_person in Adventureworks 2022. Load transformed data from person and person_phone by standardizing phones, removing duplicates, and adding a timestamp, then verify with no rows.

Create and run a new SSIS package in Visual Studio 2022, configure server and database variables, edit the script task, and execute to load and standardize data.

Explore dimensional modeling concepts and the Kimball methodology for building data warehouses with star schemas, fact and dimension tables, conformed dimensions, slowly changing dimensions, and surrogate keys.

Leverage ai dimensional modeling to design a star schema that converts stage_persons into an analytics-ready customer data warehouse, including SCD type two, dim date, and ETL logic.

Build a complete data warehouse by executing scripts to create dim customer, dim date, and lookup tables, then load and verify the fact customer activity through etl.