06 May

SQL Cookbook

SQL Cookbook

The SQL Cookbook, Available At Amazon.com.

I’m overdue on this book as I’ve been reading, and posting about it, for several days now.  In fact, it’s pretty much replaced my desire to write about the SAS Advanced certification.  Frankly, it’s exactly the kind of thing I’ve been looking for in a SQL book, slightly exotic queries and approaches, from a variety of platforms.  Outside of a couple of bugs, and the fact that it’s slightly dated (published in 2006) it’s an excellent book.

In fact, I’ll start the review process by giving it 5 stars.  It’s rare that I read a tech book, outside of certification books, from start to finish.  Usually, I get part way in, get bored, and stop.  This book has kept me plugging through it.

What this book does is present a series of problems / questions that it solves with SQL.  It shows variants for the following platforms: SQL Server, MySQL, Oracle, PostgreSQL and DB2.  Frankly, I think this is awesome.  One of the problems I’ve run into over the years, and it’s something I’ve tried to discuss on this site, is that people don’t understand that different platforms do things differently.  That it covers multiple platforms, is by far, the best part of this book.

The problems / questions, for the most part, range from really basic, to more complex variations on the problem.  This is useful in a couple of ways.  First, a novice won’t know the basic approach so showing it allows the book to be useful for even relative beginners.  The more complex problems, while not often things you would do on a job, are the kinds of things some wanker would ask in an interview.  And, unless you are exceptionally good, or lucky, scrambling to throw together a SQL solution, to a problem that might take a while to even get clear in your head won’t happen in an interview.  This book can help you be prepared for that sort of thing.  It’s a terrible approach to interviewing but I can attest that it will happen.

The book also moves at a really fluid pace.  If you’ve written SQL in the past you don’t need to plow each example so you can skip around to pick the areas you don’t know that well or are curious about.  It doesn’t need a thorough reading.

While I grade the book at 5 stars, there are a few problems with it.

First, the book, is kind of written as a reference.  But, truthfully, with the internet, and places like Stackoverflow, you can almost always get a workable answer, somewhere.  However, despite that, I would still trust what I find here more than what I might find online.  Books, even as bad as O’Reilly has been with a few recent books, should still have an editing process.  Online, who knows what you might stumble on, and who knows if it’s the best way.

Second, because the book was published in 2006 it doesn’t include some things that have been added in later versions.  However, I believe, and would encourage people, to rely on basic SQL as a general rule.  The catch with the tricks is that they are almost always version specific and if you ever switch, or wind up working with some other platform, then you could be in trouble.  I think, mostly, the author tends towards this approach and I appreciate it.

Finally, the book does have a couple of errors or less than ideal approaches.  I’ve written posts about a couple of those.  Unfortunately, O’Reilly has gotten sloppy with their QA, although it’s a lot worse today than it was when this book was written.  I’ve had some terrible experiences with them, see my reviews on the Microsoft Training Kits, for example.  Overall though, I’m not that bothered by what I’ve noticed, or not noticed, and the Errata, especially on a book 6 years old, is decent.

Overall, I’ve really enjoyed this book.  I’m not quite finished (2/3rds of the way) but it’s the rare technical book that I will take all the way home, and I can’t help but recommend it.   I wish I’d found it a few years ago.  It would have helped to save me some grief in a few cases.  So, if you are learning SQL, or want to dabble in some more exotic approaches to problems, get this book, and get it now.

You can get this book at Amazon.com.

04 Apr

The Little SAS Book: A Primer, Fourth Edition

Available at Amazon.com

Available at Amazon.com

This is the kind of technical book that I really like.  It takes a topic, breaks it into simple and logical chunks and rolls through them with an example for each.  And, it does a good job of it.

First, this book is not an in-depth comprehensive review of statistics.  It’s directed at the SAS language, and for the most part, lines up nicely with the SAS Base certification.

Second, it’s not a deep review of the SAS language.  It takes you through most of the basics into some slightly more advanced topics.  As a starter kit, which is what it’s meant to be, it’s really good.

Third, and I’ve seen this put some people off, it has a relatively casual style to it.  I happen to like that style of writing.  I don’t need to read another stale author (especially since I have to proofread the stuff I write).  I’d rather read a book with examples from a frog jumping contest, than some of the alternatives.  It’s just plain more interesting.

