Knight's Microsoft Business Intelligence 24-Hour Trainer

Knight's Microsoft Business Intelligence 24-Hour Trainer

by Brian Knight, Mike Davis, Devin Knight, Adam Jorgensen

View All Available Formats & Editions

A book-and-video introduction to Microsoft's Business Intelligence tools

If you are just starting to get a handle on Microsoft Business Intelligence (BI) tools, this book and accompanying video provides you with the just the right amount of information to perform basic business analysis and reporting. You'll explore the components and related tools that

…  See more details below


A book-and-video introduction to Microsoft's Business Intelligence tools

If you are just starting to get a handle on Microsoft Business Intelligence (BI) tools, this book and accompanying video provides you with the just the right amount of information to perform basic business analysis and reporting. You'll explore the components and related tools that comprise the Microsoft BI toolset as well as the new BI features of Office 2010. After a basic primer on BI and data modeling, the expert team of authors provides you with step-by-step lessons in the book and videos on the accompanying DVD on how to use SQL Server Integration Services, SQL Server Analysis Services, SQL Server Reporting Services, Excel BI (including PowerPivot), and SharePoint.

  • Integrates instructional videos with each of the lessons found in the book to enhance your learning experience
  • Explores the Microsoft Business Intelligence (BI) toolset as well as the new BI features of Office 2010
  • Encourages you to practice what you've learned in "Try It Out" sections
  • Contains video demonstrations that walk you through how to tackle each lesson featured in the book

With Knight's Microsoft Business Intelligence 24-Hour Trainer, veteran authors present you with an ideal introductory book-and-video package so that you can get started working with the BI toolset immediately!/p

Note: As part of the print version of this title, video lessons are included on DVD. For e-book versions, video lessons can be accessed at using a link provided in the interior of the e-book.

Read More

Product Details

Publication date:
Sales rank:
Product dimensions:
7.40(w) x 9.10(h) x 1.10(d)

Read an Excerpt

Knight's Microsoft Business Intelligence 24-Hour Trainer

By Brian Knight Devin Knight Adam Jorgensen Patrick LeBlanc Mike Davis

John Wiley & Sons

Copyright © 2010 John Wiley & Sons, Ltd
All right reserved.

ISBN: 978-0-470-88963-3

Chapter One

Why Business Intelligence?

Congratulations on your choice to explore how Business Intelligence can improve your organization's view into its operations and uncover hidden areas of profitability and analysis. The largest challenges most organizations face around their data are probably mirrored in yours. Challenges include:

* Data is stored in a number of different systems on different platforms, such as inventory and logistics in SAP, financials in Oracle, web analytics in SQL Server, and manufacturing on the mainframe. This can make data difficult to get to, require multiple accounts for access, and keep teams and departments at arm's length from each other.

* Pockets of knowledge about the data are spread throughout teams that don't regularly interact. This spread causes data to be analyzed in different ways and metrics to be calculated inconsistently, which, in turn, leads to unpredictable analysis and inappropriate actions being taken based on the data.

* Documentation is limited or nonexistent. Many times documentation is not created for reports or the metadata underneath them, and this lack of documentation is a critical problem. If you don't know where the data is coming from for the report, or how certain metrics are being calculated, then you can't truly understand or communicate the value of the data and calculations.

* Consolidated reporting is very time-consuming, when it is possible at all. With reports coming from so many different places, you run into the same problems mentioned in the previous point. These challenges require more people to know different reporting systems, lead to more administrative headaches, and so on.

* Reporting teams spend significant time finding and aligning data instead of analyzing and mining it for actionable information. If reporting teams need to go out and gather data from across the company constantly, this doesn't leave much time for analyzing and interpreting the data. These challenges cause many reporting teams to rework large portions of their reports several times as opposed to spending that time understanding what the users are asking for and delivering more actionable information.


Business Intelligence (BI) is a term that encompasses the process of getting your data out of the disparate systems and into a unified model, so you can use the tools in the Microsoft BI stack to analyze, report, and mine the data. Once you organize your company's data properly, you can begin to find information that will help you make actionable reports and decisions based on how the data from across your organization lines up. For instance, you can answer questions like, "How do delays in my manufacturing or distribution affect my sales and customer confidence?" Answers like this come from aligning logistics data with sales and marketing data, which, without a Business Intelligence solution, would require you to spend time exporting data from several systems and combining it into some form that you could consume with Excel, or another reporting tool.

