book

Training Kit (Exam 70-463): Implementing a Data Warehouse with Microsoft SQL Server 2012

by Dejan Sarka, Grega Jerkic, Matija Lah

December 2012

Beginner to intermediate

845 pages

23h 33m

English

Microsoft Press

Read now

Unlock full access

Exam 70-463: Implementing a Data Warehouse with Microsoft® SQL Server® 2012: Training Kit
Exam 70-463: Implementing a Data Warehouse with Microsoft SQL Server 2012
Introduction
System RequirementsSQL Server and Other Software RequirementsHardware and Operating System RequirementsData RequirementsUsing the Companion CDHow to Install the Practice TestsHow to Use the Practice TestsHow to Uninstall the Practice TestsAcknowledgmentsSupport & FeedbackErrataWe Want to Hear from YouStay in TouchPreparing for the Exam
I. Designing and Implementing a Data Warehouse
1. Data Warehouse Logical Design
Before You BeginLesson 1: Introducing Star and Snowflake SchemasReporting Problems with a Normalized SchemaStar SchemaSnowflake SchemaGranularity LevelAuditing and LineagePractice: Reviewing the AdventureWorksDW2012 Internet Sales SchemaLesson SummaryLesson ReviewLesson 2: Designing DimensionsDimension Column TypesHierarchiesSlowly Changing DimensionsPractice: Reviewing the AdventureWorksDW2012 DimensionsLesson SummaryLesson ReviewLesson 3: Designing Fact TablesFact Table Column TypesAdditivity of MeasuresAdditivity of Measures in SSASMany-to-Many RelationshipsPractice: Reviewing the AdventureWorksDW2012 Fact TablesLesson SummaryLesson ReviewCase ScenariosCase Scenario 1: A Quick POC ProjectCase Scenario 2: Extending the POC ProjectInterviewsQuestionsSuggested PracticesAnalyze the AdventureWorksDW2012 Database ThoroughlyCheck the SCD and Lineage in the AdventureWorksDW2012 DatabaseAnswersLesson 1Lesson 2Lesson 3Case Scenario 1Case Scenario 2
2. Implementing a Data Warehouse
Before You BeginLesson 1: Implementing Dimensions and Fact TablesCreating a Data Warehouse DatabaseImplementing DimensionsImplementing Fact TablesPractice: Implementing Dimensions and Fact TablesLesson SummaryLesson ReviewLesson 2: Managing the Performance of a Data WarehouseIndexing Dimensions and Fact TablesIndexed ViewsUsing Appropriate Query TechniquesData CompressionColumnstore Indexes and Batch ProcessingPractice: Loading Data and Using Data Compression and Columnstore IndexesLesson SummaryLesson ReviewLesson 3: Loading and Auditing LoadsUsing PartitionsData LineagePractice: Performing Table PartitioningLesson SummaryLesson ReviewCase ScenariosCase Scenario 1: Slow DW ReportsCase Scenario 2: DW Administration ProblemsSuggested PracticesTest Different Indexing MethodsTest Table PartitioningAnswersLesson 1Lesson 2Lesson 3Case Scenario 1Case Scenario 2
II. Developing SSIS Packages
3. Creating SSIS Packages
Before You BeginLesson 1: Using the SQL Server Import and Export WizardPlanning a Simple Data MovementPractice: Creating a Simple Data MovementLesson SummaryLesson ReviewLesson 2: Developing SSIS Packages in SSDTIntroducing SSDTPractice: Getting Started with SSDTLesson SummaryLesson ReviewLesson 3: Introducing Control Flow, Data Flow, and Connection ManagersIntroducing SSIS DevelopmentIntroducing SSIS Project DeploymentPractice: Modifying an Existing Data MovementLesson SummaryLesson ReviewCase ScenariosCase Scenario 1: Copying Production Data to DevelopmentCase Scenario 2: Connection Manager ParameterizationSuggested PracticesUse the Right ToolAccount for the Differences Between Development and Production EnvironmentsAnswersLesson 1Lesson 2Lesson 3Case Scenario 1Case Scenario 2
4. Designing and Implementing Control Flow
Before You BeginLesson 1: Connection ManagersConnection Manager Scope32-Bit and 64-Bit Data ProvidersParameterizationPractice: Creating a Connection ManagerLesson SummaryLesson ReviewLesson 2: Control Flow Tasks and ContainersPlanning a Complex Data MovementTasksData Preparation TasksWorkflow TasksData Movement TasksSQL Server Administration TasksSQL Server Maintenance TasksAnalysis Services TasksThe Script TaskCustom TasksContainersPractice: Determining the Control FlowLesson SummaryLesson ReviewLesson 3: Precedence ConstraintsPractice: Determining Precedence ConstraintsLesson SummaryLesson ReviewCase ScenariosCase Scenario 1: Creating a Cleanup ProcessCase Scenario 2: Integrating External ProcessesSuggested PracticesA Complete Data Movement SolutionAnswersLesson 1Lesson 2Lesson 3Case Scenario 1Case Scenario 2
5. Designing and Implementing Data Flow
Before You BeginLesson 1: Defining Data Sources and DestinationsCreating a Data Flow TaskDefining Data Flow Source AdaptersAdding a Data Flow Source Adapter by Using the Source AssistantConfiguring the Data Flow Source AdapterDefining Data Flow Destination AdaptersConfiguring the Data Flow Destination AdapterSSIS Data TypesUsing Fast ParsePractice: Implementing Simple Data FlowsLesson SummaryLesson ReviewLesson 2: Working with Data Flow TransformationsSelecting TransformationsLogical Row-Level TransformationsMulti-Input and Multi-Output TransformationsMulti-Row TransformationsAdvanced Data-Preparation TransformationsUsing TransformationsResolving Column ReferencesPractice: Using Data Flow TransformationsLesson SummaryLesson ReviewLesson 3: Determining Appropriate ETL Strategy and ToolsETL StrategyETL ArchitectureLookup TransformationsUsing a Lookup TransformationUsing the Cache Transform Transformation with the Lookup TransformationSorting the DataSet-Based UpdatesPractice: Enhancing Data Flow TransformationsLesson SummaryLesson ReviewCase ScenarioCase Scenario: New Source SystemSuggested PracticesCreate and Load Additional TablesAnswersLesson 1Lesson 2Lesson 3Case Scenario

