Study material exam 70-463 Implement Data Warehouse with SQL Server

Problem

Would you like to know Microsoft’s tips for passing the Implementing a Data Warehouse exam for the MCSA SQL Server 2012 certification? Would you like access to a collection of free, quality sources for preparing for the exam? Even if you do not plan on taking the exam, would you like access to high quality resources that offer guidelines for building and maintaining data warehouses with SQL Server 2012?

Solution

There are many ways to prepare for any Microsoft certification exam, such as the 70-463 exam for implementing a data warehouse. One preparation strategy is to grow your mastery for what Microsoft says will be covered by the exam. Even if you already have another set of exam training material, you may find it beneficial to study material that is available for free — especially if it is on one of the Microsoft web sites or specifically targets content that Microsoft says you need to know.

The Microsoft Learning web site includes a list of major topics for the 70-463 exam. These major topics are further segmented into a collection of sub-topics. Each major topic is listed with a percentage that denotes the weight of that topic on the exam. By focusing on a succession of individual sub-topics, you can concentrate your preparation time on discrete units of content. The Microsoft Learning web site also includes text further elaborating the type of content within the scope of a sub-topic. However, this text always begins with the phrase “may include but is not limited to”. Therefore, be on guard to learn thoroughly the topics and sub-topics and not just specific items mentioned in the text.

This tip has two main objectives.

• The first is to review the major topics and sub-topics for the 70-463 exam.
• The second is to provide a collection of about 130 URLs that are keyed to exam sub-topics.

In compiling the list of URLs a conscious effort was made to focus on content for SQL Server 2012 from Microsoft web sites. This is because the exam is for SQL Server 2012 proficiency with implementing a data warehouse, and you will be tested on your knowledge of the Microsoft way to implement data warehouses. I also selectively included URLs for non-Microsoft web sites and content that did not specifically target SQL Server 2012 when I felt the URL’s content could materially improve your grasp of exam major topics and sub-topics.

Exam Major Topics and Sub-topics

The following table includes three columns: one for major topics, a second for percent exam weight, and a third for sub-topics within the scope of a major topic. There are five major topics.

• The first major exam topic introduces data warehouses by focusing on the design of dimension tables and fact tables. When implementing these elements of a data warehouse, you will primarily use Analysis Services. This major topic has just an 11 percent exam weight.
• The second, third, and fourth exam topics focus on using Integration Services for extracting, transforming, and loading values into a data warehouse as well as configuring and deploying SSIS solutions to extract, transform, and load values into a data warehouse. These topics comprise nearly 74 percent of the exam weight. So one critical take-away is to make sure you know SSIS.
• The last major topic covers how to use Data Quality Services and Master Data Services to manage the data on which to build a data warehouse. You can use both of these services together to manage enterprise data on which to build a data warehouse. These two services are relatively new versus Integrations Services — especially if you consider its DTS heritage. The exam weight for the two newer services together is just 15 percent.

The preceding table lists between two and five sub-topics per major topic. Across all five major topics, there are 20 sub-topics. Mastering these 20 sub-topics will allow you to pass the exam. While learning about each of these sub-topics independently is of value, you will also need to know how they complement one another. For example, the architecture of a data warehouse consists of dimension tables and fact tables. The Design and Implement a Data Warehouse major topic has two sub-topics — one for dimension tables and another for fact tables. Of course, when designing a data warehouse you need to know how to use the two types of tables of tables together. URLs for these two sub-topics selectively highlight the interplay between dimension tables and fact tables.

Many SQL Server professionals will have exposure to a subset of Integration Services features in the context of one or more applications that they designed and maintain. However, the preceding table itemizes a specific set of Integration Services features that are critical for the exam because they are essential for data warehouse applications. No matter how long you have been using Integration Services, your mastery of Integration Services for the exam will not be adequate until it includes in-depth knowledge of the content within the scope of the sub-topics for the second, third, and fourth major topics.

Chances are that even if you do not routinely rely on Data Quality Services, you are invoking some ad hoc measures to manage data quality. For example, you may assign a NULL to a value for one column when a corresponding value in a second column does not match at once item in a set of pre-defined legitimate values. Data Quality Services includes a rich set of data quality management features which are especially appropriate for large scale solutions. When Data Quality Services is used with Master Data Services you can define re-usable consistent data resources that are accurate for multiple data warehouse applications throughout the units of an enterprise. The sub-topics for the last major topic has these kinds of issues within its scope.

