Microsoft® Business Intelligence For Dummies®

Microsoft® Business Intelligence For Dummies®

by Ken Withee
Released March 2010
Publisher(s): For Dummies
ISBN: 9780470526934

Read it now on the O’Reilly learning platform with a 10-day free trial.

O’Reilly members get unlimited access to books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.

Start your free trial

Book description

Learn to create an effective business strategy using Microsoft's BI stack

Microsoft Business Intelligence tools are among the most widely used applications for gathering, providing access to, and analyzing data to enable the enterprise to make sound business decisions. The tools include SharePoint Server, the Office Suite, PerformancePoint Server, and SQL Server, among others.

With so much jargon and so many technologies involved, Microsoft Business Intelligence For Dummies provides a much-needed step-by-step explanation of what's involved and how to use this powerful package to improve your business.

  • Microsoft Business Intelligence encompasses a broad collection of tools designed to help business owners and managers direct the enterprise effectively

  • This guide provides an overview of SharePoint, PerformancePoint, the SQL Server suite, Microsoft Office, and the BI development technologies

  • Explains how the various technologies work together to solve functional problems

  • Translates the buzzwords and shows you how to create your business strategy

  • Examines related technologies including data warehousing, data marts, Online Analytical Processing (OLAP), data mining, reporting, dashboards, and Key Performance Indicators (KPIs)

  • Simplifies this complex package to get you up and running quickly

Microsoft Business Intelligence For Dummies demystifies these essential tools for enterprise managers, business analysts, and others who need to get up to speed.

