Post reply

data needed at different rollup level

  • December 7, 2004 at 8:20 am

    #109773

    Is there a way to include a data item in a cube but NOT have it rollup in the same way that other data items do?  For example, I have 20 orders that came in on 3 distinct mail codes.  Those three distinct mail codes have a Mail Quantity associated with them.  I want to include the Mail Quantity in my cube, but I don't want it to be summed up 20 times for the 20 orders; I only want the three Mail Quantities to be summed once.  I think there must be a way to do it.  Any suggestions?

    Thanks-

    cvm

     

  • December 9, 2004 at 7:04 pm

    #533271

    Hi Carolyn,

    If the quantity for the mail code always refers to an order (ie, postcards for instance are not mailed out with one of the mail codes for example) then couldn't you create a calculated measure that simply counts on order number or order id? Call it 'Mail Quantity' and in conjuntion with your mail code dimension you should always get the same total of mail quantity per code as reflected by the underlying data.

    Otherwise you could achieve what you seek through a Parent-Child dimension since you can use custom rollup operators in them. You could specify at what level the mail quantity would sum. However, from what you describe I am not sure a Parent-Child dimension is appropriate.

    Michael


    mhweiss

  • December 10, 2004 at 10:29 am

    #533382

    Thanks for your response.  I do think it can be solved with either a calculated member or a custom rollup but I haven't quite figured it out yet.

    The mail quantity is like a Measure and would be most useful displayed with the other Measures, but it is an attribute of a Mail Code, rather than a count or sum of the underlying data.  Assume the Mail Quantity for a given Mail Code is 15,000 (we mailed out 15,000 solicitations).  Assume we received 1,000 orders against that Mail Code.  The total Mail Quantity for those 1,000 orders is still 15,000 (the amount we mailed out doesn't change). 

    I use the Mail Quantity to figure out Response Rate, so that's why having the number is important, but it is a data item that doesn't get calculated from the underlying data - so that is really my question - how to use data in a cube without having it participate in a rollup.

    -Carolyn

     

  • December 12, 2004 at 6:08 pm

    #533453

    A very simplistic approach could be to create a second cube that uses the MailOut (ok, it's not called that but you know what I mean) table as a fact table, and has only the one measure (count of mailout ID's).  Assuming that most of your dimensional attributes are related to the mailouts (e.g. date of mailout, demographics of recipients), you could thenuse shared dimensions between the two cubes and create a virtual cube that has all measure values in it.  This will be problematic where the dimensions in the 'respodents' cube don't exist in the MailOut cube (ie the measure won't "drill down" into these unrelated dims), but at a higher level, you'll easily get a conversion ratio and also indicators of work done over time. 

    You're issue is really the same as trying to get a budget figure into an AS cube now, it's a pain.  bring on Yukon with it's multiple fact tables!!

    Steve.

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

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