Post reply

Aggregate SUM problem

  • April 16, 2003 at 3:33 pm

    #159846

    I am new at cubes, and am haveing some problems with how the default aggregate sum function for a measure is working. In my fact table I have a column R that has reading scores in it. There are only integers or null values in the column. If I execute SQL against the underlying datasource this is what I get :

    select sum(r)from sms_assess_benchmark

    91145 as the answer

    select count(r) from sms_assess_benchmark

    29040 as the answer

    select avg(convert(decimal,r))from sms_assess_benchmark

    3.138601 as the answer

    as you can see, the average is correct based on the above numbers.

    Now if I create a measure for reading based on the same table, the sum comes back as :

    5005094 as the answer

    using the count agg I get 1558122

    If I create a caluclated member : [Measures].[Reading_SUM]/[Measures].[Reading_Count]

    I get 3.21

    None of the above are correct answers. Even more interesting if I use the AVE function instead of my formula, I get a totally different number of 160.2

    Any help on how these functions are working would be greatly appreciated!!!

    Thanks,

    Mike

  • April 16, 2003 at 7:58 pm

    #454459

    Hi Mike,

    Quick question - Do you have dimension tables in your model also? If so, are you sure your joins are correct? When the cube processes (if done manually), prior to closing the processing window on completion, have you checked the record count of the trxn records read in? This should equal the record count of the fact/txn table - does it?

    I have seen people have issues before where the figures are all coming out wrong because they are reading in multiple copies of their trxn records due to problems with the joins between fact and dimension tables (e.g. the foreign key in the fact table matches several rows in the dimension table, or even just the way in which the tables are joined (maybe snowflaking dimensions) causes multiple rows to be returned).

    You can test this query quite easily, either by copying and pasting it out of the processing window in query analyser, or by logging it (overkill ) and then re-running it.

    Regarding your averages and counts,get the sum working first and the others should be a lot easier to work out afterwards.

    HTH,

    Steve.

    Steve.

  • April 17, 2003 at 9:56 am

    #454578

    Thanks a bunch,

    That was exactly the problem. I joined it to a table with multiple rows, when it should have been joined to a table one level higher in the oltp relational database. Again thanks, the numbers look correct now.

    Mike

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply