Leverage the integration of SQL Server and Office for more
effective BI

Applied 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
models

Monitor performance, build a BI portal, and deploy and manage
the BI Solution

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.

Zusammenfassung
Leverage the integration of SQL Server and Office for more effective BI

Applied Microsoft Business Intelligence shows you how to leverage the complete set of Microsoft toolsincluding Microsoft Office and SQL Serverto 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 models

Monitor performance, build a BI portal, and deploy and manage the BI Solution

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...