URLs for Sub-topics

My first attempt to discover and organize URLs with content pertaining to the scope of the 20 sub-topics for the 70-463 exam resulted in an overall count of 134 URLs. These URLs are listed below:

Design and Implement a Data Warehouse (11%)

Design and implement dimensions

• Introduction to Dimensions (Analysis Services – Multidimensional Data) http://msdn.microsoft.com/en-us/library/ms175439.aspx
• Attributes and Attribute Hierarchies http://msdn.microsoft.com/en-us/library/ms174760.aspx
• Attribute Relationships http://msdn.microsoft.com/en-us/library/ms174557.aspx
• SQL Server Analysis Services Interview Questions Part II – Dimensions https://www.mssqltips.com/sqlservertip/2662/sql-server-analysis-services-interview-questions-part-ii–dimensions/
• Conformed dimensions http://www.jamesserra.com/archive/2011/11/conformed-dimensions/
• Slowly Changing Dimension Transformation http://msdn.microsoft.com/en-us/library/ms141715.aspx
• Analysis Services – 11 Star and Snowflake Schemas http://www.youtube.com/watch?v=ewX1nsjX-uA
• Defining Dimension Granularity within a Measure Group http://msdn.microsoft.com/en-us/library/ms166573.aspx

Design and implement fact tables

• Defining a Fact Relationship http://msdn.microsoft.com/en-us/library/ms167409.aspx
• Defining a Many-to-Many Relationship http://msdn.microsoft.com/en-us/library/ms170463.aspx
• Columnstore Indexes http://msdn.microsoft.com/en-us/library/gg492088.aspx
• Different Fact Tables for Partitions http://msdn.microsoft.com/en-us/library/ms174755.aspx
• Configure Measure Properties http://msdn.microsoft.com/en-us/library/ms175623.aspx
• Define Semiadditive Behavior http://technet.microsoft.com/en-us/library/ms175356.aspx
• Use Aggregate Functions http://technet.microsoft.com/en-us/library/ms365396.aspx

Extract and Transform Data (23%)

Define connection managers

• Working with Data in Data Flows http://msdn.microsoft.com/en-us/library/ms141040(v=sql.105).aspx
• Data Flow How-to Topics (Integration Services) http://msdn.microsoft.com/en-us/library/ms137612(v=sql.105).aspx
• Data Flow http://msdn.microsoft.com/en-us/library/ms140080.aspx
• Integration Services Transformations  http://msdn.microsoft.com/en-us/library/ms141713.aspx
• SSIS: List of Transformations  http://social.technet.microsoft.com/wiki/contents/articles/6752.ssis-list-of-transformations.aspx
• Debugging Data Flow http://msdn.microsoft.com/en-us/library/ms137944.aspx
• Lesson 1: Creating the Project and Basic Package http://msdn.microsoft.com/en-us/library/ms170419(v=sql.110).aspx
• Lesson 2: Adding Looping http://msdn.microsoft.com/en-us/library/ms166566.aspx
• Slowly Changing Dimension Transformation http://msdn.microsoft.com/en-us/library/ms141715.aspx
• Fuzzy Lookup Transformation http://msdn.microsoft.com/en-us/library/ms137786.aspx

Design data flow

• Data Flow Task http://msdn.microsoft.com/en-us/library/ms141122.aspx
• Data Flow http://msdn.microsoft.com/en-us/library/ms140080.aspx
• Integration Services Transformations http://msdn.microsoft.com/en-us/library/ms141713.aspx
• Slowly Changing Dimension Transformation http://msdn.microsoft.com/en-us/library/ms141715.aspx
• Fuzzy Lookup Transformation http://msdn.microsoft.com/en-us/library/ms137786.aspx

Implement data flow

