Extending and Customizing Cubes - SQL Server Analysis Services 2012 Cube Development - page 132

member button, which looks like a calculator, and the new named set button, which
has a couple of dots enclosed in curly braces. Also ensure that you are using Form
View —you'll switch to Script View shortly. Also note the Calculation Tools pane in
the bottom left-hand corner.
Create a reseller sales moving-average calculation using the following steps:
1. Click on the Templates tab within Calculation Tools , right-click on Moving
Average , and choose Add Template . This creates a new calculated member
and provides the syntax for deriving the moving average.
2. Change the name of the calculation to Reseller Sales Moving Aver-
age .
3. Edit the expression supplied by the template to the following:
Avg
([Date].[Calendar].CurrentMember.Lag(6):[Date].[Calendar]
.CurrentMember, [Measures].[Reseller
Sales Amount])
4. Change the measure's Format String property to Currency , Associated
Measure Group to Reseller Sales , and Nonempty Behavior to Reseller
Sales Amount .
5. Click on the save button to save the calculation to the server. Quickly switch
to SQL Server Profiler to see what takes place behind the scenes—if you
examine the Command End event, you will see that SSDT sends an ALTER
command to the server to change the cube's definition.
6. Next, switch to SSMS, and execute the following query:
SELECT {measures.[reseller sales amount],
measures.[Reseller Sales Moving
Average]} ON 0,
NONEMPTY([Date].[Calendar].[Month].members)
ON 1
FROM
[Adventure Works]
Here is a partial result of this query: