You are previewing Microsoft SQL Server 2014 Business Intelligence Development: Beginner’s Guide.
O'Reilly logo
Microsoft SQL Server 2014 Business Intelligence Development: Beginner’s Guide

Book Description

Get to grips with Microsoft Business Intelligence and Data Warehousing technologies using this practical guide

In Detail

Take advantage of the real power behind the BI components of SQL Server 2014, Excel 2013, and SharePoint 2013 with this hands-on book. This book will give you a competitive advantage by helping you to quickly learn how to design and build BI system with Microsoft BI tools.

This book starts with designing a data warehouse with dimensional modeling, and then looks at creating data models based on SSAS multidimensional and Tabular technologies. It will illustrate how to use SSIS for ETL, and MDS and DQS for data governance and data quality. The book is packed with real-world examples that will give you a good understanding of the BI and DW components of SQL Server 2014, Excel 2013, and SharePoint 2013.

What You Will Learn

  • Perform Data Modeling with SSAS Multidimensional and MDX
  • Use DAX and SSAS tabular for Data Modeling
  • Manage Master Data with MDS
  • Reveal Knowledge Driven Data Quality with DQS
  • Understand prediction and Data Mining
  • Identify data patterns and predictive models
  • Design Dashboards with PerformancePoint and Power View
  • Explore Power Query and Power Map as components of Power BI
  • Create Reports with SQL Server Reporting Services and integrate them in applications
  • Downloading the example code for this book. You can download the example code files for all Packt books you have purchased from your account at http://www.PacktPub.com. If you purchased this book elsewhere, you can visit http://www.PacktPub.com/support and register to have the files e-mailed directly to you.

    Table of Contents

    1. Microsoft SQL Server 2014 Business Intelligence Development Beginner's Guide
      1. Table of Contents
      2. Microsoft SQL Server 2014 Business Intelligence Development Beginner's Guide
      3. Credits
      4. About the Author
      5. About the Reviewers
      6. www.PacktPub.com
        1. Support files, eBooks, discount offers, and more
          1. Why subscribe?
          2. Free access for Packt account holders
          3. Instant updates on new Packt books
      7. Preface
        1. What this book covers
        2. What you need for this book
        3. Who this book is for
        4. Conventions
        5. Time for action – heading
          1. What just happened?
        6. Reader feedback
        7. Customer support
          1. Downloading the example code
          2. Downloading color versions of the images for this book
          3. Errata
          4. Piracy
          5. Questions
      8. 1. Data Warehouse Design
        1. Understanding Business Intelligence
        2. The architecture and components of a BI system
          1. The data warehouse
          2. Extract Transform Load
          3. Data model – BISM
          4. Data visualization
          5. Master Data Management
          6. Data Quality Services
        3. Building the data warehouse
          1. Dimensional modeling
            1. Fact or measure
            2. Dimension
            3. The Fact table
            4. Grain
          2. The star schema
          3. An example of Internet sales
            1. FactSales
            2. The customer dimension
            3. DimDate
            4. DimProduct
            5. DimStore
          4. Types of facts
            1. The Factless Fact table – The Bridge table
          5. Types of dimensions
          6. Slowly Changing Dimension
            1. SCD type 0
            2. SCD type 1
            3. SCD type 2
        4. Summary
      9. 2. SQL Server Analysis Services Multidimensional Cube Development
        1. Why is OLAP required?
        2. Understanding the SQL Server Analysis Services engine
        3. Developing your first cube
        4. Time for action – creating an Analysis Services project
          1. What just happened?
        5. Time for action – creating the first cube
          1. What just happened?
        6. Time for action – viewing the cube in the browser
          1. What just happened?
        7. Dimensions and measures
        8. Time for action – using the Dimension Designer
          1. What just happened?
        9. Time for action – change the order of the Month attribute
          1. What just happened?
        10. Time for action – modifying the measure properties
          1. What just happened?
        11. Data Source View
        12. Time for action – creating a Named Calculation
          1. What just happened?
        13. Time for action – using a Named Query
          1. What just happened?
        14. Using dimensions
        15. Time for action – adding a Fact relationship
          1. What just happened?
        16. Hierarchies
        17. Time for action – creating a hierarchy
          1. What just happened?
        18. Multidimensional eXpression, calculated members, and Named Sets
        19. Time for action – writing an MDX query
          1. What just happened?
        20. Time for action – calculated members
          1. What just happened?
        21. Deploying and processing
        22. Time for action – deploying an SSAS project
          1. What just happened?
        23. Time for action – processing the data
          1. What just happened?
        24. Summary
      10. 3. Tabular Model Development of SQL Server Analysis Services
        1. Introducing SSAS Tabular
        2. Developing a tabular project
        3. Time for action – creating a tabular project
          1. What just happened?
        4. Time for action – creating measures
          1. What just happened?
        5. Creating hierarchies
        6. Time for action – creating a hierarchy from a single table
          1. What just happened?
        7. Time for action – creating a hierarchy from multiple tables
          1. What just happened?
        8. Data Analysis eXpression, calculated columns, and measures
        9. Time for action – using time intelligence functions in DAX
          1. What just happened?
        10. Securing the data
        11. Time for action – security in tabular
          1. What just happened?
        12. Storage modes
        13. Time for action – creating a model with the DirectQuery storage mode
          1. What just happened?
        14. Tabular versus Multidimensional SSAS
        15. Summary
      11. 4. ETL with Integration Services
        1. Understanding ETL and data consolidation
          1. Staging
        2. SQL Server Integration Services
        3. Integration Service projects and packages
        4. Time for action – creating your first SSIS project
          1. What just happened?
        5. The Control Flow tab
        6. Time for action – working with Control Flow tasks
          1. What just happened?
        7. The Data Flow tab
        8. Time for action – loading customer information from a flat file into a database table with a Data Flow Task
          1. What just happened?
        9. Containers and dynamic packages
        10. Time for action – looping through CSV files in a directory and loading them into a database table
          1. What just happened?
        11. Deploying and executing
        12. Time for action – deploying an SSIS project
          1. What just happened?
        13. Time for action – executing an SSIS package from a catalog
          1. What just happened?
        14. Summary
      12. 5. Master Data Management
        1. Understanding Master Data Management
        2. Master Data Services
        3. Time for action – configuring MDS
          1. What just happened?
        4. Comparing WebUI with the Excel Add-in
        5. Time for action – installing Excel Add-in
          1. What just happened?
        6. Creating models and entities
        7. Time for action – creating a model and an entity
          1. What just happened?
        8. Time for action – creating an entity with data from the Excel Add-in
          1. What just happened?
        9. Time for action – change tracking
          1. What just happened?
        10. The entity relationship
        11. Time for action – creating a domain-based relationship
          1. What just happened?
        12. Business rules
        13. Time for action – creating a simple business rule
          1. What just happened?
        14. Working with hierarchies
        15. Time for action – creating a derived hierarchy
          1. What just happened?
        16. Security and permission
        17. Time for action – permission walkthrough
          1. What just happened?
        18. Integration management
        19. Time for action – a subscription view
          1. What just happened?
        20. Time for action – entity-based staging
          1. What just happened?
        21. Summary
      13. 6. Data Quality and Data Cleansing
        1. Understanding data quality
        2. Data Quality Services
        3. Time for action – installing Data Quality Services
          1. What just happened?
        4. Knowledge Base Management
        5. Time for action – creating a Knowledge Base
          1. What just happened?
        6. Knowledge discovery
        7. Time for action – knowledge discovery
          1. What just happened?
        8. Data cleansing with Data Quality Projects
        9. Time for action – using Data Quality Project for cleansing
          1. What just happened?
        10. Domain and composite domain rules
        11. Time for action – composite domain rules
          1. What just happened?
        12. Synonyms and standardization
        13. Time for action – creating synonyms and setting standardization
          1. What just happened?
        14. Matching
        15. Time for action – matching policy
          1. What just happened?
        16. Time for action – matching projects
          1. What just happened?
        17. Integrating with MDS and SSIS
        18. Time for action – the DQS cleansing component in SSIS
          1. What just happened?
          2. DQS integration with MDS
        19. Summary
      14. 7. Data Mining – Descriptive Models in SSAS
        1. An introduction to data mining
        2. The Microsoft Decision Tree algorithm
        3. Time for action – creating a data mining solution with the Microsoft Decision Tree algorithm
          1. What just happened?
        4. Microsoft association rules
        5. Time for action – the Microsoft association rule
          1. What just happened?
            1. Algorithm parameters
        6. Summary
      15. 8. Identifying Data Patterns – Predictive Models in SSAS
        1. Finding the best algorithm
        2. Time for action – finding the best mining model with Lift Chart and Profit Chart
          1. What just happened?
        3. Predicting data with DMX
        4. Time for action – predicting prospective bike buyers
          1. What just happened?
        5. Microsoft Time Series
        6. Time for action – predicting future sales with Microsoft Time Series
          1. What just happened?
        7. Summary
      16. 9. Reporting Services
        1. The Reporting Services architecture
        2. Developing a basic report
        3. Time for action – creating our first report using SSRS
          1. What just happened?
        4. Extended report development
          1. Parameters
        5. Time for action – adding parameters to a report
          1. What just happened?
        6. Printing and page configuration
        7. Time for action – changing a page's properties
          1. What just happened?
        8. Sorting and grouping
        9. Time for action – applying ordering and grouping on the data rows
          1. What just happened?
        10. Expressions
        11. Time for action – changing the background color of data rows based on expressions
          1. What just happened?
          2. Adding charts
        12. Time for action – working with charts in Reporting Services
          1. What just happened?
        13. Deploying and configuring
        14. Time for action – deploying a report
          1. What just happened?
        15. Time for action – using Report Manager
          1. What just happened?
        16. Summary
      17. 10. Dashboard Design
        1. The PerformancePoint service
        2. Time for action – configuring PerformancePoint in SharePoint
          1. What just happened?
          2. The Dashboard Designer utility
        3. Time for action – creating your first dashboard with PerformancePoint Dashboard Designer
          1. What just happened?
          2. The dashboard pages
        4. Time for action – creating a dashboard page
          1. What just happened?
          2. PPS dashboard's on-the-fly features
        5. Time for action – exploring on-the-fly features
          1. What just happened?
          2. Filters
        6. Time for action – working with filters
          1. What just happened?
          2. PerformancePoint Wrap Up
        7. Power View
        8. Time for action – enabling Power View in Excel
          1. What Just Happened?
        9. Time for action – creating the first Power View dashboard
          1. What just happened?
          2. Map
        10. Time for action – geographical data visualization using Power View
          1. What just happened?
          2. Scatter chart
        11. Time for action – visualizing time-based information with a scatter chart
          1. What just happened?
          2. Filtering data
        12. Time for action – using Filter in Power View
          1. Wrapping up Power View
        13. Summary
      18. 11. Power BI
        1. Self-service ETL with Power Query
        2. Time for action – self-service ETL with Power Query
          1. What just happened?
        3. Power Map
        4. Time for action – data visualization with Power Map
          1. What just happened?
        5. Summary
      19. 12. Integrating Reports in Applications
        1. Designing .NET applications with reports
        2. Time for action – installing AdventureWorks SSRS sample reports
          1. What just happened?
        3. Developing reports in a web application
        4. Time for action – working with reports in web/Windows applications
          1. What just happened?
            1. Processing modes
            2. ReportViewer in Web and Windows
            3. The ReportViewer toolbar
        5. Developing reports in a Metro application
        6. Time for action – working with reports in Metro applications
          1. What just happened?
        7. Working with ReportViewer in a local processing mode
        8. Time for action – designing reports and working with the local processing mode
          1. What just happened?
        9. Passing parameters to a report
        10. Time for action – changing a report configuration with a ReportViewer Object through code behind
          1. What just happened?
        11. Using the results of a mining model in an application
        12. Time for action – running DMX queries from a .NET application
          1. What just happened?
        13. Summary
      20. Index