Microsoft SQL Server 2012 Certification Training Exam 70-463

Explore data warehouse challenges in SQL Server 2012, including large data volumes, speed, and size, and how memory and per-instance processor allocation affects performance.

Discover how to manage physical storage in data warehousing by using multiple file groups and files to spread large tables, boosting performance and balancing data and log files.

Learn data warehouse architecture in SQL Server 2012, configure default data, log, and backup locations, and ensure SQL Server Agent and Integration Services run automatically.

Configure a data warehouse appliance in SQL Server 2012 by setting memory and processor affinities, enabling multiprocessor (MPP) support, and applying IO and processor affinity rules to the instance.

Design a data warehouse logical design with SQL Server 2012, covering star and snowflake schemas and dimension and fact tables. Map entities and attributes and create database diagrams showing relationships.

Configure data warehouse physical design in SQL Server 2012 by creating file groups (index, dimension, fact), assigning tables and indexes to groups, and reducing contention for terabyte-scale data.

Create a data warehouse star schema in SQL Server by building a database named infinit scales with three tables (customers, dim product, fact sales) and define primary and foreign keys.

Implement a data warehouse schema in SQL Server 2012 by creating the infinite skills d-w database, three tables (two dimension tables and a fact table), and a diagram with keys.

Learn to implement ETL using SQL Server 2012 Integration Services with Visual Studio, including building packages, control flow, data flow, and deploying to the Integration Services catalog.

Define and create a data source in SQL Server 2012, use the import/export wizard to extract from Adventure Works and load into Excel.

Identify adventure works database as the source, create a sosias package in sql server data tools, and build a data flow with a connection manager to extract and load data.

Learn to implement a data flow in SSIS using source and destination wizards. Extract dim customer data from Adventure Works and write it to a delimited flat file.

Implement an SSIS package to load data from dim product to stage product table. Create three records in dim product, map columns, and execute the package.

Learn to implement an SSIS package by creating a stage product table without primary keys, configuring data flow from dim product to stage product, and loading data to destination.

Learn to build a control flow in integration services, configure a connection to the Adventure Works database, and run an execute sql task against the dim customer table.

Learn to parameterize Sosias packages in Visual Studio 2010 by defining project parameters and control-flow variables, then use file system tasks to delete, create, and copy files with dynamic paths.

Group tasks into a sequence container to run them as one logical unit. Link tasks with a precedent constraint to enforce order and use enable/disable to control execution.

Create a for loop container in Visual Studio 2010 to run tasks repeatedly. Define a counting variable and expressions, then use execute process to open Notepad.

Learn to manage consistency and transactions in a Sosias package by configuring precedence constraints, error handling, maximum error count, and transaction options like serializable isolation and support.

Implement control flow in SSIS by using a for loop container with a counter variable, a data flow task, and validating stage product records in the infinite skills database.

Demonstrates implementing control flow in SSIS by deleting stage product data, validating no primary keys, and loading records from product to stage product with a for loop executing four iterations.

Learn to debug and troubleshoot an SSIS package using breakpoints, watchers, data viewers, and logging with SSMS and SSIS, to monitor variables, data flow, and logs.

Configure error log providers and log viewers for your Sosias package, enable data viewers to monitor data flow, and use control flow breakpoints for debugging and monitoring.

Learn how to handle errors in an SSIS package, distinguishing anticipated versus unexpected issues, and use control flow, event handlers, and email alerts to manage failures.

Learn to implement error handling in SSIS by validating incoming data, routing valid records to a staging table, and bad data to a log file or flat file.

Learn to debug and troubleshoot SSIS packages by setting breakpoints, using data viewers in the data flow task, and configuring logging to text files and Windows event log.

Explore incremental ETL for data warehouses by loading only new or changed data using date-time stamps or slowly changing dimensions, via SSIS in Visual Studio.

Use a Sosias package to extract modified data from source products into stage products, using the extract log to compare last load time with current time.

Learn to extract modified data using a date time stamp or change data capture in SQL Server, enabling CDC and tracking changes with log sequence numbers.

Learn to perform incremental data loads with SQL Server Integration Services using change data capture, enabling CDC at database and table levels and configuring CDC control and data flow tasks.

Execute incremental data loads by configuring a load table with last load time, updating it, and building an SSIS package to extract new dim product data into stage product.

Learn to implement incremental data loads by creating a load table, tracking last load times, and loading new records into stage and dim product tables.

Learn to build a data cleansing workflow using data quality services to create a knowledge base, define cleansing and matching rules, and integrate with ETL processes.

Configure data quality services, build and publish a knowledge base with a gender domain, and import valid values from Excel to cleanse data.

Create a data quality services project with a knowledge base to cleanse invalid gender values (x to m) and load corrected data into stage employees.

Learn to install and configure SQL Server 2012 Data Quality Services, create a knowledge base, define domain values, map the gender column, and execute a data cleansing workflow.

Build a gender knowledge base in data quality services, map domain values, run knowledge discovery, and create a data quality project to cleanse the gender column in SQL Server.

Install and configure data quality services, connect to the knowledge base, then create a 100 percent matching policy and cleanse data in a staging table using SSIS.

Learn to de-duplicate data using data quality services in SQL Server 2012, building knowledge bases, domains, and matching rules, then cleanse data and load into a staging table via SSIS.

Discover how to install and configure Master Data Services as a centralized repository that cleanses and standardizes data from multiple sources, with entities and attributes mapped on a domain-joined server.

Implement Master Data Services by creating and deploying a model with entities and attributes, mapping to tables and enabling data cleansing in a centralized data warehouse.

Explore mastering data services with the Excel add-in: connect to a local Master Data Services server, load models like Infinite Skills, manage entities, refresh data, and implement business rules.

Explore customizing SSIS components by adding a script task, using languages such as C#, C++, or Visual Basic, and editing parameters, expressions, and script code in the design environment.

Learn to extend Sosias with scripting in a pre-configured Visual Studio project, using script sources, transformations, and destinations within data flow tasks and script tasks.

Enhance the Sosias scripting environment by creating data sources and destinations for SSIS data flows, using script source, transformation, and destination with variables.