Search code examples
ssasbusiness-intelligenceolap-cube

How can i create and use generic calculation for statistical function Stdev ?

How can create generic calculated member which i can reuse in order to calculate the statistical deviation on any logical related dimension(s) and measurement ? I need to be able to select any dimension and measure.

I found related discussion here from which i took stdDev function example.

I've created the calculation, but the result it empty. 

CREATE MEMBER CURRENTCUBE.[Measures].calcTest
AS iif(IsEmpty(Axis(0).Item(0)), NULL,
StdDev(Axis(1).Item(0).Item(0).Dimension.Children, Axis(0).Item(0))), 
FORMAT_STRING = "#,##0.00;-#,##0.00", 
VISIBLE = 1 ,  ASSOCIATED_MEASURE_GROUP = 'TestMeasure' ;
Solution

  • If you want to use it for all measures, you would put your calcTest member to another hierarchy not used in the query, maybe an utility hierarchy (see below), and then reference the Measures.CurrentMember instead of Axis(0).Item(0).

    To get the calculation generalized for all hierarchies except the measures is hard, as somewhere you have to tell the calculation which hierarchy to use. And to do this in the cube before the query is defined cannot be done. Hence, the approach to use the leftmost hierarchy in the rows (Axis(1).Item(0)) taken in your question can be changed to reference something different in the query, but cannot be completely flexible. Of course, an approach could be to define, say, three similar members, one for the first, one for the second, and one for the third hierarchy in the rows.

    For the utility hierarchy, you would create a dimension table with just one row and one column. Let's say the data content of this column in the single row is "-standard-"and reference this single member in all fact tables from an additional foreign key. Then you build the dimension into the cube with the references from all measure groups. Lets say the attribute hierarchy is called [Utility].[Utility]. Make the attribute hierarchy non aggregatable, and set the default member to the "-standard-" member. In the calculation script, you can then add members to this utility dimension like 

    CREATE MEMBER [Utility].[Utility].StdDevRows1 // StdDev for leftmost hierarchy in the rows
AS StdDev(Axis(1).Item(0).Item(0).Dimension.Children, Measures.CurrentMember), 
FORMAT_STRING = "#,##0.00;-#,##0.00", 
VISIBLE = 1 ,  ASSOCIATED_MEASURE_GROUP = 'TestMeasure' ;

CREATE MEMBER [Utility].[Utility].StdDevRows2 // StdDev for second hierarchy in the rows
AS StdDev(Axis(1).Item(1).Item(0).Dimension.Children, Measures.CurrentMember), 
FORMAT_STRING = "#,##0.00;-#,##0.00", 
VISIBLE = 1 ,  ASSOCIATED_MEASURE_GROUP = 'TestMeasure' ;

CREATE MEMBER [Utility].[Utility].StdDevRows3 // StdDev for third hierarchy in the rows
AS StdDev(Axis(1).Item(2).Item(0).Dimension.Children, Measures.CurrentMember), 
FORMAT_STRING = "#,##0.00;-#,##0.00", 
VISIBLE = 1 ,  ASSOCIATED_MEASURE_GROUP = 'TestMeasure' ;

    The advantage of a separate utility hierarchy is that you can combine it with all measures, times, etc., as it is not itself a member of these.