In 2010, Microsoft announced a change to its Business Intelligence environment, and said it will focus its development efforts on semantic modeling. At that time, the current technology used for analysis was SQL Server Analysis Server (SSAS), a technology that relied on disk-based storage and the distinct steps of model development, deployment, and processing—a function usually under the control of IT. The new technology will house all its data in memory and allow the user (or model designer) to change the model in real time and view those changes instantaneously. In addition to this, the platform sought to remove many of the barriers that had existed in the traditional Business Intelligence landscape. It offered a uniform platform for data analysis across an entire organization. The same platform can now be used by an individual user in Excel deployed to SharePoint (for team Business Intelligence) or directly to a server (for corporate Business Intelligence). This will remove a large proportion of the rework that was traditionally involved in Business Intelligence projects and lead to the catchcry "BI to the masses" (meaning that anyone can model a Business Intelligence solution). A free add-in was released for Excel 2010, and the 2012 release of Analysis Server (in SQL Server) included a new storage mode called tabular.
This was an interesting challenge to the traditional methods for implementing Business Intelligence models. Under that structure, Business Intelligence was essentially controlled by an IT department, which used a waterfall methodology and there were distinct phases in an analytical project involving the separation of duties and more importantly, the separation of people. Those that had to use data models were often involved with a back-and-forth battle to make the model work as the business user required.
Tabular models were then introduced and overnight Excel users were able to consume massive amounts of data and create their own analytical models without the need to involve IT (other than access to the data of course!). The product extended the familiar pivot table by allowing users to create pivot tables using many different data sources (and removed the requirements for a pivot table to be sourced from a single data table). More importantly, the ability to create models for the analysis of data was delivered directly to those who needed it most—the analytical end user. The restrictions on analysis and data manipulation that they had previously encountered were removed.
This book is primarily written for those users—individuals who need to answer questions based on large amounts of data. For this reason, we focus on how these users can use that technology to build models in Excel using PowerPivot. We simply don't want to exclude those users who need it the most and do not have access to the more traditional tools developed for corporate BI. Furthermore, these techniques are also directly applicable to corporate tabular models.
Finally, the book looks at how these models can be managed and incorporated into production environments and corporate systems to provide robust and secure reporting systems.
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.
As a book which covers many aspects of tabular modeling, the recipes can be followed using a variety of software that incorporates tabular modeling. Although we focus on PowerPivot in Excel 2010 (this is still the most prevalent installation in corporate environments), the recipes can also be completed in Excel 2013. When recipes focus on server and corporate features, SQL Server Analysis Services 2012 (in tabular storage mode) is used. The complete list of software applications used in this book is:
Excel 2010 (with the free PowerPivot add-in)
Excel 2013
SQL Server Data Tools (installed with SQL Server 2012)
SQL Server 2012 (SQL Server Analysis Server Tabular Mode)
This book is designed for two types of users. First and foremost, it is designed for those users who wish to create tabular models for analysis regardless of whether they create the model for personal use in Excel using PowerPivot or server-based models that are deployed to Analysis Services. For those modelers, we show how to design, create, and manipulate the model so that it can be used to answer the types of questions that appear in business. For these users and consumers of model data, we also show how the model can be used to provide an intuitive and interactive report (both in Excel and Power View). Our goal for these users was to give them the skills so that they can build a model capable of answering their business questions.
The second category of users are those who are responsible for the maintenance of models in corporate environments. These are administrators who must ensure that the corporate model data is up-to-date and secure. For these users we show tricks and techniques to deploy the model and keep it running smoothly.
In this book, you will find a number of styles of text that distinguish between different kinds of information. Here are some examples of these styles, and an explanation of their meaning.
Code words in text, database table names, folder names, filenames, file extensions, pathnames, dummy URLs, user input, and Twitter handles are shown as follows: "Each product is identified by a product_id value."
A block of code is set as follows:
=LOOKUPVALUE (Subcategory[Subcategory] , Subcategory[product_id],Products[Product ID] )
New terms and important words are shown in bold. Words that you see on the screen, in menus or dialog boxes for example, appear in the text like this: "Then on the PowerPivot tab, click on the Create Linked Table button."
Feedback from our readers is always welcome. Let us know what you think about this book—what you liked or may have disliked. Reader feedback is important for us to develop titles that you really get the most out of.
To send us general feedback, simply send an e-mail to <[email protected]>, and mention the book title via the subject of your message.
If there is a topic that you have expertise in and you are interested in either writing or contributing to a book, see our author guide on www.packtpub.com/authors.
Now that you are the proud owner of a Packt book, we have a number of things to help you to get the most from your purchase.
You can download the example code files for all Packt books you have purchased from your account at http://www.packtpub.com. If you purchased this book elsewhere, you can visit http://www.packtpub.com/support and register to have the files e-mailed directly to you.
Although we have taken every care to ensure the accuracy of our content, mistakes do happen. If you find a mistake in one of our books—maybe a mistake in the text or the code—we would be grateful if you would report this to us. By doing so, you can save other readers from frustration and help us improve subsequent versions of this book. If you find any errata, please report them by visiting http://www.packtpub.com/submit-errata, selecting your book, clicking on the errata submission form link, and entering the details of your errata. Once your errata are verified, your submission will be accepted and the errata will be uploaded on our website, or added to any list of existing errata, under the Errata section of that title. Any existing errata can be viewed by selecting your title from http://www.packtpub.com/support.
Piracy of copyright material on the Internet is an ongoing problem across all media. At Packt, we take the protection of our copyright and licenses very seriously. If you come across any illegal copies of our works, in any form, on the Internet, please provide us with the location address or website name immediately so that we can pursue a remedy.
Please contact us at <[email protected]> with a link to the suspected pirated material.
We appreciate your help in protecting our authors, and our ability to bring you valuable content.
You can contact us at <[email protected]> if you are having a problem with any aspect of the book, and we will do our best to address it.