Dears

I have a financial cube, developed using SQL Server 2012. It has a weekly partitions. I noticed an abnormal increase in revenues in a day within a week. I returned back to the original table and made a query to calculate revenues of that day and the result was normal and different from cube result. I did the same with the view that the measure group is based on, and the result also normal.

I reprocessed that partition (full process), and deleted it and created it again and processed it and nothing changed.

Why the cube result is different from the table or view result? What could be the reasons behind that, and what are possible solutions?

I appreciate your assistance.

Regards

Hi 

There could be many reasons for this but here are two things I'd check first.

There may be an overlap in the partitions, for example if you had two partition with these partition queries.

select * from dbo.actsales where transactionDate between '2015-01-01' and '2015-01-07'

and

select * from dbo.factsales where transactionDate between '2015-01-07' and '2015-01-14'

you would get duplicate transactions as '2015-01-07' would be included into both partitions.

Secondly if you are using a changing field such as a last update date for you partitioning definitionthen you may find that transactions are "shifting" between partitions and if you are not processing all the affected partitions then you would again have duplicate transactions.

The best way to "debug" this would be to get a count of the transactions per partition and compare this to what is in your cube. If they differ then you can find the partitions that are incorrect an isolate the problem. 

• Proposed as answer by Darren GosbellMVP, Moderator Sunday, July 12, 2015 1:43 AM

Dear MichaelAdrianJohnson

Thanks for your replay, and accept my apologies for replying late :)

-For the overlap issue: I am using a time stamp boundary in the form of 20150701000000 and 20150701235959, in other words, the granularity is of Seconds level, so that the probability of overlapping is QUITE low. In additions, I checked the day before and the day after, and they are normal and very similar between the cube partition and DB table.

-For the second option, shifting: The time column is not updated, so there is no possibility of shifting.

Please any other ideas or clues?

Regards

Can you run a processClear on that partition (which will remove all the data from that partition) and then see if you still have data for the problem date? If so there must be an overlap in your partitions and the easiest thing to do would be to do a processFull on that measure group (assuming that it's not too large)