III. Enhancing SSIS Packages
6. Enhancing Control Flow
Before You BeginLesson 1: SSIS VariablesSystem and User VariablesVariable Data TypesVariable ScopeProperty ParameterizationPractice: Creating a User Variable and Parameterizing a TaskLesson SummaryLesson ReviewLesson 2: Connection Managers, Tasks, and Precedence Constraint ExpressionsExpressionsProperty ExpressionsPrecedence Constraint ExpressionsPractice: Using Expressions to Parameterize SSIS ObjectsLesson SummaryLesson ReviewLesson 3: Using a Master Package for Advanced Control FlowSeparating Workloads, Purposes, and ObjectivesHarmonizing Workflow and ConfigurationThe Execute Package TaskThe Execute SQL Server Agent Job TaskThe Execute Process TaskPractice: Creating and Configuring a Master PackageLesson SummaryLesson ReviewCase ScenariosCase Scenario 1: Complete SolutionsCase Scenario 2: Data-Driven ExecutionSuggested PracticesConsider Using a Master PackageAnswersLesson 1Lesson 2Lesson 3Case Scenario 1Case Scenario 2
7. Enhancing Data Flow
Before You BeginLesson 1: Slowly Changing DimensionsDefining Attribute TypesInferred Dimension MembersUsing the Slowly Changing Dimension TaskEffectively Updating DimensionsChecking Attribute ChangesSet-Based Update LogicPractice: Implementing Slowly Changing Dimension LogicLesson SummaryLesson ReviewLesson 2: Preparing a Package for Incremental LoadUsing Dynamic SQL to Read DataUsing the OLE DB Source AdapterUsing the ODBC or ADO Net Source AdapterImplementing CDC by Using SSISEnabling CDC on the DatabaseSSIS CDC ComponentsETL Strategy for Incrementally Loading Fact TablesPractice: Implementing Change Data Capture (CDC) by Using SSISLesson SummaryLesson ReviewLesson 3: Error FlowUsing Error FlowsPractice: Using Error FlowLesson SummaryLesson ReviewCase ScenarioCase Scenario: Loading Large Dimension and Fact TablesSuggested PracticesLoad Additional DimensionsAnswersLesson 1Lesson 2Lesson 3Case Scenario
8. Creating a Robust and Restartable Package
Before You BeginLesson 1: Package TransactionsDefining Package and Task Transaction SettingsTransaction Isolation LevelsManually Handling TransactionsPractice: Implementing TransactionsLesson SummaryLesson ReviewLesson 2: CheckpointsImplementing Restartability CheckpointsPractice: Setting and Observing Checkpoints in a PackageLesson SummaryLesson ReviewLesson 3: Event HandlersUsing Event HandlersPractice: Implementing Event HandlersLesson SummaryLesson ReviewCase ScenarioCase Scenario: Auditing and Notifications in SSIS PackagesSuggested PracticesUse Transactions and Event HandlersAnswersLesson 1Lesson 2Lesson 3Case Scenario
9. Implementing Dynamic Packages
Before You BeginLesson 1: Package-Level and Project-Level Connection Managers and ParametersUsing Project-Level Connection ManagersParametersUsing ParametersDefining ParametersBuild Configurations in SQL Server 2012 Integration ServicesCreating Build ConfigurationsUsing Build ConfigurationsProperty ExpressionsPractice: Implementing ParametersLesson SummaryLesson ReviewLesson 2: Package ConfigurationsImplementing Package ConfigurationsCreating a ConfigurationCreating an XML File ConfigurationCreating a SQL Server ConfigurationAdding Properties to Your ConfigurationSharing, Ordering, and Editing Your ConfigurationsPractice: Using Package ConfigurationsLesson SummaryLesson ReviewCase ScenarioCase Scenario: Making SSIS Packages DynamicSuggested PracticesUse a Parameter to Incrementally Load a Fact TableAnswersLesson 1Lesson 2Case Scenario
10. Auditing and Logging
Before You BeginLesson 1: Logging PackagesLog ProvidersSelecting a Log ProviderConfiguring LoggingInheritance of Log SettingsLog Configuration TemplatesPractice: Configuring SSIS LoggingLesson SummaryLesson ReviewLesson 2: Implementing Auditing and LineageAuditing TechniquesRow Count Data Flow Transformation ComponentAggregate Data Flow Transformation ComponentAudit Data Flow Transformation ComponentCorrelating Audit Data with SSIS LogsRetentionPractice: Implementing Elementary AuditingLesson SummaryLesson ReviewLesson 3: Preparing Package TemplatesSSIS Package TemplatesCreating an SSIS Package TemplateUsing an SSIS Package TemplatePractice: Creating and Using an SSIS Package TemplateLesson SummaryLesson ReviewCase ScenariosCase Scenario 1: Implementing SSIS Logging at Multiple Levels of the SSIS Object HierarchyCase Scenario 2: Implementing SSIS Auditing at Different Levels of the SSIS Object HierarchySuggested PracticesAdd Auditing to an Update Operation in an Existing Execute SQL TaskCreate an SSIS Package Template in Your Own EnvironmentAnswersLesson 1Lesson 2Lesson 3Case Scenario 1Case Scenario 2
IV. Managing and Maintaining SSIS Packages
11. Installing SSIS and Deploying Packages
Before You BeginLesson 1: Installing SSIS ComponentsPreparing an SSIS InstallationDevelopment vs. ProductionHardware and Software RequirementsSecurity Considerations64-Bit Environments vs. 32-Bit EnvironmentsInstalling SSISUpgrading SSISSSIS ToolsPractice: Installing SSISLesson SummaryLesson ReviewLesson 2: Deploying SSIS PackagesSSISDB CatalogConfiguring SSISDBSSISDB ObjectsFoldersProjects and PackagesParametersServer Environments, Server Variables, and Server Environment ReferencesOperationsProject DeploymentPractice: Deploying SSIS ProjectsLesson SummaryLesson ReviewCase ScenariosCase Scenario 1: Using Strictly Structured DeploymentsCase Scenario 2: Installing an SSIS ServerSuggested PracticesUpgrade Existing SSIS SolutionsAnswersLesson 1Lesson 2Case Scenario 1Case Scenario 2
12. Executing and Securing Packages
Before You BeginLesson 1: Executing SSIS PackagesOn-Demand SSIS ExecutionSQL Server Management StudioDTExecUITransact-SQL, Windows PowerShell, the SSIS Managed API, and DTExecAutomated SSIS ExecutionSQL Server AgentJobs and Job StepsSchedulesSQL Server Agent Jobs vs. the Master Package ConceptMonitoring SSIS ExecutionOperationsValidationsExecutionsLogging LevelsSSIS Monitoring in SQL Server Management StudioPractice: Starting and Monitoring SSIS ProcessesLesson SummaryLesson ReviewLesson 2: Securing SSIS PackagesSSISDB SecurityPrincipalsSecurablesPermissionsPermission InheritanceDefault PermissionsPractice: Managing SSISDB PermissionsLesson SummaryLesson ReviewCase ScenariosCase Scenario 1: Deploying SSIS Packages to Multiple EnvironmentsCase Scenario 2: Remote ExecutionsSuggested PracticesImprove the Reusability of an SSIS SolutionAnswersLesson 1Lesson 2Case Scenario 1Case Scenario 2
13. Troubleshooting and Performance Tuning
Before You BeginLesson 1: Troubleshooting Package ExecutionDesign-Time TroubleshootingDebugging the Control Flow with BreakpointsUsing Data Viewers in the Data FlowUsing Other Methods for DebuggingProduction-Time TroubleshootingTroubleshooting Packages by Using the SSISDB CatalogUsing Data TapsPractice: Troubleshooting PackagesLesson SummaryLesson ReviewLesson 2: Performance TuningSSIS Data Flow EngineData Buffer ArchitectureTransformation TypesExecution TreesBackpressure MechanismData Flow Tuning OptionsBuffer OptimizationData Flow Tips for PerformanceParallel Execution in SSISTroubleshooting and Benchmarking PerformanceUsing the SSISDB Catalog to Benchmark Package PerformanceMonitoring SSIS Performance with Performance CountersPractice: Observing Package ExecutionLesson SummaryLesson ReviewCase ScenarioCase Scenario: Tuning an SSIS PackageSuggested PracticeGet Familiar with SSISDB Catalog ViewsAnswersLesson 1Lesson 2Case Scenario
V. Building Data Quality Solutions
14. Installing and Maintaining Data Quality Services
Before You BeginLesson 1: Data Quality Problems and RolesData Quality DimensionsCompletenessAccuracyInformationConsistencyData Quality Soft DimensionsData Quality Schema DimensionsData Quality Activities and RolesPractice: Checking for Data Quality IssuesLesson SummaryLesson ReviewLesson 2: Installing Data Quality ServicesDQS ArchitectureDQS InstallationPractice: Installing Data Quality ServicesLesson SummaryLesson ReviewLesson 3: Maintaining and Securing Data Quality ServicesPerforming Administrative Activities with Data Quality ClientPerforming Administrative Activities with Other ToolsPractice: Monitoring DQS ActivityLesson SummaryLesson ReviewCase ScenarioCase Scenario: Data Warehouse Not UsedSuggested PracticesAnalyze the AdventureWorksDW2012 DatabaseReview Data Profiling ToolsAnswersLesson 1Lesson 2Lesson 3Case Scenario
15. Implementing Master Data Services
Before You BeginLesson 1: Defining Master DataWhat Is Master Data?Master Data ManagementMDM ChallengesPractice: Defining Master DataLesson SummaryLesson ReviewLesson 2: Installing Master Data ServicesMaster Data Services ArchitectureMDS InstallationPractice: Installing Master Data ServicesLesson SummaryLesson ReviewLesson 3: Creating a Master Data Services ModelMDS Models and Objects in ModelsMDS ObjectsPractice: Creating an MDS ModelLesson SummaryLesson ReviewCase ScenariosCase Scenario 1: Introducing an MDM SolutionCase Scenario 2: Extending the POC ProjectInterviewsQuestionsSuggested PracticesAnalyze the AdventureWorks2012 DatabaseExpand the MDS ModelAnswersLesson 1Lesson 2Lesson 3Case Scenario 1Case Scenario 2
16. Managing Master Data
Before You BeginLesson 1: Importing and Exporting Master DataCreating and Deploying MDS PackagesImporting Batches of DataExporting DataPractice: Importing and Exporting Master DataLesson SummaryLesson ReviewLesson 2: Defining Master Data SecurityUsers and PermissionsOverlapping PermissionsPractice: Defining MDS SecurityLesson SummaryLesson ReviewLesson 3: Using Master Data Services Add-in for ExcelEditing MDS Data in ExcelCreating MDS Objects in ExcelPractice: Using the MDS Add-in for ExcelLesson SummaryLesson ReviewCase ScenarioCase Scenario: Editing Batches of MDS DataSuggested PracticesAnalyze the Staging TablesTest SecurityAnswersLesson 1Lesson 2Lesson 3Case Scenario
17. Creating a Data Quality Project to Clean Data
Before You BeginLesson 1: Creating and Maintaining a Knowledge BaseBuilding a DQS Knowledge BaseDomain ManagementPractice: Creating a Knowledge BaseLesson SummaryLesson ReviewLesson 2: Creating a Data Quality ProjectDQS ProjectsData CleansingPractice: Creating a DQS ProjectLesson SummaryLesson ReviewLesson 3: Profiling Data and Improving Data QualityUsing Queries to Profile DataSSIS Data Profiling TaskPractice: Using the SSIS Data Profiling TaskLesson SummaryLesson ReviewCase ScenarioCase Scenario: Improving Data QualitySuggested PracticesCreate an Additional Knowledge Base and ProjectAnswersLesson 1Lesson 2Lesson 3Case Scenario
VI. Advanced SSIS and Data Quality Topics
18. SSIS and Data Mining
Before You BeginLesson 1: Data Mining Task and TransformationWhat Is Data Mining?SSAS Data Mining AlgorithmsUsing Data Mining Predictions in SSISPractice: Using Data Mining Predictions in SSISLesson SummaryLesson ReviewLesson 2: Text MiningTerm ExtractionTerm LookupPractice: Performing Text MiningLesson SummaryLesson ReviewLesson 3: Preparing Data for Data MiningPreparing the DataSSIS SamplingPractice: Performing Random SamplingLesson SummaryLesson ReviewCase ScenarioCase Scenario: Preparing Data for Data MiningSuggested PracticesTest the Row Sampling and Conditional Split TransformationsAnswersLesson 1Lesson 2Lesson 3Case Scenario
19. Implementing Custom Code in SSIS Packages
Before You BeginLesson 1: Script TaskConfiguring the Script TaskCoding the Script TaskPractice: Using the Script Task to Read Data Profiling ResultsLesson SummaryLesson ReviewLesson 2: Script ComponentConfiguring the Script ComponentCoding the Script ComponentPractice: Using the Data Profiling Results to Process Data Flow RowsLesson SummaryLesson ReviewLesson 3: Implementing Custom ComponentsPlanning a Custom ComponentDeveloping a Custom ComponentDesign Time and Run TimeDesign-Time MethodsProvideComponentPropertiesValidateReinitializeMetaDataFireError, FireWarning, and FireInformationRun-Time MethodsAcquireConnections (for Validation)ValidateReleaseConnections (after Validation)PrepareForExecuteAcquireConnections (for Execution)PreExecutePrimeOutputProcessInputPostExecuteReleaseConnections (after Execution)CleanupPractice: Designing, Deploying, and Using a Custom Data Flow ComponentLesson SummaryLesson ReviewCase ScenarioCase Scenario: Data CleansingSuggested PracticesCreate a Web Service SourceAnswersLesson 1Lesson 2Lesson 3Case Scenario
20. Identity Mapping and De-Duplicating
Before You BeginLesson 1: Understanding the ProblemIdentity Mapping and De-Duplicating ProblemsSolving the ProblemsPractice: Preparing the DataLesson SummaryLesson ReviewLesson 2: Using DQS and the DQS Cleansing TransformationDQS Cleansing TransformationDQS MatchingPractice: Using the DQS Cleansing Transformation and DQS MatchingLesson SummaryLesson ReviewLesson 3: Implementing SSIS Fuzzy TransformationsFuzzy Transformations AlgorithmVersions of Fuzzy TransformationsPractice: Using the SSIS Fuzzy Lookup Transformation and MDS Add-in for Excel with the DQS Matching KBLesson SummaryLesson ReviewCase ScenarioCase Scenario: Improving Data QualitySuggested PracticesResearch More on MatchingAnswersLesson 1Lesson 2Lesson 3Case Scenario
A. About the Authors
Index
About the Authors
Copyright

