Microsoft 70-463 Implementing a Data Warehouse with SQL...

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.

Explore centralized, data mart, and hub-and-spoke architectures for data warehouses, enabling department-specific olap databases and feeding data into a centralized warehouse.

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.

demonstrates the contrast between oltp and olap databases using Adventure Works samples, highlighting normalization, joins, and the shift to a data warehouse with dim and fact 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, examine data warehouse architectures—hub-and-spoke, data marts, and centralized warehouses—and apply data modeling with star and snowflake schemas through hands-on practice.

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.

Explore designing a data warehouse and learn data modeling, then implement a data model for analytics.

Identify organizational metrics and define success using numeric measures like total sales and profit, then analyze by time, department, customer, and product through dimensional modeling.

Define measures within a fact table and group related dimensions in dimension tables. Compare star schema and snowflake schema in data warehouse contexts, noting performance differences and scenarios requiring each.

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.

Set the data model to the lowest granularity for both dimensions and facts, enabling time analysis from day to hour to minute to second and penny-level traceability.

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.

Master data warehouse concepts for the exam, including dimension and fact tables, surrogate and business keys, conformed and degenerate dimensions, star versus snowflake schemas, granularity, and total sales reporting.

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 an introduction to sql server integration services, examine the tools for creating SSIS packages, and review data flows in ETL.

Explore SQL Server integration services and upgrade ssis packages to sql server 2012, using script components in control flow and data flow for efficient etl in sql server.

Upgrade SSIS packages from 2005–2008 R2 to 2012 via intermediate steps; some run executable without upgrade, but ActiveX control flow tasks are deprecated in 2012.

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.

Explains the three primary types of SSIS transformations—fully blocking, semi blocking, and nonblocking—with examples like sort, merge, and derived column to illustrate memory and performance impacts.

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 SQL Server Data Tools to build a data flow task using a conditional split that routes records to manager or employee flat files, with configured destinations and mappings.

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 sequel server integration services data flows with sources, destinations, and transformations; compare blocking, semi blocking, and nonblocking types, and learn to configure properties using SSD and import export wizard.

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

Master control flow tasks in integration services, including variables and executables like database transfer and data flow tasks, and learn how parameters pass values between tasks.

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.

Explore how expressions drive control flow tasks in SQL data warehouse design, enabling dynamic values, properties, and connection strings within loop containers and data flow tasks to control next tasks.

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.

Explore incremental updates to the data warehouse, examine script components, and discuss custom components to extend SQL Server Integration Services and SQL Server Data Tools.

Monitor warehouse after a full load, update fact and dimension tables, slowly changing dimensions, and handle deletes while detecting updates with checksums or date-time stamps via change data capture.

Enable change data capture on a database and tables, then query CDC tables for inserts, updates, and deletes, and load changes incrementally using CDC control flow and data flow tasks.

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.

Configure SSIS script components for control flow and data flow via Visual Studio Tools for Applications, using Visual Basic or C#, then deploy custom .NET components to extend SSIS.

Enable change data capture on the database and dim_customer for incremental warehouse updates, then surface column-level changes via the CDC mask and integrate with SSIS control flow.

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 across server and client components, using a semantic model for data cleansing, duplicate matching, reference validation, profiling, monitoring, and knowledge base with domain mappings to fields.

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.

Explore data quality services by creating or importing a knowledge base from existing data, validating U.S. states, and setting up domains for city, state, and store type through knowledge discovery.

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.

Configure data quality services to define domains and rules and enforce data consistency in SSIS. See automatic corrections, like whale sale to wholesale, via knowledge discovery and training.

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 architecture and components of data quality services on server and client sides, three catalogs, and how transformation maps incoming and outgoing columns to the domain and knowledge base.

Explore Master Data Services, compare data quality services with Master Data Services, and examine how Master Data Services works.

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.

Master Data Services defines entities and attributes with versioning, enables Excel-based data stewardship, and enforces business rules and validation to maintain consistent, high-quality data across the enterprise.

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.

Deploy Sosias packages and projects while implementing security practices to safeguard both packages and projects.

Deploy integration services packages and projects using either the msde database or the file system. Ssis 2012 introduces project parameters for easier cross-server deployment from development to test and production.

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.

Explore deployment options: compare MSDE and file system deployments, and deploy projects to the SSIS catalog to use project parameters. Secure packages with protection levels and mark parameters sensitive.

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.