ssas mdx and t-sql gives different values

Question

My situation:

There is a work database. Every day it is copied to another database named RetailDB. Fact and dimension tables of SSAS are based on VIEW-objects of tables in RetailDB.

So, I have the problem:

MDX query:

SELECT {[Date].[Y-M-D].[Day].[01.01.2013]} ON 0,
   { [Measures].[Quantity]} ON 1
FROM [Company]

gives me NULL value

at the same time t-sql query:

SELECT SUM([Quantity])
FROM [RetailDB].[dbo].[fact_Income]
WHERE Day = '2013-01-01'

gives me 7937338,023

On other date MDX-query can return correct value.

On some other date MDX-query can return not NULL value but it does not equal to t-sql value.

Please, help to correct this problem. What should I check in my cube to fix this?

Thanks for everyones answer.

Daryl Wenman-Bateson Daryl Wenman-Bateson · Accepted Answer · 2013-03-07T13:44:30

If you are using the right date dimension, then it would indicate that you need to process the cube to synchronise with your database.

Try bringing back all quantity / dates to see if the data appears to have processed up to a certain point

SELECT {[Measures].[Quantity]} ON 0,
   { [Date].[Y-M-D].[Day]} ON 1
FROM [Company]

ssas mdx and t-sql gives different values

1 Answers