Dejan Sarka is a mentor with SolidQ and focuses on development of database and business intelligence applications. He is a frequent speaker at international conferences such as TechEd, SqlDevCon, and PASS. He is the founder of the Slovenian SQL Server and .NET Users Group. As main author or coauthor, Dejan has written nine books about SQL Server. He has also developed three courses for SolidQ: Data Modeling Essentials, Data Quality and Master Data Management, and Data Mining.Grega Jerkic is an independent consultant and trainer for SolidQ. For the last 12 years, he has been developing, architecting, and managing projects focusing on data warehousing, MDM, data integration, analytical/planning solutions and predictive analytics--primarily using Microsoft technology. He invented and was lead architect for a predefined business intelligence solution on top of ERP Microsoft Dynamics NAV BI4Dynamics, which is now used worldwide by more than 150 clients and has earned two Microsoft awards for best business intelligence solution for the CEE region. Grega currently provides training and mentoring for the Microsoft BI platform around the world (Microsoft FastTrack DW workshops, Enterprise ETL with SQL 2005/2008, MDX, Advanced PowerPivot, Microsoft SQL 2008 R2 technical workshops, etc).

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

Leverage the integration of SQL Server and Office for more effective BIApplied Microsoft Business Intelligence shows you how to leverage the complete set of Microsoft tools--including Microsoft Office and SQL Server--to better analyze business data.This book provides best practices for building complete BI solutions using the full Microsoft toolset. You will learn how to effectively use SQL Server Analysis and Reporting Services, along with Excel, SharePoint, and other tools to provide effective and cohesive solutions for the enterprise. Coverage includes BI architecture, data queries, semantic models, multidimensional modeling, data analysis and visualization, performance monitoring, data mining, and more, to help you learn to perform practical business analysis and reporting. Written by an author team that includes a key member of the BI product team at Microsoft, this useful reference provides expert instruction for more effective use of the Microsoft BI toolset.* Use Microsoft BI suite cohesively for more effective enterprise solutions* Search, analyze, and visualize data more efficiently and completely* Develop flexible and scalable tabular and multidimensional modelsMonitor performance, build a BI portal, and deploy and manage the BI Solution