MatrixA (MDX+)
Returns a MatrixA.
Parameters
Name | Type | Cardinality | Default value |
set | set-expression | two | |
value | numeric-expression | one |
Return
value
Description
This function is similar to Matrix but returning a MatrixA. This new structure holds not only the numerical Matrix but also the information on the axis that generated it.
The MatrixA can be used to put the result into a modified MDX statement (SELECT [my-matrix] ON AXES ...) that will return the matrix as a standard MDX statement with Column and Rows.
MatrixA shares the same functions and scalar operators (+-*) as Matrix.
See Also
Examples
A standard example of a MatrixA adding the result 'on Axes' so the matrix is expanded. Note that as in a matrix EMPTY/NULL are converted to 0.0 we're using a calculated member to simulate this operation.
Query
WITH MEMBER [Measures].[matrix-a] AS MatrixA( [Geography].[Geo].[Country], [Product].[Prod].[Licence], [Measures].[Amount] ) SELECT [Measures].[matrix-a] on Axes FROM [Sales]
Result
Canada | Mexico | United States | Venezuela | France | Spain | Switzerland | |
Corporate | 0.0 | 0.0 | 768.0 | 0.0 | 0.0 | 0.0 | 144.0 |
Partnership | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 96.0 |
Personal | 0.0 | 0.0 | 0.0 | 0.0 | 4.0 | 3.0 | 8.0 |
Startup | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 |
Assertion : Cell Equals
WITH MEMBER [Measures].[matrix-a-element] AS [Measures].[Amount] + 0.0 // EMPTY + 0.0 = 0.0 SELECT [Geography].[Geo].[Country] on 0, [Product].[Prod].[Licence] on 1 FROM [Sales] WHERE [Measures].[matrix-a-element]
An example using the inverse method of the Matrix, axes are swapped.
Query
WITH MEMBER [Measures].[matrix-a] AS MatrixA( nonempty([Geography].[Geo].[Country],[Measures].[Amount]).tail(4), [Product].[Prod].[Licence].head(4), [Measures].[Amount] + 2.0 )->inverse() SELECT [Measures].[matrix-a] on Axes FROM [Sales]
Result
Corporate | Partnership | Personal | Startup | |
United States | 0.0013020833333333335 | -0.001953125 | 5.767392335715099E-19 | 6.510416666666651E-4 |
France | 0.003906250000000002 | -0.05794270833333328 | 1.0000000000000002 | -2.4459635416666674 |
Spain | -0.005208333333333336 | 0.0494791666666666 | -1.0000000000000004 | 2.955729166666668 |
Switzerland | -0.0 | 0.010416666666666666 | -0.0 | -0.010416666666666666 |
An example to show we're actually calculating the matrix inverse.
Query
WITH MEMBER [Measures].[matrix-a] AS MatrixA( nonempty([Geography].[Geo].[Country],[Measures].[Amount]).tail(4), [Product].[Prod].[Licence].head(4), [Measures].[Amount] + 2.0 ) MEMBER [Measures].[matrix-i] AS ([matrix-a]->inverse() * [matrix-a])->round(10) SELECT [Measures].[matrix-i] on Axes FROM [Sales]
Result
United States | France | Spain | Switzerland | |
United States | 1.0 | 0.0 | 0.0 | 0.0 |
France | 0.0 | 1.0 | 0.0 | 0.0 |
Spain | 0.0 | 0.0 | 1.0 | 0.0 |
Switzerland | 0.0 | 0.0 | 0.0 | 1.0 |
Assertion : Cell Equals
WITH MEMBER [Measures].[val] AS MatrixN( VectorN(1,0,0,0), VectorN(0,1,0,0), VectorN(0,0,1,0), VectorN(0,0,0,1) ).setAxes( nonempty([Geography].[Geo].[Country],[Measures].[Amount]).tail(4), [Product].[Prod].[Licence].head(4) ) SELECT [Measures].[val] ON Axes FROM [Sales]
This example shows the formatting of Matrix elements. Cell properties of Matrix elements use the properties defined by the calculated member. In this example we use the FORMAT_STRING.
Query
WITH MEMBER [Measures].[matrix-a] AS MatrixA( [Geography].[Geo].[Country], [Product].[Prod].[Licence], [Measures].[Amount] ), FORMAT_STRING = IIF( currentCellValue() <> 0.0, "j:%,3.2f", "j:-") SELECT [Measures].[matrix-a] on Axes FROM [Sales]
Result
Canada | Mexico | United States | Venezuela | France | Spain | Switzerland | |
Corporate | - | - | 768.00 | - | - | - | 144.00 |
Partnership | - | - | - | - | - | - | 96.00 |
Personal | - | - | - | - | 4.00 | 3.00 | 8.00 |
Startup | - | - | - | - | - | - | - |
Assertion : Cell Equals
WITH MEMBER [Measures].[matrix-a-element] AS [Measures].[Amount] + 0.0 , // EMPTY + 0.0 = 0.0 FORMAT_STRING = IIF( currentCellValue() <> 0.0, "j:%,3.2f", "j:-") SELECT [Geography].[Geo].[Country] on 0, [Product].[Prod].[Licence] on 1 FROM [Sales] WHERE [Measures].[matrix-a-element]
Assertion : Cell Equals
WITH MEMBER [Measures].[matrix-a] AS MatrixA( [Geography].[Geo].[Country], [Product].[Prod].[Licence], [Measures].[Amount] ), FORMAT_STRING = IIF( currentCellValue() <> 0.0, "j:%,3.2f", "j:-") MEMBER [Measures].[matrix-a-element] AS [Measures].[matrix-a].elementValue() SELECT [Geography].[Geo].[Country] on 0, [Product].[Prod].[Licence] on 1 FROM [Sales] WHERE [Measures].[matrix-a-element]
MatrixA class support the + operator.
In this example, ( (1,2),(3,4) ) + 2 = ( (3,5),(5,6) )
Query
WITH FUNCTION toA( a_ ) AS a_.SetAxes( Head([Geography].[Geo].[Country],2), Head([Product].[Prod].[Licence],2) ) MEMBER [Measures].[val] AS toA ( MatrixN( VectorN(1,2), VectorN(3,4) ) ) + 2 SELECT [Measures].[val] ON Axes FROM [Sales]
Result
Canada | Mexico | |
Corporate | 3.0 | 5.0 |
Partnership | 4.0 | 6.0 |
Assertion : Cell Equals
WITH FUNCTION toA( a_ ) AS a_.SetAxes( Head([Geography].[Geo].[Country],2), Head([Product].[Prod].[Licence],2) ) MEMBER [Measures].[val] AS 2 + toA( MatrixN( VectorN(1,2), VectorN(3,4) ) ) SELECT [Measures].[val] ON Axes FROM [Sales]
Assertion : Cell Equals
WITH FUNCTION toA( a_ ) AS a_.SetAxes( Head([Geography].[Geo].[Country],2), Head([Product].[Prod].[Licence],2) ) MEMBER [Measures].[val] AS toA( MatrixN( VectorN(1+2,2+2), VectorN(3+2,4+2) ) ) SELECT [Measures].[val] ON Axes FROM [Sales]
MatrixA class support the + operator.
In this example, ( (1,2),(3,4) ) - 2 = ( (-1,0),(1,2) )
Query
WITH FUNCTION toA( a_ ) AS a_.SetAxes( Head([Geography].[Geo].[Country],2), Head([Product].[Prod].[Licence],2) ) MEMBER [Measures].[val] AS toA( MatrixN( VectorN(1,2), VectorN(3,4) ) ) - 2 SELECT [Measures].[val] ON Axes FROM [Sales]
Result
Canada | Mexico | |
Corporate | -1.0 | 1.0 |
Partnership | 0.0 | 2.0 |
Assertion : Cell Equals
WITH FUNCTION toA( a_ ) AS a_.SetAxes( Head([Geography].[Geo].[Country],2), Head([Product].[Prod].[Licence],2) ) MEMBER [Measures].[val] AS (-2) + toA( MatrixN( VectorN(1,2), VectorN(3,4) ) ) SELECT [Measures].[val] ON Axes FROM [Sales]
Assertion : Cell Equals
WITH FUNCTION toA( a_ ) AS a_.SetAxes( Head([Geography].[Geo].[Country],2), Head([Product].[Prod].[Licence],2) ) MEMBER [Measures].[val] AS toA( MatrixN( VectorN(1-2,2-2), VectorN(3-2,4-2) ) ) SELECT [Measures].[val] ON Axes FROM [Sales]
MatrixA class support the + operator.
In this example, ( (1,2),(3,4) ) * 2 = ( (2,4),(6,8) )
Query
WITH FUNCTION toA( a_ ) AS a_.SetAxes( Head([Geography].[Geo].[Country],2), Head([Product].[Prod].[Licence],2) ) MEMBER [Measures].[val] AS toA( MatrixN( VectorN(1,2), VectorN(3,4) ) * 2 ) SELECT [Measures].[val] ON Axes FROM [Sales]
Result
Canada | Mexico | |
Corporate | 2.0 | 6.0 |
Partnership | 4.0 | 8.0 |
Assertion : Cell Equals
WITH FUNCTION toA( a_ ) AS a_.SetAxes( Head([Geography].[Geo].[Country],2), Head([Product].[Prod].[Licence],2) ) MEMBER [Measures].[val] AS toA( 2 * MatrixN( VectorN(1,2), VectorN(3,4) ) ) SELECT [Measures].[val] ON Axes FROM [Sales]
Assertion : Cell Equals
WITH FUNCTION toA( a_ ) AS a_.SetAxes( Head([Geography].[Geo].[Country],2), Head([Product].[Prod].[Licence],2) ) MEMBER [Measures].[val] AS toA( MatrixN( VectorN(1*2,2*2), VectorN(3*2,4*2) ) ) SELECT [Measures].[val] ON Axes FROM [Sales]
MatrixA class support the / operator.
In this example, ( (1,2),(3,4) ) / 2 = ( (1/2,2/2),(3/2,4/2) )
Query
WITH FUNCTION toA( a_ ) AS a_.SetAxes( Head([Geography].[Geo].[Country],2), Head([Product].[Prod].[Licence],2) ) MEMBER [Measures].[val] AS toA( MatrixN( VectorN(1,2), VectorN(3,4) ) / 2 ) SELECT [Measures].[val] ON Axes FROM [Sales]
Result
Canada | Mexico | |
Corporate | 0.5 | 1.5 |
Partnership | 1.0 | 2.0 |
Assertion : Cell Equals
WITH FUNCTION toA( a_ ) AS a_.SetAxes( Head([Geography].[Geo].[Country],2), Head([Product].[Prod].[Licence],2) ) MEMBER [Measures].[val] AS toA( MatrixN( VectorN(1/2,2/2), VectorN(3/2,4/2) ) ) SELECT [Measures].[val] ON Axes FROM [Sales]
MatrixA class support the * operator with a vector
In this example, ( (11,12),(21,22) ) * (1,2) = ( 11*1 + 21*2, 12*1 + 22*2 )
Query
WITH FUNCTION toA( a_ ) AS a_.SetAxes( Head([Geography].[Geo].[Country],2), Head([Product].[Prod].[Licence],2) ) MEMBER [Measures].[val] AS toA( MatrixN( VectorN(11,12), VectorN(21,22) ) ) * VectorN(1,2) SELECT [Measures].[val] ON Axes FROM [Sales]
Result
val | |
Corporate | 53.0 |
Partnership | 56.0 |
Assertion : Cell Equals
WITH FUNCTION toA( a_ ) AS a_.SetAxes( Head([Product].[Prod].[Licence],2) ) MEMBER [Measures].[val] AS toA( VectorN( 11*1 + 21*2, 12*1 + 22*2 ) ) SELECT [Measures].[val] ON Axes FROM [Sales]
Since Version
4.8
XMLA Properties
Find here the value of the XMLA properties when not specified otherwise.