What this book covers

Chapter 1, Getting Started with Excel, covers the basics of the tabular model, that is, how to get started with modeling and summarizing the data. This chapter includes a basic overview of how the tabular model works and how the model presents to an end user (we also look at some general data modeling principles, so that you can better understand the underlying structure of the datasets that you use). In doing so, we look at the basics of combining data within the model, calculations, and the control (and formatting) of what an end user can see.

Chapter 2, Importing Data, examines how different forms of data can be incorporated and managed within the model. In doing so, we examine some common sources of data which are used (for example, text files) and examine ways that these sources can be controlled and defined. We also examine some non-traditional sources (for example, data that is presented in a report).

Chapter 3, Advanced Browsing Features, examines how the model can be structured to provide an intuitive and desirable user experience. We examine a variety of techniques that include model properties and configurations, data structures and design styles, which can be used to control and present data within the model. We also examine how to create some common analytical features (for example, calculation styles, value bounds, ratios, and key performance indicators) and how these can be used.

Chapter 4, Time Calculations and Date Functions, explains how time and calendar calculations are added and used within the model. This chapter looks at defining the commonly used month-to-date and year-to-date calculations, as well as comparative calculations (for example, the same period last year). We also look at alternate calendars (for example, the 445 calendar) running averages and shell calculations.

Chapter 5, Applied Modeling, discusses some advanced modeling functionality and how the model can be used to manipulate its own data thus presenting new information. For example, we look at the dynamic generation of bins (that is, the grouping of data), currency calculations, many-to-many relationships, and stock calculations over time. We also look at how the model can be used to allocate its own data so that datasets that have been imported into the model at various levels of aggregation can be presented under a consistent view.

Chapter 6, Programmatic Access via Excel, explains how the tabular model can open a new world of possibilities for analysis in Excel by allowing the creation of interactive reports and visualizations that combine massive amounts of data. This chapter looks at how Excel and the tabular model can be used to provide an intuitive reporting environment through the use of VBA—Visual Basic for Applications is the internal programming language of Excel.

Chapter 7, Enterprise Design and Features, examines the corporate considerations of the tabular model design and the additional requirements of the model in that environment. We look at the various methods of upgrading PowerPivot model, perspectives, and the application of security.

Chapter 8, Enterprise Management, examines how the model is managed in a corporate environment (that is on SQL Server Analysis Server). This chapter looks at various techniques for deploying the tabular model to a SSAS server and the manipulation of objects once they have been deployed (for example, the addition and reconfiguration of data sources). We look at the addition of new data to the model through petitions and the processing of the model data through SQL Server Agent Jobs.

Chapter 9, Querying the Tabular Model with DAX, shows how to query the model using the language of the tabular model—DAX (Data Analysis Expressions). We look at how to retrieve data from the model and then go on to combine data from different parts of the model, create aggregate summaries and calculations, and finally filter data.

Chapter 10, Visualizing Data with Power View, explains how Power View can be used to analyze data in tabular models. This chapter looks at how to use Power View and how to configure and design a tabular model for use with Power View.

Appendix, Installing PowerPivot and Sample Databases, shows how to install PowerPivot in Excel 2010 and install the sample data used in this book.