02 Mar

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

Note: This isn’t really a review. It’s more of an example, one of many, of what I’m finding in the book.  Also, I’m mellowing on the book’s content.  It’s been useful, unlike the exercises.

I’ve been studying for 70-463 and frankly it’s been going slower than the earlier two, much slower. In fact, it’s been a real struggle to study for, so much so that I’m finding excuses to not study. What’s been especially frustrating is that I thought I knew something about data warehousing and SSIS but I struggle to get even basic things done with the exercises in this book. Well, it turns out, after a lot of digging, that maybe it isn’t all me. Here’s one example from the book.

Chapter 13, titled “Troubleshooting and Performance Tuning”, which is all good, as it fits right in with the first exercise and will actually happen. It will break and you won’t know why.

Exercise 1 consists of the following steps.

  1. Install missing database objects, assuming you didn’t install them earlier, by executing the chapter 5 code scripts to build them for the TK463 DW database.
  2. Open the Chapter 13 project in the starter kit.
  3. Execute the package to retrieve an error.
  4. Look in the data flow to find the error.
  5. Look on the progress tab. It also lists the specific error.
  6. Then it takes you through a couple of more steps to fix the error.

Here is what actually happens.

  1. The package crashes. Good!
  2. The error that comes up is not the error in the lesson 13 instructions which is supposed to be about truncating tables and a derived column transformation. Instead, you get this.

Error 5 DimCustomerNew.dtsx Validation error. Dim Customer: Dim Customer: Opening a rowset for “[stg].[SalesTerritory]” failed. Check that the object exists in the database.

This is a hard crash and there is nothing you can do about it. You can’t even get to the progress tab because the package won’t confirm. At this point I attempted the following:

  1. Tried both the starter and the completed package. Same error.
  2. Ran the chapter 5 setup script, several times, because, just maybe it would work the 4th time after not working the first three.
  3. Searched the PDF for a script to create the [stg].[SalesTerritory]. There were no references with it in a CREATE TABLE script.
  4. Executed every SQL script from chapter 5 to chapter 13. Chapter 13 had the script to build the stg.Salesterritory table.

You are probably wondering why I tried everything else first. Well, the instructions don’t mention the chapter 13 code folder, anywhere, they mention chapter 5. Second, this kind of thing has happened to me in chapter 5, chapter 7, chapter 10, chapter 4 was a fight but I got it to work. In fact, it’s happened so often that more than once I’ve thrown the book in frustration and walked away from a chapter. My assumption was that I had skipped a step in an earlier exercise or chapter out of frustration.

Nope, the starter code and the solution code, just like in a bunch of other chapters, is defective. So I run the Chapter 13 scripts and guess what happens.

The package runs perfectly. Everything turns green without error.

Normally that would be great news, except for one thing, it’s supposed to generate an error.

I gave up on the exercise at that point.

When I look at the earlier books, and some of the frustrations I had with them, I have to say that they pale in comparison to this. I would take the 70-461 book, rearrange every chapter in random order, instead of just the couple where they seemingly did exactly that, and it would still be light years ahead of this book.

Oh, and what could make this even better? Exercise 2, where everything runs without error, appears not to produce any results after you run the code. That’s probably a product of exercise 1 not working correctly, although without error, in some fashion.

If it were only chapter 13 I could move on but I find every exercise a struggle to complete. Even the one’s that work need multiple passes because it’s easy to miss a step where the author decided to combine multiple steps in an illogical fashion. By comparison, Knight’s 24 Hour Trainer, is incredibly easy to follow, has similar exercises in many cases, is clearly written, and almost everything works on the first pass.

I have no idea if the content in the book is enough to pass the test, I’m struggling to get past the exercises, and if it is I’ll revise my rating of the book, but the exercises are a -5.

Final note: This book is running 3-1/2 stars from 6 reviews on Amazon. Some of the reviewers gave it 5 stars. If this doesn’t make you distrust reviewers on Amazon I don’t know what would. I guess maybe they are just looking at the content and not doing the exercises but the exercises themselves are as sloppily put together as anything I’ve seen in a book.

You can get the book at the link below, and you likely will if you are studying for this test, because it’s the only one out there. Just know going in, it will be unpleasant if you decide to do the exercises.

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

6 thoughts on “Training Kit (Exam 70-463): Implementing a Data Warehouse with Microsoft® SQL Server® 2012

  1. Pingback: The Review: Training Kit (Exam 70-463): Implementing a Data Warehouse with Microsoft® SQL Server® 2012 | Erik Haselhofer

  2. I am having the same issues, and I am getting very frustrated with them. This morning, working on Exercise 6.1, all that was necessary was to change the SQL statements to truncate a table from a Direct Query to a variable. That works, but the package fails on a validation error. Same sort of thing happened on most of the Chapter 5 exercises.

    • Hi Tim,

      Yeah, that’s the worst part. I’ve never been a big fan of the Training Kit books but the 3 for the MCSA have had some pretty serious problems. The 70-461 book had out of order chapters.

      I wound up using the official documentation and that worked pretty well. But, alas, besides that there really aren’t any other alternatives.

  3. Agreed, the exercises are full of mistakes and it’s incredibly frustrating, and definitely caused multiple dips in my motivation. Even with the corrections listed in the errata on the publisher’s website I couldn’t get a lot of them working as expected!

    • There’s a ton of ground to cover, so I could deal with some mistakes, but the exercises were just painful. With SSIS you simply have to work through it, and when the working through don’t work, damn.

      And so, I did this. It worked really well, much better than studying did, so I guess all things considered, it was a win.

  4. Wow, I thought it was just me but after read this I feel better. After none of the practicals in Chapter 5 worked I started the book again from the beginning assuming I had missed something but no, the exercises just don’t work. Incredibly painful and frustrating, my new method of learning this is to understand what is being demonstrated in the exercises and just accept that they do not work.

Leave a Reply

Your email address will not be published. Required fields are marked *