Home > SQL Server 2012 > Preparing for 70-463 Exam : Implementing a Data Warehouse with Microsoft SQL Server 2012

Preparing for 70-463 Exam : Implementing a Data Warehouse with Microsoft SQL Server 2012


After passing 70-461 exam last year I’m now preparing for 70-463 exam i.e. Implementing a Data Warehouse with Microsoft SQL Server 2012. This exam mainly focuses on implementing Data Warehouses with Dimension & Fact tables, working with SSIS packages and Data Quality solutions. Thus being a Database Developer (primarily) this exam will be bit tough for me as I’ve not worked with Dimensional modelling, SSIS and DQS.

For preparation I’m referring to Training Kit (Exam 70-463) Implementing a Data Warehouse with Microsoft SQL Server 2012 book.

This exam is primarily intended for ETL and Data Warehouse (DW) developers who create Business Intelligence (BI) solutions, and whose responsibilities include Data Cleansing, and Extract Transform Load and Data Warehouse implementation.
 

–> The Exam is divided into 5 modules:

1. Design and Implement a Data Warehouse | 11%
– Introducing Star and Snowflake Schemas
– Design and Implement Dimensions
– Design and Implement Fact tables
– Managing the Performance of a Data Warehouse
– PREP links: Introduction to dimensions (Analysis Services – multidimensional data) | Dimension relationships | Columnstore indexes

2. Extract and Transform data | 23%
– Define Connection managers
– Design Data Flow
– Implement Data Flow with Transformations
– Control Flow tasks and Containers, Precedence Constraints
– Manage SSIS package execution
– Implement Script tasks in SSIS
– PREP links: Integration Services (SSIS) connections | Data flow | Slowly changing dimension transformation

3. Load Data | 27%
– Design control flow
– Implement package logic by using SSIS variables and parameter
– Implement Control flow
– Implement Data Load options
– Implement Script components in SSIS
– Slowly Changing Dimensions
– Preparing a Package for Incremental Load
– Package Transactions, Checkpoints, Event Handlers
– PREP links: Integration Services transactions | Developing a custom task | Integration Services (SSIS) parameters

4. Configure and Seploy SSIS solutions | 24%
– Troubleshoot Data Integration issues
– Install and Maintain SSIS components
– Implement Auditing, Logging, and Event handling
– Deploy SSIS solutions
– Configure SSIS security settings
– Data Mining Task and Transformation
– Preparing Data for Data Mining
– Implementing SSIS Fuzzy Transformations
– PREP links: Troubleshooting tools for package development | Load-balancing packages on remote servers by using SQL Server Agent | Integration Services (SSIS) logging

5. Build Data Quality Solutions (DQS) | 15%
– Install and maintain Data Quality services
– Creating and Maintaining a Knowledge Base
– Create a Data Quality project to clean data, Profiling Data and Improving Data Quality
– Using DQS and the DQS Cleansing Transformation
– Implement Master Data Management (MDM) solutions
– Using Master Data Services Add-in for Excel
– PREP links: Install Data Quality Services | Install Master Data Services | Master Data Services features and tasks
 

-–> You can visit following Study materials to prepare for this Exam:

Microsoft Link for this Certification: http://www.microsoft.com/learning/en-us/exam-70-463.aspx

Book on AMAZON.com: Training Kit (Exam 70-463) Implementing a Data Warehouse with Microsoft SQL Server 2012
 

I will try to update this blog post or will put up a new post with my learning while preparing for this exam.

All The Best!!!


  1. February 3, 2016 at 3:51 pm

    UPDATE on SQL 2014:

    MCSA (461/462/463) exams will be having SQL 2012 content only.

    MCSE (DP: 464/465 and BI: 466/467) exams are updated with SQL 2014 topics. And same will go with 2016 after release.

    ~manoj

  2. Maanvi
    January 19, 2016 at 11:16 pm

    hello Guys..i want switch my profile to SQL server DBA ..and I’m very new to it..i have to read each and everything of it..i even have to study the basic queries..i have around 3 years of working experience in technical support….as i have started studying about SQL Server i feel i can some how will be able to attempt the 70-462 exam..i don’t think 70-461 will be easy for me even if i do hard work and i can not even think about 70-463…as i don’t have any work experience as a DBA, at any cost i need to have the certification…really need you guys suggessions..how do i work on it..is passing only the 70-462 exam will be considerable?…please put your valuables suggessions

  3. Huzaifa
    January 19, 2016 at 5:34 pm

    Hi Manoj,
    Do i need to give 70-461 exam or any other exam before giving this exam? Also can you please let me know the value of this certification.

    • January 19, 2016 at 5:55 pm

      No @Huzaifa, you can give any of the 3 exams in any order.
      Value wise I’m not sure, but good for self-valuation at least🙂

      • Huzaifa
        January 29, 2016 at 9:31 am

        Thank you Manoj. What is the validity period for the cert.Will validity get extended if i give any other exam from MCSA or MCSE? (LIke for Cisco certs)

  4. alison
    December 23, 2015 at 4:31 am

    Manoj – did you take the 463 exam yet? the training press received some poor reviews as far as not preparing folks for the exam – so wondering if this was your principal prep guide – and to see if it was sufficient. please let me know.

    i passed 461 in oct, and scheduled for 462 on 1/5. the microsoft double shot was a great way to take a REAL practice exam – tweak the prep/studying then pass. bummer i didn’t know about it early enough to squeeze in the third exam.

    really appreciate the info.

    • December 23, 2015 at 12:59 pm

      No @Alison, I did not get time to give that exam, and since then its not in my pref list.

      Regarding the book I haven’t heard any bad review. But yes you should not be totally dependent on the book, and should also do good hands-on & practice with SQL Server.

      Best of luck, cheers, Manoj.

  5. Patrick
    May 19, 2014 at 8:39 am

    Hi Poonam,

    I used only the 70-463 Training Kit book and it was sufficient for me. I had been using SSIS with BIDS 2008 as well as Data Tools that came with SQL Server 2012 on a daily basis for my job for the 7 Months prior to taking the test. Since I had been using the environment so extensively, I felt comfortable in the test when asked to use the tools. I feel that you would be going into the exam with the same level of preparation I had and should come out successful as well.

  6. Poonam
    May 14, 2014 at 4:31 am

    Hi Patrick,

    Congrats for passing the Data Warehouse Certification Exam! Could you please share with us what books did you refer to study for the exam? How long did you for the exam? I have already passed 70-461 exam and I have around 6 months of experience working in a Business Intelligence team. What do you think how hard the exam is?

  7. Patrick
    April 5, 2014 at 3:22 am

    Hi Manoj,

    I passed this test today, and I have to say it was much more enjoyable thank the 70-461 exam. I look forward to your analysis of your experience that I could compare mine to.

    -Patrick

    • May 14, 2014 at 10:13 am

      Congratulations Partick and best of luck for the next exams!

  8. ekkowekko
    January 16, 2014 at 12:38 pm

    Thank you for the information. I’m also preparing for the exam. Did you already take the exam?

    • January 16, 2014 at 1:51 pm

      Thanks @Srikanth & @Ekkowekko, for your comments.
      I’m planning to give this exam from long time, but my office work load is delaying this. I’ll take this exam very soon and post the experience here.

      ~manoj

  9. Srikanth M
    December 5, 2013 at 5:07 pm

    Thank You Manoj for the overview of 70463 exam.
    I also started prepating for this exam.

  1. May 1, 2015 at 8:12 pm

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 530 other followers

%d bloggers like this: