InVitroCousin("Dimension1.MemberA","Dimension2.Mem berB") ?

Promise... this will be my last post for now. Actually the last one was to be it until I ran into something else!

I just read your article about the Cousin() function (is it obvious I'm all over the place reading a bit without order? ;)), and thought for a moment that I could use it in yet another problem I have...

As I mentioned earlier, my cube has four time dimensions. Mainly the Order Date, and a Refund Date.
Underlying to the aggregation data provided to MSAS, refunds are tied to the orders to which they refer, quite obviously.

So this allows me to report, say for a given month how much revenue came from specific orders, and how much refund was issued against these orders.

Now here is the kicker: obviously it will happen that refund are issued the next month, for instance... Say a customer orders the last day of January and not satisfied is issued a refund during February. The first level of reporting is suficient in most cases, however, for financial purposes, we want to "synchronize" refunds, that is, we want to be able to see" revenue in January: $10,000, refunds issued in January: $2,000, refunds issued against orders placed in January $2,200 (because $200 were issued in February).
So there is a litle sublety to deal with.

The way I went about this is yet again to use a separate cube and mount the "synchronized" data into a virtual cube. Basically I create a refund cube where I tie both the Order Date dimension and First Refund Date dimension against the same refund date field. And that works like a charm. Of course I went a little "smarter" that this because my main dataset being quite heavy (5 million pre-aggregated records), I didn't want to incur a 20 minute load time. So I created a subset of data containing only the refund-pertinent information.

While this works, yet again, you'll have understood from my previous post already, that I hate having to maintain useless aggregation data when the information is already available in the main dataset! It makes the system more complex that it realy needs to be and prepares the field for future issues.

So, reading about the "Cousin()" function I thought... "well, essentially the [synchronized refund value] is the sum of values where the refund date equals the current order date and the order date changed to unfiltered (All)"

So if my selection for [Order Date] is [2001] then I would want the value for [Refund Date].[2001], therefore something along the lines of: (Cousin([Order Date].CurrentMember, [Refund Date]), ...[Other Dimensions].CurrentMember..., [Order Date].[All], [Measures].[Refund Amount]).
My (little) understanding is that this won't work because Cousin() only works within the same dimension. conceptually I can even understand why, as it has no way of knowing that even 2 similar/clone-like time dimensions really are, well... similar. But yet, there seems to be a valid assumption that I should be able to translate a selection against a specified dimension against an identical selection onto another similar dimension.

Of course I did think about using LookupCube, replacing in the string occurences of "[Order Date]" by "[Refund Date]" but as we now know LookupCube has a serious limitation when it comes to integration with Excel (at least that I know of and unless yuo do have a solution for that first post I won't be able to use it).
Besides the "Replace" VBA function actually gives me an error (oh yes, what's up with that? if I can't replace strings, how can I programmatically generate sets and tuples?).

How would you do it? Is the solution of reloading the same data with re-worked dimensional ties the only way?

Thanks again,

Seb

So, in my "tradition" of finding things after I ask about them, LinkMember([Order Date].[YQMD].CurrentMember, [First Credit Date].[YQMD]) would seem to somewhat do the job at the node level, but runs into the same issues as LookupCube, obviously...

Any idea?


Seb