Learn more about SQL Server tools

mssqltips logo
giveaway
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

SQL Server Analysis Services MDX Average Calculation


By:   |   Read Comments   |   Related Tips: > Analysis Services Development

Problem

How can you calculate an average in SQL Server MDX?  Can calculating an average in MDX be accomplished in multiple ways? Check out this tip to learn more.

Solution

The solution presented in this tip was created in SQL Server Management Studio. The AVG function in SQL Server MDX allows for the calculation of an average measure over a specified set of dimension values.

In the following code sample, I have added line numbers at the end of each line to help explain the syntax of this query.

  • In lines 3 through 6, we are creating a member named AverageSalesPerDay that uses the SQL Server MDX Avg function to calculate the Average Internet Sales Per Day for each Ship Date. Each Ship Date is returned by the SQL Server MDX Descendants function.
  • Lines 9 and 10 define the calculated member AverageAmountPerSale by dividing the Internet Sales Amount by the Internet Sales Count.
  • Lines 12 through 21 are the select statement.
  • Lines 14 and 15 are the measures (sometimes these are called facts) that we are selecting directly from the Analysis Services Tutorial cube. Please note that the measures are separated by a comma just like columns are separated by a comma in an SQL statement.
  • Lines 16 and 17 are the measures from the calculated members defined in Lines 1 through 10.
  • Line 19 specifies that we are slicing the measures by the Ship Date's Fiscal Quarter.
  • Line 20 is used to select dates only from the 2008 fiscal year from the Ship Date role-playing dimension.
  • Line 21 specifies that we are selecting from the Analysis Services Tutorial cube. Please note the closing parenthesis at the end of the line goes with the opening parenthesis on Line 20.


WITH                                                                       //01
// using the SQL Server MDX Avg function                                   //02
MEMBER Measures.[AverageSalesPerDay] AS                                    //03 
 Avg(Descendants([Ship Date].[Fiscal Date].CurrentMember,                  //04
                 [Ship Date].[Fiscal Date].[Date]),                        //05
      [Measures].[Internet Sales-Sales Amount])                            //06
                                                                           //07
// an average of two measures                                              //08  
MEMBER Measures.[AverageAmountPerSale] as                                  //09
[Measures].[Internet Sales-Sales Amount]/[Measures].[Internet Sales Count] //10
                                                                           //11
SELECT non empty                                                           //12
{                                                                          //13  
  [Measures].[Internet Sales-Sales Amount],                                //14
  [Measures].[Internet Sales Count],                                       //15
  [Measures].[AverageSalesPerDay],                                         //16
  [Measures].[AverageAmountPerSale]                                        //17
} ON COLUMNS,                                                              //18
non empty [Ship Date].[Fiscal Date].[Fiscal Quarter].ALLMEMBERS ON ROWS    //19
from (SELECT ([Ship Date].[Fiscal Year].&[2008]) on columns                //20
FROM [Analysis Services Tutorial])                                         //21

The output from the SQL Server MDX code above is shown below. As you can see in this image, we can see the measure values sliced by fiscal quarter for the 2008 fiscal year.

The output from the SQL Server MDX code.

Changing line 19 to slice on the Ship Date's Fiscal Semester yields the output shown below. Notice how all of the averages have changed due to the changes in the cube measure values.

Calculating an average in SQL Server MDX can be accomplished in multiple ways depending on the desired results.

To validate the results of the calculations, I ran the following SQL Server MDX query and then copied and pasted the results into Excel and used the Excel Average function to ensure the results were correct.

select non empty 
{
  [Measures].[Internet Sales-Sales Amount],
  [Measures].[Internet Sales Count]
} on columns,
non empty [Ship Date].[Fiscal Date].[Date] on rows
from (SELECT ([Ship Date].[Fiscal Year].&[2008]) on columns
from [Analysis Services Tutorial])
Next Steps


Last Update:






About the author
MSSQLTips author Dallas Snider Dr. Dallas Snider is an Assistant Professor in the Computer Science Department at the University of West Florida and has 18+ years of SQL experience.

View all my tips





More SQL Server Solutions











Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



Learn more about SQL Server tools