Exame 70-463 – Implementing a Data Warehouse with Microsoft SQL Server 2012

 

Código: 70-463
Prova Implementing a Data Warehouse with Microsoft SQL Server 2012
Idiomas Inglês, Português
Preço US$ 100.00
Duração 3 horas
Número de questões 56
Nota de aprovação 700 pontos (70%)
Treinamento oficial recomendado 10777A- DMOC – Implementing a Data Warehouse with SQL Server 2012

Síntese dos Assuntos

  • Design and implement a data warehouse
  • Develop and enhance SQL Server Integration Services packages
  • Manage and maintain SQL Server Integration Services packages
  • Build data quality solutions
  • Implement custom code in SQL Server Integration Services packages

Tópicos do Exame

Design and Implement a Data Warehouse (11%)

  • Design and implement dimensions.
    • This objective may include but is not limited to: design shared/conformed dimensions; determine whether you need support for slowly changing dimensions; determine attributes; design hierarchies; determine whether you need star or snowflake schema; determine the granularity of relationship with fact tables; determine the need for auditing or lineage; determine keys (business transactional or your own data warehouse/surrogate keys); implement dimensions; implement data lineage of a dimension table
  • Design and implement fact tables.
    • This objective may include but is not limited to: design a data warehouse that supports many to many relationships; appropriately index a fact table using columnstore indexes; partitioning; additive measures; semi-additive measures; non-additive measures; implement fact tables; determine the loading method for the fact tables; implement data lineage of a fact table; design summary aggregation tables

Extract and Transform Data (23%)

  • Define connection managers.
    • This objective may include but is not limited to: plan the configuration of connection managers; package level or project level connection manager; define a connection string; parameterization of connection strings
  • Design data flow.
    • This objective may include but is not limited to: define data sources and destinations; distinguish blocking and non-blocking transformations; use different methods to pull out changed data from data sources; determine appropriate data flow components; determine the need for supporting Slowly Changing Dimensions (SCD); determine whether to use SQL Joins or SSIS lookup or merge join transformations; batch processing vs. row by row processing; determine the appropriate transform to use for a specific task; determine the need and method for identity mapping and deduplicating; fuzzy lookup, fuzzy grouping, and Data Quality Services (DQS) transformation; determine the need for text mining; determine the need for custom data sources, destinations, and transforms; determine what to do with erroneous rows; determine auditing needs; determine sampling needs for data mining; trusted/authoritative data sources, including warehouse metadata
  • Implement data flow.
    • This objective may include but is not limited to: debug data flow; use the appropriate data flow components; SQL/SSIS data transformation; create SSIS packages that support slowly changing dimensions; use the Lookup task in SSIS; map identities using SSIS Fuzzy Lookup; specify a data source and destination; use data flows; different categories of transformations; read, transform, and load data; understand which transforms to use to accomplish a specific business task; data correction transformation; performance tune an SSIS dataflow; optimize Integration Services packages for speed of execution; maintain data integrity, including good data flow
  • Manage SSIS package execution.
    • This objective may include but is not limited to: schedule package execution by using SQL Server Agent; execute packages by using DTEXEC; execute packages by using SQL Server Management Studio; implement package execution; plan and design package execution strategy; use Windows PowerShell to execute scripts; monitor the execution using Management Studio; use DTEXECUI; ETL restartability
  • Implement script tasks in SSIS.
    • This objective may include but is not limited to: determine whether it is appropriate to use a script task; extend the capability of a control flow; perform a custom action as needed (not on every row) during a control flow

Load Data (27%)

  • Design control flow.
    • This objective may include but is not limited to: determine control flow; determine containers and tasks that are needed; determine precedence constraints; design an SSIS package strategy with rollback, staging, and transaction control; decide between one package or multiple packages; determine event handlers; determine variables; determine parameters on package and project level; determine connection managers and whether they are package or project level; determine the need for custom tasks; determine how much information you need to log from a package; determine the need for checkpoints; determine security needs
  • Implement package logic by using SSIS variables and parameters.
    • This objective may include but is not limited to: user variables; variable scope, data type; implement parameterization of properties using variables; use variables in precedence constraints; refer to SSIS system variables; design dynamic SSIS packages; package configurations (file or SQL tables); expressions; package and project parameters; project level connection managers; implement dynamic package behavior; configure packages in SSIS for different environments, package configurations (xmlconfiguration file, SQLServer table, registry entry; parent package variables, environment variable); parameters (package and project level); project connection managers; property expressions (use expressions for connection managers)
  • Implement control flow.
    • This objective may include but is not limited to: checkpoints; debug control flow; implement the appropriate control flow task to solve a problem; data profiling; use sequence containers and loop containers; manage transactions in SSIS Packages; manage parallelism; use precedence constraint to control task execution sequence; create package templates; use the execute package task
  • Implement data load options.
    • This objective may include but is not limited to: implement a full and incremental data load strategy; plan for an incremental update of the relational Data Mart
  • Implement script components in SSIS.
    • This objective may include but is not limited to: create an SSIS package that handles SCD Type 2 changes without using the SCD component; work with Script component in SSIS; decide when it is appropriate to use a script component vs. one that is built in; source, transformation, and destination components; use cases: web service source and destination, getting the error message

Configure and Deploy SSIS Solutions (24%)

  • Troubleshoot data integration issues.
    • This objective may include but is not limited to: performance issues; connectivity issues; execution of a task or transformation failed; logic issues; demonstrate awareness of the new SSIS logging infrastructure; troubleshoot a failed package execution to determine the root cause of failure; troubleshoot SSIS package failure from an invalid datatype; implement break points; data viewers; profile data with different tools; batch cleanup
  • Install and maintain SSIS components.
    • This objective may include but is not limited to: software installation (IS, management tools); development box and server; install specifics for remote package execution; plan for installation (32 vs. 64 bit); upgrade; provision the accounts; create the catalog
  • Implement auditing, logging, and event handling.
    • This objective may include but is not limited to: audit package execution by using system variables; propagate events; use log providers; log an SSIS execution; create alerting and notification mechanisms; use Event Handlers in SSIS to track ETL events and errors; implement custom logging
  • Deploy SSIS solutions.
    • This objective may include but is not limited to: create and configure an SSIS catalog; deploy SSIS packages by using the deployment utility; deploy SSIS packages to SQL or file system locations; validate deployed packages; deploy packages on multiple servers; install custom components and tasks; deploy SSIS packages by using DTUTIL
  • Configure SSIS security settings.
    • This objective may include but is not limited to: SSIS catalog database roles; package protection levels; secure Integration Services packages that are deployed at the file system; secure Integration Services parameters, configuration

Build Data Quality Solutions (15%)

  • Install and maintain Data Quality Services.
    • This objective may include but is not limited to: installation prerequisites; use Data Quality Server Installer; add users to the DQ roles; identity analysis, including data governance
  • Implement master data management solutions.
    • This objective may include but is not limited to: install Master Data Services (MDS); implement MDS; create models, entities, hierarchies, collections, and attributes; define security roles; import/export; subscriptions
  • Create a data quality project to clean data.
    • This objective may include but is not limited to: profile Online Transaction Processing (OLTP) and other source systems; data quality knowledge base management; create a data quality project; use Data Quality Client; improve data quality; identity mapping and deduplicating; handle history and data quality; manage data quality/cleansing.