Microsoft Business Intelligence Tools for Excel Analysts

Michael Alexander, Jared Decker, Bernard Wehbe

• Publisher: Wiley
• Year: 2014
• Print ISBN: 9781118821527
• eBook ISBN: 9781118821565
• Edition: 1
• Topic: Computer Science
• Subtopic: Desktop Applications
• Index: Computer Science

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.

PART I: Leveraging Excel for Business Intelligence

Chapter 1: Important Database Concepts

Chapter 2: PivotTable Fundamentals

Chapter 3: Introduction to Power Pivot

Chapter 4: Loading External Data into Power Pivot

Chapter 5: Creating Dashboards with Power View

Chapter 6: Adding Location Intelligence with Power Map

Chapter 7: Using the Power Query Add-In

Chapter 1: Important Database Concepts

In This Chapter

• Using a database to get past Excel limitations
• Getting familiar with database terminology
• Understanding relational databases
• How databases are designed

Although Excel is traditionally considered the premier tool for data analysis and reporting, it has some inherent characteristics that often lead to issues revolving around scalability, transparency of analytic processes, and confusion between data and presentation. Over the last several years, Microsoft has recognized this and created tools that allow you to develop reporting and business intelligence by connecting to various external databases. Microsoft has gone a step further with Excel 2013, offering business intelligence (BI) tools like Power Pivot natively; it effectively allows you to build robust relational data models within Excel.

With the introduction of these BI tools, it’s becoming increasingly important for you to understand core database fundamentals. Unlike traditional Excel concepts, where the approach to developing solutions is relatively intuitive, good database-driven development requires a bit of prior knowledge. There are a handful of fundamentals you should know before jumping into the BI tools. These include database terminology, basic database concepts, and database best practices.

The topics covered in this chapter explain the concepts and techniques necessary to successfully use database environments and give you the skills needed to normalize data and plan and implement effective tables.

If you’re already familiar with the concepts involved in database design, you may want to skim this chapter. If you’re new to the world of databases, spend some time in this chapter gaining a thorough understanding of these important topics.

Traditional Limits of Excel and How Databases Help

Managers, accountants, and analysts have had to accept one simple fact over the years: Their analytical needs had outgrown Excel. They all met with fundamental issues that stemmed from one or more of Excel’s three problem areas: scalability, transparency of analytical processes, and separation of data and presentation.

Scalability

Scalability is the ability for an application to develop flexibly to meet growth and complexity requirements. In the context of Excel, scalability refers to Excel’s ability to handle ever-increasing volumes of data. Most Excel aficionados are quick to point out that as of Excel 2007, you can place 1,048,576 rows of data into a single Excel worksheet. This is an overwhelming increase from the limitation of 65,536 rows imposed by previous versions of Excel. However, this increase in capacity does not solve all of the scalability issues that inundate Excel.

Imagine that you're working in a small company and using Excel to analyze your daily transactions. As time goes on, you build a robust process complete with all the formulas, PivotTables, and macros you need to analyze the data that is stored in your neatly maintained worksheet.

As your data grows, you start to notice performance issues. Your spreadsheet becomes slow to load and then slow to calculate. Why does this happen? It has to do with the way Excel handles memory. When an Excel file is loaded, the entire file is loaded into RAM. Excel does this to allow for quick data processing and access. The drawback to this behavior is that each time something changes in your spreadsheet, Excel has to reload the entire spreadsheet into RAM. A large spreadsheet takes a great deal of RAM to process even the smallest change. Eventually, each action you take in your gigantic worksheet will result in an excruciating wait.

Your PivotTables will require bigger pivot caches (memory containers), almost doubling your Excel workbook’s file size. Eventually, your workbook will become too big to distribute easily. You may even consider breaking down the workbook into smaller workbooks (possibly one for each region). This causes you to duplicate your work.

In time, you may eventually reach the 1,048,576-row limit of your worksheet. What happens then? Do you start a new worksheet? How do you analyze two datasets on two different worksheets as one entity? Are your formulas still good? Will you have to write new macros?

These are all issues that need to be dealt with.

You can find various clever ways to work around these limitations. In the end, though, they are just workarounds. Eventually you will begin to think less about the most effective way to perform and present analysis of your data and more about how to make something “fit” into Excel without breaking your formulas and functions. Excel is flexible enough that you can make most things “fit” into Excel just fine. However, when you think only in terms of Excel, you’re limiting yourself, albeit in an incredibly functional way.

In addition, these capacity limitations often force you to have the data prepare...

Table of contents

• Cover
• Title Page
• Table of Contents
• Introduction
• Part I: Leveraging Excel for Business Intelligence
• Part II: Leveraging SQL for Business Intelligence
• Part III: Delivering Business Intelligence with SharePoint and Excel Services
• Part IV: Appendixes
• End User License Agreement