Share via

Power Pivot Glossary

  • Article

Microsoft SQL Server Power Pivot provides features that span many technology areas, from relational databases to analytics, to Web publishing. This article provides definitions for some Power Pivot terms and provides links to related content in the TechNet Library.

Add words and tweak the definitions by clicking the Edit tab.

In this article:

This article was formatted and published by Mary Browning, Microsoft SQL Server technical writer..

Power Pivot Terms and Concepts

Term Definition

.NET Framework

An integral Windows component that supports building, deploying, and running the next generation of applications and Web services. It provides a highly productive, standards-based, multilanguage environment for integrating existing investments with next-generation applications and services, as well as the agility to solve the challenges of deployment and operation of Internet-scale applications. The .NET Framework consists of three main parts: the common language runtime, a hierarchical set of unified class libraries, and a componentized version of ASP called ASP.NET. See also: ASP.NET, common language runtime, .NET Framework class library.

PowerPivot supports the .NET Framework Data Provider.

To install Power Pivot, .NET Programmability Support, in Microsoft Excel, must also be installed.

ad hoc report

An .rdl report created with Report Builder 1.0 that accesses report models.

You can create ad hoc reports based on PowerPivot workbooks published to the PowerPivot Gallery in SharePoint. For more information, see PowerPivot Workbooks on SharePoint in the TechNet Library.

add-in

A custom extension that provides specific functionality. PowerPivot is an add-in for Excel.

aggregate function

A function that performs a calculation on multiple values and returns a single summary value. For more information, see Understanding Aggregations in Formulas in the TechNet Library.

aggregate of aggregates

A summary value calculated from aggregates, such as the maximum of a set of sums.

aggregate query

A query that summarizes information from multiple rows by including an aggregate function such as Sum or Avg.

aggregation

A table or structure that contains precalculated data for a cube.

aggregation design

In Analysis Services, the process of defining how aggregation is created.

alert

A user-defined response to changes made to a PowerPivot workbook in SharePoint. For more information, see Secure a PowerPivot Workbook on SharePoint in the TechNet Library.

alias

An alternative name for a table or column in expressions that are often used to shorten the name for subsequent reference in code, prevent possible ambiguous references or provide a more descriptive name in the query output.
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.

argument

A value that provides information to an action, an event, a method, a property, a function, or a procedure.

attribute

In SQL Server, the building block of dimensions and their hierarchies that corresponds to a single column in a dimension table. For information about how PowerPivot interacts with SQL Server Analysis Services, see Import Data from Analysis Services or PowerPivot in the TechNet Library.

attribute relationship

The hierarchy associated with an attribute containing a single level based on the corresponding column in a dimension table.

authentication

The process of verifying the identity of a user, computer, process, or other entity by validating the credentials provided by the entity. Common forms of credentials are digital signatures, smart cards, biometric data, and a combination of user names and passwords. For more information, see Connect to a Microsoft SQL Server Database or to SQL Server Analysis Services in the TechNet Library.

axis

The vertical and horizontal lines on a graph used to show the position of a point.
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 in the TechNet Library.
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) in the TechNet Library.
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 in the TechNet Library.
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 "workspace" 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 in the TechNet Library.
primary key (PK)

A column that uniquely identifies 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 in the TechNet Library.
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 in the TechNet Library.
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 in the TechNet Library.
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 in the TechNet Library.
sparkline

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.

↑ Back to top

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

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

 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.

↑ Back to top

 

 

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 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 in the TechNet Library.
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.
expression

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

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 in the TechNet Library.
function

A function performs a calculation on a column in a set of rows and returns a single value.
measure

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 in the TechNet Library.

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 expression and a subclause. There are three primary logical operators: AND, OR, and NOT. For more information, see DAX: Operator Reference.
processing

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 in the TechNet Library.
recalculation

Formulas and measures in a PowerPivot workbook must be recalculated when underlying data is updated, or when the formulas have changed. 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 in the TechNet Library.
relationship

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 in the TechNet Library.
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 in the TechNet Library.
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 in the TechNet Library.

See Also