You are previewing Knight's Microsoft® Business Intelligence 24-Hour Trainer: Leveraging Microsoft SQL Server® Integration, Analysis, and Reporting Services with Excel® and SharePoint®.
O'Reilly logo
Knight's Microsoft® Business Intelligence 24-Hour Trainer: Leveraging Microsoft SQL Server® Integration, Analysis, and Reporting Services with Excel® and SharePoint®

Book Description

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 Video 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!

Table of Contents

  1. Copyright
  2. ABOUT THE AUTHORS
  3. ABOUT THE TECHNICAL EDITOR
  4. CREDITS
  5. ACKNOWLEDGMENTS
  6. INTRODUCTION
    1. WHO THIS BOOK IS FOR
    2. WHAT THIS BOOK COVERS
    3. HOW THIS BOOK IS STRUCTURED
    4. WHAT YOU NEED TO USE THIS BOOK
    5. INSTRUCTIONAL VIDEOS ON DVD
    6. CONVENTIONS
    7. SUPPORTING PACKAGES AND CODE
    8. ERRATA
    9. P2P.WROX.COM
  7. I. Data Warehousing And Business Intelligence
    1. 1. Why Business Intelligence?
      1. 1.1. HOW INTELLIGENT IS YOUR ORGANIZATION?
        1. 1.1.1. Getting Intelligence from Data
        2. 1.1.2. BI to the Rescue
        3. 1.1.3. BI = Business Investment
        4. 1.1.4. Microsoft's Business Intelligence Stack
        5. 1.1.5. BI Users and Technology
      2. 1.2. TRY IT
    2. 2. Dimensional Modeling
      1. 2.1. KEY DIMENSIONAL MODELING ELEMENTS
      2. 2.2. HOW DOES DIMENSIONAL MODELING WORK?
      3. 2.3. TRY IT
        1. 2.3.1. Lesson Requirements
        2. 2.3.2. Hints
        3. 2.3.3. Step-by-Step
    3. 3. Fact Table Modeling
      1. 3.1. TRY IT
        1. 3.1.1. Lesson Requirements
        2. 3.1.2. Hints
        3. 3.1.3. Step-by-Step
  8. II. SQL Server Integration Services
    1. 4. Understanding SSIS
      1. 4.1. BUSINESS INTELLIGENCE DEVELOPMENT STUDIO (BIDS)
      2. 4.2. SOLUTION EXPLORER
      3. 4.3. SSIS DESIGNER
      4. 4.4. VARIABLES
      5. 4.5. SSIS ARCHITECTURE
      6. 4.6. TRY IT
        1. 4.6.1. Lesson Requirements
        2. 4.6.2. Hints
        3. 4.6.3. Step-by-Step
    2. 5. Using the Control Flow
      1. 5.1. CONTROL FLOW CONTAINERS
      2. 5.2. CONTROL FLOW TASKS
      3. 5.3. PRECEDENCE CONSTRAINTS
      4. 5.4. CONNECTION MANAGER
      5. 5.5. CONTROL FLOW DESIGNER
      6. 5.6. TRY IT
        1. 5.6.1. Lesson Requirements
        2. 5.6.2. Hints
        3. 5.6.3. Step-by-Step
    3. 6. Using the Data Flow
      1. 6.1. DATA FLOW SOURCES (EXTRACTING DATA)
      2. 6.2. DATA FLOW TRANSFORMATIONS
      3. 6.3. DATA FLOW PATHS
      4. 6.4. DATA FLOW DESIGNER
      5. 6.5. TRY IT
        1. 6.5.1. Lesson Requirements
        2. 6.5.2. Hints
        3. 6.5.3. Step-by-Step
    4. 7. Solving Common SSIS Scenarios
      1. 7.1. TRY IT
        1. 7.1.1. Lesson Requirements
        2. 7.1.2. Hints
        3. 7.1.3. Step-by-Step
    5. 8. Loading Dimensions
      1. 8.1. USING THE SLOWLY CHANGING DIMENSION TASK
      2. 8.2. TRY IT
        1. 8.2.1. Lesson Requirements
        2. 8.2.2. Hints
        3. 8.2.3. Step-by-Step
    6. 9. Loading a Fact Table
      1. 9.1. TRY IT
        1. 9.1.1. Lesson Requirements
        2. 9.1.2. Hints
        3. 9.1.3. Step-by-Step
    7. 10. Deploying SSIS Packages
      1. 10.1. DEPLOYING A SINGLE PACKAGE USING SSMS
      2. 10.2. DEPLOYING A SINGLE PACKAGE USING BIDS
      3. 10.3. CREATING A DEPLOYMENT UTILITY USING BIDS
      4. 10.4. TRY IT
        1. 10.4.1. Lesson Requirements
        2. 10.4.2. Hints
        3. 10.4.3. Step-by-Step
  9. III. SQL Server Analysis Services
    1. 11. Understanding SSAS
      1. 11.1. SSAS ARCHITECTURE
      2. 11.2. CUBES
        1. 11.2.1. Measure Groups
        2. 11.2.2. Dimensions
          1. 11.2.2.1. Attribute Relationships
      3. 11.3. MDX
      4. 11.4. BIDS FOR SSAS
      5. 11.5. TRY IT
        1. 11.5.1. Lesson Requirements
        2. 11.5.2. Hints
        3. 11.5.3. Step-by-Step
    2. 12. Configuring a Data Source and Data Source View
      1. 12.1. CREATING A DATA SOURCE
      2. 12.2. CREATING A DATA SOURCE VIEW
        1. 12.2.1. Adding Named Calculations
        2. 12.2.2. Giving Tables and Columns Friendly Names
      3. 12.3. TRY IT
        1. 12.3.1. Lesson Requirements
        2. 12.3.2. Hints
        3. 12.3.3. Step-by-Step
    3. 13. Using the Cube Wizard
      1. 13.1. TRY IT
        1. 13.1.1. Lesson Requirements
        2. 13.1.2. Hints
        3. 13.1.3. Step-by-Step
    4. 14. Editing Your Dimension
      1. 14.1. DIMENSION EDITOR
        1. 14.1.1. User Hierarchies
      2. 14.2. ATTRIBUTE RELATIONSHIPS
      3. 14.3. KEY COLUMNS
      4. 14.4. DIMENSION AND ATTRIBUTE TYPES
      5. 14.5. TRY IT
        1. 14.5.1. Lesson Requirements
        2. 14.5.2. Hints
        3. 14.5.3. Step-by-Step
    5. 15. Editing Your Cube
      1. 15.1. CUBE EDITOR TOUR
      2. 15.2. BROWSING THE CUBE
      3. 15.3. TRY IT
        1. 15.3.1. Lesson Requirements
        2. 15.3.2. Hints
        3. 15.3.3. Step-by-Step
    6. 16. Adding New Dimensions and Measure Groups
      1. 16.1. ADDING A MEASURE GROUP AND MEASURES
      2. 16.2. ADDING A DIMENSION
        1. 16.2.1. Relationship Types
        2. 16.2.2. Adding Snowflake Dimension Tables
      3. 16.3. TRY IT
        1. 16.3.1. Lesson Requirements
        2. 16.3.2. Hints
        3. 16.3.3. Step-by-Step
    7. 17. Using MDX
      1. 17.1. ANATOMY OF BASIC MDX
        1. 17.1.1. Tuples and Sets
        2. 17.1.2. Filtering Data
      2. 17.2. NAVIGATION FUNCTIONS
      3. 17.3. TRY IT
        1. 17.3.1. Lesson Requirements
        2. 17.3.2. Hints
        3. 17.3.3. Step-by-Step
    8. 18. Creating Calculations
      1. 18.1. CALCULATION BASICS
      2. 18.2. COLOR-CODING MEASURES
      3. 18.3. NAMED SETS
      4. 18.4. MORE ADVANCED CALCULATIONS
        1. 18.4.1. Percentage of All Level
        2. 18.4.2. Percentage of Parent
        3. 18.4.3. Year-over-Year Growth
      5. 18.5. TRY IT
        1. 18.5.1. Lesson Requirements
        2. 18.5.2. Hints
        3. 18.5.3. Step-by-Step
    9. 19. Data Mining
      1. 19.1. INTRODUCTION TO DATA MINING
      2. 19.2. DATA-MINING PROCESS
        1. 19.2.1. Understanding the Question
        2. 19.2.2. It's All About the Data
        3. 19.2.3. Data-Mining Model
        4. 19.2.4. Evaluating the Model
        5. 19.2.5. Productionalizing the Model
      3. 19.3. CREATING A MINING MODEL
      4. 19.4. EXPLORING THE MODEL
      5. 19.5. EVALUATING THE MODEL
      6. 19.6. QUERYING THE MODEL
      7. 19.7. TRY IT
        1. 19.7.1. Lesson Requirements
        2. 19.7.2. Hints
        3. 19.7.3. Step-by-Step
    10. 20. Administering the SSAS Instance
      1. 20.1. SECURING THE DATABASE
      2. 20.2. PARTITIONING THE DATA
      3. 20.3. AGGREGATIONS
      4. 20.4. USAGE-BASED OPTIMIZATION
      5. 20.5. PROCESSING THE CUBE
      6. 20.6. DEPLOYING CHANGE
      7. 20.7. TRY IT
        1. 20.7.1. Lesson Requirements
        2. 20.7.2. Hints
        3. 20.7.3. Step-by-Step
  10. IV. SQL Server Reporting Services
    1. 21. Understanding SSRS
      1. 21.1. BUILDING YOUR FIRST REPORT
        1. 21.1.1. Putting Items on the Report
        2. 21.1.2. Previewing and Formatting the Report
      2. 21.2. TRY IT
        1. 21.2.1. Lesson Requirements
        2. 21.2.2. Hints
        3. 21.2.3. Step-by-Step
    2. 22. Using Report Wizard
      1. 22.1. TRY IT
        1. 22.1.1. Lesson Requirements
        2. 22.1.2. Hints
        3. 22.1.3. Step-by-Step
    3. 23. Building a Matrix Report
      1. 23.1. TRY IT
        1. 23.1.1. Lesson Requirements
        2. 23.1.2. Hints
        3. 23.1.3. Step-by-Step
    4. 24. Parameterizing Your Reports
      1. 24.1. CREATING PARAMETERS
      2. 24.2. DEFAULT PARAMETER VALUES
      3. 24.3. PARAMETER AVAILABLE VALUES
      4. 24.4. MULTI-VALUE PARAMETERS
      5. 24.5. ALTERING PROPERTIES WITH PARAMETERS
      6. 24.6. TRY IT
        1. 24.6.1. Lesson Requirements
        2. 24.6.2. Hints
        3. 24.6.3. Step-by-Step
    5. 25. Building Reports on Your Cube
      1. 25.1. TRY IT
        1. 25.1.1. Lesson Requirements
        2. 25.1.2. Hints
        3. 25.1.3. Step-by-Step
    6. 26. Using Maps in Your Report
      1. 26.1. TRY IT
        1. 26.1.1. Lesson Requirements
        2. 26.1.2. Hints
        3. 26.1.3. Step-by-Step
    7. 27. Building a Dashboard
      1. 27.1. TABLES AND FILTERS
      2. 27.2. DRILL-DOWNS
      3. 27.3. TRY IT
        1. 27.3.1. Lesson Requirements
        2. 27.3.2. Hints
        3. 27.3.3. Step-by-Step
    8. 28. Deploying and Administering SSRS
      1. 28.1. STORED CREDENTIALS
      2. 28.2. SUBSCRIPTIONS
      3. 28.3. SHARED SCHEDULES
      4. 28.4. SECURITY
      5. 28.5. DATASETS AND CACHING
      6. 28.6. REPORT BUILDER 3.0
      7. 28.7. REPORT PARTS
      8. 28.8. VIEWING REPORTS
      9. 28.9. TRY IT
        1. 28.9.1. Lesson Requirements
        2. 28.9.2. Hints
        3. 28.9.3. Step-by-Step
    9. 29. New Reporting Services Visualizations — Sparklines, Data Bars, and Indicators
      1. 29.1. ADDING SPARKLINES
      2. 29.2. USING DATA BARS
      3. 29.3. CONFIGURING INDICATORS
      4. 29.4. TRY IT
        1. 29.4.1. Lesson Requirements
        2. 29.4.2. Hints
        3. 29.4.3. Step-by-Step
    10. 30. Using Report Builder
      1. 30.1. OPENING REPORT BUILDER
      2. 30.2. WORKING WITH THE TABLE, MATRIX, OR CHART WIZARD
      3. 30.3. REPORT PARTS
      4. 30.4. SHARED DATASETS
      5. 30.5. TRY IT
        1. 30.5.1. Lesson Requirements
        2. 30.5.2. Hints
        3. 30.5.3. Step-by-Step
  11. V. Containers
    1. 31. Reporting against a Cube with Excel
      1. 31.1. TRY IT
        1. 31.1.1. Lesson Requirements
        2. 31.1.2. Hints
        3. 31.1.3. Step-by-Step
    2. 32. Loading Data into a PowerPivot Workbook
      1. 32.1. WHAT IS POWERPIVOT?
        1. 32.1.1. Step 1: Install the Add-In
        2. 32.1.2. Step 2: Explore the Environment
        3. 32.1.3. Step 3: Get Some Data
      2. 32.2. TRY IT
        1. 32.2.1. Lesson Requirements
        2. 32.2.2. Hints
        3. 32.2.3. Step-by-Step
    3. 33. Creating a PowerPivot Report
      1. 33.1. COMPONENTS OF A POWERPIVOT REPORT
      2. 33.2. BUILDING THE REPORT
      3. 33.3. ADDING BELLS AND WHISTLES
      4. 33.4. TRY IT
        1. 33.4.1. Lesson Requirements
        2. 33.4.2. Hints
        3. 33.4.3. Step-by-Step
    4. 34. Data Mining in Excel
      1. 34.1. GETTING READY FOR EXCEL DATA MINING
        1. 34.1.1. Installing the Data Mining Add-Ins for Excel 2007
        2. 34.1.2. Configuring the Data Mining Add-Ins for Excel 2007
      2. 34.2. EXPLORING THE DATA MINING ADD-INS
      3. 34.3. ANALYZING KEY INFLUENCERS IN EXCEL
      4. 34.4. TRY IT
        1. 34.4.1. Lesson Requirements
        2. 34.4.2. Hints
        3. 34.4.3. Step-by-Step
  12. VI. SharePoint
    1. 35. Understanding SharePoint for Business Intelligence
      1. 35.1. TRY IT
    2. 36. Deploying and Using Reporting Services in SharePoint 2010
      1. 36.1. TRY IT
        1. 36.1.1. Lesson Requirements
        2. 36.1.2. Hints
        3. 36.1.3. Step-by-Step
    3. 37. Building PerformancePoint Dashboards in SharePoint 2010
      1. 37.1. TRY IT
        1. 37.1.1. Lesson Requirements
        2. 37.1.2. Hints
        3. 37.1.3. Step-by-Step
    4. 38. Deploying and Using Excel Services
      1. 38.1. TRY IT
        1. 38.1.1. Lesson Requirements
        2. 38.1.2. Hints
        3. 38.1.3. Step-by-Step
    5. 39. Deploying and Using PowerPivot in SharePoint
      1. 39.1. TRY IT
        1. 39.1.1. Lesson Requirements
        2. 39.1.2. Hints
        3. 39.1.3. Step-by-Step
    6. 40. Managing SharePoint Business Intelligence
      1. 40.1. TRY IT
  13. VII. Appendix
    1. A. What's on the DVD?
      1. A.1. SYSTEM REQUIREMENTS
      2. A.2. USING THE DVD
      3. A.3. WHAT'S ON THE DVD
      4. A.4. TROUBLESHOOTING
      5. A.5. CUSTOMER CARE