Database Reference
In-Depth Information
The Associated Measure Group dropdown box allows you to determine where the new calculated measure
will be displayed within a client application. Although it may seem strange, even after you change this setting and
process the cube, you will not see your new calculated member displayed in the Cube Structure tab as you would
expect. However, you will be able to see it in the Browser tab, and most Microsoft clients will see it as well.
Calculated Members vs. Derived Members
In our example, we want a measure that multiplies the current price by the sales quantity. You would think
that the simple MDX statement in Listing 11-2 would do this for us. After all, it multiplies the TitlePrice for the
DimTitles table by the SaleQuantity in the FactSales table.
Listing 11-2.
This MDX Expression Produces a Null Value
[DimTitles].[TitlePrice] * [Measures].[SalesQuantity] -- Will be Null due to the context!
Nevertheless, if you use this expression to create a calculated measure, you will find that it does not return
the extended sales price because of the context of the expression. Unfortunately, SSAS cannot automatically
map the TitlePrice from the DimTitles table to the SalesQuantity in the FactSales table, so you have to help it by
creating a derived member in the FactSales table.
Derived members are created in SSAS by modifying the SQL code behind each table in a data source view. This
can be somewhat confusing, so let's review the difference between calculated members and derived members.
Calculated members use MDX expressions to create new members to the measures or other dimensions.
Derived members use SQL expressions to create new members on the measures or other dimensions.
Both are similar, but they each use a different language for the expression, and they evaluate their
expressions during different events. In the case of a calculated member, the event that causes the expression to
evaluate is when a client application queries the cube or dimension. In the case of a derived member, the event
that causes the expression to evaluate is when a cube or dimension is processed.
Listing 11-3 is a SQL statement that can be used to create two additional columns in a data source view. One
is a copy of the TitlePrice member of the Titles dimension, and one is a new member that represents the product
of the price of a given title by the sales quantity of a sales event.
Listing 11-3.
SQL Code for a Derived Measure
SELECT
FactSales.OrderNumber
, FactSales.OrderDateKey
, FactSales.TitleKey
, FactSales.StoreKey
, FactSales.SalesQuantity
-- Adding derived measures
, DimTitles.TitlePrice as [CurrentStdPrice]
, (DimTitles.TitlePrice * FactSales.SalesQuantity) as DerivedTotalPrice
FROM FactSales
INNER JOIN DimTitles
ON FactSales.TitleKey=DimTitles.TitleKey
■
We could have created both of these during the ETL process instead of here, but we chose not to do so
in order to show you this feature.
Note
