Pro SQL Server 2012 BI Solutions

Pro SQL Server 2012 BI Solutions

Released September 2012
Publisher(s): Apress
ISBN: 9781430234883

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

Business intelligence projects do not need to cost multi-millions of dollars or take months or even years to complete! Using rapid application development (RAD) techniques along with Microsoft SQL Server 2012, this book guides database administrators, SQL programmers, and report specialists in creating practical, cost-effective business intelligence solutions for their companies and departments.

Pro SQL Server 2012 BI Solutions provides practical examples of cost-effective business intelligence projects. Readers will be guided through several complete projects that build a foundation for real-world solutions. Even with limited experience using Microsoft's SQL Server, Integration Server, Analysis Server, and Reporting Server, you can leverage your existing knowledge of SQL programming and database design to provide users with the business intelligence reports they need.

  • Provides recipes for multiple business intelligence scenarios

  • Progresses from simple to advanced projects using several examples

  • Shows Microsoft SQL Server technology used to complete real-world business intelligence projects

    • What you'll learn

  • How to plan and implement cost-effective business intelligence projects

  • How to create practical data warehouse databases

  • How to extract, transform, and load data with Integration Services

  • How to develop OLAP cubes and dimensions on Analysis Server

  • How to create Reporting Server reports using both SQL and MDX

  • How to apply performance-tuning techniques to get the most from your solutions

    • Who this book is for

    Pro SQL Server 2012 BI Solutions is aimed at database administrators, SQL programmers, and report developers who create business intelligence solutions for midsized businesses and departments.

    Table of contents

    1. Title
    2. Dedication
    3. Contents at a Glance
    4. Contents
    5. About the Authors
    6. About the Technical Reviewers
    7. Acknowledgments
    8. Chapter 1: Business Intelligence Solutions
      1. Who Should Read This Book?
      2. What Is a Business Intelligence Solution?
      3. Practice Exercises and More
      4. Downloadable Content
      5. Our Example Scenarios
      6. Setup Instructions
      7. Think Small, Win Big
      8. Rapid Application Development for BI Solutions
      9. Moving On
      10. What's Next?
    9. Chapter 2: A Big-Picture Overview
      1. The 10,000-Foot View
      2. Interviewing and Isolating Data
      3. Plan the Solution
      4. Creating the Data Warehouse
      5. Create the ETL Process
      6. Creating a Cube
      7. Creating Reports
      8. Testing the Solution
      9. Approve, Release, and Prepare
      10. Moving On
      11. What's Next?
    10. Chapter 3: Planning Solutions
      1. Outline the Steps in the Process
      2. Interviewing
      3. “Hey, Wait! I'm a Developer, Not a Manager”
      4. Documenting the Requirements
      5. Locating Data
      6. Defining the Roles
      7. Defining the Team
      8. Determining the Schedule
      9. The IT, Security and Licensing Requirements
      10. Estimating the Cost
      11. Documenting the Solution Plan
      12. Implementation
      13. Moving On
      14. What's Next?
    11. Chapter 4: Designing a Data Warehouse
      1. What Is a Data Warehouse?
      2. What Is a Data Mart?
      3. Competing Definitions
      4. Starting with an OLTP Design
      5. A Typical OLTP Database Design
      6. A Typical Data Warehouse Database Design
      7. Stars and Snowflakes
      8. Dimensional Patterns
      9. Conformed Dimensions
      10. Adding Surrogate Keys
      11. Slowly Changing Dimensions
      12. Moving On
      13. What's Next?
    12. Chapter 5: Creating a Data Warehouse
      1. SQL Server Management Studio
      2. Configuration Manager
      3. Management Studio Windows
      4. Creating Data Warehouse Database
      5. Creating Tables
      6. Creating a Date Dimension Table
      7. Getting Organized
      8. Moving On
      9. What's Next?
    13. Chapter 6: ETL Processing with SQL
      1. Performing the ETL Programming
      2. Deciding on Full or Incremental Loading
      3. Isolating the Data to Be Extracted
      4. Identifying the Transformation Logic
      5. Programming Your Transformation Logic
      6. The SQL Query Designer
      7. Updating Your BI Documentation
      8. Building an ETL Script
      9. Working in the Abstract
      10. Moving On
      11. What's Next?
    14. Chapter 7: Beginning the ETL Process with SSIS
      1. Starting Your SSIS Project
      2. Adding a Project to an Existing Solution
      3. Renaming Your SSIS Package
      4. The Anatomy of an SSIS Package
      5. Data Connections
      6. Execute SQL Tasks
      7. The Progress/Execution Results Tabs
      8. Resetting Your Destination Database
      9. Moving On
      10. What's Next?
    15. Chapter 8: Concluding the ETL Process with SSIS
      1. Data Flows
      2. Outlining a Data Flow Task
      3. Configuring the Data Source
      4. The OLE DB Source Editor
      5. Data Flow Paths
      6. Error Outputs Paths
      7. Configuring the Data Destination
      8. Executing the Entire Package
      9. Moving On
      10. What's Next?
    16. Chapter 9: Beginning the SSAS Project
      1. SQL Server vs. Analysis Server Databases
      2. OLAP Cubes vs. Reporting Tables
      3. SQL Server vs. Analysis Server Applications
      4. SSAS Projects
      5. Data Sources
      6. Data Source Views
      7. Dimensions
      8. Moving On
      9. What's Next?
    17. Chapter 10: Configuring Dimensions with SSAS
      1. The Dimension Designer
      2. Build, Deploy, and Process
      3. Browsing the Dimension
      4. Moving On
      5. What's Next?
    18. Chapter 11: Creating and Configuring SSAS Cubes
      1. Creating Cubes
      2. Processing the Cube
      3. Configuring Cubes
      4. The Browser Tab
      5. The Cube Structure Tab
      6. The Dimension Usage Tab
      7. The Calculations Tab
      8. Making a Test Copy of a Cube
      9. KPIs
      10. Moving On
      11. What's Next?
    19. Chapter 12: Additional Cube and Dimension Configurations
      1. Additional Cube Configurations
      2. Additional Dimension Configurations
      3. Managing Your Cubes and Dimensions
      4. Moving On
      5. What's Next?
    20. Chapter 13: Creating Reports with SQL Queries
      1. Identifying the Data
      2. Joining Table Data
      3. Ordering Results
      4. Formatting Results Using SQL Functions
      5. Filtering Results
      6. Adding Dynamic Filters with Parameters
      7. Adding Aggregations
      8. Using Subqueries
      9. Creating KPI Queries
      10. Adding Abstraction Layers
      11. Using Your Code in Reporting Applications
      12. Moving On
      13. What's Next?
    21. Chapter 14: Reporting with MDX Queries
      1. Key Concepts and Terms
      2. Programming with MDX
      3. The Non Empty Clause
      4. Using Your Code in Reporting Applications
      5. Moving On
      6. What Next?
    22. Chapter 15: Reporting with Microsoft Excel
      1. Microsoft's BI Reporting
      2. Excel Reports from the Data Warehouse
      3. Changing Connection Properties
      4. Using Stored Procedures
      5. Working with Excel Reports from a Cube
      6. Testing Your Reports
      7. Creating Charts
      8. Saving to PDF
      9. Moving On
      10. What's Next?
    23. Chapter 16: Creating Reports with SSRS
      1. SSRS Architecture
      2. Creating SSRS Objects
      3. Managing the Report
      4. Moving On
      5. What's Next?
    24. Chapter 17: Configuring Reports with SSRS
      1. Creating a Report Template
      2. Adding a Header and Footer
      3. Setting Report Properties
      4. Designing the Header
      5. Renaming Report Items
      6. Using Expressions
      7. Completing the Header
      8. Configuring the Footer
      9. Saving the Report Template
      10. Moving On
      11. What's Next
    25. Chapter 18: Testing and Tuning BI Solutions
      1. Testing the BI Solution
      2. Tuning the BI Solution
      3. Performance Measurements
      4. Creating a Metadata Database
      5. Moving On
      6. What's Next
    26. Chapter 19: Approve, Release, and Prepare
      1. The End of the Cycle
      2. The Final Approval Process
      3. Announcing the Release
      4. Releasing the Solution
      5. Release Documentation
      6. User Documentation
      7. User Training
      8. Say Thank You
      9. Moving On
      10. What's Next?
    27. Index

    Product information

    • Title: Pro SQL Server 2012 BI Solutions
    • Author(s):
    • Release date: September 2012
    • Publisher(s): Apress
    • ISBN: 9781430234883