Microsoft Business Intelligence Tools for Excel Analysts
eBook - ePub

Microsoft Business Intelligence Tools for Excel Analysts

Michael Alexander, Jared Decker, Bernard Wehbe

Partager le livre
  1. English
  2. ePUB (adapté aux mobiles)
  3. Disponible sur iOS et Android
eBook - ePub

Microsoft Business Intelligence Tools for Excel Analysts

Michael Alexander, Jared Decker, Bernard Wehbe

DĂ©tails du livre
Aperçu du livre
Table des matiĂšres
Citations

À propos de ce livre

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.

Foire aux questions

Comment puis-je résilier mon abonnement ?
Il vous suffit de vous rendre dans la section compte dans paramĂštres et de cliquer sur « RĂ©silier l’abonnement ». C’est aussi simple que cela ! Une fois que vous aurez rĂ©siliĂ© votre abonnement, il restera actif pour le reste de la pĂ©riode pour laquelle vous avez payĂ©. DĂ©couvrez-en plus ici.
Puis-je / comment puis-je télécharger des livres ?
Pour le moment, tous nos livres en format ePub adaptĂ©s aux mobiles peuvent ĂȘtre tĂ©lĂ©chargĂ©s via l’application. La plupart de nos PDF sont Ă©galement disponibles en tĂ©lĂ©chargement et les autres seront tĂ©lĂ©chargeables trĂšs prochainement. DĂ©couvrez-en plus ici.
Quelle est la différence entre les formules tarifaires ?
Les deux abonnements vous donnent un accĂšs complet Ă  la bibliothĂšque et Ă  toutes les fonctionnalitĂ©s de Perlego. Les seules diffĂ©rences sont les tarifs ainsi que la pĂ©riode d’abonnement : avec l’abonnement annuel, vous Ă©conomiserez environ 30 % par rapport Ă  12 mois d’abonnement mensuel.
Qu’est-ce que Perlego ?
Nous sommes un service d’abonnement Ă  des ouvrages universitaires en ligne, oĂč vous pouvez accĂ©der Ă  toute une bibliothĂšque pour un prix infĂ©rieur Ă  celui d’un seul livre par mois. Avec plus d’un million de livres sur plus de 1 000 sujets, nous avons ce qu’il vous faut ! DĂ©couvrez-en plus ici.
Prenez-vous en charge la synthÚse vocale ?
Recherchez le symbole Écouter sur votre prochain livre pour voir si vous pouvez l’écouter. L’outil Écouter lit le texte Ă  haute voix pour vous, en surlignant le passage qui est en cours de lecture. Vous pouvez le mettre sur pause, l’accĂ©lĂ©rer ou le ralentir. DĂ©couvrez-en plus ici.
Est-ce que Microsoft Business Intelligence Tools for Excel Analysts est un PDF/ePUB en ligne ?
Oui, vous pouvez accĂ©der Ă  Microsoft Business Intelligence Tools for Excel Analysts par Michael Alexander, Jared Decker, Bernard Wehbe en format PDF et/ou ePUB ainsi qu’à d’autres livres populaires dans Computer Science et Desktop Applications. Nous disposons de plus d’un million d’ouvrages Ă  dĂ©couvrir dans notre catalogue.

Informations

Éditeur
Wiley
Année
2014
ISBN
9781118821565
Édition
1

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 des matiĂšres