Microsoft Business Intelligence Tools for Excel Analysts

Microsoft Business Intelligence Tools for Excel Analysts

by Michael Alexander, Jared Decker, Bernard Wehbe
Released May 2014
Publisher(s): For Dummies
ISBN: 9781118821527

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

Bridge the big data gap with Microsoft Business Intelligence Tools for Excel Analysts

The distinction between departmental reporting done by business analysts with Excel and the enterprise reporting done by IT departments with SQL Server and SharePoint tools is more blurry now than ever before. With the introduction of robust new features like PowerPivot and Power View, it is essential for business analysts to get up to speed with big data tools that in the past have been reserved for IT professionals. Written by a team of Business Intelligence experts, Microsoft Business Intelligence Tools for Excel Analysts introduces business analysts to the rich toolset and reporting capabilities that can be leveraged to more effectively source and incorporate large datasets in their analytics while saving them time and simplifying the reporting process.

  • Walks you step-by-step through important BI tools like PowerPivot, SQL Server, and SharePoint and shows you how to move data back and forth between these tools and Excel

  • Shows you how to leverage relational databases, slice data into various views to gain different visibility perspectives, create eye-catching visualizations and dashboards, automate SQL Server data retrieval and integration, and publish dashboards and reports to the web

  • Details how you can use SQL Server's built-in functions to analyze large amounts of data, Excel pivot tables to access and report OLAP data, and PowerPivot to create powerful reporting mechanisms

    • You'll get on top of the Microsoft BI stack and all it can do to enhance Excel data analysis with this one-of-a-kind guide written for Excel analysts just like you.

    Table of contents

      1. About the Authors
      2. Introduction
        1. What You Need to Know
        2. What the Icons Mean
        3. How This Book Is Organized
          1. Part I: Leveraging Excel for Business Intelligence
          2. Part II: Leveraging SQL Server for Business Intelligence
          3. Part III: Delivering Business Intelligence with SharePoint and Excel Services
          4. Part IV: Appendixes
        4. About the Companion Web Site
      3. Part I: Leveraging Excel for Business Intelligence
        1. Chapter 1: Important Database Concepts
          1. Traditional Limits of Excel and How Databases Help
            1. Scalability
            2. Transparency of analytical processes
            3. Separation of data and presentation
          2. Database Terminology
            1. Databases
            2. Tables
            3. Records, fields, and values
            4. Queries
          3. How Databases Are Designed
            1. Step 1: The overall design — from concept to reality
            2. Step 2: Report design
            3. Step 3: Data design
            4. Step 4: Table design
        2. Chapter 2: PivotTable Fundamentals
          1. Introducing the PivotTable
            1. Anatomy of a PivotTable
            2. Creating the basic PivotTable
          2. Customizing Your PivotTable
            1. Changing the PivotTable layout
            2. Renaming the fields
            3. Formatting numbers
            4. Changing summary calculations
            5. Suppressing subtotals
            6. Hiding and showing data items
            7. Hiding or showing items without data
            8. Sorting your PivotTable
          3. Understanding Slicers
            1. Creating a standard slicer
            2. Formatting slicers
            3. Controlling multiple PivotTables with one slicer
          4. Creating a Timeline Slicer
          5. Understanding the Internal Data Model
            1. Building out your first Data Model
            2. Using your Data Model in a PivotTable
        3. Chapter 3: Introduction to Power Pivot
          1. Understanding the Power Pivot Internal Data Model
          2. Linking Excel Tables to Power Pivot
            1. Preparing your Excel tables
            2. Adding your Excel tables to the Data Model
          3. Creating Relationships Among Your Power Pivot Tables
          4. Creating a PivotTable from Power Pivot Data
          5. Enhancing Power Pivot Data with Calculated Columns
            1. Creating a calculated column
            2. Formatting your calculated columns
            3. Referencing calculated columns in other calculations
            4. Hiding calculated columns from end users
          6. Utilizing DAX to Create Calculated Columns
            1. Identifying DAX functions that are safe for calculated columns
            2. Building DAX-driven calculated columns
          7. Understanding Calculated Fields
        4. Chapter 4: Loading External Data into Power Pivot
          1. Loading Data from Relational Databases
            1. Loading data from SQL Server
            2. Loading data from Microsoft Access databases
            3. Loading data from other relational database systems
          2. Loading Data from Flat Files
            1. Loading data from external Excel files
            2. Loading data from text files
            3. Loading data from the clipboard
          3. Loading Data from Other Data Sources
          4. Refreshing and Managing External Data Connections
            1. Manually refreshing your Power Pivot data
            2. Setting up automatic refreshing
            3. Preventing Refresh All
            4. Editing your data connection
        5. Chapter 5: Creating Dashboards with Power View
          1. Activating the Power View Add-In
          2. Creating a Power View Dashboard
            1. Creating and working with Power View charts
            2. Visualizing data in a Power View map
            3. Changing the look of your Power View dashboard
        6. Chapter 6: Adding Location Intelligence with Power Map
          1. Installing and Activating the Power Map Add-In
          2. Loading Data into Power Map
            1. Choosing geography and map level
            2. Handling geocoding alerts
            3. Navigating the map
          3. Managing and Modifying Map Visualizations
            1. Visualization types
            2. Adding categories
            3. Visualizing data over time
            4. Adding layers
          4. Adding Custom Components
            1. Adding a top/bottom chart
            2. Adding annotations and text boxes
            3. Adding legends
            4. Customizing map themes and labels
          5. Customizing and Managing Power Map Tours
            1. Understanding scenes
            2. Configuring scenes
            3. Playing and sharing a tour
            4. Sharing screenshots
        7. Chapter 7: Using the Power Query Add-In
          1. Installing and Activating the Power Query Add-In
            1. Downloading the Power Query Add-In
          2. Power Query Basics
            1. Searching for source data
            2. Shaping the selected source data
            3. Understanding query steps
            4. Outputting your query results
            5. Refreshing Power Query data
            6. Managing existing queries
          3. Understanding Column and Table Actions
            1. Column level actions
            2. Table actions
          4. Power Query Connection Types
          5. Creating and Using Power Query Functions
            1. Creating and using a basic custom function
            2. Advanced function example: Combining all Excel files in a directory into one table
      4. Part II: Leveraging SQL for Business Intelligence
        1. Chapter 8: Essential SQL Server Concepts
          1. SQL Server Components
            1. SQL Server Relational Database Engine
            2. SQL Server Management Studio
          2. Connecting to a Database Service
          3. SQL Server Security
            1. Server access
            2. Database access
            3. Database object access
          4. Working with Databases
            1. Creating a database
            2. Database maintenance
          5. Working with Tables and Views
            1. Creating a table
            2. Creating a view
          6. Data Importing and Exporting
        2. Chapter 9: Introduction to SQL
          1. SQL Basics
            1. The Select statement
            2. The From clause
            3. Joins basics
            4. The Where clause
            5. Grouping
            6. The Order By clause
            7. Selecting Distinct records
            8. Selecting Top records
          2. Advanced SQL Concepts
            1. The Union operator
            2. Case expression
            3. Like operator
            4. Subqueries
            5. Advanced joins
            6. Advanced grouping
            7. Manipulating data
        3. Chapter 10: Creating and Managing SQL Scripts
          1. Design Concepts
            1. Stay organized
            2. Move data in one direction
            3. Divide data according to metrics and attributes
            4. Consider data volumes up front
            5. Consider full data reload requirements
            6. Set up logging and data validation
          2. Working with SQL Scripts
            1. Data extraction scripting
            2. Data preparation scripting
            3. Data delivery scripting
            4. Error handling
            5. Creating and altering stored procedures
          3. Indexing and Performance Considerations
            1. Understanding index types
            2. Creating an index
            3. Dropping an index
            4. Additional tips and tricks
          4. SQL Solutions to Common Analytics Problems
            1. Creating an Active Members Report
            2. Creating a Cumulative Amount Report
            3. Creating a Top Performers Report
            4. Creating an Exception List Report
        4. Chapter 11: Calling Views and Stored Procedures from Excel
          1. Importing Data from SQL Server
          2. Passing Your Own SQL Statements to External Databases
            1. Manually editing SQL statements
            2. Running stored procedures from Excel
            3. Using VBA to create dynamic connections
          3. Creating a Data Model with Multiple SQL Data Objects
          4. Calling Stored Procedures Directly from Power Pivot
        5. Chapter 12: Understanding Reporting Services
          1. Reporting Services Overview
          2. Developing a Reporting Services Report
            1. Defining a shared data source
            2. Defining a shared dataset
          3. Deploying Reports
            1. The deployment process
            2. Accessing reports
            3. SSRS security
          4. Managing Subscriptions
        6. Chapter 13: Browsing Analysis Services OLAP Cubes with Excel
          1. What Is an OLAP Database and What Can It Do?
          2. Understanding OLAP Cubes
            1. Understanding dimensions and measures
            2. Understanding hierarchies and dimension parts
          3. Connecting to an OLAP Data Source
          4. Understanding the Limitations of OLAP PivotTables
          5. Creating Offline Cubes
          6. Using Cube Functions
          7. Adding Calculations to Your OLAP PivotTables
            1. Creating calculated measures
            2. Creating calculated members
            3. Managing your OLAP calculations
            4. Performing what-if analysis with OLAP data
        7. Chapter 14: Using the Data Mining Add-In for Microsoft Office
          1. Installing and Activating the Data Mining Add-In
            1. Downloading the Data Mining Add-In
            2. Pointing to an Analysis Services database
          2. Analyze Key Influencers
          3. Detect Categories
          4. Fill From Example
          5. Forecast
          6. Highlight Exceptions
          7. Scenario Analysis
            1. Using the Goal Seek Scenario tool
            2. Using the What-If Scenario tool
          8. Prediction Calculator
            1. Interactive cost and profit inputs
            2. Score Breakdown
            3. Data table
            4. Profit for Various Score Thresholds
            5. Cumulative Misclassification Cost for Various Score Thresholds
          9. Shopping Basket Analysis
      5. Part III: Delivering Business Intelligence with SharePoint and Excel Services
        1. Chapter 15: Publishing Your BI Tools to SharePoint
          1. Understanding SharePoint
            1. Why SharePoint?
            2. Understanding Excel Services for SharePoint
            3. Limitations of Excel Services
          2. Publishing an Excel Workbook to SharePoint
          3. Publishing to a Power Pivot Gallery
          4. Managing Power Pivot Performance
            1. Limit the number of columns in your Data Model tables
            2. Limit the number of rows in your Data Model
            3. Avoid multi-level relationships
            4. Let your back-end database servers do the crunching
            5. Beware of columns with non-distinct values
            6. Avoid the excessive use of slicers
        2. Chapter 16: Leveraging PerformancePoint Services
          1. Why PerformancePoint?
            1. PerformancePoint strengths
            2. PerformancePoint limitations
          2. Authoring Dashboards
            1. Getting started
            2. Launching the Dashboard Designer
            3. Adding a data connection
            4. Adding content
            5. Publishing dashboards
          3. Using PerformancePoint Dashboards
            1. Interacting with filters
            2. Dashboard navigation
            3. Dashboard interactive capabilities
      6. Part IV: Appendixes
        1. Appendix A: Understanding the Big Data Toolset
          1. Big Data SQL Offerings
            1. Amazon Redshift
            2. Hortonworks Hive
            3. Cloudera Impala
            4. IBM Big SQL
            5. Google BigQuery
            6. Facebook Presto SQL
          2. Defining a Big Data Connection
          3. Connecting to Big Data Tools with Excel
            1. Modifying your connection
            2. Using your connection
        2. Appendix B: Considerations for Delivering Mobile BI
          1. Mobile Deployment Scenarios and Considerations
            1. Mobile devices
            2. Browser-based deployments on mobile devices
            3. Running apps on mobile devices
          2. Office 365
          3. SQL Server Reporting Services
          4. SharePoint 2010 and 2013

    Product information

    • Title: Microsoft Business Intelligence Tools for Excel Analysts
    • Author(s): Michael Alexander, Jared Decker, Bernard Wehbe
    • Release date: May 2014
    • Publisher(s): For Dummies
    • ISBN: 9781118821527