Incorrect Measure result

1
votes

I have a fact table X with Column Y and I have add it that column as a measure on SSAS 2012. The properties of that measure are AggregateFunction: Sum, DataType: Integer, source: X.Y, DataType: Integer, BindingType: Column binding, NullProcessing: Automatic.

On measure Group Properties, Error Configuration ReportAndStop, Key ErrorAction: DiscardRecord, KeyErrorLimit:0, KeyErrorLimitAction: stopprocessing. IgnoreUnrelatedDimensions:True.

When I add (only) this measure on the browser Grid I get 318597 and when I exec SELECT SUM(Y) FROM X I get 990416. The same happens when I use the date dimension for year 2015 & 2016. The data are for testing. Any thoughts from where to start? Is wrong the sql query compared to SSAS measure?

1
ssas

1 Answers

0
votes

Problem is likely to be in the dimensions that relate to this measure-group. Your SQL query is covering the entire fact table. Your result in the browser is only covering that part of the fact table that corresponds to a dimension member in every dimension that slices it. There must be rows in the fact table that don't relate to any member in at least one dimension.

This would most likely be because some dimension members have not loaded, or (before we even get to the SSAS stage), the fact table itself already contains rows that have a missing dimension key.

A better comparison would be 

SELECT SUM(Y) FROM X
INNER JOIN Dimension1 ON [something]
INNER JOIN Dimension2 ON [something else]
...

If that gives the same result as the browser grid, there's nothing going wrong in the cube processing. If not, the problem is in the processing: probably dimension members not making it from the source into the dimension.