Explore the differences between OLTP and OLAP databases, define data warehousing, review architectures, and learn the steps to create a data warehouse.

Compare oltp and olap in data warehousing, and learn how highly normalized oltp databases use primary keys to reduce redundancy and optimize inserts, updates, and deletes.

Compare OLTP and OLAP architectures to show why denormalized warehouses use batch updates for fast queries, using a retail store example with fact and dimension tables and latency considerations.

Define a data warehouse as a relational, normalized database for reporting, trending, and forecasting. Capture historical breadth to enable metrics slicing across dimensions for trends and forecasting.

Define core metrics and reporting needs, then slice measures by department, time, products, and demographics to guide a data warehouse, and ensure data quality across diverse sources.

Define sources and the target data warehouse, apply ETL transformations and aggregations, and load data with SSIS. Migrate DTS packages to SSIS before upgrading to SQL Server 2012.

Present OLAP data by integrating SQL Server Reporting Services, SharePoint, and Analysis Services to deliver relational OLAP and multidimensional cubes, accessible via Excel, dashboards, and pivot tables.

Compare oltp and olap architectures using internet sales data, join dim customers to the fact table, and explore measures, aggregations, and performance differences.

Compare OLTP and OLAP databases, highlighting speed through denormalization versus normalization for transactions. Outline a hub-and-spoke data warehouse with centralized OLAP presentation via Reporting Services, Analysis Services, and SharePoint.

Link measures to calendar and fiscal timeframes such as year, quarter, and month. Use a time dimension table to simplify queries for reporting and data warehousing, including holidays.

Use surrogate keys in dimension tables to preserve history with slowly changing dimensions; apply degenerate and conformed dimensions and classify measures as additive, semi-additive, or not additive.

Learn to align data warehouse modeling with business needs and rules, modeling dimension and fact tables with surrogate keys for slowly changing dimensions, gathering input from stakeholders to define granularity.

Define dimension and fact tables, keys, data types, and measures; discuss foreign keys, referential integrity, and trade-offs between load speed and data accuracy, including disabling constraints during load.

Implement the physical data warehouse model by evaluating partitioning, file groups, and disk layout for historical versus new data; optimize performance with non-clustered indexes, time-dimension keys, compression, and raid considerations.

Compare star and snowflake schemas by joining fact internet sales to conformed dimensions such as dim date, dim customer, and dim geography, including multiple date joins.

Explore dimensions and facts in a data warehouse, using star and snowflake schemas, to query sales measures by gender, marital status, and time dimensions with SQL and OLAP tools.

Identify that a star schema directly joins the fact table to dimensions, unlike snowflake or normalized schemas, and note that dimension keys use surrogate and business keys in data warehouses.

Compare fact and dimension tables to identify facts and measures for reporting. Explore star and snowflake schemas, logical and physical implementations, and hardware configurations for scalable data warehousing.

Explore SSIS tools for creating packages with the import export wizard and SQL Server Data Tools (SSD), and learn the wizard’s limits—one data source, one destination, few transformations.

Explore data flow tasks as the workhorse of EDL process, managing data sources, transformations, and destinations, and use connection managers to store providers and credentials across package and project scopes.

Identify data sources and configure a connection to pull data from databases, files, or custom sources, using an SSIS script component as source, transformation, or destination in the data flow.

Create an SSIS package in SQL Server Data Tools and run the Import/Export Wizard to copy data from Adventure Works 2012 into a flat file, mapping the Person table.

Configure a data flow task by creating a single source from Adventure Works, set up a connection manager, select columns, build a query, and add a full name derived column.

Explore exam scenarios for a data warehouse with integration services, focusing on data sources and file destinations. Differentiate synchronous and asynchronous transformations, and use conditional split to route to destinations.

Explore an introduction to control flow in SSIS, examining dynamic execution and how tasks and packages are executed.

Explore system and user defined variables in integration services, their data types and scope, enabling dynamic execution and error recording; compare package versus project parameters and their encryption.

Learn how precedents drive task sequencing in control flow, enabling parallel execution and routing to multiple dataflow tasks on success, failure, or completion, contrasted with a single dataflow pipeline.

Explore containers and annotations in Sosias, including for each, for loop, while loop, and sequence container, to control execution, scope, and transactions while processing files.

Explore how transactions govern SSIS containers, ensuring all tasks roll back if one fails. Configure the MSA DTC service and use begin tran and rollback within a sequence container.

Configure checkpoints in SSIS by recording failures in an XML file, resume from the failure point on failure, and use maximum error count and execute package tasks for modular reuse.

Demonstrate control flow tasks and precedence constraints in a warehouse package, deriving full names and routing by job title to managers or employees, and processing files with a for-each loop.

Learn control flow tasks and precedence in ssis by building a dynamic file path workflow with foreach loops, a dynamic connection manager, and vb.net script tasks.

Demonstrate control flow and precedence in data tasks by managing read/write variables, casting row counts to string, and displaying employee and manager totals with foreach and sequence containers and transactions.

