Applied Microsoft Business Intelligence
Description
More details
Other editions
Additional editions
Persons
Content
- Cover
- Title Page
- Copyright
- Contents
- Introduction
- Part I Overview of the Microsoft Business Intelligence Toolset
- Chapter 1 Which Analysis and Reporting Tools Do You Need?
- Selecting a SQL Server Database Engine
- Building a Data Warehouse
- Selecting an RDBMS
- Selecting SQL Server Analysis Services
- Working with SQL Server Reporting Services
- Understanding Operational Reports
- Understanding Ad Hoc Reporting
- Working with SharePoint
- Working with Performance Point
- Using Excel for Business Intelligence
- What Is Power Query?
- What Is Power Pivot?
- What Is Power View?
- Power Map
- Which Development Tools Do You Need?
- Using SQL Server Data Tools
- Using SQL Management Studio
- Using Dashboard Designer
- Using Report Builder
- Summary
- Chapter 2 Designing an Effective Business Intelligence Architecture
- Identifying the Audience and Goal of the Business Intelligence Solution
- Who's the Audience?
- What Is the Goal(s)?
- What Are the Data Sources?
- Using Internal Data Sources
- Using External Data Sources
- Using a Data Warehouse (or Not)
- Implementing and Enforcing Data Governance
- Planning an Analytical Model
- Planning the Business Intelligence Delivery Solution
- Considering Performance
- Considering Availability
- Summary
- Chapter 3 Selecting the Data Architecture that Fits Your Organization
- Why Is Data Architecture Selection Important?
- Challenges
- Benefits
- How Do You Pick the Right Data Architecture?
- Understanding Architecture Options
- Understanding Research Selection Factors
- Interviewing Key Stakeholders
- Completing the Selection Form
- Finalizing and Approving the Architecture
- Summary
- Part II Business Intelligence for Analysis
- Chapter 4 Searching and Combining Data with Power Query
- Downloading and Installing Power Query
- Importing Data
- Importing from a Database
- Importing from the Web
- Importing from a File
- Transforming Data
- Combining Data from Multiple Sources
- Splitting Data
- Aggregating Data
- Introducing M Programming
- A Glance at the M Language
- Adding and Removing Columns Using M
- Summary
- Chapter 5 Choosing the Right Business Intelligence Semantic Model
- Understanding the Business Intelligence Semantic Model Architecture
- Understanding the Data Access Layer
- Using Power Pivot
- Using the Multidimensional Model
- Using the Tabular Model
- Implementing Query Languages and the Business Logic Layer
- Data Analytics Expressions (DAX)
- Multidimensional Expressions (MDX)
- Direct Query and ROLAP
- Data Model Layer
- Comparing the Different Types of Models
- Which Model Fits Your Organization?
- Departmental
- Team
- Organizational
- Summary
- Chapter 6 Discovering and Analyzing Data with Power Pivot
- Understanding Hardware and Software Requirements
- Enabling Power Pivot
- Designing an Optimal Power Pivot Model
- Importing Only What You Need
- Understanding Why Data Types Matter
- Working with Columns or DAX Calculated Measures
- Optimizing the Power Pivot Model for Reporting
- Understanding Power Pivot Model Basics
- Adding All Necessary Relationships
- Adding Calculated Columns and DAX Measures
- Creating Hierarchies and Key Performance Indicators (KPIs)
- Sorting Your Data to Meet End-User Needs
- Implementing Role-Playing Dimensions
- Summary
- Chapter 7 Developing a Flexible and Scalable Tabular Model
- Why Use a Tabular Model?
- Understanding the Tabular Model
- Using the Tabular Model
- Comparing the Tabular and Multidimensional Models
- Understanding the Tabular Development Process
- How Do You Design the Model?
- Importing Data
- Designing Relationships
- Calculated Columns and Measures
- How Do You Enhance the Model?
- Adding Hierarchies
- Designing Perspectives
- Adding Partitions
- How Do You Tune the Model?
- Optimizing Processing
- Optimizing Querying
- Summary
- Chapter 8 Developing a Flexible and Scalable Multidimensional Model
- Why Use a Multidimensional Model?
- Understanding the Multidimensional Model
- Understanding the Multidimensional Model Process
- How Do You Design the Model?
- Creating Data Sources and the Data Source View
- Using the Cube Creation Wizard
- Adjusting Measures
- Completing Dimensions
- How Do You Enhance the Model?
- Adding Navigation with Hierarchies
- Using the Business Intelligence Wizard for Calculations
- Using Partitions and Aggregations
- How Do You Tune the Model?
- Resolving Processing Issues
- Querying
- Summary
- Chapter 9 Discovering Knowledge with Data Mining
- Understanding the Business Value of Data Mining
- Understanding Data Mining Techniques
- Common Business Use Cases
- Driving Decisions, Strategies, and Processes Through Data Mining
- Getting the Basics Right
- Understanding the Data
- Training and Test Datasets
- Defining the Data Mining Structure
- The Data Mining Model
- Applying the Microsoft Data Mining Techniques with Best Practices
- Using Microsoft Association Rules
- Grouping Data with Microsoft Clustering
- Building Mining Models with Microsoft Naïve Bayes
- Using the Microsoft Decision Trees
- Using Microsoft Neural Network and Microsoft Logistic Regression
- Using Microsoft Linear Regression and Microsoft Regression Trees
- Microsoft Sequence Clustering
- Forecasting with Microsoft Time Series
- Developing and Deploying a Scalable and Extensible Data Mining Solution
- Choosing Between a Relational or a Cube Source for Your Data Mining Structure
- Deploying Data Mining Models
- Using DMX to Query Data Mining Models
- Maintaining Data Mining Models
- Fine-Tuning the Data Mining Structure
- Keeping the Data Model Relevant
- Continuous Learning Cycle
- Integrating Data Mining with Your BI Solution
- Integrating Data Mining in Your DW and ETL Processes
- Integrating Data Mining with Reporting Services
- Data Mining in Excel
- Summary
- Part III Business Intelligence for Reporting
- Chapter 10 Choosing the Right Business Intelligence Visualization Tool
- Why Do You Need to Choose?
- Identifying Users
- Selecting Tools
- What Are the Selection Criteria?
- Business Capabilities
- Technical Capabilities
- How Do You Gather the Necessary Information?
- What Are the Business Intelligence Visualization Options?
- Using SQL Server Reporting Services
- Using Power View
- Using Power Map
- How Do You Create and Complete the Evaluation Matrix?
- How Do You Verify and Complete the Process?
- Evaluation Matrix #1
- Evaluation Matrix #2
- Summary
- Chapter 11 Designing Operational Reports with Reporting Services
- What Are Operational Reports and Reporting Services?
- Understanding Analytical versus Operational Reports
- Using Reporting Services
- What Are Development Best Practices?
- Using Source and Version Control
- Using Shared Data Sources and Datasets
- Creating Templates
- What Are Performance Best Practices?
- Investigating Performance
- Performance Tuning
- What Are Functionality Best Practices?
- Using Visualizations
- Using Filters and Parameters
- Providing Drilldown and Drillthrough
- Summary
- Chapter 12 Visualizing Your Data Interactively with Power View
- Where Does Power View Fit with Your Reporting Solution?
- Power View System Requirements
- Creating Power View Data Source Connections
- Creating Data Sources Inside Excel
- Creating Data Sources Inside SharePoint
- Creating Power View Reports
- Using SharePoint to Create Power View Reports
- Using Multiple Views in Power View
- Creating Power View Visualizations
- Creating Tables
- Converting Visualizations
- Creating Matrices
- Creating Charts
- Creating Multiples
- Creating Cards
- Creating Maps
- Using Excel to Create Power View Reports
- Filtering Data with Power View
- Adding Filters
- Using Advanced Filters
- Adding Slicers
- Invoking Cross-Filters
- Adding Tiles
- Adding Filters to a Report URL
- Exporting Power View Reports
- Summary
- Chapter 13 Exploring Geographic and Temporal Data with Power Map
- How Power Map Fits into Reporting Solutions
- Understanding Power Map Features and Advantages
- Comparing Power Map to Other SQL Server Geospatial Reporting Tools
- Understanding Power Map Requirements
- Optimizing Your Data Model for Power Map
- Using Tours, Scenes, and Layers in Power Map
- Defining Geography Fields in Your Data Model
- Defining Date and Time Fields in Your Data Model
- Working with Geospatial and Temporal Data
- Visualizing Data Aggregation
- Creating a Power Map Tour
- Visualizing Data Over Time with Rich Animations
- Deploying and Sharing Power Map Visualizations
- Sharing Power Map Tours
- Enhancing Power Map Deployment and Configurations in Office 365
- Summary
- Chapter 14 Monitoring Your Business with PerformancePoint Services
- Where Does PerformancePoint Services Fit with Your Reporting Solution?
- Understanding PPS Features
- When Is PPS the Right Choice?
- Implementing PPS Requirements for SharePoint
- Extending PPS Dashboards
- Adding PerformancePoint Time Intelligence
- Using Interactivity Features
- Adding Reporting Services Reports to PerformancePoint
- Extending Filters and KPIs
- Deployment Best Practices
- Following Best Practices for PerformancePoint Data Connections and Content Libraries
- Deploying Dashboards Across Dev, Test, and Production Environments
- Customizing PerformancePoint SharePoint Web Parts
- Security and Configuration Best Practices
- Configuring the Unattended Service Account in SharePoint
- Optimizing PerformancePoint Services Application Settings
- Summary
- Part IV Deploying and Managing the Business Intelligence Solution
- Chapter 15 Implementing a Self-Service Delivery Framework
- Planning a Self-Service Delivery Framework
- Creating a Data Governance Plan for Enterprise, Team, and Personal BI
- Identifying Stakeholders, Subject Matter Experts, and Data Stewards
- Understanding Industry Compliance Considerations
- Managing Data Quality and Master Data
- Identifying Target Audience and Roles
- Developing a Training Plan
- Inventorying Tools and Skillset
- Understanding Data Quality Services
- Understanding Master Data Services
- Managing Data Quality and Master Data in Excel
- Business Intelligence Features Across the Microsoft Data Platform Versions and Editions
- Defining Success Criteria
- Summary
- Chapter 16 Designing and Implementing a Deployment Plan
- What Is a Deployment Plan?
- How Do You Deploy Business Intelligence Code?
- Using Analysis Services (Multidimensional or Tabular)
- Using Reporting Services
- How Do You Implement the Deployment Plan?
- Planning the Deployment
- Designing Scripts
- Documenting Steps
- Testing the Plan
- Training Your Staff
- Summary
- Chapter 17 Managing and Maintaining the Business Intelligence Environment
- Using SQL Server Reporting Services
- Configuring Memory
- Caching Data and Pre-Rendering Reports
- Using ExecutionLog Views
- Working with SQL Server Analysis Services
- Using Multidimensional Models
- Using Tabular Models
- Using SharePoint to Improve Performance
- Summary
- Chapter 18 Scaling the Business Intelligence Environment
- Why Would You Scale the Business Intelligence Environment?
- How Do You Scale Each Tool?
- Using Analysis Services (Multidimensional or Tabular)
- Reporting Services
- Using Power Pivot and Power View
- Summary
- Index
- EULA
Introduction
Business intelligence, including reporting and analytics, is essential to an organization's survival and growth. Understanding your data and turning it into actionable insights is the key to sustaining and growing your business. As companies recognize this need, the employees that facilitate these insights, also known as "data scientists," are highly sought after across the board.
Microsoft's business intelligence suite contains tools that help the data scientists and developers perform their duties quickly and efficiently. Understanding which tool to use and how to use it in the best manner is required to provide the data. This book discusses each of the business intelligence tools and best practices associated with the Microsoft business intelligence stack, including reporting and analysis.
Overview of the Book and Technology
Microsoft's business intelligence landscape is changing at a faster rate than ever before. Several new methodologies have been incorporated into the stack, including self-service, big data, and the cloud. With the advent of these new methodologies affecting business intelligence, the number of tools is increasing. With all these changes, we recognized a need to have one place that describes each of these business intelligence tools and how they fit into a business intelligence solution.
The book also takes you outside the boundary of just the tools. You learn about different data and business intelligence architectures and when to use each type. You learn how to pick the tool right for your organization. You learn how to design and develop in each of the tools. You even learn what to do after you've developed everything and need to maintain the business intelligence solution! By the time you've completed this book, you will be comfortable implementing and administering any type of Microsoft business intelligence solution.
How This Book Is Organized
This book contains four different sections. The first section provides an overview of business intelligence solutions and a discussion of some of the tools you may need. The next two sections focus on the two halves of business intelligence: reporting and analysis. And the final section takes you through the administration and maintenance of business intelligence solutions.
In Part I, Overview of the Microsoft Business Intelligence Toolset, you learn about business intelligence tools available from Microsoft and how those tools fit into an effective and useful data and business intelligence architecture. Chapter 1, "Which Analysis and Reporting Tools Do You Need?," introduces each of the business intelligence tools, some of which have been around for many years and some of which are new to the Microsoft business intelligence stack. You will also learn what development tool to use with each of the tools.
Next, Chapter 2, "Designing an Effective Business Intelligence Architecture," takes you to the architecture level of business intelligence solutions. Designing a business intelligence architecture involves knowing your audience, defining the goals for the solution, and understanding where your information resides. You then align that information with the delivery strengths and limitations of your organization to decide on the best business intelligence architecture for your needs.
The final chapter in Part I, Chapter 3, "Selecting the Data Architecture that Fits Your Organization," completes out the architecture discussion by covering data architecture. Because a huge part of business intelligence depends on the underlying data, you will learn the available structures and when to use each one.
In Part II, Business Intelligence for Analysis, you learn about the business intelligence tools meant for analyzing and gathering insights about your data. Chapter 4, "Searching and Combining Data with Power Query," introduces the self-service data integration tool, Power Query, which allows you to combine data from a variety of sources for analysis.
Chapters 5 to 8 cover the semantic modeling tools within the Microsoft stack: Power Pivot, Analysis Services multidimensional, and Analysis Services tabular. Chapter 5, "Choosing the Right Business Intelligence Semantic Model," discusses the difference between the three products and when you would use one over the others. Chapter 6, "Discovering and Analyzing Data with Power Pivot," explains how to design and use a Power Pivot model. Chapter 7, "Developing a Flexible and Scalable Tabular Model," discusses the Analysis Services tabular model, and Chapter 8, "Developing a Flexible and Scalable Multidimensional Model," discusses the Analysis Services multidimensional model.
Chapter 9, "Discovering Knowledge with Data Mining," which is the final chapter in the analysis section, explains Analysis Services data mining. You will learn about the different mining structures and models available within the tool and the best practices for populating the datasets. Finally, you will learn how to integrate your results from the mining into the rest of the business intelligence solution.
Part III, Business Intelligence for Reporting, discusses how to use the different Microsoft business intelligence tools for reporting. Chapter 10, "Choosing the Right Business Intelligence Visualization Tool," opens this section by discussing which reporting visualization tool you should pick. You will learn a little about each of the Microsoft reporting tools, and then learn how to complete an evaluation matrix and process to pick the right tool for your organization.
Chapters 11 to 14 conclude the business intelligence for reporting section by covering each of the reporting tools in the Microsoft business intelligence stack. Chapter 11, "Designing Operational Reports with Reporting Services," teaches you about Reporting Services, specifically how to design operational reports and the best practices associated with using this tool. Chapter 12, "Visualizing Your Data Interactively with Power View," moves onto this newer reporting tool. You will learn what the requirements are to create a report in Power View and then walk through the report creation. Power Map is the focus of Chapter 13, "Exploring Geographic and Temporal Data with Power Map," which discusses how to display your geographical and temporal data. Finally, Chapter 14, "Monitoring Your Business with PerformancePoint Services," introduces you to PerformancePoint Services within SharePoint and how you can include this tool in your business intelligence solution.
Part IV, Deploying and Managing the Business Intelligence Solution, wraps up the book by covering how to administer and maintain the business intelligence solution that you have created from earlier chapters in this book. Chapter 15, "Implementing a Self-Service Delivery Framework," kicks off this section by describing data governance within the Microsoft business intelligence framework, and includes a discussion about the Data Quality Services and Master Data Services tools.
Chapter 16, "Designing and Implementing a Deployment Plan," discusses deployment plans for business intelligence solutions. You learn about ways to deploy the corporate business intelligence tools and the best way to document the plan.
Next, in Chapter 17, "Managing and Maintaining the Business Intelligence Environment," you learn how to keep your business intelligence up and running once deployed. You will learn how to monitor your solution to ensure that it performs well and any changes you need to make to keep it running.
Finally, Chapter 18, "Scaling the Business Intelligence Environment," covers how to handle performance issues by scaling the business intelligence tools. You learn how to scale up and scale out each of the tools.
Who Should Read This Book
This book is intended for business intelligence developers and architects, and those who are interested in learning more about the Microsoft business intelligence suite. If you need to create reports for your day-to-day operational work, design business-friendly analytics models for end users, or perform advanced analysis to make big business decisions, this book is for you.
It is assumed that you have some basic programming or SQL knowledge before picking up this book. You should understand query constructs and basic programming principles. You don't need experience with any of the business intelligence tools discussed here, but if you do have some experience, there is still quite a bit to learn!
If you are new to Microsoft's business intelligence tools, you would be best served by reading this book from start to finish. However, if you have some background with the business intelligence layout and need to learn about analysis versus reporting, you may want to look at just Part II or Part III, respectively. Finally, if you already have a business intelligence solution, but need to ensure that it is being managed properly, turn to the final section.
Tools You Will Need
This book is based on the SQL Server 2014 business intelligence tools, Excel 2013, and the November 2014 edition of the cloud-based software. All examples use the AdventureWorks...
