Fill the gap between planning and doing with SSIS 2014 The 2014 release of Microsoft's SQL Server Integration Services provides enhancements for managing extraction, transformation, and load operations, plus expanded in-memory capabilities, i...
An in-depth look at the radical changes to the newest release of SISS Microsoft SQL Server 2012 Integration Services (SISS) builds on the revolutionary database product suite first introduced in 2005. With this crucial resource, you will explore h...
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.
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 Whos 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 Perspec