Kick some SSAS - Expert Cube Development with Microsoft SQL Server 2008 Analysis Services

Recently finished (cover-cover) reading of Expert Cube Development with Microsoft SQL Server 2008 Analysis Services. Since then I've gone through a number of chapters several times as topics aligned themselves with work efforts. Looking at this book today, I noticed the covers are all curled, the pages dog-eared, and yellow highlighter is visible across multiple chapters. In my view of all things bibliothèque – that indicates value for my dollar..

Overall I would highly recommend this book to anyone with some SSAS project experience that wants to take it up a notch. Note - this is not a book for a beginner, there is not much in the way of introductory material, just a bunch of usable info around designing, tuning, securing, productionization, and monitoring SSAS cubes in SQL 2008.

There are definitely some authors opinions sprinkled across the book, and some of the designs give one pause. For instance, the chapter on calculations included a design for calculation dimensions that had me scratching my head a bit – particularly around how supportable (or explainable) it would be in my particular shop.

On the other hand, I think the value of chapter 8 alone - Query Performance Tuning, and its discussions of performance, partitioning, aggregation design, and MDX calculation performance provided value to me beyond the purchase price of the book.

Is the book perfect – not! Is it the best SSAS cube development treatment I have read to date – absolutely!

SharePoint 2007 Developer’s Guide to Business Data Catalog

SharePoint 2007 developer’s Guide to Business Data Catalog is an essential guide to anyone who wishes to become proficient in building complex SharePoint applications. This book starts off assuming that the reader is a complete beginner to BDC, and explains in detail what it is. Soon afterwards, the reader is taught what can be done with it and how.

The book is divided into 11 chapters. Chapter 1 provides information for what BDC is. The BDC is a layer of defining heterogeneous data sources so that the SharePoint is aware of them. The data sources can be any of those: Microsoft SQL Server, Oracle, ODBC (Open Database Connectivity) and Web Services. Chapter 2 discusses the Application Definition File (You can think of it as being a configuration file). The ADF is used to tell SharePoint to get the data from those different data sources. Chapters 3 through Chapter 7 examine the security which allows users to use different authentication methods within the BDC and the out-of-the-box functionality of BDC can be configured through the application definition file. I think the configuration is the responsibility of SharePoint system administrator, but the lucid writing style of the book makes it easy to understand by most developers. Chapters 8 through Chapter 11 delve deep into the customized solutions which are really the job for developers. This book provides the namespace and DLL and sample code to allow developers easily to follow.

Most SharePoint books will spend one or two chapters to describe the BDC; this is the first book I have seen that provides a detailed analysis of a specific concept of SharePoint. Users should be aware of that the BDC is a component that can be used only on Microsoft Office SharePoint Server 2007 Enterprise Edition.

Reviewed by Henry

The Art of Unit Testing

As a Database Architect, I often work with Object-Oriented Programmers, but thankfully don’t often have to code in other languages other than SQL.  However, it is useful to understand the methods and basic frameworks by which my team members develop; hence, I decided to review Roy Osherove’s The Art of Unit Testing (Manning Press, 2009).  Although many of the examples in the latter half of the book were a little over my head, I do appreciate the fact that Osherove builds to that point in a simple, easy-to-follow manner.

The first chapter was particularly useful to me, because the author lays out some very basic criteria for defining a unit test, which I’ll paraphrase below:

• Is the test repeatable after a period of time (years, months, etc?)
• Is the test portable?  Can other team members run the same unit test?
• Is the test simple to run?  Can it be run with the push of a button, and in just a few minutes?
• Is the test simple to build?

If the answer to any of the previous questions is no, then you’re probably not doing a unit test; Osherove argues that you’re probably doing some form of integration testing, which may be useful, but is not a true unit test.  His argument for the art builds from there.

