PASS Book Review by: Grant Fritchey
Rating: 
(4 out of 5)
Overview:
When SQL Server 2005 was released, the two most frequently talked about areas of change were the introduction of the CLR and the expansion of the Business Intelligence suite. This book, part of a series from McGraw Hill Osborne on 2005, provides a timely and thorough introduction to the new and improved BI capabilities within SQL Server 2005. The author explains core concepts in a clear and efficient manner which makes reading the book easy. The examples are, with only a few exceptions, on target for providing the reader with the tools needed to apply the lessons learned to real world problems. Best of all, the examples build on each other so that simple datamarts designed in early chapters are expanded in later chapters, making for a more comprehensive feel to the book and again, improving the understanding that the reader will take away. These examples are clear, well thought out, and provide a large number of screen shots outlining most steps, making them clear and easy to follow. The web site associated with the book provides a download of all the examples and sample data needed. Overall, the book succeeds quite well in its goal of introducing the concepts and tools provided by SQL Server 2005 that will allow you to delivery Business Intelligence technology.
Who Will Benefit:
If you’re already a thorough expert in most aspects of business intelligence and data mining, large portions of this book may not be that useful to you. If on the other hand, you’re an intermediate BI user who needs a complete introduction to the 2005 suite of tools, or you’re a complete beginner who needs most of the concepts explained, then this book is for you. Since most of the books take the time to build upon its earlier chapters, it acts as a great teaching mechanism for introducing or reinforcing the concepts needed to apply BI within the enterprise.
Chapter Breakdown:
Part I Business Intelligence
Chapter 1: Equipping the Organization for Effective Decision Making
Starting the book off with core concepts, this concise chapter takes the time to explain that the reason we’ve gathered all this data together is to attempt to assist the business in making decisions, hopefully correct ones, based on that data.
Chapter 2: Making the Most of what You've Got-Using Business Intelligence
Building on the concepts introduced, this chapter explains how Business Intelligence is used to get information out in multiple levels of detail to the high, mid and low level managers and decisions makers that need it. It also introduces the faux company that is used in all the examples in the rest of the book, Maximum Miniatures. The best part of this chapter is the introduction of the business plan of the fake company because in understanding the business plan, you can better design and deliver the kinds of reports and data needed to help the company make good decisions.
Chapter 3: Searching for the source-The source of Business Intelligence
Now begins the explanations of how the data is going to be delivered back to the business from the collection points, online transaction processing (OLTP) systems. The author argues that that data warehouse, as a concept is dead, and that datamart farms are the wave of the future. This isn’t dwelled on in the book, but the rest of the entire structure of the book leads one down this path so that it is a core concept. It goes on to discuss the possible structures and delivery mechanisms of data marts and introduce the concepts of snowflake and star schema’s as data storage mechanisms.
Chapter 4: One-stop Shopping-The Unified Dimensional Model
After establishing the basics in the first three chapters, SQL Server 2005 and the Unified Dimensional Model are introduced as a mechanism for delivery of online analytical processing (OLAP), completing along with datamarts, and the meat and potatoes for delivering BI. The UDM is, at its root, a web service, that allows OLAP structures to be built on top of OLTP data with extremely low latency on the data (meaning it’s available to the users quickly), that easy to create and maintain.
Chapter 5: First Steps-Beginning the Development of Business Intelligence
The BI design suite is now a part of Visual Studio 2005. This chapter provides a basic overview of VS2005, its various windows, menu’s and behavior. It also introduces the SQL Server 2005 Management Studio. These two tools will likely be open on your desktop the entire time you’re working with BI, so this is a necessary chapter to read if you’re not already familiar with these tools.
Part II Defining Business Intelligence Structures
Chapter 6: Building Foundations-Creating and Populating Data Marts
The process and decisions for designing a datamart are explored here. The measures, dimensions and hierarchies that will be stored within a given datamart must be decided upon based on the data available and the types of reports and OLAP cubes that you hope to offer up in the future. This chapter begins the process, followed through the rest of the book, of “Learn by Doing.” First the business requirements are defined. Then the technology decisions for meeting those requirements are walked through. Finally, step by step, the tasks needed are explained, in order, and quite thoroughly. This is where you’ll first see a previously mentioned strength of the book, the extensive screen captures. These make it extremely easy to follow along and not get lost if, at times, the written step seems confusing (I found this to be quite rare, the author did a great job explaining each step).
Chapter 7: Fill'er up-Using Integrations Services for Populating Data Marts
This is easily the most disappointing chapter in the book. While it is a quick introduction to SQL Server Integration Services, it's so simplistic and short that it doesn't in any way introduce the types of challenges you will meet when loading your own datamarts from real production data. In all honesty, if they couldn't expand this chapter (and there was space since almost half of the chapter was devoted to an overview of every Control Flow & Data Flow task), or even add a second chapter on some of the more complex problems that SSIS users may face, they should have skipped it entirely.
Part III Analyzing Cube Content
Chapter 8: Cubism-Measures and Dimensions
SSIS concepts are built on in order to complete the datamart started in previous chapters. The first OLAP cube is designed and built in this chapter. The knowledge required to make good decisions in cube design are explored at length. Measures, measure groups, calculated measures, measure aggregates are all discussed and then applied to your very own cube through the “Learn by Doing” sections of this chapter. Dimensions are then covered in the same way by first discussing the concepts of reference, parent/child, data mining, many-to-many and finally slowly changing dimensions.
Chapter 9: Bells and Whistles-Special Features of OLAP Cubes
Now that we’ve designed and built a cube, it now has to be deployed to a server in order to make it available to the user community. Deployments from VS2005 and the Deployment Wizard are explained. Additional concepts needed for effective cubes such as key performance indicators, a tool that lets you build a “dashboard” of red, yellow, and green lights, showing the status of the business at a glance are explored. Partition and storage options, key concepts for scalability and performance, are touched on and, as always, added to the project we’ve been working on throughout the book.
Chapter 10: Writing a New Script-MDX Scripting
Now you’ve built a cube, but how is it storing the data and how can you get it back out? The short answer is using queries written in MDX to access the tuples of data. Tuples, literally a cell location within a cube, are simultaneously a very simple concept and one of the most mind-numbing exercises available within databases. The author does a fine job of explaining the concept and then goes on to apply the basics of MDX scripts to the cube we’ve been building to limit access based on the data. This chapter is worth reading twice because the concepts developed here are very important to the chapter that follows. Further, these concepts will be vital when you begin to design and deploy your own cubes.
Chapter 11: Pulling it OUT and Building it Up-MDX Queries
This chapter gets down to the raw, nitty-gritty, of writing MDX queries to retrieve data from cubes. From a technical standpoint, it's one of the most interesting and informative chapters in the book. Mr. Larson knows and understands the concepts here so well that he communicates them flawlessly. I learned more about how cubes work by going through the MDX examples in this chapter than I did in his introductory chapter (which was still quite good).
Part IV Mining
Chapter 12: Panning for Gold – Introduction to Data Mining
A nice overview of the goals, purposes, and methods of data mining is followed by a detailed examination of the seven data mining algorithms offered by SQL Server 2005. Even if you’re already an expert at data mining, half this chapter is worth perusing in order to get the information on the SQL Server algorithms.
Chapter 13: Building the Mine – Working with the Data Mining Model
After the introduction to SQL Server 2005 data mining in the last chapter, it’s time to apply that knowledge, creating training and testing data sets. Difficult concepts are boiled down to a set of easy to follow steps through this chapter. It takes nearly 15 pages, but you end up with enough data that you can begin exploring using the various algorithms introduced in the last chapter. Seeing the algorithms in action through the Mining Model Viewer certainly made the concepts easier to understand.
Chapter 14: Spelunking – Exploration Using Data Mining
Building on the previous chapter, the concept of Mining Accuracy is introduced. This is basically the ability to test the models by applying them to test data with anticipated results. These are somewhat complicated concepts and processes, but the step-by-step instructions provided in the “Learn by Doing” section again prove their worth. Finally, the Data Mining query language, DMX is introduced.
Part V Delivering
Chapter 15: On Report – Delivering Business Intelligence with Reporting Services
For a book focused on BI, this chapter on Reporting Services is surprisingly complete. I honestly expected a more cursory examination like that from Chapter 7 on Integration Services. I’m happy to report I was wrong. While the details and nuances of Reporting Services require another book (and there is one within the same series from McGraw Hill), this overview will get you started in style. I found just enough detail and clarity to get all of the reports working against the data that I’d spent the previous 14 chapters creating. Each of the lessons establishes a good foundation for building your own reports and they build on each other where appropriate.
Chapter 16: Let’s Get Together – Integrating OLAP with Your Applications
Another great chapter on how to get all that data you’ve collected and massaged into the hands of the users. I really appreciate the fact that this chapter received focus separate from Reporting Services and Excel Pivot Tables (next chapter). Either of those two delivery mechanism are probably more likely to be used by most people, but if you are building a large scale, custom, enterprise application, having a chapter like this, that introduces, even at a fairly high level, the concepts behind using ASP.NET web services and ADOMD (ADO for multidimensional data) is a boon. While a lot more detail would have been appreciated, I think it would have detracted from the focus of the book.
Chapter 17: Another Point of View – Excel Pivot Tables and Pivot Charts
The opening sentence of this chapter sums up my own experiences with delivery BI to the user community, “… Business Intelligence is not useful unless it can be delivered to decision-makers at the most opportune moment.” How very true. If the companies you’ve worked at are like mine, then your decision makers live and die in Excel which makes the final chapter on using Excel Pivot Tables the correct place to end any discussion of BI. The review of Pivot tables was a bit light, but the basics were covered.