Applied Microsoft Business Intelligence

Leverage the power of SQL and Office to build a complete enterprise BI solution

The business intelligence world is changing. Hardware is getting more powerful, platforms are getting more capable, and decision timeframes are getting shorter. Microsoft's SQL Server and Office products provide powerful core BI technologies that allow decision makers who rely on Office for basic analytics to tap into a sophisticated BI toolset for both analysis and reporting. Applied Microsoft Business Intelligence reveals the best practices for building complete BI solutions using SQL Server, Reporting, and Analysis Services along with Excel and SharePoint for a more robust business intelligence framework.

This book shows you how to use the Microsoft business intelligence building blocks to construct synergetic and complete solutions to suit any organization. Organized chronologically by implementation order, it guides you through the data layer, data transformation and quality, the semantic layer, and the presentation layer, and ties it all together with comprehensive case studies. Focusing on best practices rather than specific tools keeps your skills relevant beyond the 2014 SQL release. Comprehensive explanations including architecture, strengths and weaknesses, and practical applications bring you fully up to speed quickly.

Applied Microsoft Business Intelligence shows you how to:

• Design an effective BI architecture that best fits your organization
• Develop flexible, scalable, tabular and multi-dimensional models
• Create interactive visualizations with Power View
• Explore geographic and temporal data with Power Map
• Implement self-service delivery and an efficient deployment strategy
• Manage, maintain, and scale the BI environment

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

Patrick LeBlanc is a Microsoft SQL Server and Business Intelligence Technical Solution Professional. He holds a Masters of Science from Louisiana State University and has authored four SQL Server books.

Jessica M. Moss, a Microsoft SQL Server MVP, is a well-known practitioner, author, and speaker in Microsoft SQL Server business intelligence. She has created numerous data warehousing solutions for companies in the retail, internet, health services, finance, and energy industries.

Dejan Sarka, MCT and SQL Server MVP, focuses on development of database and business intelligence applications. He is the founder of the Slovenian SQL Server and .NET Users Group.

Dustin Ryan, a Senior Business Intelligence Consultant and Trainer at Pragmatic Works, is a blogger, speaker, and author in the Microsoft SQL Server Business Intelligence field. He has developed enterprise business intelligence solutions and provided training for customers in the retail, finance, transportation, healthcare, energy, and manufacturing industries.