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.
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:
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 http://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 http://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:
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.
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.