Search code examples
ssasmdxcubeolap-cube

How to cast an MDX measure field?

I am very new to ssas and mdx.

I am trying to compare the results I am getting from SSAS with the SQL database. Somehow for this particular query it does not match. The data is exactly the same.

I could not see any casting function for MDX.

Any idea what is happening?

SQL:

SELECT sum(CAST(LOS_MINUTES as BIGINT))
  FROM [db].[dbo].[ FACT_WARD_STAY]

Result is: 3311473500

MDX:

SELECT NON EMPTY { [Measures].[Stay Minutes] } ON COLUMNS FROM [Ward Stays]

Result is: -983493796
Solution

  • Your measure in the cube is an integer it looks like. So you are experiencing an overflow. The negative number is a telltale sign of that.

    The easiest way to fix this is to install the free BI Developer Extensions and run Measure Group Health Check.