Synopses & Reviews
In the past, Business Intelligence had a tangible line separating the departmental reporting performed by business analysts and the enterprise reporting managed by IT professionals. On the business side of the organization, Microsoft Excel is heavily relied upon by business analysts to provide the mechanism for operational data analysis, reporting and dashboarding. Because of its easy availability, its array of analytical tools, and its presentation and graphing strengths, Excel is a natural choice for business analysts who need to report and manipulate data. On the other side of the organization, IT departments use Microsoft SQL Server and SharePoint to store and manage data in varying capacities. The types of data being handled by SQL server range from enterprise-level transactional data, to departmental data marts. Traditionally SQL Server tools like Reporting Services and Analysis Services rarely touch the Excel business analyst. That paradigm has shifted and the latest versions of Office, SQL Server and SharePoint have changed the Business Intelligence world. Tools like PowerPivot and Power View have blurred the line between what is a departmental Excel tool and what is an enterprise-level SQL Server/SharePoint tool. It’s becoming increasingly important for business analysts to expand their skill-set
from the one dimensional spreadsheet to new territory like relational databases, data integration, and multidimensional reporting.
This book is aimed squarely at business analysts and managers who find it increasingly necessary to become
more efficient at working with “big data” tools traditionally reserved for IT professionals. This book is written from a business analyst’s point of view, guiding readers through the mysterious world of PowerPivot, SQL Server and
SharePoint reporting in terms they understand. Readers are introduced to the rich set of tools and reporting capabilities that can be leveraged to more effectively source and incorporate “Big Data” Business Intelligence and
Dashboard reports. Not only can these tools allow Excel analysts to save time and simplify their processes, they will also enable them to substantially enhance their data analysis and reporting capabilities.
The book covers:
Moving data between Excel and SQL Server
Leveraging relational databases
Using SQL Server’s built-in functions to analyze large amounts of data
Using Excel pivot tables to access and report OLAP data
Using PowerPivot to create powerful reporting mechanisms
Easily slicing data into various views to gain different visibility perspectives
Creating eye-catching visualizations and dashboards
Automating SQL Server data retrieval and integration
Publishing dashboards and reports to the web
Bridge the big data gap with Microsoft Business Intelligence Tools for Excel Analysts
The distinction between departmental reporting done by business analysts with Excel and the enterprise reporting done by IT departments with SQL Server and SharePoint tools is more blurry now than ever before. With the introduction of robust new features like PowerPivot and Power View, it is essential for business analysts to get up to speed with big data tools that in the past have been reserved for IT professionals. Written by a team of Business Intelligence experts, Microsoft Business Intelligence Tools for Excel Analysts introduces business analysts to the rich toolset and reporting capabilities that can be leveraged to more effectively source and incorporate large datasets in their analytics while saving them time and simplifying the reporting process.
- Walks you step-by-step through important BI tools like PowerPivot, SQL Server, and SharePoint and shows you how to move data back and forth between these tools and Excel
- Shows you how to leverage relational databases, slice data into various views to gain different visibility perspectives, create eye-catching visualizations and dashboards, automate SQL Server data retrieval and integration, and publish dashboards and reports to the web
- Details how you can use SQL Server’s built-in functions to analyze large amounts of data, Excel pivot tables to access and report OLAP data, and PowerPivot to create powerful reporting mechanisms
You’ll get on top of the Microsoft BI stack and all it can do to enhance Excel data analysis with this one-of-a-kind guide written for Excel analysts just like you.
Self-Service Business Intelligence with Excel
For the first time, Excel is an integral part of the Microsoft BI stack - capable of integrating multiple data sources, defining relationships between data sources, processing analysis services cubes, and developing interactive dashboards that can be shared on the web. With these new tools, it’s becoming important for Excel analysts to expand their knowledge to include new skills, like database management, query design, data integration, multidimensional reporting, and a host of other practices.
This book is aimed squarely at business analysts and managers who find it increasingly necessary to become more efficient at working with the new Microsoft BI tools like Power Pivot, Power Query, and Power View.
Let Mr. Spreadsheet show you how to:
- Use PowerPivot to create powerful reporting mechanisms
- Automate data integration with Power Query
- Create geo-spatial reporting with Power Map
- Develop eye-catching Dashboards with Power View
- Use SQL Server® to leverage relational and OLAP databases
- Gain insight and analytical power with Data Mining tools
About the Author
is a Microsoft Certified Application Developer (MCAD) and author of several books on advanced business analysis with Microsoft Access and Excel.
Jared Decker is the co-founder of StatSlice Systems and a certified BI developer with more than 14 years’ experience training and developing enterprise reporting solutions.
Bernard Wehbe a is a veteran BI consultant and co-founder of StatSlice Systems where he helps organizations implement business analytics and data visualization solutions.
John Walkenbach is arguably the foremost authority on Excel. He has written more than 30 books and maintains the popular Spreadsheet Page at www.j-walk.com/ss.
Table of Contents
Part I: Leveraging Excel for Business Intelligence
Chapter 1: Important Database Concepts 7
Chapter 2: PivotTable Fundamentals 19
Chapter 3: Introduction to Power Pivot 47
Chapter 4: Loading External Data into Power Pivot 69
Chapter 5: Creating Dashboards with Power View 93
Chapter 6: Adding Location Intelligence with Power Map 107
Chapter 7: Using the Power Query Add-In 129
Part II: Leveraging SQL for Business Intelligence
Chapter 8: Essential SQL Server Concepts 157
Chapter 9: Introduction to SQL 181
Chapter 10: Creating and Managing SQL Scripts 195
Chapter 11: Calling Views and Stored Procedures from Excel 215
Chapter 12: Understanding Reporting Services 231
Chapter 13: Browsing Analysis Services OLAP Cubes with Excel 249
Chapter 14: Using the Data Mining Add-In for Microsoft Office 269
Part III: Delivering Business Intelligence with SharePoint and Excel Services
Chapter 15: Publishing Your BI Tools to SharePoint 293
Chapter 16: Leveraging PerformancePoint Services 307
Part IV: Appendixes
Appendix A: Understanding the Big Data Toolset 331
Appendix B: Considerations for Delivering Mobile BI 341