Table of contents

  1. Copyright
  2. About the Author
  3. Author's Acknowledgments
  4. Publisher's Acknowledgments
  5. Introduction
    1. About This Book
    2. How to Use This Book
    3. How This Book Is Organized
      1. Part I: Embracing a Microsoft Business Intelligence Solution
      2. Part II: Wrapping Your Head Around Business Intelligence Concepts
      3. Part III: Introducing the Microsoft Business Intelligence Technologies
      4. Part IV: Incorporating Microsoft Business Intelligence into Your Business Environment
      5. Part V: The Part of Tens
    4. Icons Used In This Book
    5. Let's Get Started!
  6. I. Embracing a Microsoft Business Intelligence Solution
    1. 1. Surveying Microsoft Business Intelligence from 50,000 Feet
      1. 1.1. Introducing Microsoft Business Intelligence
        1. 1.1.1. Knowing the components of Microsoft BI
        2. 1.1.2. Tracing the terminology
      2. 1.2. Getting to the Core of Microsoft BI
        1. 1.2.1. Date warehousing and data marts
        2. 1.2.2. Reporting on data
        3. 1.2.3. Integrating data from many sources
        4. 1.2.4. Analyzing data
        5. 1.2.5. Data mining
      3. 1.3. Microsoft BI Data Presentation
        1. 1.3.1. Microsoft Office Excel
          1. 1.3.1.1. An analysis tool for everyone
          2. 1.3.1.2. The Data Mining Add-in
        2. 1.3.2. Microsoft Office Visio
        3. 1.3.3. Microsoft SharePoint
          1. 1.3.3.1. Excel Services
          2. 1.3.3.2. PerformancePoint Services
          3. 1.3.3.3. Reporting Services Integration
      4. 1.4. Microsoft BI Development Tools
        1. 1.4.1. Visual Studio
        2. 1.4.2. Report Builder
        3. 1.4.3. Silverlight
        4. 1.4.4. Microsoft .NET
    2. 2. Blazing a Trail through the Data Jungle
      1. 2.1. Exploring the Data Lifecycle
        1. 2.1.1. Data generation and collection
          1. 2.1.1.1. Business processes — the midwife for data
          2. 2.1.1.2. Ways to collect data
          3. 2.1.1.3. Data silos
        2. 2.1.2. Data transformation and organization
        3. 2.1.3. Data visualization and reporting
        4. 2.1.4. Data analysis
        5. 2.1.5. Data mining
      2. 2.2. Understanding How Microsoft BI Fits into the Data Lifecycle
      3. 2.3. Juggling Data
      4. 2.4. It's a Flood of Data! Headed This Way!
    3. 3. Adopting Microsoft Business Intelligence
      1. 3.1. Understanding the Adoption Process
        1. 3.1.1. Determining what to ask the BI genie
        2. 3.1.2. Investigating your current Microsoft product usage
          1. 3.1.2.1. Checking your current licensing
          2. 3.1.2.2. Mapping your IT environment
          3. 3.1.2.3. Determining which Microsoft software to purchase
        3. 3.1.3. Taking stock of your Microsoft knowledge
        4. 3.1.4. Saving your sanity with a prototype
        5. 3.1.5. Iterating the prototype to success
      2. 3.2. Documenting Your Key Business Processes
      3. 3.3. Understanding Where to Find Microsoft BI Guidance
        1. 3.3.1. Taking advantage of in-house expertise
        2. 3.3.2. Calling in the experts
        3. 3.3.3. Tracking down individual experts
        4. 3.3.4. Who you gonna call? Microsoft Support!
        5. 3.3.5. Other resources online and on paper
  7. II. Wrapping Your Head Around Business Intelligence Concepts
    1. 4. Using Data to Inform and Drive Business Activities
      1. 4.1. The Importance of Data in Making Business Decisions
        1. 4.1.1. Tracking down the relevant data
        2. 4.1.2. Getting the right data to the right person at the right time
        3. 4.1.3. BI and the risk of high-tech tunnel vision
      2. 4.2. Why All the Fuss about OLAP?
        1. 4.2.1. What is OLAP?
        2. 4.2.2. What makes OLAP so fast?
        3. 4.2.3. Why OLAP?
        4. 4.2.4. Databases and cubes
        5. 4.2.5. Measures and facts (of life)
        6. 4.2.6. Hierarchies of detail
    2. 5. Taking a Closer Look at Data Collection
      1. 5.1. The King of BI Concepts — ETL
        1. 5.1.1. Extracting data
        2. 5.1.2. Transforming data
          1. 5.1.2.1. Data cleansing
          2. 5.1.2.2. Mapping numeric values
          3. 5.1.2.3. Calculating new values
          4. 5.1.2.4. Aggregating values
        3. 5.1.3. Loading data
      2. 5.2. SQL Server Integration Services (SSIS) — Microsoft's ETL Tool
        1. 5.2.1. Tossing the packages into the projects
        2. 5.2.2. Connecting to data sources
        3. 5.2.3. SSIS Toolbox
          1. 5.2.3.1. The Control Flow Toolbox
          2. 5.2.3.2. Data Flow Toolbox
        4. 5.2.4. Data transformations
        5. 5.2.5. Anything is possible with custom code
      3. 5.3. A Simple SSIS Walk-Through
      4. 5.4. Exploring Data Generation
        1. 5.4.1. Computers speed everything up
        2. 5.4.2. Enterprise Resource Planning (ERP)
        3. 5.4.3. Rise of the machines
          1. 5.4.3.1. Robots
          2. 5.4.3.2. Scanners
          3. 5.4.3.3. Point of sale
    3. 6. Turning Data into Information
      1. 6.1. Data Storage for BI
        1. 6.1.1. Data warehouse
          1. 6.1.1.1. Dual purposes of a data warehouse
          2. 6.1.1.2. What runs a data warehouse: SQL Server database engine
          3. 6.1.1.3. Making the case — reasons for a data warehouse
          4. 6.1.1.4. Getting data out of a data warehouse
        2. 6.1.2. Data mart
          1. 6.1.2.1. Purpose of a data mart
          2. 6.1.2.2. Origin of data in a data mart
        3. 6.1.3. Data-storage patterns
          1. 6.1.3.1. Dimensional models
          2. 6.1.3.2. Relational models
          3. 6.1.3.3. Hybrid models
        4. 6.1.4. Models, schemas, and patterns
      2. 6.2. Understanding SQL Server Reporting Services (SSRS)
        1. 6.2.1. Business Intelligence Developer Studio (BIDS)
        2. 6.2.2. Report Builder
      3. 6.3. Getting Familiar with SharePoint
        1. 6.3.1. Excel Services
        2. 6.3.2. PerformancePoint Services for SharePoint
        3. 6.3.3. KPI lists
        4. 6.3.4. Dashboards
        5. 6.3.5. Scorecards
          1. 6.3.5.1. Business balancing act — The Balanced Scorecard
          2. 6.3.5.2. Microsoft and the balanced scorecard
    4. 7. Data Mining for Information Gold
      1. 7.1. Going Deep with Data Mining
        1. 7.1.1. An algorithm defined
        2. 7.1.2. Data mining's role in the BI process
      2. 7.2. Digging In to Data Mining in the Microsoft World
        1. 7.2.1. The Microsoft data-mining process
          1. 7.2.1.1. Defining the problem
          2. 7.2.1.2. Preparing the data
          3. 7.2.1.3. Exploring the data
          4. 7.2.1.4. Building your models
          5. 7.2.1.5. Exploring and validating your models
          6. 7.2.1.6. Deploying and updating your models
        2. 7.2.2. Data-mining structures
        3. 7.2.3. Data mining models
      3. 7.3. Knowing the Microsoft Data-Mining Tools
        1. 7.3.1. Integrating with Microsoft Office
          1. 7.3.1.1. Table Analysis Tools for Excel
          2. 7.3.1.2. Data Mining Client for Excel
          3. 7.3.1.3. Data Mining Templates for Visio
        2. 7.3.2. Visual Studio
          1. 7.3.2.1. Data Mining Wizard
          2. 7.3.2.2. Data Mining Designer
          3. 7.3.2.3. SQL Server Integration Services
            1. 7.3.2.3.1. Tasks
            2. 7.3.2.3.2. Data-flow transformations
            3. 7.3.2.3.3. Data-flow destinations
        3. 7.3.3. SQL Server Management Studio
      4. 7.4. Using Microsoft Data Mining Algorithms
  8. III. Introducing the Microsoft Business Intelligence Technologies
    1. 8. Meeting SQL Server
      1. 8.1. First Contact with SQL Server
      2. 8.2. Primary Components of SQL Server
        1. 8.2.1. The SQL Server Database Engine
          1. 8.2.1.1. Creating a database
            1. 8.2.1.1.1. Creating a database using the GUI
            2. 8.2.1.1.2. Creating a database using the DDL
          2. 8.2.1.2. SQL Server clustering and high availability
            1. 8.2.1.2.1. Failover clustering
            2. 8.2.1.2.2. Database mirroring
            3. 8.2.1.2.3. Log shipping
            4. 8.2.1.2.4. Replication
          3. 8.2.1.3. SQL Server in the cloud
        2. 8.2.2. SQL Server Reporting Services
          1. 8.2.2.1. Report Definition Language
          2. 8.2.2.2. Report-building tools
            1. 8.2.2.2.1. Report Builder
            2. 8.2.2.2.2. Visual Studio and Business Intelligence Developer Studio
          3. 8.2.2.3. Report models
        3. 8.2.3. SQL Server Integration Services
        4. 8.2.4. SQL Server Analysis Services
          1. 8.2.4.1. OLAP
          2. 8.2.4.2. Data Mining Engine
      3. 8.3. Looking at the Different Versions of SQL Server
        1. 8.3.1. Core editions
          1. 8.3.1.1. Standard
          2. 8.3.1.2. Enterprise
        2. 8.3.2. Specialized editions
          1. 8.3.2.1. Express
          2. 8.3.2.2. Web
          3. 8.3.2.3. Workgroup
          4. 8.3.2.4. Developer
      4. 8.4. Installing SQL Server
      5. 8.5. Checking Out SQL Server Tools
        1. 8.5.1. SQL Server Management Studio
          1. 8.5.1.1. Terminology confusion with Management Studio
          2. 8.5.1.2. Development in Management Studio
        2. 8.5.2. Transact-SQL
        3. 8.5.3. MDX
    2. 9. Excel — Digital Data Power to the People
      1. 9.1. Excel as a BI Application
      2. 9.2. Generating Data
      3. 9.3. Collecting Data
      4. 9.4. Getting Organized
      5. 9.5. Show Me the Data! — Data Visualization
        1. 9.5.1. Conditional formatting
        2. 9.5.2. Charts and graphs
      6. 9.6. Analyzing Data: Pivot on This and Pivot on That
        1. 9.6.1. Using Excel PivotTables
        2. 9.6.2. PivotChart
      7. 9.7. Data Mining with Excel
        1. 9.7.1. Using Excel to boss SSAS
        2. 9.7.2. Pulling cube data for PivotTables and PivotCharts
      8. 9.8. Keeping Score with the Excel Scorecard
      9. 9.9. Knowing the Limits of Excel
      10. 9.10. Looking at the Future of Excel
    3. 10. SharePoint Shines
      1. 10.1. Getting to Know SharePoint
        1. 10.1.1. What exactly is SharePoint?
          1. 10.1.1.1. Starting with the roots — computer hardware
          2. 10.1.1.2. Software that talks to the hardware — the operating system
          3. 10.1.1.3. Software frameworks and servers — .NET and IIS
          4. 10.1.1.4. A computer language for the Web — ASP.NET
          5. 10.1.1.5. The first step into the SharePoint world — SharePoint Foundation
          6. 10.1.1.6. A finished product — SharePoint Server
        2. 10.1.2. Understanding the versions and editions of SharePoint
          1. 10.1.2.1. WSS 2.0 and MOSS 2007 (previous version of SharePoint)
          2. 10.1.2.2. SharePoint Foundation 2010 and SharePoint Server 2010 (latest and greatest version of SharePoint)
      2. 10.2. Making BI Information Available in SharePoint
        1. 10.2.1. SSRS integration
          1. 10.2.1.1. SSRS in Native mode
          2. 10.2.1.2. SSRS in Integrated mode
        2. 10.2.2. Excel integration
        3. 10.2.3. InfoPath Form Services
        4. 10.2.4. Using Key Performance Indicators
        5. 10.2.5. Business Connectivity Services
      3. 10.3. Unleashing Human Business Intelligence with SharePoint
        1. 10.3.1. SharePoint Web sites
        2. 10.3.2. Document libraries
        3. 10.3.3. SharePoint Lists
        4. 10.3.4. Wikis
        5. 10.3.5. Blogs
        6. 10.3.6. Discussion boards
        7. 10.3.7. Office integration
          1. 10.3.7.1. Word
          2. 10.3.7.2. Outlook
      4. 10.4. Learning What Was Added with SharePoint Server 2010
        1. 10.4.1. Cruising with the Navigation Ribbon
        2. 10.4.2. Providing a more fluid user experience
        3. 10.4.3. Developing applications with Silverlight
        4. 10.4.4. Integrating visualizations with PowerPoint themes
        5. 10.4.5. Visio Services
        6. 10.4.6. Sorting and filtering lists dynamically
        7. 10.4.7. Using Business Connectivity Services
        8. 10.4.8. Increasing efficiency with Office integration
        9. 10.4.9. Taking SharePoint offline with SharePoint Workspace
    4. 11. Expressing Yourself with Development Tools
      1. 11.1. Taking a Look at Visual Studio
        1. 11.1.1. The Visual Studio interface
        2. 11.1.2. Flavors of Visual Studio
        3. 11.1.3. Visual Studio in the BI world
          1. 11.1.3.1. Analysis Services Project
          2. 11.1.3.2. Import Analysis Services Database
          3. 11.1.3.3. Integration Services Connections Project Wizard
          4. 11.1.3.4. Integration Services Project
          5. 11.1.3.5. Report Server Project Wizard
          6. 11.1.3.6. Report Model Project
          7. 11.1.3.7. Report Server Project
      2. 11.2. Examining the .NET Framework
        1. 11.2.1. A language only a computer chip can love
        2. 11.2.2. Intermediate Language (IL)
        3. 11.2.3. The Common Language Runtime (CLR)
      3. 11.3. Exploring Report Builder
      4. 11.4. Diving In to SQL Server Management Studio
      5. 11.5. Getting to Know SharePoint Designer
      6. 11.6. Seeing the (Silver)light and Tasting Expression Blend
      7. 11.7. Understanding PerformancePoint
  9. IV. Incorporating Microsoft Business Intelligence into Your Business Environment
    1. 12. Setting Your BI Goals and Implementation Plan
      1. 12.1. Setting Your Business Intelligence Goals
        1. 12.1.1. Understanding the components of business goals
          1. 12.1.1.1. Politics, politics, politics!
          2. 12.1.1.2. Asking a genie
          3. 12.1.1.3. Prioritizing
          4. 12.1.1.4. Assigning complexities
        2. 12.1.2. Examining technology goals
      2. 12.2. Determining Your Implementation Plan
        1. 12.2.1. Comparing waterfall and iterative methodologies
          1. 12.2.1.1. Identifying the phases of the waterfall approach
          2. 12.2.1.2. Using an iterative approach to implement a BI system
        2. 12.2.2. Discovering how things really work
          1. 12.2.2.1. Building process maps and process flows
          2. 12.2.2.2. Sending in the BI version of a SWAT team
        3. 12.2.3. Identifying the power users
        4. 12.2.4. Solidifying the goals of the BI project
        5. 12.2.5. Identifying the data needed to attain your goals
        6. 12.2.6. Setting a solid foundation for a BI implementation
        7. 12.2.7. Scope creep can be your friend
    2. 13. Evaluating and Choosing Technologies
      1. 13.1. Assessing Your BI Capabilities
        1. 13.1.1. Identifying your current BI-friendly tools
        2. 13.1.2. Knowing your current licensing
        3. 13.1.3. Determining your current skill sets
          1. 13.1.3.1. Windows client OS
          2. 13.1.3.2. Microsoft Office productivity suite
          3. 13.1.3.3. Windows Server
          4. 13.1.3.4. Server products and features
          5. 13.1.3.5. Developers familiar with Visual Studio and .NET
          6. 13.1.3.6. SQL Server Database administrators
          7. 13.1.3.7. Microsoft products all over the place
      2. 13.2. Choosing Technologies to Incorporate
        1. 13.2.1. Understanding your business foundation
        2. 13.2.2. Putting together the BI technology puzzle
        3. 13.2.3. Plugging in the pieces
      3. 13.3. Utilizing Free BI Tools: Try Before You Buy
        1. 13.3.1. Trying SQL Server
        2. 13.3.2. Checking out SharePoint
      4. 13.4. Reducing Risk
    3. 14. Testing and Rolling Out
      1. 14.1. Continuously Adding Value
      2. 14.2. Testing Your BI Implementation
        1. 14.2.1. BI testing diversity
          1. 14.2.1.1. Business process testing
          2. 14.2.1.2. Testing to ensure data are captured and stored
          3. 14.2.1.3. ETL testing
          4. 14.2.1.4. Keeping the project honest — did you answer the question?
        2. 14.2.2. Unit testing
          1. 14.2.2.1. Documenting the goal
          2. 14.2.2.2. Mapping the current state of your business process
          3. 14.2.2.3. Creating a data-storage mechanism
          4. 14.2.2.4. Mapping the future process state
          5. 14.2.2.5. Modifying the current processes
          6. 14.2.2.6. Creating the ETL package using SSIS
          7. 14.2.2.7. Creating information and surfacing data
          8. 14.2.2.8. So, to reiterate . . .
      3. 14.3. Rolling It Out — Again and Again
        1. 14.3.1. Surfacing information
          1. 14.3.1.1. Excel
          2. 14.3.1.2. SQL Server Reporting Services (SSRS)
          3. 14.3.1.3. SharePoint Key Performance Indicators (KPIs)
          4. 14.3.1.4. PerformancePoint Services for SharePoint
          5. 14.3.1.5. Report Builder reports
      4. 14.4. Having a BI Management Plan
      5. 14.5. Managing Change
        1. 14.5.1. Gaining early adoption
        2. 14.5.2. Transparency is crucial
        3. 14.5.3. Delegating ownership
        4. 14.5.4. Changing business processes
          1. 14.5.4.1. Deciding on the best change mechanism
          2. 14.5.4.2. Iterative and inclusive change
        5. 14.5.5. Introducing new technology without mutiny
    4. 15. Training, Using, and Evaluating Results
      1. 15.1. Tackling Training Efforts
        1. 15.1.1. Continuous education
        2. 15.1.2. Enabling self-service training
        3. 15.1.3. SharePoint training resources
          1. 15.1.3.1. SharePoint training roadmap
          2. 15.1.3.2. SharePoint training
          3. 15.1.3.3. SharePoint Designer training
        4. 15.1.4. SQL Server training resources
        5. 15.1.5. Training users at the grassroots level
      2. 15.2. Evaluating Results
        1. 15.2.1. Getting feedback with SharePoint
          1. 15.2.1.1. SharePoint surveys
          2. 15.2.1.2. Discussion boards
          3. 15.2.1.3. Blogs
          4. 15.2.1.4. Wikis
          5. 15.2.1.5. Interviews
      3. 15.3. Incorporating Feedback
      4. 15.4. Creating a BI Culture
        1. 15.4.1. Inclusion
        2. 15.4.2. Communication and collaboration
        3. 15.4.3. Ownership
        4. 15.4.4. Merit-based recognition
        5. 15.4.5. Trust
  10. V. The Part of Tens
    1. 16. Ten Microsoft BI Implementation Pitfalls
      1. 16.1. Drowning Under the Waterfall
      2. 16.2. Getting Stuck on the Shelf(-ware)
      3. 16.3. Letting Politics Kill the BI Project
      4. 16.4. Ignoring IT
      5. 16.5. Disregarding Power Users
      6. 16.6. Snubbing Business Processes
      7. 16.7. Overpromising Results
      8. 16.8. Getting Squashed by Top-Down Decree
      9. 16.9. Skimping on the Foundation
      10. 16.10. Misjudging How to Use Consultants
    2. 17. Ten Keys to Successful Microsoft Business Intelligence
      1. 17.1. Reiterating an Iterative Approach
      2. 17.2. Obtaining Executive-Level Sponsorship
      3. 17.3. Assessing Your Current Environment
      4. 17.4. Developing an Implementation Plan
      5. 17.5. Choosing the Right People for the Implementation Team
        1. 17.5.1. Your in-house team members
        2. 17.5.2. Calling in consultants
      6. 17.6. Creating an Inclusive Environment
      7. 17.7. Fostering a Culture of Communication and Collaboration
      8. 17.8. Starting with the Right Goals
      9. 17.9. Reducing Risk
      10. 17.10. Maintaining Perspective
    3. 18. Ten Ways to Boost Your Bottom Line with Microsoft Business Intelligence
      1. 18.1. Increasing Efficiency
      2. 18.2. Improving Agility
      3. 18.3. Increasing the Visibility of Business Processes
      4. 18.4. Forecasting
      5. 18.5. Taking Advantage of Existing Skill Sets
      6. 18.6. Collaborating and Communicating
      7. 18.7. Reusing Code in Various Functional Areas
      8. 18.8. Consolidating Content
      9. 18.9. Increasing Productivity
      10. 18.10. Making Deep Use of SQL Server and SharePoint
  11. Glossary

Product information

  • Title: Microsoft® Business Intelligence For Dummies®
  • Author(s): Ken Withee
  • Release date: March 2010
  • Publisher(s): For Dummies
  • ISBN: 9780470526934