Learn to create an effective business strategy using Microsoft's BI stack
Microsoft Business Intelligence tools are among the most widely used applications for gathering, providing access to, and analyzing data to enable the enterprise to make sound business decisions. The tools include SharePoint Server, the Office Suite, PerformancePoint Server, and SQL Server, among others.
With so much jargon and so many technologies involved, Microsoft Business Intelligence For Dummies provides a much-needed step-by-step explanation of what's involved and how to use this powerful package to improve your business.
Microsoft Business Intelligence For Dummies demystifies these essential tools for enterprise managers, business analysts, and others who need to get up to speed.
Introduction 1
Part I: Embracing a Microsoft Business Intelligence Solution 7
Chapter 1: Surveying Microsoft Business Intelligence from 50,000 Feet 9
Chapter 2: Blazing a Trail through the Data Jungle 23
Chapter 3: Adopting Microsoft Business Intelligence 39
Part II: Wrapping Your Head Around Business Intelligence Concepts 57
Chapter 4: Using Data to Inform and Drive Business Activities 59
Chapter 5: Taking a Closer Look at Data Collection 77
Chapter 6: Turning Data into Information 99
Chapter 7: Data Mining for Information Gold 123
Part III: Introducing the Microsoft Business
Intelligence Technologies 145
Chapter 8: Meeting SQL Server 147
Chapter 9: Excel — Digital Data Power to the People 175
Chapter 10: SharePoint Shines 211
Chapter 11: Expressing Yourself with Development Tools 247
Part IV: Incorporating Microsoft Business Intelligence into Your Business Environment 273
Chapter 12: Setting Your BI Goals and Implementation Plan 275
Chapter 13: Evaluating and Choosing Technologies 297
Chapter 14: Testing and Rolling Out 315
Chapter 15: Training, Using, and Evaluating Results 335
Part V: The Part of Tens 353
Chapter 16: Ten Microsoft BI Implementation Pitfalls 355
Chapter 17: Ten Keys to Successful Microsoft Business Intelligence 363
Chapter 18: Ten Ways to Boost Your Bottom Line with Microsoft Business Intelligence 375
Glossary 383
Index 387
Table of Contents
Introduction 1
About This Book 2
How to Use This Book 2
How This Book Is Organized 3
Part I: Embracing a Microsoft Business Intelligence Solution 3
Part II: Wrapping Your Head Around Business Intelligence Concepts 3
Part III: Introducing the Microsoft Business Intelligence Technologies 4
Part IV: Incorporating Microsoft Business Intelligence into Your Business Environment 4
Part V: The Part of Tens 4
Icons Used In This Book 5
Let’s Get Started! 6
Part I: Embracing a Microsoft Business Intelligence Solution 7
Chapter 1: Surveying Microsoft Business Intelligence from 50,000 Feet 9
Introducing Microsoft Business Intelligence 9
Knowing the components of Microsoft BI 10
Tracing the terminology 11
Getting to the Core of Microsoft BI 12
Date warehousing and data marts 13
Reporting on data 13
Integrating data from many sources 14
Analyzing data 14
Data mining 15
Microsoft BI Data Presentation 15
Microsoft Office Excel 16
Microsoft Office Visio 16
Microsoft SharePoint 16
Microsoft BI Development Tools 18
Visual Studio 19
Report Builder 20
Silverlight 20
Microsoft NET 21
Microsoft Business Intelligence For Dummies xii
Chapter 2: Blazing a Trail through the Data Jungle 23
Exploring the Data Lifecycle 24
Data generation and collection 25
Data transformation and organization 29
Data visualization and reporting31
Data analysis 32
Data mining 33
Understanding How Microsoft BI Fits into the Data Lifecycle 34
Juggling Data 36
It’s a Flood of Data! Headed This Way! 37
Chapter 3: Adopting Microsoft Business Intelligence 39
Understanding the Adoption Process 40
Determining what to ask the BI genie 42
Investigating your current Microsoft product usage 43
Taking stock of your Microsoft knowledge 47
Saving your sanity with a prototype 48
Iterating the prototype to success 49
Documenting Your Key Business Processes 50
Understanding Where to Find Microsoft BI Guidance 51
Taking advantage of in-house expertise 51
Calling in the experts 51
Tracking down individual experts 53
Who you gonna call? Microsoft Support! 54
Other resources online and on paper 55
Part II: Wrapping Your Head Around Business Intelligence Concepts 57
Chapter 4: Using Data to Inform and Drive Business Activities 59
The Importance of Data in Making Business Decisions 60
Tracking down the relevant data 62
Getting the right data to the right person at the right time 63
BI and the risk of high-tech tunnel vision 65
Why All the Fuss about OLAP? 66
What is OLAP? 66
What makes OLAP so fast? 67
Why OLAP? 69
Databases and cubes 70
Measures and facts (of life) 74
Hierarchies of detail 75
Table of Contents xiii
Chapter 5: Taking a Closer Look at Data Collection 77
The King of BI Concepts — ETL 78
Extracting data 78
Transforming data 79
Loading data 81
SQL Server Integration Services (SSIS) — Microsoft’s ETL Tool 83
Tossing the packages into the projects 84
Connecting to data sources 85
SSIS Toolbox 86
Data transformations 88
Anything is possible with custom code 89
A Simple SSIS Walk-Through 89
Exploring Data Generation 95
Computers speed everything up 95
Enterprise Resource Planning (ERP) 96
Rise of the machines 97
Chapter 6: Turning Data into Information 99
Data Storage for BI 100
Data warehouse100
Data mart 106
Data-storage patterns 108
Models, schemas, and patterns 110
Understanding SQL Server Reporting Services (SSRS) 111
Business Intelligence Developer Studio (BIDS) 112
Report Builder 114
Getting Familiar with SharePoint 115
Excel Services 116
PerformancePoint Services for SharePoint 117
KPI lists119
Dashboards 119
Scorecards 120
Chapter 7: Data Mining for Information Gold 123
Going Deep with Data Mining 124
An algorithm defined 124
Data mining’s role in the BI process 126
Digging In to Data Mining in the Microsoft World 126
The Microsoft data-mining process 127
Data-mining structures 131
Data mining models 132
Knowing the Microsoft Data-Mining Tools 133
Integrating with Microsoft Office 133
Visual Studio 135
SQL Server Management Studio 139
Using Microsoft Data Mining Algorithms 140
Microsoft Business Intelligence For Dummies xiv
Part III: Introducing the Microsoft Business Intelligence Technologies 145
Chapter 8: Meeting SQL Server 147
First Contact with SQL Server 148
Primary Components of SQL Server 149
The SQL Server Database Engine 151
SQL Server Reporting Services 155
SQL Server Integration Services162
SQL Server Analysis Services 162
Looking at the Different Versions of SQL Server 163
Core editions 163
Specialized editions 164
Installing SQL Server 166
Checking Out SQL Server Tools 169
SQL Server Management Studio 170
Transact-SQL 172
MDX 173
Chapter 9: Excel — Digital Data Power to the People 175
Excel as a BI Application 176
Generating Data 178
Collecting Data 179
Getting Organized 181
Show Me the Data! — Data Visualization 183
Conditional formatting 184
Charts and graphs 189
Analyzing Data: Pivot on This and Pivot on That 191
Using Excel PivotTables 191
PivotChart 195
Data Mining with Excel 197
Using Excel to boss SSAS 197
Pulling cube data for PivotTables and PivotCharts 200
Keeping Score with the Excel Scorecard 205
Knowing the Limits of Excel 207
Looking at the Future of Excel 209
Chapter 10: SharePoint Shines 211
Getting to Know SharePoint 212
What exactly is SharePoint? 212
Understanding the versions and editions of SharePoint 216
Making BI Information Available in SharePoint 218
SSRS integration 219
Excel integration 220
InfoPath Form Services 226
Using Key Performance Indicators 227
Business Connectivity Services 228
Table of Contents xv
Unleashing Human Business Intelligence with SharePoint 229
SharePoint Web sites 230
Document libraries 231
SharePoint Lists 232
Wikis 234
Blogs 235
Discussion boards 235
Office integration 236
Learning What Was Added with SharePoint Server 2010 239
Cruising with the Navigation Ribbon 240
Providing a more fluid user experience 240
Developing applications with Silverlight 241
Integrating visualizations with PowerPoint themes 241
Visio Services 242
Sorting and filtering lists dynamically 243
Using Business Connectivity Services 243
Increasing efficiency with Office integration 243
Taking SharePoint offline with SharePoint Workspace 244
Chapter 11: Expressing Yourself with Development Tools 247
Taking a Look at Visual Studio 248
The Visual Studio interface 248
Flavors of Visual Studio 250
Visual Studio in the BI world 255
Examining the NET Framework 259
A language only a computer chip can love 259
Intermediate Language (IL) 260
The Common Language Runtime (CLR) 260
Exploring Report Builder 261
Diving In to SQL Server Management Studio 263
Getting to Know SharePoint Designer 264
Seeing the (Silver)light and Tasting Expression Blend 268
Understanding PerformancePoint 269
Part IV: Incorporating Microsoft Business Intelligence
into Your Business Environment 273
Chapter 12: Setting Your BI Goals and Implementation Plan 275
Setting Your Business Intelligence Goals 276
Understanding the components of business goals 276
Examining technology goals 279
Determining Your Implementation Plan 281
Comparing waterfall and iterative methodologies 281
Discovering how things really work 285
Identifying the power users 289
Solidifying the goals of the BI project 290
Microsoft Business Intelligence For Dummies xvi
Identifying the data needed to attain your goals 290
Setting a solid foundation for a BI implementation 291
Scope creep can be your friend 292
Chapter 13: Evaluating and Choosing Technologies 297
Assessing Your BI Capabilities 298
Identifying your current BI-friendly tools 298
Knowing your current licensing 303
Determining your current skill sets 303
Choosing Technologies to Incorporate 306
Understanding your business foundation 306
Putting together the BI technology puzzle 307
Plugging in the pieces 308
Utilizing Free BI Tools: Try Before You Buy 309
Trying SQL Server 311
Checking out SharePoint 312
Reducing Risk 313
Chapter 14: Testing and Rolling Out 315
Continuously Adding Value 316
Testing Your BI Implementation 316
BI testing diversity 317
Unit testing 320
Rolling It Out — Again and Again 323
Surfacing information 324
Having a BI Management Plan 327
Managing Change 328
Gaining early adoption 329
Transparency is crucial 330
Delegating ownership 331
Changing business processes 332
Introducing new technology without mutiny 333
Chapter 15: Training, Using, and Evaluating Results 335
Tackling Training Efforts 336
Continuous education 336
Enabling self-service training 336
SharePoint training resources 337
SQL Server training resources 340
Training users at the grassroots level 342
Evaluating Results 342
Getting feedback with SharePoint 343
Incorporating Feedback 349
Creating a BI Culture 349
Inclusion 350
Communication and collaboration 350
Ownership 350
Merit-based recognition 351
Trust 351
Table of Contents xvii
Part V: The Part of Tens 353
Chapter 16: Ten Microsoft BI Implementation Pitfalls 355
Drowning Under the Waterfall 356
Getting Stuck on the Shelf(-ware) 357
Letting Politics Kill the BI Project 358
Ignoring IT 358
Disregarding Power Users 359
Snubbing Business Processes 360
Overpromising Results 360
Getting Squashed by Top-Down Decree 361
Skimping on the Foundation 361
Misjudging How to Use Consultants 362
Chapter 17: Ten Keys to Successful Microsoft Business Intelligence 363
Reiterating an Iterative Approach 364
Obtaining Executive-Level Sponsorship 365
Assessing Your Current Environment 366
Developing an Implementation Plan 367
Choosing the Right People for the Implementation Team 368
Your in-house team members 368
Calling in consultants 368
Creating an Inclusive Environment 369
Fostering a Culture of Communication and Collaboration 370
Starting with the Right Goals 371
Reducing Risk 371
Maintaining Perspective 372
Chapter 18: Ten Ways to Boost Your Bottom Line with Microsoft Business Intelligence 375
Increasing Efficiency 376
Improving Agility 377
Increasing the Visibility of Business Processes 378
Forecasting 378
Taking Advantage of Existing Skill Sets 379
Collaborating and Communicating 380
Reusing Code in Various Functional Areas 380
Consolidating Content 381
Increasing Productivity 381
Making Deep Use of SQL Server and SharePoint 382
Glossary 383
Index 387
Microsoft Business Intelligence For Dummies xvii
Overview
Learn to create an effective business strategy using Microsoft's BI stack
Microsoft Business Intelligence tools are among the most widely used applications for gathering, providing access to, and analyzing data to enable the enterprise to make sound business decisions. The tools include SharePoint Server, the Office Suite, PerformancePoint Server, and SQL Server, among others.
With so much jargon and so many technologies involved, Microsoft ...