New book: MCTS Self-Paced Training Kit (Exam 70-448): Microsoft SQL Server 2008—Business Intelligence Development and Maintenance

We’re seeing a tremendous demand for Business Intelligence books. The field is growing, even in a down economy. This is basically a reflection of the need to make sense of an enormous, and ever-expanding, quantity of data. And also SQL Server’s maturity as a platform for BI and its smoother integration with Microsoft technologies such as Office and Visual Studio.

This month, we’re proud to add to our Microsoft Press portfolio of BI titles with the just-published MCTS Self-Paced Training Kit (Exam 70-448): Microsoft® SQL Server® 2008—Business Intelligence Development and Maintenance.

9780735626362f

The book’s authors are three of the finest SQL Server experts, consultants, and all-around very nice people – Erik Veerman, Teo Lachev, and Dejan Sarka. We’re privileged to have Erik say a few words about the book:

I was very excited to get my first copies of the 70-448 last week!  A big thanks to Ken Jones and Microsoft Press for their commitment to this. I think you'll find this book a tremendous help in your professional goals and technical expertise. Specifically, there are three things that Teo, Dejan and I focused on in this book to make it valuable:

1.) SQL 2008 BI Resource... This book is much more than an exam prep guide.  We wanted to also broaden its value to make it a great one-stop-shop for learning and excelling on the SQL 2008 BI technologies, whether you plan to take the exam or not.

2.) Exam preparation... If you are planning on taking the exam, we also put a lot of time into the content to make sure that it really prepares you for the exam by covering not just the general material but the background to best understand the types of questions you will come across (with lots of Exam Tips!). 

3.) Readable and real world... you'll find the content flow to be helpful, plus, each of us has worked hard to bring all our real world experience to the book, not just in the "real world" sections, but also in the lesson content, exercises, and case studies.

Much success to your SQL 2008 BI pursuits!

We’re including a few samples from the book to help illustrate Erik’s points:

Introduction

This Training Kit is designed for business intelligence (BI) developers and administrators who
plan to take the Microsoft Certified Technology Specialist (MCTS) Exam 70-448, Microsoft
SQL Server 2008, Business Intelligence Development and Maintenance. The primary objective of
this exam is to certify that BI developers and administrators know how to develop and maintain
solutions built on the Microsoft SQL Server 2008 BI platform, which includes SQL Server Integration
Services (SSIS), SQL Server Analysis Services (SSAS), and SQL Server Reporting Services
(SSRS).

We assume that before you begin using this Training Kit, you have experience developing
or implementing BI solutions. We also assume that you have experience managing or
supporting BI project security, deployment, and maintenance. The Preparation Guide for Exam
70-448 is available from https://www.microsoft.com/learning/exams/70-448.mspx. The practice
exercises in this Training Kit require you to use Microsoft SQL Server 2008 Enterprise or Microsoft
SQL Server 2008 Developer. A 180-day evaluation edition of SQL Server 2008 Enterprise
is included on this book’s SQL Server 2008 evaluation DVD. If you do not have access to this
software, you can download a 180-day trial of SQL Server 2008 from https://www.microsoft.com/sqlserver/2008/en/us/trial-software.aspx. You can also consider purchasing SQL Server 2008 Development, which contains all of the required features.

By using this Training Kit, you will learn how to:

  • Install and configure the SQL Server 2008 BI components.
  • Work with the design and management tools in SQL Server 2008 for BI.
  • Develop and deploy SSIS projects.
  • Secure, manage, and troubleshoot SSIS packages.
  • Develop and deploy SSAS solutions.
  • Secure SSAS cubes and dimensions.
  • Implement, configure, and deploy SSRS reports.
  • Manage and secure SSRS report servers.

 

REAL WORLD

Teo Lachev

Ad hoc reporting—letting business users create their own reports—is a popular
data analytics requirement. A good ad hoc reporting solution should be
designed with the end user in mind and shouldn’t require the user to know the
database schema and query language. SSAS is designed from the ground up to efficiently support ad hoc reporting needs.

One reporting project I was involved in required implementing an ad hoc solution
on top of an existing operational database. Because the database schema was highly
normalized and the database contained gigabytes of data, we quickly overruled ad
hoc reporting tools that are layered on top of the database and autogenerate SQL
queries. Instead, we designed a “classic” OLAP solution in which data is extracted
from the operational database and loaded into a data mart. We followed the data
dimensional methodology to architect the data mart schema. From some 200 tables
in the operational database, the data mart schema contained about 10 dimension
tables and 2 fact tables.

On top of the data mart, we built an SSAS Unified Dimensional Model (UDM) layer,
which provided several important benefits. To start, SSAS is designed to deliver
exceptional performance for large data volumes. Unlike relational databases,
which have no notion of the hierarchical relationships in data, SSAS understands
the semantics of data. This helped us extend our cube with important business
calculations in the form of Multidimensional Expressions (MDX). For example, we
defined Key Performance Indicators (KPIs) that measure company performance. We
also benefited from the flexible, role-based security model that SSAS provides. This
security model is enforced when the user connects to the cube. Last, many Microsoft-provided
and third-party tools support SSAS and its MDX query language. In our case,
we designed a Microsoft Office SharePoint dashboard to help executive managers
understand the company’s performance. End users could use Microsoft Office Excel
or the SQL Server Report Builder from SQL Server Reporting Services to author ad hoc
reports.

So if your task is to design an ad hoc reporting solution, your first stop should be SSAS.

EXAM TIP

SQL Server Integration Services (SSIS) can be very useful for preparing data for data mining.
For example, you can use a different path in the SSIS data flow for regular missing
data, and then later when the missing data is handled, you can merge both datasets.