July 13, 2011 at 10:27 am
Hi,
I have this strange situation where the week dimension Grand Total for a measure, say NumberOfUsers, is different from the sum of NumberOfUsers for all days in a week. PLEASE NOTE: This is so only for some weeks while other week show the correct grand total i.e Grand Total = sum of NumberOfUsers across 7 days.
Have searched a great deal :hehe: but to no good. :w00t:
July 13, 2011 at 10:46 am
How is a week defined? And is it the same definition that you use for verification?
It might be an issue where NumberOfUsers of a Sunday is assigned to the wrong week.
For weeks with NumberOfUsers = 0 the result would be identical.
Please note that I used Sunday only as an example...
July 14, 2011 at 12:26 am
Hi Lutz,
The Week is defined Sunday through Saturday. Also, it is actually the Time Dimension I have with Hierarchies as :
1. Year> Qtr > Month > Day
2.Week - Day with WeekName > Day, among some other hierarchies as well.
On the browser, I have Week-Day and a measure NumberOfUsers with arbitrarily inaccurate Grand Total for some weeks and correct for others. The Measure NumberOfUsers has Aggregation operation as DISTINCT COUNT and the data for number of users is present at day level which is later aggregated to week level.
I am perplexed, can summing up of DISTINCT COUNT aggregated measure from day level to week be a reason for this?
July 14, 2011 at 2:22 am
I am perplexed, can summing up of DISTINCT COUNT aggregated measure from day level to week be a reason for this?
It depends.
What do you expect to be a valid result for the sum of the following values: (1,1,2) and (2,2,4,4)? Do you expect it to be 4 or 7? 4 would be the result of SUM(COUNT(DISTINCT)) whereas 7 would be the result of SUM(COUNT()).
July 14, 2011 at 3:48 am
If NumberOfUsers (distinct count of Users) each day were as below:
Sunday = 10
Monday = 10
Tuesday = 20
Wednesday = 20
Thursday = 30
Friday = 30
Saturday = 40
I expect that these values add up to give 10 + 10 + 20 + 20 + 30 + 30 + 40 = 160. That means, I simply want days values to get added.
In this case, Week Grand Total is actually greater than sum across 7 days for some weeks. There is no specific trend as one week grand total seems fine but the next one is messed up.
July 14, 2011 at 7:16 am
From BOL http://msdn.microsoft.com/en-us/library/ms175623.aspx:
SSAS aggregrate functions fall into three categories: additive, semiadditive, and nonadditive.
DistinctCount is nonadditive, which is why you don't get the results you expect when you compare the sum of the daily counts with the weekly counts.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
July 14, 2011 at 8:05 am
Drew,
It appears in this case NumberOfUsers having DISTINCT COUNT Aggregation function cannot be aggregated along any dimension (going by the BOL definition) . I am more inclined to then consider it an arbitrary behaviour of SSAS to produce few Week level aggregated values which are perfect sum of values across 7 days, while some are inaccurate.
Do let me know if we have a way :hehe: around this issue which I am definitely not aware of. 😀
July 14, 2011 at 8:51 am
It's not an arbitrary behavior, it's just that you can't predict the value for the week based on the values for each day.
If the same five users log in every day for a week, the distinct count for each day is going to be five, and the distinct count for the week is ALSO going to be five, because you only count each user once per day at the day level, but once per WEEK at the week level. You're expecting them to be counted once per day at both the day and the week level, and that's just not the way it works.
That's why it says that "the measure must be individually calculated for each cell in the cube."
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
July 14, 2011 at 10:59 am
Got that. Thank you Lutz and Drew. 🙂
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply