none
cube total doesn't equal star total

    Question

  • Hi.  One measure of mine is defined as a data type of money in my star.  There are many occasions where a single value can go out to 4 decimal places.  But by design the sum of this measure across the entire fact table goes to at most two decimal places.  SQL returns a sum of $1,123,219,801.94000000 when I take sum across a subselect in which the measure is cast as decimal(20,8).   When I drag the measure to my pivot area in the cube I get $1,123,219,801.09.

    Why?  What are my options for consistency between the two?

    My metadata for this measure in the cube shows a display format of "currency" and a source data type of "double".    

    Wednesday, September 29, 2010 5:24 PM

Answers

  • this may not prove anything, and maybe I'm forgetting the rules surrounding "process full",  but in a very similar environment, after deleting the SSAS DB and then completely rebuilding it, t-sql and the dragged cube measure match exactly.  I'm going to do the same thing in the original environment and see what happens. 

    I deleted the original SSAS database and rebuilt it entirely.  Again the two totals match even without casting the t-sql column.   That worries me a lot because I believe we use process full often on other measure group(s).

    Please dont mark this as an answer until I've had a chance to either propose that there is a bug in ssas or research any special rules surrounding the use of process full after source records have been deleted.  It's possible I've forgotten some of the rules and honestly the few minutes I had last week to find such rules turned up nothing.  I will spend more time this week looking at past posts and web articles to find the answer. 

    I did my best to understand the author at http://msdn.microsoft.com/en-us/library/ms345142.aspx and have to conclude that "process full" comes with no special baggage (until now).  It seems from that article that this processing option is supposed to totally discard prior content of the measure group before doing its thing.  For SSAS to generate different results in one measure group when db is and isnt first deleted seems to me a bug.  And it worries me.  I may post separately asking for an explanation. 

    • Proposed as answer by Kalman Toth Friday, October 1, 2010 1:36 PM
    • Edited by db042188 Monday, October 4, 2010 4:16 PM more info
    • Marked as answer by db042188 Monday, October 4, 2010 4:17 PM
    Thursday, September 30, 2010 7:52 PM

