Post reply

problems with Time dimension and rollups

  • February 8, 2006 at 3:23 pm

    #167435

    Having a problem with my cube and the Time dimension.

    So far in my data warehouse, I have loaded 4 monthly data snapshots - and thus, there are essentially 4 members of the Time dimension that have been used (10/31/05, 11/30/05, 12/31/05, 1/31/06).

    When I browse the cube, if I slice by the Time dimension to look at the exact day of the snapshot, the "All" member sums are perfect.  However, if I drill up to specify only the month (which is more like what a business user would do), it multiplies my "All" member sums by 5.  If I look at a different day in the month, the data is completely null, as I would expect.

    Why does it suddenly multiply everything by 5 when summing the month??

    One possible design factor: one of the measures is a DISTINCT COUNT - I've read that it can mess things up.  This is an extremely important measure for this cube, though.

     

    Please help!

    Thanks

    Jeff

  • February 9, 2006 at 3:21 am

    #619713

    Hi Jeff

    Are you joining the fact table to the time dimesnion with a key or are you using dates in the fact table?

    Maybe the time dimension is reading the year value of time on the month level or just the month without taking year into account. I know you mentioned you only have 4 snapshots so this is probably not it, but I have made the mistake of not specifying year and month and ending up with the total of say all January's when I wanted January 2006 only for example.

    Is everythign multiplied by exactly 5 or are you getting the total of all snapshots at the month level?

  • February 9, 2006 at 8:48 am

    #619817

    it's joined by a key.  and everything is multiplied by exactly 5, it's not a sum.

    the annoying part is that I ran into this problem a month ago, solved it, and didn't document the solution.  Recently I had to expand the cube a bit and the problem resurfaced.  Seems like something with the join structure.

    thanks for the reply!

     

  • February 9, 2006 at 9:30 am

    #619836

    Ok, now here's a new twist - I played around with it some more, and to save time I didn't preprocess any aggregations - and the numbers WORKED.  Run the aggregations, and they multiply by 5 again.

    What's up with that?? 

  • February 9, 2006 at 3:55 pm

    #619971

    Have you checked out the Distinct Count doco from msdn (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql2k/html/distinct2.asp) ? It's a little aged but still relevant to AS2K. You may want to seperate out the distinct count measure into it's own cube (allowing you to put aggs on your "main" cube with the other measures. Also maybe consider using the count(crossjoin(....)) approach to creating your distinct cnt (if you're not already).

    Steve.

Viewing 5 posts - 1 through 4 (of 4 total)

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