Implementing a Data Warehouse with SQL Server 2012

Explore dimension tables in a data warehouse, learn the types of dimensions, how to work with hierarchies, and define granularity and lineage.

Explore Adventureworks OLTP and data warehouse databases, create dim period with an auto incrementing key and attribute, and see dim date connect to the internet sales fact in star schema.

Explore dimension table components, including attributes and hierarchies, and learn how denormalized and snowflake designs enable drill down, drill up, and flexible granularity.

Explore hierarchies in data warehouse dimensions, including product, employee, and geography. See snowflake dimensions with product subcategory and category in normalized tables and self‑referencing keys enabling drill down and up.

Explore the granularity of a dimension by adding quarter and month hierarchies to a year-based attribute, enabling a fact table to store monthly data and summarize by quarter or year.

Demonstrates SCD type 2 in product dimension, using alternate keys to identify versions and querying records with start-end dates to compare changes in standard cost, list price, and dealer price.

Explore slowly changing dimensions in data warehouses, how type 1 overwrites values, type 2 preserves history with new records and end dates, and type 3 tracks selective attributes.

Identify dimensions as tables that represent entities for data analysis. Differentiate standalone dimensions from shared or conformed dimensions across data marts and star schemas, noting hierarchies, granularity, and data lineage.

Define measures and describe how they are added and summarized in fact tables. Outline steps to implement a fact table, including ETL data loading, indexing, and partitioning.

Discover how to model measures in fact tables, their numeric, additive nature and granularity, and how to design and load fact tables with dimensions via ETL in a data warehouse.

Examine how to index fact tables with clustered primary keys, non-clustered foreign keys, and custom composites, and apply columnstore indexes and table partitioning to boost data warehouse performance.

Explore adding and configuring indices on a fact table, including a clustered primary key, non-clustered foreign key indexes, and a column store index for aggregated sales data.

Create an audit dimension table with auditing attributes and save it; connect audit key foreign key to the fact table and relate the order dimension to the sales fact table.

Identify facts to analyze and determine dimensions and granularity of measures in a fact table; align with star schema and use ETL and SQL Server 2012 columnstore indexes.

Explore the concepts for implementing a data warehouse with SQL Server 2012 by detailing the database engine roles, uses of SSIs, deployment, installation components, and the two SQL Server architectures.

Explore SQL Server components, including the database engine, SSIS catalog, and ETL processes, and see how data warehouses use star schemas, data marts, dimensions, and facts with mdf and ldf.

Explore the components of SSIS, including the integration services catalog and SQL Server Data Tools, and how deployment architectures—shared and dedicated—affect data sources, connectivity, and central data warehouses.

Learn the installation and deployment requirements for SQL Server integration services, including .NET Framework 3.5 and 4.0, PowerShell 2.0+, 32-bit vs 64-bit architectures, and 2 GB RAM.

Understand that the database engine stores the data warehouse and supports ETL with SSIS. SSIS can be deployed shared or dedicated, with integration services.

Run SQL Server installer to add features to existing installation. Select default instance, include the client SDK, start SQL Server Data Tools, and set BI settings in Environment dialog.

Define RS structures and elements, distinguish project and package connection managers, explain connection manager properties, SSIs catalog, parameterization, package type and transactions, and option values in the transaction support matrix.

Log into the SMS interface to create an SSIS catalog via the Integration Services catalog node, set a password, enable automatic startup, and create a folder for SSIs projects.

Start SQL Server data tools, create a new Integration Services project, name it, and explore the solution explorer. Use the toolbox and design surface to add tasks and connection managers.

Reuse a prior project to add a global ADO.NET connection manager and a local file-based connection manager within SSIS packages, using AdventureWorks and illustrating project prefix naming and package visibility.

Review ssis package components, including connection managers and parameters for production deployment, and note the CIS catalog database (CIS DB) and transaction options at package and task levels.

Explore the control flow of SSIS as the core workflow engine that defines and executes package tasks and containers, using the designer surface, toolbox, and connection managers.

Learn how tasks drive a control flow in SSIS, with examples like Execute SQL and Sendmail, and how to configure properties to assemble a complete ETL workflow.

Add and configure control flow tasks in a sql server data warehouse project, connect to Adventureworks, and run a debug session to populate a customers object variable.

Add a for each loop container, drop the sendmail task inside it, configure an add enumerator with the existing variable, and set up smtp connection manager for subject and body.

Understand how containers group tasks in RS control flow, including sequence, for loop, and for each loop, and how constraints—success, failure, and completion—link tasks and control execution.

