PowerPivot for Excel Glossary
Because Microsoft SQL Server PowerPivot provides features that span many technology areas, from relational databases, to analytics, to Web publishing, this Help file also contains terms that may be new to you, or that come from new technology areas. This topic provides definitions for the terms used in this Help file, and provides links to related Help content.
PowerPivot Terms and Concepts
SharePoint Terms and Concepts
DAX Terms and Concepts
PowerPivot Terms and Concepts
Term |
Definition |
Analysis Services |
Analysis Services is a component of SQL Server that provides support for multidimensional databases. It is also the technology that provides in-memory data storage and processing of PowerPivot data in PowerPivot for Excel. |
Connection string |
Information used by PowerPivot to connect to an external data source. |
Cube |
A set of data that is organized and summarized into a multidimensional structure defined by a set of dimensions and measures. |
Data source connection |
The connection information that PowerPivot uses to connect to a data source. |
Foreign Key (FK) |
The column whose values match the primary key (PK) or unique key in the same or another table. For more information, see Understanding Relationships. |
Formulas |
PowerPivot provides a new formulas language, Data Analysis Expressions, that you can use to manipulate PowerPivot data. For more information about working with formulas, see Getting Started with Data Analysis Expressions (DAX). |
Granularity |
The degree of specificity of information that is contained in a data element. A fact table that has fine granularity contains many discrete facts, such as individual sales transactions. A table that has coarse granularity stores facts that are summaries of individual elements, such as sales totals per day. |
In-memory processing engine |
PowerPivot contains a new powerful in-memory data processing module. This VertiPaq engine enables rapid processing of very large amounts of data. The high performance is accomplished through patented new technologies for columnar storage and data compression. |
PowerPivot data |
Data that has been added to the PowerPivot window. For more information, see Adding Data to PowerPivot. |
PowerPivot table |
A worksheet (a tab) in Excel can contain multiple different data regions (or different tables) whereas a Table (a tab) in PowerPivot can contain only one data region (one Table). Excel offers a ‘work space’ for users whereas the PowerPivot tables contain only the data that has been imported. |
PowerPivot workbook |
A PowerPivot workbook is an Excel workbook with embedded PowerPivot data. Although the PowerPivot add-in launches a second window, the PowerPivot workbook runs in Excel’s process space and is not a separate application. For more information, see Learn About PowerPivot Capabilities. |
Primary Key (PK) |
A column that uniquely identify all the rows in a table. Primary keys do not allow null values. No two rows can have the same primary key value; therefore, a primary key value always uniquely identifies a single row. More than one key can uniquely identify rows in a table. For more information, see Understanding Relationships. |
Refresh |
Refresh, or data refresh, is the process of querying the data source used in a PowerPivot workbook and then updating the PowerPivot workbook with the latest data. For more information, see Refreshing or Changing Imported Data. |
Relational model |
A relationship is a connection that you create between two tables of data that establishes how the data in the two tables should be correlated. By creating relationships, you can easily create sophisticated analyses. For more information, see Understanding Relationships. |
Schema |
The names of tables, fields, data types, and primary and foreign keys of a database. Also known as the database structure. |
Slicer |
Horizontal and vertical A feature that provides one-click filtering controls that make it easy to narrow down the portion of a data set that's being looked at. For more information, see Filter Data using Slicers. |
Sparklines |
A miniature chart that can be inserted into text or embedded within a cell on a spreadsheet to illustrate highs, lows, and trends in your data. |
SharePoint Terms and Concepts
Term |
Definition |
Analysis Services in SharePoint integrated mode |
Analysis Services in SharePoint integrated mode is the SQL Server Analysis Services (PowerPivot) instance that loads, stores, and calculates PowerPivot data. Analysis Services in SharePoint integrated mode can only perform in-memory storage and processing of PowerPivot data. It does not support traditional MOLAP or ROLAP processing modes. |
Application server |
A SharePoint server that provides resources to the farm. Examples of applications that run on an application server include Search Server, Access Database Services, Business Data Connectivity Services, Excel Services, and SQL Server PowerPivot for SharePoint. |
Configuration database |
A SQL Server relational database that stores the farm configuration settings. |
Content database |
A SQL Server relational database that stores user documents and items for a site collection. |
Data Feed Library |
A special-purpose library that provides a common access point for browsing data service documents that have been published to a SharePoint server. |
Data Service Document |
Specifies one or more connections to online sources that publish data in the Atom wire format. |
Farm |
One or more SharePoint servers that share the same configuration database, services, and content. |
Middle tier |
The logical layer between a client application and the database. This is typically where the Web server resides and where business objects are instantiated. Also known as application server tier. |
PowerPivot System Service |
PowerPivot System Service is a Web service that works with Analysis Services instance in SharePoint integrated mode to load and process PowerPivot data. The service monitors PowerPivot server health across the farm, collects usage data, and allocates data load operations to specific PowerPivot service instances in the farm. |
PowerPivot Web service |
PowerPivot Web service is a thin middle-tier connection manager that runs on a Web front end. It coordinates and handles request-response exchanges between client applications and PowerPivot service instances in the farm. |
SharePoint Web application |
A collection of sites that share a single point of access and a common configuration at the application level, including the authentication subsystem used to authenticate user identity, service connections and availability, and general settings. |
Site collection |
A multi-hierarchical group of sites. There is one root site collection for each SharePoint Web application. However, a farm administrator can add additional site collections under the root, as well as under other sites in other collections. |
Web front-end server |
A SharePoint server dedicated to hosting Web applications. A Web front-end server can be on a physical computer that is separate from other computers that host shared applications and services in the farm. |
DAX Terms and Concepts
The following table describes the terms and concepts that you need to understand to work with Data Analysis Expressions (DAX) formulas.
Term |
Definition |
Calculated column |
A calculated column is a column that you add to an existing PowerPivot table. Instead of pasting or importing values in the column, you create a DAX formula that defines the column values. If you include the PowerPivot table in a PivotTable (or PivotChart), the calculated column can be used as you would any other data column. Unlike in Excel, you cannot create different formulas for different rows in a table; instead, the DAX formula is automatically applied to the entire column. For more information, see Create a Calculated Column. |
Context |
Context defines the current subset of data that is being evaluated. For example, if your data has been filtered by year, the year filter defines the current context. For more information about the different types of context, and how they can affect the results of formulas, see Context in DAX Formulas. |
Expression |
An expression is a portion of a formula. When you update the workbook, the value in the expression is evaluated and the value of the expression becomes available for use in other calculations. |
Filter |
A filter is an expression that you create, using DAX, to restrict the rows and columns that are used in the current context. Filters can also be used to return a table of values to another function. For example, you can create a filter that returns only the customers who are in a particular region, and then use that filter expression in place of a table within an expression that calculates an average or sum for the table. For more information, see Filtering Data in Formulas. |
Function |
A function performs a calculation on a column in a set of rows and returns a single value. |
Measure |
A measure is a formula that is created specifically for use in a PivotTable (or PivotChart) that uses PowerPivot data. Measures can be based on standard aggregation functions, such as COUNT or SUM, or you can define your own formula by using DAX. A measure is used in the Values area of a PivotTable. If you want to place calculated results in a different area of a PivotTable, use a calculated column instead. Each measure that you drop into a PivotTable or PivotChart is evaluated in a specific context. For example, when you place a measure on an Excel PivotTable, each cell in the PivotTable represents some unique combination of the values in that cell’s row and column headers. To populate the cell, a query is issued to get all the relevant values, and the formula is evaluated against that set of data. For more information, see Create a Measure in a PivotTable or PivotChart. Note If you are familiar with Analysis Services, it might help to think of a DAX measure as a “calculated measure” that is created using DAX syntax instead of MDX. |
Operator |
In a query, a connector between two expressions, two subclauses, or a combination of an expression and a subclause. There are three primary logical operators: AND, OR, and NOT. For more information, see Operator Reference (DAX). |
Processing |
Processing is an automatic operation of the in-memory BI engine, in which data is updated in memory and all necessary aggregations are recalculated. For more information, see Recalculating Formulas. |
Recalculation |
Formulas and measures in a PowerPivot workbook must be recalculated when underlying data is updated, or when the formulas have changes. Therefore, recalculation often follows a refresh operation. Because recalculation can take a while, you may wish to control the timing of recalculation. For more information, see Recalculating Formulas. |
Relationship |
A relationship is a mapping between two tables that joins one column from each table, where the columns have matching data. PowerPivot supports a single relationship between any two tables, on a single column. You can create one-to-one relationships, or one-to-many relationships. For more information, see Understanding Relationships. |
Scalar |
A single value, either text or a number. In a PowerPivot workbook, some functions can work only with scalar values, whereas other functions can work with entire columns or tables of data |
Table data type |
A new data type for use in DAX functions. Tables are used like you use arrays in Excel functions, to sum or work with multiple values. However, in DAX you cannot define an ad hoc array, but instead get the table values by defining an expression that returns a column or table. The columns or tables in a table data type cannot be stored in individual cells in a PowerPivot table; rather, you use the column or table as an intermediate result, stored in memory, over which to perform other operations. For more information, see Key Concepts in DAX. |
Unknown member |
A member of a dimension for which no key is found during processing of a cube that contains the dimension. For example, if you create a Pivot Table that is supposed to group sales by store, but some records in the sales table do not have a store name listed, all records without a valid store name are grouped together as the unknown member. For more information, see Referential Integrity, PowerPivot Relationships, and the Unknown Member. |