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

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
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
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
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
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
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

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
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
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
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
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
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
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
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
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
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
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
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
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
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
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

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

Chapter 7. Enhancing Data Flow

Exam objectives in this chapter:

Extract and Transform Data
- Design data flow.
- Implement data flow.
Load Data
- Implement data load options.

In Chapter 5 you learned about the data flow task, which is a crucial component in data warehouse projects when it comes to extracting, transforming, and loading data (ETL). Building on the foundation laid in that chapter, this chapter focuses on specific solutions for common data warehouse problems such as managing Slowly Changing Dimensions (SCD) and loading incremental data.

Lessons in this chapter:

Before You Begin

To complete this chapter, you must have:

Basic knowledge of Microsoft ...

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.

Start your free trial

Expert Scripting and Automation for SQL Server DBAs

Peter A. Carter

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

Andy Leonard

Microsoft SQL Server 2014 Unleashed

Ray Rankins, Paul Bertucci, Chris Gallelli, Alex T. Silverstein

SQL Server 2012 Data Integration Recipes: Solutions for Integration Services and Other ETL Tools

Adam Aspin

Publisher Resources

ISBN: 9780735668300Errata Page