• Working with Data in Data Flows http://msdn.microsoft.com/en-us/library/ms141040(v=sql.105).aspx
• Configure an Error Output in a Data Flow Component http://msdn.microsoft.com/en-us/library/ms140083.aspx
• Debugging Data Flow http://msdn.microsoft.com/en-us/library/ms137944.aspx
• Lesson 1: Creating the Project and Basic Package http://msdn.microsoft.com/en-us/library/ms170419(v=sql.110).aspx
• Lesson 2: Adding Looping http://msdn.microsoft.com/en-us/library/ms166566.aspx
• Data Flow How-to Topics (Integration Services) http://msdn.microsoft.com/en-us/library/ms137612(v=sql.105).aspx
• Configure Outputs Using the Slowly Changing Dimension Wizard http://msdn.microsoft.com/en-us/library/ms141662.aspx

Manage SSIS package execution

• Execution of Projects and Packages http://msdn.microsoft.com/en-us/library/ms141708.aspx
• dtexec Utility http://msdn.microsoft.com/en-us/library/hh231187.aspx
• Different ways to execute a SQL Server SSIS package https://www.mssqltips.com/sqlservertip/1775/different-ways-to-execute-a-sql-server-ssis-package/
• SSIS 2012: Better Execute Package Task http://www.rafael-salas.com/2012/01/ssis-2012-better-execute-package-task.html#!/2012/01/ssis-2012-better-execute-package-task.html
• SQL Server Integration Services Package Scheduling with SQL Agent https://www.mssqltips.com/sqlservertip/1180/sql-server-integration-services-package-scheduling-with-sql-agent/
• SQL Server Agent Jobs for Packages http://msdn.microsoft.com/en-us/library/ms141701.aspx

Implement script tasks in SSIS

• Script Task http://msdn.microsoft.com/en-us/library/ms141752.aspx
• Extending the Package with the Script Task http://msdn.microsoft.com/en-us/library/ms136127.aspx
• Script Component http://msdn.microsoft.com/en-us/library/131c2d0c-2e33-4785-94af-ada5c049821e
• Script Component Debugging in SSIS 2012 http://blogs.msdn.com/b/mattm/archive/2012/01/13/script-component-debugging-in-ssis-2012.aspx

Load Data (27%)

Design control flow

• Control Flow http://msdn.microsoft.com/en-us/library/ms137681.aspx
• Integration Services (SSIS) Variables http://msdn.microsoft.com/en-us/library/ms141085.aspx
• Control Flow vs Data Flow http://msdn.microsoft.com/en-us/library/ms140246.aspx
• http://bidn.com/blogs/BradSchacht/ssis/305/control-flow-vs-data-flow
• SSIS Designer http://msdn.microsoft.com/en-us/library/ms137973.aspx
• Integration Services Tasks http://msdn.microsoft.com/en-us/library/ms139892.aspx
• Integration Services (SSIS) Logging http://msdn.microsoft.com/en-us/library/ms140246.aspx

Implement package logic by using SSIS variables and parameters

• SSIS 2012: Parameters and Variables, what is the difference? http://www.rafael-salas.com/2011/12/ssis-2012-parameters-and-variables-what.html#!/2011/12/ssis-2012-parameters-and-variables-what.html
• Integration Services (SSIS) Parameters http://msdn.microsoft.com/en-us/library/hh213214.aspx
• SSIS Basics: Introducing Variables http://www.simple-talk.com/sql/ssis/ssis-basics-introducing-variables/
• Package Configurations http://msdn.microsoft.com/en-us/library/ms141682.aspx
• Using Parent Package Variables in Package Configurations http://www.sqlis.com/sqlis/post/Using-Parent-Package-Variables-in-Package-Configurations.aspx
• Easy Package Configuration http://www.sqlis.com/sqlis/post/Easy-Package-Configuration.aspx
• Change Package Properties Using Parameters-SSIS 2012 http://www.sqlservercentral.com/blogs/samvangassql/2012/04/24/change-package-properties-using-parametersssis-2012/
• Use Property Expressions in Packages http://msdn.microsoft.com/en-us/library/ms141214.aspx

Implement control flow

