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.