This book is very well laid out, with lots of examples (written in C#).  It’s a short read (under 300 pages, including indexes), and has a straightforward style that lends itself well to both the beginning programmer (or programmers from other traditions, like myself) all the way to experienced programmers who want to refresh their memory on basic concepts.

SQL Server 2008 Administration in Action

Rod Colledge’s book SQL Server 2008 Administration in Action is a great asset for most SQL Server DBA’s; it covers a variety of issues in a simple-to-understand format. Let me preface this post by saying that while I am a DBA, I’ve been doing mostly development work for the last few years. However, armed with this book, I felt like I could easily dive back into administrative duties.

The book is broken up into three sections, each with several supporting chapters: Planning and Installation, Configuration, and Operations. Each chapter does a relatively deep dive into issues by starting with basic definitions, moving quickly through options and concepts, and then finally wrapping up with a bulleted list of best-practice considerations. If I had but one suggestion to make for the publishers, it would be that they consider consolidating all of the checklists into one document. What is probably the most amazing fact to me is that the book is very short (compared to some other administrative guides); it’s only 440 pages, including the index. It’s just well-written, straight to the point, and focuses on just the important stuff.

I also like the Appendices at the end, especially the Top 25 WORST practices, and the basic schedule in Appendix B. Although it’s probable that most DBA’s will have much more complicated schedules than described herein, it’s a useful template (especially for those “accidental DBA’s”). Overall, I really liked this book; I think it will be very helpful for me in the future (especially as I begin to study for the 2008 certification exams).

Here's an interview with the author:

Also, you can download a few sample chapters:

Sample chapter 4 Installing and upgrading SQL Server 2008
Sample chapter 10 Backup and recovery

Stu

UltraEdit Text Editor 15

Most SQL Server DBA’s are used to dealing with data that comes in via some other method than a database connection; flat files, csv’s, and XML files are all too common.  However, as databases get larger, and XML becomes more prevalent, I don’t think I’m alone in suggesting that Notepad doesn’t really cut it as a text editor anymore.

Quick case in point; last week I was charged with importing a 32 Meg XML file into a database so that we could use some of the values to update one of our older security scanning tools. Unfortunately, while the XML file was valid (e.g., all tags were closed), SQL Server was screaming about certain characters not being valid XML characters.  Rendering the file in IE 7 killed IE.  Wordpad would open the file, but 32 megs of text yields something along the lines of 300,000 characters; not exactly easy to scan and edit.

Enter UltraEdit.  I downloaded the 45-day free trial, and went to work.  SQL Server identified the line and character position of the invalid characters in the xml column in my scratch table; I opened the file in UltraEdit, used the Goto Line command (including the column number) and discovered the first invalid character: the trademark symbol, or ™.  I edited it out, used the find and replace feature to find the rest of them and do the same, and uploaded the file to SQL Server again.  Using XQuery on the new imported contents gave me a new character position, so I repeated the process.  After about 10 repeats (less than an hour of time), I had a clean XML file, and was off to the races.

UltraEdit was also very useful when querying the file; I could use the XML manager to explore and identify the nodes far better than the XML parser included in SQL Server Management Studio (which choked when trying to open the whole XML value).  This was a great help when attempting to write valid XQuery statements to select particular nodes, attributes, and values from the database.

I realize that UltraEdit is more than an XML editor, and that there are probably better XML editors out there, but I was very pleased with how easy it was to use it for this particular project.  I’m hoping that I’ll have time to more fully explore it’s capabilities for authoring scripts, etc, in the future.  The cost for a license is only $49.95, and it was well worth it to solve this particular problem.

How to become an Exceptional DBA (Second Edition)

I downloaded this free ebook from RedGate and quickly perused it in a couple of hours; Brad McGehee lays out an interesting set of observations about what is necessary to transform from an average DBA to an exceptional one.  To be honest, there weren’t any hidden secrets here; most of the book deals with personal work habits (which you probably already have or you wouldn’t be interested in being an exceptional DBA).

There were a few gems here and there, like Brad’s suggestions on how to become an MVP, as well as how to manage your career; however, even these were not necessarily new ideas, just general concepts that were slimmed down and located in a single easy-to-use guide.  I don’t regret reading this book, but at the same time, I didn’t really walk away with any new insights either.

If you’re a new DBA or are planning to transition to a DBA role, you may find his overview of the career path useful.  Also, if you’re just now beginning to expand your skillset, there is some basic information on how to do that.  However, if you’re already working long hours solving problems and you enjoy your job, this ebook may not be the best use of your time.  You’ve probably already encountered these same ideas along the way; spend your time building a network of database associates instead.

Pros:

• FREE!  Can’t beat the price!
• Concise explanation of what makes a DBA “exceptional”
• Overview of community-building

Cons

• Nothing really new under the sun

SQL in a Nutshell- 3rd Edition

Kevin Kline’s SQL in a Nutshell (like many of the technical books from O’Reilly) is one of those essential desktop reference books that every programmer should have access to; it covers several different flavors of SQL, including MySQL, Oracle, PostgreSQL, and SQL Server. The nice thing about this book is that it uses the 2003 ANSI SQL as the foundation, and then attempts to tie in the various flavors back to that source.  While this gives you a great overall picture of how the various database platforms interact with another, it does make it a bit difficult to translate from one dialect to another.  In other words, if you know how CHARINDEX works in SQL Server, it’s difficult using the book to figure out a comparable function in MySQL.

Despite this limitation, this book does provide a very useful codex for the major dialects of SQL.  IT’s worth having a copy if you are skilled on one platform and need to interact with another flavor of SQL.

3 of 5 stars.