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