Now, with the semi-negative out-of-the-way, here’s is what the book does.  It takes you through a series of examples, around 100, give or take, using SAS.  Most, but not all, of the chapters / sections are only a couple of pages in length, usually, two.  The examples are quick, easy to follow and well done for the most part.

The book covers the following topics:

  • Data sets
  • PROC SORT, PROC PRINT and a few others.
  • ODS
  • Combining data sets.
  • SAS Macros.
  • Graphing.
  • Exporting data.
  • Debugging data.

Like I said earlier, it’s not in-depth, but as a companion to the SAS Base Certification Guide, or as introduction to the language, it’s an excellent buy.  I have no problems recommending this book to someone wanting to learn SAS.

There is one negative about the book, it’s not cheap.  This, unfortunately, is a consistent trend with SAS books.  However, unlike the SAS certification books, this one is available used at a reduced price.  You can get it at the link below:

The Little SAS Book: A Primer, Fourth Edition

03 Apr

SAS Certification Prep Guide: Base Programming for SAS 9, Third Edition

Available at Amazon.com.

Available at Amazon.com.

I’m not sure what to say about this book except that If you want to pass the certification you probably need to get it.

The exam objectives are very broad, or maybe vague, so if you want to know what to study, you probably need to get this book.

Sure the book is overly long, has a few errors, is a bit repetitive, and it’s not cheap, but, you probably need to get it.

Yes, this reviews sucks, but if you want to pass the SAS Base exam you probably need to get this book.

Available at Amazon.com.

SAS Certification Prep Guide: Base Programming for SAS 9, Third Edition

 

24 Mar

Professional Microsoft SQL Server 2012 Integration Services

Available at Amazon.com

Available at Amazon.com

This will be short.

I wanted to add this book to the review section because I did use it during the SSIS test prep process. Unfortunately, I came to heavily rely on the MSDN near the end so it was down on the list in terms of how much I used it.

I used the book as a reference. For instance, when I wanted to know more about blocking transformations and scripting this this was my resource. It also does a good job of covering transformations and tasks but I’m not sure that their 24-hour book isn’t the better choice for that. On the other hand, this has a lot more technical depth than the 24-hour book so it’s kind of an either/or type of thing.

I also have to admit that I didn’t even come close to reading the whole book. I started tuning out around page 200. I was really looking for test preparation, not, detail. And the one thing this book is not, is test preparation.

Also, the book does not cover, at least I didn’t read it if it does, Master Data Services or Data Quality Services. It touches on Data Warehousing, it pretty much has to, but it doesn’t go hard into data warehousing concepts.

In closing, I’m not going to score the book. I don’t think I read it closely enough for that. I’ll just say that it was part of the study process, it helped in a couple of emergency situations and I think it could be useful if you wanted to learn the product. But, for a primary testing resource or as an entry level tome, I think there are better options.

Available at Amazon.com

21 Mar

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

Image2A few weeks ago I did what, I guess you can call a prereview, of this book (you can read it here) and I was very frustrated with it.  I believe, without checking the review that I gave it a -5.  I’ve mellowed a bit, and now rate the book higher, but, I just can’t get to the point that I think “wow, they really nailed it”.  There’s just too much frustration in it for me.

This review will focus on the following things:

  • Content
  • Exercises
  • measureUp practice test.
  • How well it prepares you for the test.

Lets start with the first one of those.

Content

This is one of two areas that I’ve somewhat mellowed on the book.  When I started my writing binge there were a small handful of times where this book was the only place to go.  The MSDN was funky, both of the Knight books, which were very SSIS specific, maybe didn’t cover the concept well, and I wound up using the book as my primary source.  It really does cover the breadth of the content, and there is a lot of breadth to cover.

However, despite 850 pages, there are times you are wishing for something more.  You are wishing for an explanation that just isn’t there.  As such, I wound up using the MSDN, a lot.  In some cases, you get a single paragraph on a transform, and nothing else.  Or, they skim a topic, or, the explanation is confusing.

However, the scope of the book is easily is greatest strength so, for that, it gets 3 Tucos.

tucotucotuco

Exercises