Business Intelligence systems take this repetitive activity out of your life. BI automates the extracting, transforming, and loading (ETL) process and puts the data in a dimensional model (you'll create one in the next two lessons) that sets you up to be able to use cutting-edge techniques and everyday tools like Microsoft Excel to analyze, report on, and deliver results from your data.

Getting Intelligence from Data

How do you get information from data? First, you need to understand the difference. As you learned earlier, data can come from many different places, but information requires context and provides the basis for action and decision-making. Identifying your data, transforming it, and using the tools and techniques you learn from this book will enable you to provide actionable information out of the mass of data your organization stores. There are several ways to transform your data into actionable information and each has its pros and cons.

Typical solutions for reporting include a few different architectures:

* Departmental reporting: Many organizations have their own departmental reporting environments. This situation leads to a significant increase in licensing costs, since using different vendors for each department and reporting environment increases spending on hardware and software licensing, end-user training, and ramp-up time.

* Individual data access: Some organizations find it easier to grant lots of individual users access to the data. This is not only dangerous from a security perspective, but likely to lead to performance problems, because users are not the most adept at creating their own queries in code. Also, with all the industry and federal compliance and regulation governing data access, widespread access can quickly lead to a security audit failure, especially in a publicly held company.

* BI add-on from each vendor: When teams seek out and apply different strategies, it exacerbates the original problem of data being all over the organization. The data will still be segmented, and additionally the analysis on it will be inconsistent and applied based on each team's individual understanding of how its data fits into the enterprise, instead of the correct view based on the organizational goals.

* Automated reports from different systems: It may be nice to get the automated reports and they likely serve a purpose, but they usually cannot be counted on to run an enterprise. Strategic reporting, dashboard drill-through, and detailed analysis require a BI implementation to support them and provide the "at your fingertips" data and analysis that your end users, managers, and executives are craving.

You have likely seen some form of all of these problems in your organization. These are the opposite of what you want to accomplish with a great BI infrastructure.

BI to the Rescue

A well-thought-out BI strategy will mitigate the problems inherent to each of the previously listed approaches. A good BI approach should provide the targeted departmental reporting that is required by those end users while adjusting the data so it can be consumed by executives through a consolidated set of reports, ad hoc analysis using Excel, or a SharePoint dashboard. Business Intelligence provides a combination of automated reporting, dashboard capabilities, and ad hoc capabilities that will propel your organization forward.

BI provides a single source of truth that can make meetings and discussions immediately more productive. How many times have you gotten a spreadsheet via e-mail before a meeting and shown up to find that everyone had his or her own version of the spreadsheet with different numbers? Business Intelligence standardizes organizational calculations, while still giving you the flexibility to add your own and enhance the company standard. These capabilities allow everyone to speak the same language when it comes to company metrics and to the way the data should be measured across the enterprise or department.

Integrating Business Intelligence with your organization's current reporting strategy will improve the quality of the data as well as the accuracy of the analysis and the speed at which you can perform it. Using a combination of a data warehouse and BI analytics from Analysis Services and Excel, you can also perform in-depth data mining against your data. This enables you to utilize forecasting, data-cluster analysis, fraud detection, and other great approaches to analyze and forecast actions. Data mining is incredibly useful for things like analyzing sales trends, detecting credit fraud, and filling in empty values based on historical analysis. This powerful capability is delivered right through Excel, using Analysis Services for the back-end modeling and mining engine.

BI = Business Investment

A focused Business Intelligence plan can streamline the costs of reporting and business analytics. The Microsoft BI stack does a great job of providing you with the entire tool set for success within SQL Server Enterprise Edition. We provide more details on that shortly, but the most important bit of information you should take away right now is that the cost of managing multiple products and versions of reporting solutions to meet departmental needs is always higher than the cost of a cohesive strategy that employs one effective licensing policy from a single vendor. When organizations cannot agree or get together on their data strategy, you need to bring them together for the good of the organization. In the authors' experience, this single, cohesive approach to reporting is often a gateway to a successful BI implementation. Realizing the 360-degree value of that approach and seeing the value it can have in your organization are the two most important first steps.

Microsoft's Business Intelligence stack

Microsoft's Business Intelligence stack comes with SQL Server and is greatly enhanced with the addition of SharePoint Server.

SQL Server Enterprise includes industry-leading software components to build, implement, and maintain your BI infrastructure. The major components of the Microsoft BI stack that are included with SQL Server are the following:

* SQL Server Database Engine

* SQL Server Integration Services (SSIS)

* SQL Server Analysis Services (SSAS)

* SQL Server Reporting Services (SSRS)

These programs work together in a tightly integrated fashion to deliver solutions like those you'll build in this book. See Figure 1-1 for more details.

In Figure 1-1 you see the layers of Microsoft's Business Intelligence stack. SharePoint is at the top as the most end userfacing program for reporting, dashboard, and analytic capabilities. On the next level down you see the more common end user tools, and continuing down you can see the development tools, the core components, and some of the multitude of potential data sources you can consume with the products we discuss in this book.

BI Users and Technology

Different levels of users will have different sorts of questions around which they will use these BI technologies we are discussing. To see this at a glance, review Table 1-1. In that table you can see a breakdown that helps answer which users will rely on which tool for their reporting and data analysis and makes clear that Microsoft's Business Intelligence stack does address the needs of users at every level.


Your Try It for this lesson is a bit different than most others in the book. Throughout the book you will be challenged with hands-on tasks to enhance your understanding. For this lesson, your Try It is to make sure the things you learned in this lesson are in your mind as you learn the technologies to apply them. For instance, ask yourself these questions as you go through the rest of the book.

* What systems in my organization could I tie together using SSIS?

* What types of analysis could be made easier with the tools in Analysis Services?

* What users would benefit from the types of reporting I can do in Reporting Services and Excel?

* What types of data in my organization would be useful for dashboard- and portal-based analytics in SharePoint and PerformancePoint?

If you can keep these things in mind as you're learning and developing, you will succeed in harnessing the goals of a Business Intelligence implementation as you move through the data in your organization.

As this chapter is just an introductory overview, it does not have an accompanying video.


Excerpted from Knight's Microsoft Business Intelligence 24-Hour Trainer by Brian Knight Devin Knight Adam Jorgensen Patrick LeBlanc Mike Davis Copyright © 2010 by John Wiley & Sons, Ltd. Excerpted by permission of John Wiley & Sons. All rights reserved. No part of this excerpt may be reproduced or reprinted without permission in writing from the publisher.
Excerpts are provided by Dial-A-Book Inc. solely for the personal use of visitors to this web site.

Read More


Customer Reviews

Average Review:

Write a Review

and post it to your social network


Most Helpful Customer Reviews

See all customer reviews >