• Using Sequence Containers in SSIS http://www.bidn.com/blogs/TomLannen/bidn-blog/2620/using-sequence-containers-in-ssis
• Restart Packages by Using Checkpoints http://msdn.microsoft.com/en-us/library/ms140226.aspx
• Execute SQL Task (Transaction control) http://msbiworld.wordpress.com/2012/02/12/ssis-execute-sql-task-transaction-control/
• Debugging Control Flow http://msdn.microsoft.com/en-us/library/ms140274.aspx
• Precedence Constraints http://msdn.microsoft.com/en-us/library/ms141261.aspx
• Designing Your SSIS Packages for Parallelism http://technet.microsoft.com/en-us/sqlserver/ff686759.aspx
• How to pass parameter values to the stored procedure dynamically in ssis http://social.technet.microsoft.com/Forums/en-US/sqlintegrationservices/thread/3973ca4c-195f-498f-ba1d-ee334eccd368
• Execute Package Task http://msdn.microsoft.com/en-us/library/ms137609.aspx
• Are SSIS Package Template Useful in 2012 http://stackoverflow.com/questions/11347545/are-ssis-package-template-useful-in-2012
• Save a Package as a Package Template http://msdn.microsoft.com/en-us/library/ms345191.aspx

Implement data load options

• SSIS Design Pattern – Incremental Loads https://andyleonard.blog/2007/07/ssis-design-pattern-incremental-loads/
• Change Data Capture (SSIS) http://msdn.microsoft.com/en-us/library/bb895315.aspx
• Perform an Incremental Load of Multiple Tables http://msdn.microsoft.com/en-us/library/bb895287.aspx
• SSIS Design Pattern – ETL Instrumentation, Part 1 https://andyleonard.blog/2007/08/ssis-design-pattern-etl-instrumentation-part-1/
• SSIS Design Pattern – ETL Instrumentation, Part 2 https://andyleonard.blog/2007/11/ssis-design-pattern-etl-instrumentation-part-2-2/
• SSIS Design Pattern – ETL Instrumentation, Part 3 https://andyleonard.blog/2007/11/ssis-design-pattern-etl-instrumentation-part-3/

Implement script components in SSIS

• Data Warehousing – Slowly Changing Dimensions http://sqlserverpedia.com/wiki/Data_Warehousing_-_Slowly_Changing_Dimensions
• Slowly Changing Dimension Transformation http://msdn.microsoft.com/en-us/library/ms141715.aspx
• Configure Outputs Using the Slowly Changing Dimension Wizard http://msdn.microsoft.com/en-us/library/ms141662.aspx
• Script Component http://msdn.microsoft.com/en-us/library/ms137640.aspx
• Developing Specific Types of Script Components http://msdn.microsoft.com/en-us/library/ms345170.aspx
• Web Service Task http://msdn.microsoft.com/en-us/library/ms140114.aspx
• SSIS Nugget: Get error descriptions http://consultingblogs.emc.com/jamiethomson/archive/2005/08/08/1969.aspx

Configure and Deploy SSIS Solutions (24%)

Troubleshoot data integration issues

• Troubleshooting Tools for Package Development http://msdn.microsoft.com/en-us/library/ms137625.aspx
• Integration Services (SSIS) Logging http://msdn.microsoft.com/en-us/library/ms140246.aspx
• Runtime Debugging Using Data Taps in SSIS 2012 http://blog.gnetgroup.com/bi/2012/11/08/runtime-debugging-using-data-taps-in-ssis-2012/
• Debug a Package by Setting Breakpoints on a Task or a Container http://msdn.microsoft.com/en-us/library/ms141754.aspx

Install and maintain SSIS components

• Install Integration Services http://msdn.microsoft.com/en-us/library/ms143731.aspx
• SQL Server Integration Services 2012 – Project Deployment Model http://www.element61.be/e/resourc-detail.asp?ResourceId=545
• Running and Managing Packages Programmatically http://msdn.microsoft.com/en-us/library/aa337077.aspx
• Loading and Running a Remote Package Programmatically http://msdn.microsoft.com/en-us/library/ms403355.aspx
• SSIS Catalog http://msdn.microsoft.com/en-us/library/hh479588.aspx
• Quick Reference: SSIS in 32- and 64-bits http://toddmcdermid.blogspot.com/2009/10/quick-reference-ssis-in-32-and-64-bits.html#!/2009/10/quick-reference-ssis-in-32-and-64-bits.html

Implement auditing, logging, and event handling