Explore control flow tasks and precedence in a sequence container, including begin tran and end tran, transaction scope, and connection manager behavior for each container with required versus supported options.

Master integration services control flow by configuring tasks, containers, and properties; use checkpoints and transactions to ensure rollback and restart from the last failed task in a package.

Examine control flow concepts, including variables and parameters and their scope in projects and packages. Explore parameter deployment, transactions, and checkpoints that rerun failed tasks from the point of failure.

Explore an overview of troubleshooting, debugging, error handling, and logging, including custom logging, to improve reliability in SQL-based data warehouse tasks.

Explore debugging techniques in Microsoft 70-463, including breakpoints in integration services, task and script debugging, and built-in and custom logging to diagnose control flow and data flow.

Learn to debug integration services by setting breakpoints in data flow and control flow, inspecting system and user variables, and stepping through pre and post execution events.

Use the execution progress tab to monitor tasks and containers with verbose logging, review post-execution errors and warnings, and leverage the locals and watch windows to focus on specific variables.

Set breakpoints in control flow tasks and use data viewers to inspect data between components in a data flow pipeline, manage buffers, and capture grid view output during development.

Explore error handling in sql integration services, using on error and on air event handlers, script components, and redirection strategies to manage truncation and other data flow errors.

Configure built-in SSIS log providers to capture package and task events, using on execute, on pre execute, and on post execute options, and review the log events view for debugging.

Extend SSIS logging beyond built-in providers to capture row counts, start times, and error events. Write logs to SQL or comma-delimited file via an execute SQL task or script task.

Troubleshoot and log data warehouse processes by setting breakpoints and editing script tasks during debugging. Inspect locals and watch windows to track the row count and file path during execution.

Implement proactive error handling in SSIS with a two-error limit. Add logging through event handlers to capture manager and employee row counts in a row_counts table using execute SQL task.

Explore troubleshooting and logging in SSIS, configuring built-in log providers, enabling custom logging, and tracking variables, events, and row counts during data import from Adventure Works 2012.

Master exam scenarios by using breakpoints and the data viewer to inspect variables, redirect errors, and implement error handling, while comparing SSIS logging options like custom, sql server, and profiler.

Master troubleshooting and error handling in ssis by using breakpoints in control flow and script tasks, exploring data redirection in the data flow, and configuring log providers.

Examine slowly changing dimensions in dimension tables, covering type 1 overwrites, type 2 history preservation, and type 3 additions, and configure the SCD transformation to manage changing and historic attributes.

Implement incremental updates with change data capture, manage a CDC state with LSN markers, configure a data flow source, and apply slowly changing dimensions using type 1 and type 2.

Analyze exam scenarios for implementing a data warehouse, covering incremental updates, population methods, merge statements, slowly changing dimensions in SSIS, and script components with System.IO for extended file properties.

Choose the merge statement for incremental updates, as it provides the least administrative effort compared to change data capture, look up transformation, or dot net.

Explore maintaining a data warehouse with incremental updates, including merge statements, change data capture components, and slowly changing dimensions type 1 and type 2, plus custom script components in SSIS.

Explore an overview of data quality services and introduce the data quality services components used to ensure data quality in a data warehouse.

Explore data quality services components, including server and client setups, knowledge bases and domains, rule-driven data cleansing, domain-to-column mapping, and iterative knowledge discovery within a data flow for cleansed data.

Train the knowledge base with data quality services to discover domains, validate cities, states, and store type values, correct misspellings, and define rules for data quality and integration.

Prepare for the SQL Server 2012 data quality services exam by setting up a knowledge base, creating domains, and applying transformations to find duplicates. Domains map to columns.

Explore master data services, its distinction from data quality services, and how SQL Server Master Data Services provides data consistency across entities through its database, web configuration, and Excel add-in.

Explore the difference between master data services and data quality services, mapping entities to tables and domains to columns, and how DQS cleanses data before MDS to ensure address consistency.

Explore master data services and data quality services to ensure data consistency and staging, using the web app and Excel add-on to identify duplicates and manage data sources.

Master data services ensure enterprise-wide data consistency through a central hub, with spokes sending data and data stewards using data quality services to identify duplicates via an Excel add-on.

Learn package deployment options for a data warehouse, deploy to file system or MSDE, and migrate to the 2012 project deployment model with an SSIS catalog.

Packages support protection levels such as encrypt sensitive with user key or encrypt all with password; parameters may be marked sensitive, others are predefined as sensitive.

Deploy SSIS packages and projects to the SSIS catalog by creating the catalog, deploying Module 3, and importing packages from file system or SQL Server; explore 2012 deployment models.

Explore deployment methods for SSIS packages and projects, including project deployment to the SSIS catalog, the deployment wizard steps, server and path selection, and package protection levels for encryption.

Deploy projects to the SSIS catalog using the new project deployment model, deploying to a SQL Server instance database with parameter use, protection levels, and handling of sensitive parameters.

Explore data warehousing, including dimension and fact modeling, and logical-physical design, and learn to populate and deploy with integration services and data quality and security.