This is a hard thing to score, except for one simple thing, they are why I stopped reading the book.  I can’t begin to tell you how frustrating it is, well maybe my stuff has this same problem, but I don’t have an editor and I’m not getting paid, to do an exercise and it doesn’t work.  It doesn’t work the first, second or third time.  Sometimes, when you dig, and dig far enough, you find out that you missed a crucial step.  Maybe, you were moving too fast, or maybe, the author had a bad habit of lumping steps together.

In some cases, however, the base package that you are supposed to be working from just dies.  In one case, I received an error for a missing table.  The instructions say to load the code from an earlier chapter but that code doesn’t have the table.  I finally find the code in the starter code for the chapter.  The exercise, and they did this every time it was necessary prior to this, doesn’t mention it.  So, I load the table, run the package, and everything runs fine.  One small problem, the code is supposed to throw an error.

That was the last exercise I tried to do.  I simply gave up on the book’s exercises at that point.  The author badly needed to find an intern, or even better a completely non-technical person, hand them the exercises, and watch all the problems.

One last thought on this section.  It’s possible that the author will fix the sample code at some point.  If he did, my overall rating of this book would be much better.  The current state of that code is simply a disaster.

No Tuco’s for the exercises.

measureUp Practice test

This is another case of “what they give, they immediately take away”.  The positives, for me, were that it covered a lot of content.  It is probably the inspiration behind half the SSIS articles on this site.  I would do a sample test, note that I didn’t know an area perfectly, and, there you go, content for an article.  My final test preparation consisted of going through all of the questions (there are 189), noting areas that I was weak on, and using those as an area to focus on.  It really helped in that respect.

On the other hand.  This test almost caused me to give up.  I never came close to passing it, and I mean not even close.  For the first few weeks of studying, I would take the test as a guide post, not as a study guide, and not only did I see very little progress, I actually went backward.  I think 2 weeks prior to taking the test I broke 40%, and I think I scored 30%, one-time after that.  In practice test land, 80% is passing, I never broke 60, yet, I passed the actual test with room to spare.

When I entered the final review phase, and basically just reviewed the test questions, I discovered why I was having so many problems.  There is a lot of awkward language, awkward questions and in some cases answers to questions that I don’t agree with.  On the actual exam itself I don’t think I was confused by a question.  There were a couple where I had to guess at the final answer but there was nothing where I did a “what the heck are they asking here”.

One of the really frustrating things about old-school Microsoft exams is that they used to be vague.  You could look at a question, interpret a couple of reasonable answers, and you had to determine which was “the best” for the situation they were describing.  And, in different situations they might both be “best”.  In a sense, the tests were using vagueness to generate difficulty, rather than using difficulty to generate difficulty.  I feel the same way about this practice test.

However, all of that aside, I think it did help, it’s just that it subtracted from the experience too.  I’ll give it 2-1/2 Tucos.

tucotucohalftuco

Test Preparation

Well, this is what counts, and it will be short.  I stopped studying from the book 2 weeks prior to the test.  I did, in a couple of cases, review concepts using it.  Out of the 60 articles I wrote during that time, maybe, and I really mean maybe, I used it for 10 of those.  So, it had some content that I found useful and worthy.  And, it does touch on everything you will find on the test, and in some cases it even does a good job.  However, for the most part, I found myself relying on the MSDN, or third-party articles almost exclusively.

The one thing, however, that I have to give it credit for, is that this test covers a lot of areas, and the book does touch all of those areas, from what I can tell.  It’s just that, the overall presentation, when combined with the practice tests and the exercises, well, it’s very frustrating.   On the other hand, I did pass the test, and this book was part of it, although the MSDN was much more of a part of it in the end.

So, two Tuco’s for test preparation.

tucotuco

Conclusion

Honestly, I really hate to do what I’m about to do.  The author took a very large topic and produced a ton of content (over 850 pages).  They absolutely deserve to be commended for that.  And given the massive profits they’ll no doubt receive (that’s sarcasm) I hate to down rate a book like this.  Further, a lot of the problems in this book are likely due to not just the author, but sloppy editing and a publishing house that hasn’t done a very good job with these books.  For instance, the 70-461 book had chapters out of order in it.