• Audit Transformation http://msdn.microsoft.com/en-us/library/ms141150.aspx
• Integration Services (SSIS) Logging http://msdn.microsoft.com/en-us/library/ms140246.aspx
• Logging and Defining Log Entries in a Data Flow Component http://msdn.microsoft.com/en-us/library/ms345169.aspx
• Execute package in SSIS catalog taking advantage of the new project deployment model ,and the logging and reporting feature http://geekswithblogs.net/LifeLongTechie/archive/2012/11/14/time-to-stop-using-ldquoexecute-package-taskrdquondash-a-way-to.aspx
• What Events are Included in the SSIS Catalog Log Levels http://www.mattmasson.com/index.php/2011/12/what-events-are-included-in-the-ssis-catalog-log-levels/
• SSIS 2012 Logging http://svangasql.wordpress.com/2012/06/12/t-sql-tuesday-31-ssis-2012-logging/
• SSIS Catalog: Part 5 – Logging and Execution Reports http://www.rad.pasfu.com/index.php?/archives/78-SSIS-Catalog-Part-5-Logging-and-Execution-Reports.html
• Easy Package Execution monitoring in SSIS 2012 hhttp://visakhm.blogspot.com/2012/09/easy-package-execution-monitoring-in.html

Deploy SSIS solutions

• Deployment of Projects and Packages http://msdn.microsoft.com/en-us/library/hh213290.aspx
• Deploy Packages by Using the Deployment Utility http://msdn.microsoft.com/en-us/library/ms141693.aspx
• SSIS 2012 Deployment Models http://www.youtube.com/watch?v=viaMhHKOoEY
• SSIS Catalog http://msdn.microsoft.com/en-us/library/hh479588.aspx
• Validating SSIS 2012 packages programmatically via SSIS Catalog Managed Object Model http://stackoverflow.com/questions/12179934/validating-ssis-2012-packages-programatically-via-ssis-catalog-managed-object-mo
• dtutil Utility http://msdn.microsoft.com/en-us/library/ms162820.aspx
• dtexec Utility http://msdn.microsoft.com/en-us/library/hh231187.aspx

Configure SSIS security settings

• Security Overview (Integration Services) http://msdn.microsoft.com/en-us/library/ms137833.aspx
• Integration Services Roles (SSIS Service) http://msdn.microsoft.com/en-us/library/ms141053.aspx
• SSIS Catalog Access Control Tips http://blogs.msdn.com/b/mattm/archive/2012/03/20/ssis-catalog-access-control-tips.aspx

Build Data Quality Solutions (15%)

Install and maintain Data Quality Services

• Introducing Data Quality Services http://msdn.microsoft.com/en-us/library/ff877917.aspx
• Install Data Quality Services http://msdn.microsoft.com/en-us/library/gg492277.aspx
• Run DQSInstaller.exe to Complete Data Quality Server Installation http://msdn.microsoft.com/en-us/library/hh231682.aspx
• DQS Security http://msdn.microsoft.com/en-us/library/hh213045.aspx
• Manage DQS Users in SSMS http://msdn.microsoft.com/en-us/library/hh510409.aspx
• Grant DQS Roles to Users http://msdn.microsoft.com/en-us/library/gg492283.aspx
• Data governance hhttp://en.wikipedia.org/wiki/Data_governance

Implement master data management solutions

• Install Master Data Services http://msdn.microsoft.com/en-us/library/ee633752.aspx
• Master Data Services Overview http://msdn.microsoft.com/en-us/library/ff487003.aspx
• Managing Data Warehouse Dimensions with MDS (part 1) http://technet.microsoft.com/en-us/sqlserver/hh709039.aspx
• SQL 2012 Master Data Services – Hierarchies and Collections http://technet.microsoft.com/en-us/sqlserver/hh780975.aspx
• Deploying Models (Master Data Services) http://msdn.microsoft.com/en-us/library/ff486956.aspx
• Administrators (Master Data Services) http://msdn.microsoft.com/en-us/library/ff487051.aspx
• Security (Master Data Services) hthttp://msdn.microsoft.com/en-us/library/hh231026.aspx

Create a data quality project to clean data

