20 Mar

70-463 Passed

Tuco: “You want to know who you are? Huh? You want to know who’s son you are? You don’t, I do, everybody does… you’re the son of a thousand fathers, all bastards like you.”
The Good, the Bad and the Ugly (1966)

Well, for my legion of readers, the three who aren’t throwing bot-spam my way, I passed this thing.  It turned out to be easier than I had feared.  Without going into any specifics, as I’m now non-disclosed, it was surprisingly clean and direct.  I didn’t post a score that will have Microsoft checking for hacks but I passed with room to spare.  If I had studied for the T-SQL test like this I might not have missed a question.

Anyways, I’ll start with some new stuff soon and I plan to review the 70-463 Training Kit, again, as well as go over a generic study guide for the test.  I got really sideways in the preparation process and there are some really specific reasons for that which might make for interesting reading.

One last note on the SSIS content.

I have 42 posts on SSIS scheduled to hit this blog (through 04/27).  All of those were written before taking the exam.  As such, except to make corrections (I’m sure there are spelling, grammar and technical issues in them), I won’t touch them again, nor will I clarify any of them in the context of whether they may have, or may not have, been on the test.  What this will do is produce some weird time-distortions where posts show up saying “I think this will be on the test”, as if I haven’t taken the test, yet, here I am having taken the test.  Here is what happened.

  • 02/? – 03/05 – Going nowhere, and getting there fast, trying to study for the 70-463 test.
  • 03/05 – In a fit of complete frustration, on the verge of giving up, I make a decision: to study by writing blog posts.
  • 03/06 – 03/18 – 60 posts written and scheduled through 04/27, averaging more than 2000 words/day.
  • 03/19 – 70-463 taken and passed.
  • 03/20 – Stops, for the most part, writing about SSIS.

So, like I said, it may make for offbeat reading, but combining this with a massive plowing of the MSDN, and other study resources, I got through it.

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