Still, I can’t shake the fact that I almost quit studying because of it, and not once, not twice, but multiple times I threw the book at a wall in sheer frustration when something didn’t work.  Even if I concede that some of that was me, and surely it was, I never felt that way about the Knight books.  So, my final scores are 2 Tucos in it’s current state, and 3 Tucos if they fix the sample exercises and clean up the presentation of the exercises in the future.

Final Score:

tucotuco

If they fix the exercises:

tucotucohalftuco

You can get the book at Amazon.com at the link below:

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

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

20 Feb

Knight’s Microsoft SQL Server 2012 Integration Services 24-Hour Trainer

I’ve been studying for the 70-463 test and I’ve been having serious problems with the 70-463 Training Kit.  Things just aren’t working.  Maybe it’s me, maybe it’s the book, who knows. SSIS can be really messy and easy to mess up when you are following instructions because it’s so easy to skip a step. All I know for sure is that it was taking me too much time to process parts of that book.

So I switched books to Knight’s 2012 Integration Services 24-Hour Trainer and it’s been a massive improvement.  The explanations are clear, concise and most importantly, they work.  The book also covers a fairly broad spectrum of topics ranging from installation, to data and control flow tasks, variable usage, deployment, error handling and even administration.  None of it is super in-depth, meaning that no way is it top-end test preparation, but it’s more than enough to get you going in a positive direction.

I also have the 2008 version but SSIS has changed in 2012 so I repurchased the book.  From what I can tell a fair amount of the book is similar but there is a stronger emphasis on data warehousing and BI solutions than in the 2008 version.  The 2008 book is mostly, “here’s a EXECUTE SQL TASK and this is what you do with it”.  The 2012 version still does this but it will go deeper in a later lesson by using it in the context of building a fact table or something like that.  Data warehousing topics were barely mentioned in the 2008 edition from what I remember.

The book is also longer than the 2008 version by 140 pages and 13 lessons.  Some of that is just due to the version change but it really is a more substantive book than the prior edition.

I highly recommend the book to someone trying to get their hands dirty with SSIS 2012 for the first time.

Positives

  • I’m at chapter 44, and with one minor exception, everything has worked.
  • Much expanded from the 2008 version.
  • A great introduction to the product.
  • Easy to follow exercises.

Negatives

  • The CD doesn’t include exercise content.  It’s just videos so you still have to download everything.

This and the 2008 edition are available at amazon.com.

Knight’s Microsoft SQL Server 2012 Integration Services 24-Hour Trainer

Knight’s 24-Hour Trainer: Microsoft SQL Server 2008 Integration Services

31 Jan

Training Kit (70-462): Administering Microsoft SQL Server 2012 Databases

70-462 Training Kit

70-462 Training Kit

Well, one more test down. I have to admit, straight up, that I enjoyed this one a lot less than I enjoyed 70-461. When I did the Oracle SQL Expert test it was hard, but honest. I never had the feeling that the official study guide was missing anything major and I felt the questions on the test were really clear and direct. They were just mean at times.

This test felt more like what I remember from Microsoft tests, a tendency to rely on vagueness or very subtle word choices, and minutiae, rather than testing knowledge. Honestly, a large part of this test were simply things that unless you were a full-time DBA, then it would all be 2nd nature to you, are things the rest of us would look up.

But, this is about the book, so here goes.

First, this book doesn’t even make the attempt to be interesting. It’s simply, here are the steps to do Y, for 500 pages. It’s just a straight up grind through the content. I don’t know if that’s the author’s fault, or the content of the test, which is a straight-up grind. Just be aware that this is, a, grind.

Second, the book, at times looks to have been lifted almost directly from the online documentation. Again, that’s probably due to the content, but when you see sentences that are essentially identical to the online content you wonder, a little, as to why you bothered to buy the book.

Other commentators have noted that you need to set up 6 servers. I didn’t do this, I used brute-force on the process, but I can see that helping. There’s so much micro stuff on the test that going through the process could help, they certainly test it. I wish I’d done it but at the same time the test is so heavy on memorization that maybe it wouldn’t have made any difference.

I guess if you’ve gotten this far you’ve figured out that I wasn’t a big fan of this test or the book. The prior training kit was excellent but the content of that book is rife for more interesting writing and reading. The question I can’t really answer is the pain I felt plowing this book, due to the book, the test content or less interest on my part?