All replies

  • If you want to show value with 8 decimal places then instate of Currency try this FormatString $#,##0.00000000 


    Ani
    Wednesday, September 29, 2010 5:57 PM
  • By default the currency takes 2 decimal places.

    For the measure value in the cube, go to the properties and key-in the following value against the format string $#,##0.0000;($#,##0.0000)


    vinu
    Wednesday, September 29, 2010 5:59 PM
  • thx Ani.  We dont want to clutter our pivot areas with more than 2 decimal places. 
    Wednesday, September 29, 2010 6:01 PM
  • What exactly you want to do and what output you are expecting?
    Wednesday, September 29, 2010 6:12 PM
  • most importantly I'd like to understand why there is a discrepancy on the ssas side.

    Then, if its even possible, anything the community is aware of for rounding the cube's answer to two decimal places when the underlying sum goes to 3 or 4 places.

    Thirdly  I need to provide a tool to my qa folks for simulating in t-sql (against the star) the answer the cube will give under the same scenario.   I can probably do this myself once a clear explanation is provided for the discrepancy on the ssas side.   

    Wednesday, September 29, 2010 6:41 PM
  • I have no experience with the money data type, but I would expect that to be a fixed-precision data type, so double sounds a bit odd. If loss of accuracy occurs in the cube by converting the value to a double, then you should be able to achieve the same in your sql query by casting the field to a float inside the sum.

    You could also try to add a Calculation in the DSV where you cast your money field to another data type (eg decimal) and see if that produces more accurate results.

    Wednesday, September 29, 2010 8:07 PM
  • The default currency precision value is 2 in SSAS and it is a SSAS data type and not the SQL. In SQL it is money. This is inline with even the excel. Even in excel, if you change the cell format to currency, by default it gives as 2. So I guess that is how it is
    vinu
    Wednesday, September 29, 2010 8:15 PM
  • I tried a couple of things to simulate what the cube is doing.   If I could simulate it, I could understand it.

    First I summed the star values by chopping off completely the 3rd and 4th decimal places of all 300+ million values.  My sum was $1,123,377,195.25 which is way off so probably not what the cube is doing.

    Second , I summed a cast of my money field to decimal(10,2).   I believe sql rounds when you do this.  I got $1,123,206,036.57 which is way off so probably not what the cube is doing either.

    Does anybody know what the cube is doing?   

    Wednesday, September 29, 2010 9:59 PM
  • There could be 2 things happening

    One is round of each of the fields and then sum it up or sum it up and then round off. I guess that is why the difference.

    Example: 

    10.5663 + 10.6873 =  21.2536 = 21.25

    10.5663 + 10.6873 =  10.57 + 10.69 = 21.26

    so for millions of rows there could be a visible difference. 

    In the cube, sum is done first and then format is applied. So you would see it as 10.57 and 10.68 against the dimension and 21.25 at the grand total

    in SQL format is done first and then sum later which is 21.26

     


    vinu
    • Proposed as answer by Kalman Toth Friday, October 1, 2010 1:35 PM
    Thursday, September 30, 2010 1:05 AM
  • Thx Vinu. 

    If the cube was summing and then formatting like you describe, there would be no more than a penny difference for the entire population in my particular case.  From my first post you can see there was an 85 cent difference.

    To mitigate against t-sql being the culprit, I ran a sum in t-sql off a cast of the money column to decimal(12,4).  I then compared the result to the dragged cube value.  Today's values are updated via last eve's etl but they are $1.15 off today.  For completeness and to check if your theory that sql formats first and sums later on money columns, I reran the sum without the cast and got the same t-sql sum both ways.  So I dont believe your theory on t-sql is correct either.  BTW I ran these on tiny populations first and manually verified results with a calculator.  

    Steven, thanks but casting to float in t-sql doesnt simulate what the cube is doing.  In fact it generates a result that is within a penny of the other t-sql queries.  It is interesting that the post at  http://social.msdn.microsoft.com/Forums/en-US/sqlanalysisservices/thread/bb7bcae4-e102-482d-a4b7-60d78ec451ee seems to be suggesting that I'm doomed  till I change my sql data type to something other than money. 

    Interestingly, this measure group went thru a "process full" recently after some pretty serious maintenance to the underlying star measure table.  I'm tempted to delete the cube and rebuild it completely to see if this discrepancy somehow gets "fixed" as a result.  Maybe my "process full" hit a bug in SSAS.

    If I do either experiment, I'll post results back here whether what the cube is really doing remains a mystery or not.

    Thursday, September 30, 2010 3:30 PM
  • this may not prove anything, and maybe I'm forgetting the rules surrounding "process full",  but in a very similar environment, after deleting the SSAS DB and then completely rebuilding it, t-sql and the dragged cube measure match exactly.  I'm going to do the same thing in the original environment and see what happens. 

    I deleted the original SSAS database and rebuilt it entirely.  Again the two totals match even without casting the t-sql column.   That worries me a lot because I believe we use process full often on other measure group(s).

    Please dont mark this as an answer until I've had a chance to either propose that there is a bug in ssas or research any special rules surrounding the use of process full after source records have been deleted.  It's possible I've forgotten some of the rules and honestly the few minutes I had last week to find such rules turned up nothing.  I will spend more time this week looking at past posts and web articles to find the answer. 

    I did my best to understand the author at http://msdn.microsoft.com/en-us/library/ms345142.aspx and have to conclude that "process full" comes with no special baggage (until now).  It seems from that article that this processing option is supposed to totally discard prior content of the measure group before doing its thing.  For SSAS to generate different results in one measure group when db is and isnt first deleted seems to me a bug.  And it worries me.  I may post separately asking for an explanation. 

    • Proposed as answer by Kalman Toth Friday, October 1, 2010 1:36 PM
    • Edited by db042188 Monday, October 4, 2010 4:16 PM more info
    • Marked as answer by db042188 Monday, October 4, 2010 4:17 PM
    Thursday, September 30, 2010 7:52 PM
  • Well i feel the theory of mine is correct because i do try out before commenting. well i will come up with example during the weeken supporting my comment.

    However good to knw your problem seem to be closed.


    vinu
    Friday, October 1, 2010 2:45 PM