Skip to main content
Disciplines
Automotive Business IT + Informatics Construction + Real Estate Electrical Engineering + Electronics Energy + Sustainability Insurance + Risk Finance + Banking Management + Leadership Marketing + Sales Mechanical Engineering + Materials
Events
DE
EN
Books
Journals
Topic Page
Marketing
Start single access now
Access for companies
EXTENDED SEARCH
Log in
Top

2014 | Book

Introducing Power Pivot Read chapter Read first chapter

Beginning Power BI with Excel 2013

Self-Service Business Intelligence Using Power Pivot, Power View, Power Query, and Power Map

Author: Dan Clark

Publisher: Apress

Part of: Springer Professional "Wirtschaft+Technik" , Springer Professional "Technik" , Springer Professional "Wirtschaft"

Login to get access
insite
SEARCH

About this book

Understanding your company’s data has never been easier than with Microsoft’s new Power BI package for Excel 2013. Consisting of four powerful tools—Power Pivot, Power View, Power Query and Power Maps—Power BI makes self-service business intelligence a reality for a wide range of users, bridging the traditional gap between Excel users, business analysts and IT experts and making it easier for everyone to work together to build the data models that can give you game-changing insights into your business.

Beginning Power BI with Excel 2013 guides you step by step through the process of analyzing and visualizing your data. Daniel R. Clark, an expert in BI training and a regular speaker on these topics, takes you through each tool in turn, using hands-on activities to consolidate what you’ve learned in each chapter.

Starting with Power Pivot, you will create robust scalable data models which will serve as the foundation of your data analysis. Once you have mastered creating suitable data models, you will use them to build compelling interactive visualizations in Power View. It’s often necessary to combine data from disparate sources into a data model. Power Query allows you to easily discover, combine, and refine data from a variety of sources, so you can make accurate judgments with all the available information. Geographical awareness is another common requirement of data analysis. Using Power Maps you will create captivating visualizations that map your data in space and time.

Beginning Power BI with Excel 2013 is your practical guide to getting maximum insight from your data, and presenting it with impact.

Table of Contents

Frontmatter

Building Models in Power Pivot

Frontmatter
Chapter 1. Introducing Power Pivot
Abstract
The core of Microsoft’s self-service business intelligence (BI) toolset is Power Pivot. The rest of the tools, Power View, Power Query, and Power Map, build on top of a Power Pivot tabular model. In the case of Power View this is obvious because you are explicitly connecting to the model. In the case of Power Query and Power Map it may not be as obvious because the Power Pivot tabular model is created for you behind the scenes. Regardless of how it is created, to get the most out of the tool set and gain insight into the data you need to know how Power Pivot works.
Dan Clark
Chapter 2. Importing Data into Power Pivot
Abstract
One of the first steps in creating the Power Pivot model is importing data. Traditionally when creating a BI solution based on an OLAP cube, you need to import the data into the data warehouse and then load it into the cube. It can take quite a while to get the data incorporated into the cube and available for your consumption. This is one of the greatest strengths of the Power Pivot model. You can easily and quickly combine data from a variety of sources into your model. The data sources can be from relational databases, text files, web services, and OLAP cubes, just to name a few. This chapter shows you how to incorporated data from a variety of these sources into a Power Pivot model.
Dan Clark
Chapter 3. Creating the Data Model
Abstract
Now that you know how to get data into the Power Pivot model, the next step is to understand what makes a good model. This is very important when dealing with data in Power Pivot. A good model will make Power Pivot perform amazingly fast and allow you to analyze the data in new and interesting ways. A bad model will cause Power Pivot to perform very slowly and at worst give misleading results when performing the data analysis. Traditional Excel pivot tables are based on a single table contained in an Excel sheet. Power Pivot pivot tables are based off of multiple tables contained in the data model. This chapter guides you through the process of creating a solid model that will become the foundation for your data analysis. In addition, you will look at how to present a user-friendly model to client tools. This includes renaming tables and fields, presenting appropriate data types, and hiding extraneous fields.
Dan Clark
Chapter 4. Creating Calculations with DAX
Abstract
Now that you know how to create a robust data model to base your analysis on, the next step is to add to the model any calculations required to aid your exploration of the data. For example, you may have to translate code values into meaningful descriptions or parse out a string to obtain key information. This where Data Analysis Expressions (DAX) comes into play. This chapter introduces you to DAX and shows you how to use DAX to create calculated columns to add to the functionality of your model.
Dan Clark
Chapter 5. Creating Measures with DAX
Abstract
Creating measures in DAX is the most important skill necessary to create solid data models. This chapter covers the common functions used to create measures in the data model. It also covers the important topic of data context and how to alter or override the context when creating measures.
Dan Clark
Chapter 6. Incorporating Time Intelligence
Abstract
One of the most common types of data analysis is comparing values over time. This chapter shows the reader how to correctly implement time-based analysis in Power Pivot. It includes setting up a date table and using the various built-in functions for analyzing values to date, comparing values from different periods, and performing semi-additive aggregations.
Dan Clark
Chapter 7. Data Analysis with Pivot Tables and Charts
Abstract
Once you have the data model created in Power Pivot, you need to create an interface for users to interact with the data model and perform data analysis using the model. You can use several programs to interface with the model depending on the type of analysis taking place; these include Power View, Power Map, Performance Point, and Reporting Services. Although all these tools are viable clients to use, one of the best client tools is Excel itself. Excel is a feature-rich environment for creating dashboards using pivot tables and pivot charts. Furthermore, it is very easy to share Excel files with colleagues or host the Excel workbook on SharePoint for increased performance and security. This chapter covers the basics of building an interface for analyzing the data contained in a Power Pivot model using pivot tables and pivot charts in Excel.
Dan Clark