Finally, you really have no where else to go to study for the test. The book does provide enough to pass the test, so there is that, but for me it was just painful to get done. This sort of test screams out for a solid study guide and this isn’t that. I felt like I was fighting the book and the test so I’m very happy to have this one in my rear view mirror.

If I had to rate this book I’d probably give it a 2-1/2 out of 5. No way is this a 5, or even a 4 but at least it gets you through the basics of the content.

The book is available at amazon.com.

15 Jan

MySQL 5.0 Certification Study Guide

$38.41 from Amazon.com

I’ve read a fair amount of certification books over the years and to be honest, this was, by far, the best book I’ve read. Everything you need to know is here, the sample questions are excellent, and it should be more than enough to pass the test.

Unfortunately, it’s been almost 2 years since I’ve read the book, and I no longer have a copy, but here is what I remember. The book is broken into 4 sections, one for each exam. Each section runs from 100 to 150 pages and the sections are remarkably focused on each section. Additionally, and I believe I downloaded these online, but there are a series of sample questions that come with the book. They are opened ended questions, rather than T/F or pick one of the four, and if you know them, nail them, you will pass the exams.

Without giving anything away, the developer side was harder than the administrative side. I pretty much cruised the administration part, which could have been because I did them last, or the fact that there is a lot less material there. They could bury you in minutiae but all things considered they were pretty fair in that regard. Plus, everything that I saw in the test was covered in the book. There won’t be any surprises because you didn’t read every line of the online documentation.

Also, and I have to be honest about this, this isn’t the hardest exam in the game. By contrast, the Oracle SQL Expert exam is many times harder and it’s only dealing with SQL, no administration at all. I can’t say much more but there were questions on the MySQL exam that I think you could answer correctly with no MySQL experience, conversely, doing that with the Oracle exam you would be murdered, so to speak.

Finally, the book is a bit dated a this time. We’ve moved beyond 5.0 so if you get it, get it with that caveat.

All that said, if you want to sit this exam, and I believe it’s still offered, and you don’t have 10 years experience with the product, by all means get the book. It really will help you get through the exam as it’s rock-solid test preparation.

Available at amazon.com.

11 Jan

Training Kit (Exam 70-461): Querying Microsoft SQL Server 2012

70461

70-461 Training Kit
Available at amazon.com
Click here to get it.

As I just took this test, and passed, I thought it might be worth reviewing the book, especially since there are no other study resources.

The book itself is a pretty standard Training Kit effort. It’s 17 chapters with 2 or 3 sections each. Each section tells you that you need to know some SQL, etc. and will take anywhere from 15 minutes to 60. further each section comes with 3 test question and each chapter includes a couple of scenarios. I have to be honest and say that I really hate this format. This book is 700 pages and could easily be cut by at least 100 pages.

My version of the book had a couple of bonuses. First, chapters 13 and 14 are out of order. The book goes 12,14,13,15. Further there is some content missing that at release wasn’t on the CD or on the website. The author has indicated that all of this is to be fixed in the next printing of the book. Based on what I see at Amazon you can no longer buy it except from price-gougers. This will probably change soon and it should be available at around $37.00.

Structural problems aside, this is a very good book. It’s an excellent introduction to T-SQL and SQL Server. There is a lot of content, it’s well organized and with a couple of minor exceptions that aren’t worth mentioning, presented very well. I would recommend the content in the book to anyone who wanted to brush up on T-SQL. I would also rate it as the best Training Kit, even with the printing problems, that I’ve read. The author deserves a lot of credit.

But, that isn’t really the question, the question is does the book get you ready for the test and I think the answer is “yes”.

The test itself isn’t that hard. There were no major topics that were a surprise although there were a handful of specifics that I wasn’t certain about. Any regular user of T-SQL should pass and with this book they should easily pass. I come from a MySQL background so I had to fight in a few places. I think another week with the book and I would have cruised through it.

I would score the book as follows: 5 stars for the writing, 1 star for the construction (come on, chapters out of order) and another 5 stars for passing. Overall, rounding up I would give it 4 stars.

Training Kit (Exam 70-461): Querying Microsoft SQL Server 2012