• Create a Data Quality Project http://msdn.microsoft.com/en-us/library/hh510393.aspx
• Run the Data Quality Client Application http://msdn.microsoft.com/en-us/library/hh213023.aspx
• Data Cleansing http://msdn.microsoft.com/en-us/library/gg524800.aspx
• Data Profiling and Notifications in DQS http://msdn.microsoft.com/en-us/library/hh213055.aspx
• Creating a knowledge base and cleansing data using Data Quality Services in SQL Server 2012 – Part 3 https://www.mssqltips.com/sqlservertip/2577/creating-a-knowledge-base-and-cleansing-data-using-data-quality-services-in-sql-server-2012–part-3/
• Getting Started with Data Quality Services of SQL Server 2012 Using SSIS – Part 4 https://www.mssqltips.com/sqlservertip/2593/getting-started-with-data-quality-services-of-sql-server-2012-using-ssis–part-4/
• Demo: Deduplicating Data with SQL Server 2012 Data Quality Services http://channel9.msdn.com/posts/SQL11UPD05-REC-06

Bonus Content

• Analysis Services Tutorials (SSAS) http://msdn.microsoft.com/en-us/library/hh231701.aspx
• Data Mining Tutorials (Analysis Services) http://msdn.microsoft.com/en-us/library/bb677206.aspx
• Reporting Services Tutorials (SSRS) http://msdn.microsoft.com/en-us/library/bb522859.aspx
• SSIS Tutorial: Deploying Packages http://msdn.microsoft.com/en-us/library/de18468c-cff3-48f4-99ec-6863610e5886
• Introduction to SQL Server Data Tools https://www.mssqltips.com/sqlservertip/2804/introduction-to-sql-server-data-tools/
• SQL Server Data Tools Connected Database Development https://www.mssqltips.com/sqlservertip/2832/sql-server-data-tools-connected-database-development/
• References SSIS articles by a Microsoft employee, including many on SSIS 2012 https://www.mssqltips.com/author/arshad-ali/
• Logging – Level 11 of the Stairway to Integration Services http://www.sqlservercentral.com/articles/Integration+Services+(SSIS)/95353/
• SQL Server Reporting Services 2012 Drilldown Features https://www.mssqltips.com/sqlservertip/2831/sql-server-reporting-services-2012-drilldown-features/
• Graphing the Sine Function in SQL Server Reporting Services https://www.mssqltips.com/sqlservertip/2843/graphing-the-sine-function-in-sql-server-reporting-services/
• MDX Guide for SQL Folks: Part I – Navigating The Cube http://www.sqlservercentral.com/articles/MDX/91228/
• MDX Guide for SQL Folks: Part II – Hierarchies and Functions http://www.sqlservercentral.com/articles/MDX/91730/

The additional URLs denoted as “Bonus Content” are items I encountered that did not strictly pertain to the exam sections, but I thought they might of interest to you.

This article is the first in a two-part series on URLs for the for passing the Implementing a Data Warehouse exam. A second article will soon be forthcoming with a URL database with references for the exam. This will be enhanced with more references and several examples will be provided of how to query the database to help you prepare better for the exam. Another purpose for the database in the forthcoming article is to provide a free, open source study aid for the 70-463 exam that will allow others to add references and/or whole web sites that they deem of merit. In this way, the database can reflect the collective wisdom of many SQL Server professionals studying for the exam as well as others who have already passed the exam. In the meantime, I solicit your feedback on the workbook associated with this tip so that I can make the database better for all in its first edition.

Next Steps

This article drills down on the type of content that you can expect to encounter on the SQL Server 2012 Implementing a Data Warehouse exam. In particular, the tip highlights the major topics and sub-topics that Microsoft indicates to be within the scope of the exam. A cursory review of these topics indicates you should become familiar with Analysis Services, Data Quality Services, Master Data Services, and especially Integration Services.

Twenty exam sub-topics were identified as key areas for you to focus on during your exam preparation. In addition, 134 URLs are available to help you get started learning more about the 20 sub-topics. When using URLs that point to Microsoft web sites be sure and view the index to search for other related material that may help you master a sub-topic.

Please consider posting a comment about ways to improve the URL resources. For example, leave comments to this article about other URLs that you deem of particular value or interest. Feel free to suggest that URLs be dropped if you find better ones with which to replace them or you find gross errors that will cause those taking the 70-463 exam to provide a wrong answer.

Here are some links referenced in this tip.

• Microsoft Learning web site with major topics and sub-topics for the 70-463 exam
• Overview of SQL Server 2012 certifications and their associated exams