Add a constraint from the first task to the container by dragging the green arrow, enabling the container to receive and process records from the first task.

Explore advanced control flow features in SSIS, including event handlers, event bubbling, and checkpoints, and learn to optimize parallelism with max concurrent executable settings.

Select the executable and event to create an event handler for the execute sql task, then run to observe the onerror event and the send mail task.

Explore variables and their uses in this lesson, defining variables and properties, data types and scope, the size system variable, parameters, and steps to create a package template.

Create a variable to store the customers query results from the execute sql task, then map each column to separate variables and assign values inside a for each loop.

Learn how project-scoped system variables provide read-only values across packages, including tasks, containers, event handlers, and package variables. See how expressions and constraints shape control flow in demos.

Demonstrates parsing each row into variables, configuring a sendmail task with the local smtp server, and building expressions to populate subject and message, including an uppercase transformation.

Modify the send mail task by adding a system variable, edit expressions, and use the username variable to generate emails and display the running account name.

Learn how to parameterize package variables and supply runtime values to adapt deployments across servers, using project parameters, templates, and external configurations to streamline etl workflows.

Parameterize the from and CC properties in the send mail task to reuse the package, create parameters, and adjust values to reflect the configured emails.

Enable and use package configurations to drive variables from an XML DTS config file; edit the from and CC properties, save, run the package, and verify values propagate to emails.

Review variables and data types in a data warehouse context, detailing in-memory objects with name, scope, namespace, and value, and how parameters and package templates enable cross-level configuration.

Describe the business intelligence process and learn to use ETL with the data flow task in SSIS, including data source components, destinations, and available transforms.

Illustrate how ETL maps to the data flow task in SSIS, detailing sources, transforms, and destinations, and discuss its role in data warehousing, BI, and data mining.

Discover data source components for extracting data from ADO.NET, Excel, ODBC, XML, and CDC, and how transforms and destinations enable ETL in SSIS, including blocking, semi-blocking, and non-blocking transforms.

Explore the fuzzy lookup transform, which uses heuristics to find near matches rather than exact ones, unlike the lookup transform. It outputs similarity and confidence to indicate closeness and certainty.

Explore text mining transforms in SQL Server 2012, including the term extraction transform that pulls nouns and noun phrases with a score defining occurrences, and the term lookup transform.

Create a package using the qcd transform in a data flow, connect the ado.net source to the oltp products table, map the business key, and use start and end dates.

Create a data profiling task in a control flow, using profile requests for length distribution, null values, and pattern detection on Adventureworks product table name, color, and product number.

Explore data quality services in sql server 2012, featuring a dcs engine, a standalone dcs client, and the dqz transform for validation, cleanup, standardization, and deduplication in etl.

Review how business intelligence derives knowledge from raw data and how dataflow tasks create design surfaces in the control flow; identify etl categories and blocking, partially blocking, and non-blocking transforms.

Execute ssis packages on production servers via deployment options, using exec and the execute package utility. Manage packages with sql server management studio and the sysdb catalog.

Deploy the project to the server via the deployment wizard, create catalog folder if needed, then view and run packages in the Integration Services catalog and import DTS X files.

Convert the CES zero nine project to a package deployment model, enable deployment utility, build the installable, deploy via the wizard to SQL Server, validate in SSMS, and run it.

Configure logging and auditing in SSIs, choose log providers (text, XML, SQL, Windows event log), and enable custom events to capture production package runs.

Add and configure a new execute sql task, enable text and xml logging from the CIS menu, then run the task to create and view logs.

Use the Exec tool to run packages in batch or scheduled jobs, and the deployment utility to create an installable project deployment file; configure logging and enable custom events.

Learn to debug execution and secure packages in SSIs, handle data flow errors, set up alerts, list the uses of breakpoints, and describe package protection levels.

Learn to implement error handling in data flows with error redirection, and set up alerts and notifications via SQL Server Agent to notify operators by email, pager, or net sends.

Learn to debug SSIS packages with breakpoints, data viewers, and path breakpoints in SQL Server Data Tools, monitoring control flow and data flow to identify and fix errors.

Debug the control flow by setting breakpoints, inspecting locals and variable values, and stepping through a package to monitor the mail task and related IDs.

Secure production packages with NTFS permissions or SQL Server roles, and set package protection levels to encrypt sensitive data and manage access through roles like sysadmin, sys, and sys operator.

Learn how to secure a dtsx package by encrypting the connection password and the entire package, switching protection levels as shown in the demo.

Review the key concepts: dataflow transforms enable error row redirection, alerts for package status, and debugging with breakpoints. Note that sysadmin users have access to all packages; this course concludes.