Overview

Ace your preparation for Microsoft® Certification Exam 70-463 with this 2-in-1 Training Kit from Microsoft Press®. Work at your own pace through a series of lessons and practical exercises, and then assess your skills with online practice tests—featuring multiple, customizable testing options.

Maximize your performance on the exam by learning how to:

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

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.

Read now

Unlock full access

More than 5,000 organizations count on O’Reilly

O’Reilly covers everything we've got, with content to help us build a world-class technology community, upgrade the capabilities and competencies of our teams, and improve overall team performance as well as their engagement.

Julian F.

Head of Cybersecurity

I wanted to learn C and C++, but it didn't click for me until I picked up an O'Reilly book. When I went on the O’Reilly platform, I was astonished to find all the books there, plus live events and sandboxes so you could play around with the technology.

Addison B.

Field Engineer

I’ve been on the O’Reilly platform for more than eight years. I use a couple of learning platforms, but I'm on O'Reilly more than anybody else. When you're there, you start learning. I'm never disappointed.

Amir M.

Data Platform Tech Lead

I'm always learning. So when I got on to O'Reilly, I was like a kid in a candy store. There are playlists. There are answers. There's on-demand training. It's worth its weight in gold, in terms of what it allows me to do.

Mark W.

Embedded Software Engineer

Building Custom Tasks for SQL Server Integration Services: The Power of .NET for ETL for SQL Server 2019 and Beyond

Publisher Resources

ISBN: 9780735668300Errata Page

Cloud Computing

Data Engineering

Data Science

AI & ML

Programming Languages

Software Architecture

IT/Ops

Security

Design

Business

Soft Skills