Applied Microsoft Business Intelligence

Introduction xiii
Part I Overview of the Microsoft Business Intelligence Toolset 1
Chapter 1 Which Analysis and Reporting Tools Do You Need? 3
Selecting a SQL Server Database Engine 4
Building a Data Warehouse 4
Selecting an RDBMS 5
Selecting SQL Server Analysis Services 6
Working with SQL Server Reporting Services 7
Understanding Operational Reports 8
Understanding Ad Hoc Reporting 10
Working with SharePoint 11
Working with Performance Point 12
Using Excel for Business Intelligence 14
What Is Power Query? 14
What Is Power Pivot? 14
What Is Power View? 14
Power Map 15
Which Development Tools Do You Need? 16
Using SQL Server Data Tools 16
Using SQL Management Studio 17
Using Dashboard Designer 18
Using Report Builder 19
Summary 20
Chapter 2 Designing an Eff ective Business Intelligence Architecture 21
Identifying the Audience and Goal of the Business Intelligence Solution 21
Who's the Audience? 22
What Is the Goal(s)? 23
What Are the Data Sources? 23
Using Internal Data Sources 23
Using External Data Sources 24
Using a Data Warehouse (or Not) 24
Implementing and Enforcing Data Governance 26
Planning an Analytical Model 28
Planning the Business Intelligence Delivery Solution 29
Considering Performance 30
Considering Availability 31
Summary 32
Chapter 3 Selecting the Data Architecture that Fits Your Organization 33
Why Is Data Architecture Selection Important? 34
Challenges 34
Benefits 35
How Do You Pick the Right Data Architecture? 36
Understanding Architecture Options 36
Understanding Research Selection Factors 42
Interviewing Key Stakeholders 44
Completing the Selection Form 45
Finalizing and Approving the Architecture 46
Summary 48
Part II Business Intelligence for Analysis 49
Chapter 4 Searching and Combining Data with Power Query 51
Downloading and Installing Power Query 52
Importing Data 56
Importing from a Database 57
Importing from the Web 59
Importing from a File 61
Transforming Data 62
Combining Data from Multiple Sources 62
Splitting Data 64
Aggregating Data 66
Introducing M Programming 70
A Glance at the M Language 70
Adding and Removing Columns Using M 72
Summary 72
Chapter 5 Choosing the Right Business Intelligence Semantic Model 73
Understanding the Business Intelligence Semantic Model Architecture 74
Understanding the Data Access Layer 75
Using Power Pivot 77
Using the Multidimensional Model 78
Using the Tabular Model 78
Implementing Query Languages and the Business Logic Layer 79
Data Analytics Expressions (DAX) 79
Multidimensional Expressions (MDX) 81
Direct Query and ROLAP 81
Data Model Layer 82
Comparing the Different Types of Models 83
Which Model Fits Your Organization? 84
Departmental 84
Team 86
Organizational 87
Summary 88
Chapter 6 Discovering and Analyzing Data with Power Pivot 89
Understanding Hardware and Software Requirements 90
Enabling Power Pivot 90
Designing an Optimal Power Pivot Model 92
Importing Only What You Need 92
Understanding Why Data Types Matter 99
Working with Columns or DAX Calculated Measures 103
Optimizing the Power Pivot Model for Reporting 104
Understanding Power Pivot Model Basics 104
Adding All Necessary Relationships 107
Adding Calculated Columns and DAX Measures 114
Creating Hierarchies and Key Performance Indicators (KPIs) 118
Sorting Your Data to Meet End-User Needs 121
Implementing Role-Playing Dimensions 122
Summary 125
Chapter 7 Developing a Flexible and Scalable Tabular Model 127
Why Use a Tabular Model? 127
Understanding the Tabular Model 128
Using the Tabular Model 128
Comparing the Tabular and Multidimensional Models 130
Understanding the Tabular Development Process 130
How Do You Design the Model? 131
Importing Data 131
Designing Relationships 134
Calculated Columns and Measures 135
How Do You Enhance the Model? 137
Adding Hierarchies 137
Designing Perspectives 140
Adding Partitions 141
How Do You Tune the Model? 144
Optimizing Processing 144
Optimizing Querying 147
Summary 149
Chapter 8 Developing a Flexible and Scalable Multidimensional Model 151
Why Use a Multidimensional Model? 151
Understanding the Multidimensional Model 152
Understanding the Multidimensional Model Process 153
How Do You Design the Model? 153
Creating Data Sources and the Data Source View 153
Using the Cube Creation Wizard 156
Adjusting Measures 159
Completing Dimensions 160
How Do You Enhance the Model? 162
Adding Navigation with Hierarchies 162
Using the Business Intelligence Wizard for Calculations 164
Using Partitions and Aggregations 166
How Do You Tune the Model? 169
Resolving Processing Issues 169
Querying 171
Summary 172
Chapter 9 Discovering Knowledge with Data Mining 173
Understanding the Business Value of Data Mining 174
Understanding Data Mining Techniques 174
Common Business Use Cases 175
Driving Decisions, Strategies, and Processes Through Data Mining 176
Getting the Basics Right 179
Understanding the Data 180
Training and Test Datasets 182
Defining the Data Mining Structure 184
The Data Mining Model 184
Applying the Microsoft Data Mining Techniques with Best Practices 185
Using Microsoft Association Rules 186
Grouping Data with Microsoft Clustering 190
Building Mining Models with Microsoft Naïve Bayes 192
Using the Microsoft Decision Trees 193
Using Microsoft Neural Network and Microsoft Logistic Regression 195
Using Microsoft Linear Regression and Microsoft Regression Trees 197
Microsoft Sequence Clustering 199
Forecasting with Microsoft Time Series 200
Developing and Deploying a Scalable and Extensible Data Mining Solution 201
Choosing Between a Relational or a Cube Source for Your Data Mining Structure 202
Deploying Data Mining Models 202
Using DMX to Query Data Mining Models 204
Maintaining Data Mining Models 205
Fine-Tuning the Data Mining Structure 205
Keeping the Data Model Relevant 205
Continuous Learning Cycle 205
Integrating Data Mining with Your BI Solution 206
Integrating Data Mining in Your DW and ETL Processes 206
Integrating Data Mining with Reporting Services 207
Data Mining in Excel 207
Summary 208
Part III Business Intelligence for Reporting 209
Chapter 10 Choosing the Right Business Intelligence Visualization Tool 211
Why Do You Need to Choose? 211
Identifying Users 212
Selecting Tools 213
What Are the Selection Criteria? 213
Business Capabilities 214
Technical Capabilities 214
How Do You Gather the Necessary Information? 215
What Are the Business Intelligence Visualization Options? 215
Using SQL Server Reporting Services 215
Using Power View 218
Using Power Map 219
How Do You Create and Complete the Evaluation Matrix? 221
How Do You Verify and Complete the Process? 223
Evaluation Matrix #1 224
Evaluation Matrix #2 224
Summary 225
Chapter 11 Designing Operational Reports with Reporting Services 227
What Are Operational Reports and Reporting Services? 227
Understanding Analytical versus Operational Reports 228
Using Reporting Services 228
What Are Development Best Practices? 230
Using Source and Version Control 231
Using Shared Data Sources and Datasets 234
Creating Templates 236
What Are Performance Best Practices? 237
Investigating Performance 237
Performance Tuning 238
What Are Functionality Best Practices? 239
Using Visualizations 239
Using Filters and Parameters 240
Providing Drilldown and Drillthrough 241
Summary 244
Chapter 12 Visualizing Your Data Interactively with Power View 245
Where Does Power View Fit with Your Reporting Solution? 246
Power View System Requirements 246
Creating Power View Data Source Connections 247
Creating Data Sources Inside Excel 247
Creating Data Sources Inside SharePoint 249
Creating Power View Reports 251
Using SharePoint to Create Power View Reports 251
Using Multiple Views in Power View 252
Creating Power View Visualizations 253
Creating Tables 253
Converting Visualizations 254
Creating Matrices 255
Creating Charts 256
Creating Multiples 261
Creating Cards 261
Creating Maps 262
Using Excel to Create Power View Reports 263
Filtering Data with Power View 264
Adding Filters 264
Using Advanced Filters 266
Adding Slicers 266
Invoking Cross-Filters 267
Adding Tiles 268
Adding Filters to a Report URL 270
Exporting Power View Reports 271
Summary 272
Chapter 13 Exploring Geographic and Temporal Data with Power Map 273
How Power Map Fits into Reporting Solutions 274
Understanding Power Map Features and Advantages 274
Comparing Power Map to Other SQL Server Geospatial Reporting Tools 275
Understanding Power Map Requirements 279
Optimizing Your Data Model for Power Map 280
Using Tours, Scenes, and Layers in Power Map 280
Defining Geography Fields in Your Data Model 282
Defining Date and Time Fields in Your Data Model 283
Working with Geospatial and Temporal Data 284
Visualizing Data Aggregation 284
Creating a Power Map Tour 285
Visualizing Data Over Time with Rich Animations 288
Deploying and Sharing Power Map Visualizations 290
Sharing Power Map Tours 291
Enhancing Power Map Deployment and Configurations in Office 365 291
Summary 292
Chapter 14 Monitoring Your Business with PerformancePoint Services 293
Where Does PerformancePoint Services Fit with Your Reporting Solution? 294
Understanding PPS Features 295
When Is PPS the Right Choice? 298
Implementing PPS Requirements for SharePoint 300
Extending PPS Dashboards 301
Adding PerformancePoint Time Intelligence 301
Using Interactivity Features 304
Adding Reporting Services Reports to PerformancePoint 311
Extending Filters and KPIs 313
Deployment Best Practices 317
Following Best Practices for PerformancePoint Data Connections and Content Libraries 317
Deploying Dashboards Across Dev, Test, and Production Environments 319
Customizing PerformancePoint SharePoint Web Parts 321
Security and Configuration Best Practices 325
Configuring the Unattended Service Account in SharePoint 325
Optimizing PerformancePoint Services Application Settings 326
Summary 328
Part IV Deploying and Managing the Business Intelligence Solution 329
Chapter 15 Implementing a Self-Service Delivery Framework 331
Planning a Self-Service Delivery Framework 331
Creating a Data Governance Plan for Enterprise, Team, and Personal BI 332
Identifying Stakeholders, Subject Matter Experts, and Data Stewards 334
Understanding Industry Compliance Considerations 334
Managing Data Quality and Master Data 337
Identifying Target Audience and Roles 339
Developing a Training Plan 340
Inventorying Tools and Skillset 340
Understanding Data Quality Services 340
Understanding Master Data Services 342
Managing Data Quality and Master Data in Excel 345
Business Intelligence Features Across the Microsoft Data Platform Versions and Editions 347
Defining Success Criteria 348
Summary 349
Chapter 16 Designing and Implementing a Deployment Plan 351
What Is a Deployment Plan? 351
How Do You Deploy Business Intelligence Code? 353
Using Analysis Services (Multidimensional or Tabular) 354
Using Reporting Services 357
How Do You Implement the Deployment Plan? 359
Planning the Deployment 359
Designing Scripts 360
Documenting Steps 360
Testing the Plan 361
Training Your Staff 362
Summary 362
Chapter 17 Managing and Maintaining the Business Intelligence Environment 363
Using SQL Server Reporting Services 363
Configuring Memory 365
Caching Data and Pre-Rendering Reports 368
Using ExecutionLog Views 369
Working with SQL Server Analysis Services 372
Using Multidimensional Models 372
Using Tabular Models 374
Using SharePoint to Improve Performance 375
Summary 378
Chapter 18 Scaling the Business Intelligence Environment 379
Why Would You Scale the Business Intelligence Environment? 379
How Do You Scale Each Tool? 381
Using Analysis Services (Multidimensional or Tabular) 381
Reporting Services 385
Using Power Pivot and Power View 387
Summary 390
Index 391