Microsoft Business Intelligence For Dummies

Ken Withee is a Microsoft SharePoint and Business Intelligence consultant and a Microsoft Certified Technology Specialist. He is certified in SharePoint, SQL Server, and .NET. Among his many published works are a book on SSRS 2008 and a featured article on Self-Serve Business Intelligence in The Architecture Journal.

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