Building Interactive Reports and Dashboards with Power View

Frontmatter
Chapter 8. Optimizing Power Pivot Models for Power View
Abstract
Power View is a worksheet type in Excel that lets users develop interactive visualizations that encourage ad-hoc exploration of the data. It is an ideal tool for users who are not Excel power users who want to explore and gain insight from the data. Since Power View visualizations are based on a Power Pivot model, you need to provide users with a solid model as a foundation for these visualizations. A good Power Pivot model tuned for use by Power View will make the difference between a great user experience and an extremely frustrating one. There are ways to optimize a Power Pivot model so that it facilitates better report creation in Power View. This chapter reviews these settings and shows how to create models that provide support for many Power View features.
Dan Clark
Chapter 9. Creating Standard Visualizations with Power View
Abstract
In the last chapter you saw how important it is to create a Power Pivot model that works well with Power View. In this chapter you will investigate some of the standard visualizations used to create reports and dashboards. You will become familiar with the common features of the Power View designer. You will build standard visualizations such as column, bar, and pie charts. In addition, you will look at using a scatter chart and how to turn it into a bubble chart with a play axis. The final visualization you will investigate is how to use maps to analyze data geographically.
Dan Clark
Chapter 10. Creating Interactive Dashboards with Power View
Abstract
In the last chapter, you investigated some of the standard visualizations used to create reports and dashboards. In this chapter, you will combine these visualizations so they work together to form interactive dashboards. Interactive dashboards differ from a traditional dashboard by extending the user experience from passive to active. You can perform filtering, drill up or down through different levels of detail, and discover associations between the various metrics. You will also look at how you can group the data using tiles and create filters for the groups and views as a whole. In addition, you will look at how you can include images to enhance the dashboard experience.
Dan Clark

Exploring and Presenting Data with Power Query and Power Map

Frontmatter
Chapter 11. Data Discovery with Power Query
Abstract
Although Power Pivot provides many types of connections you can use to query data, there are times when you need to manipulate the data before loading it into the model. This process is commonly known as tttthe transform part of the ETL (extract, transform, and load) process. This is where Power Query really shines and is a very useful part of your BI arsenal. Power Query provides an easy-to-use interface for discovering and transforming data. It contains tools to clean and shape data such as removing duplicates, replacing values, and grouping data. In addition, it supports a vast array of data sources both structured and unstructured, such as relational databases, web pages, and Hadoop, just to name a few. Once the data is extracted and transformed, you can then easily load it into a Power Pivot model.
Dan Clark
Chapter 12. Geospatial Analysis with Power Map
Abstract
Power Map is an Excel add-in that provides you with a powerful set of tools to help you visualize and gain insight into large sets of data that have a geocoded component. It can help you produce 3D visualizations by plotting up to a million data points in the form of column, heat, and bubble maps on top of a Bing map. If the data is time stamped, it can also produce interactive views that display how the data changes over space and time. In this chapter, you will learn to create compelling and unique visual representations using Power Map.
Dan Clark
Chapter 13. Mining Your Data with Excel
Abstract
One of the most underutilized areas of business intelligence is data mining or predictive analytics as it is often referred to. This is due to the fact that traditional data mining uses a set of complex algorithms, and the tools often require you to be an expert in these algorithms to be able to implement them. Although it is not part of the official Microsoft self-service BI toolset, the set of table analysis tools Microsoft offers for Excel allows you to use data mining algorithms but hide much of the complexity associated with implementing them. Using these table analysis tools for Excel, you can perform advanced data analysis such as forecasting, clustering, and associations. This chapter shows you how to use these tools to implement some common data mining models and gain powerful insight into your data. I should mention, however, that to implement the table analysis tools, you need to be able to connect to an instance of SQL Server Analysis Server (SSAS), which provides the engine to run the algorithms.
Dan Clark
Chapter 14. Creating a Complete Solution
Abstract
In the previous chapters, you gained experience working with each of the pieces of Microsoft’s self-service BI tool set. You used Power Pivot, Power View, Power Query, Power Map, and the Excel table analysis tools. This chapter provides you with several use cases to solidify the concepts of the previous chapters. By working through these use cases, you will gauge which areas you have mastered and which you need to spend more time studying. Since this is sort of like your final exam, I have deliberately not included step-by-step instructions like I did for the previous exercises. Instead, I have given you general directions that should be sufficient to get you started. If you get stuck, refer back to the previous chapters to remind yourself of how to accomplish the task.
Dan Clark
Backmatter
Metadata
Title
Beginning Power BI with Excel 2013
Author
Dan Clark
Copyright Year
2014
Publisher
Apress
Electronic ISBN
978-1-4302-6446-0
Print ISBN
978-1-4302-6445-3
DOI
https://doi.org/10.1007/978-1-4302-6446-0

Premium Partner

    Image Credits