Defining Named Calculations in a Data Source View (Analysis Services)
A named calculation is a SQL expression represented as a calculated column. This expression appears and behaves as a column in the table. A named calculation lets you extend the relational schema of existing tables or views in a data source view without modifying the tables or views in the underlying data source. For example, a common calculated value derived from the columns of a fact table can be expressed as a single named calculation in the data source view, instead of modifying the underlying database table or creating a view in the underlying database. A named calculation can also be used in place of calculated member in each cube based on the data source view. Named calculations are calculated during processing whereas calculated members are calculated at query time.
A named calculation is also frequently used to construct a user friendly name for a dimension member.
Note
You cannot add a named calculation to a named query, nor can you base a named query on a table that contains a named calculation.
Creating Named Calculations
When you create a named calculation, you specify a name, the SQL expression, and, optionally, a description of the calculation. The SQL expression can refer to other tables in the data source view. After the named calculation is defined, the expression in a named calculation is sent to the provider for the data source and validated as the following SQL statement in which <Expression> contains the expression that defines the named calculation.
SELECT
<Table Name in Data Source>.*,
<Expression> AS <Column Name>
FROM
<Table Name in Data Source> AS <Table Name in Data Source View>
The data type of the column is determined by the data type of the scalar value returned by the expression. If the provider does not find any errors in the expression, the column is added to the table.
Columns referenced in the expression should not be qualified or should be qualified by the table name only. For example, to refer to the SaleAmount column in a table, SaleAmount or Sales.SaleAmount is valid, but dbo.Sales.SaleAmount generates an error.
The expression is not automatically enclosed between parentheses. Therefore, if an expression, such as a SELECT statement, requires parentheses, you must type the parentheses in the Expression box. For example, the following expression is valid only if you type the parentheses.
(SELECT Description FROM Categories WHERE Categories.CategoryID = CategoryID)
Deleting Named Calculations
When you attempt to delete a named calculation, you are prompted with a list of the objects defined in the project or database that will be invalidated by the deletion.
How To Define, Edit, View, or Delete a Named Calculation
To view instructions about how to define, edit, view or delete a named calculation, see How to: Add, View, Modify, or Delete a Named Calculation to a Table Using Data Source View Designer (Analysis Services).