December 16, 2010 at 5:10 am
hi
i'm trying to create a non-commatative measure, one that is a sum of distinct trade IDs, called volume
so if two people do a trade of 100, then the cube will show
who|volume
person a| 100
person b| 100
total| 100
also i am adding a currency conversion, so if the trade is £100...this can be converted into (say) $157
and as a final challenge/annoyance it has to have the ability to rebase by the trade length...
so a trade of £100 for ten days...if rebased (using a dimension similar to the currency converter) to two days becomes £20
at this point, i'd welcome general ideas how to do it
i have succeeded in doing the first two bits (non-cummatative & ccy conversion) by:-
1) creating a table at the trade_id level (the main deals table has two entries for buy/sell and consequent pairs of amendments/cancellations)
2) creating a DealsDistinct measure group and measure, that is a distinct count of the trade IDs in the main table
3) creating a VolumeTradeID measure that is a sum of the volume on the trade ID table created in (1)
4) creating a measure DealComponents, a count of the rows in the main table
5) creating a VolumeTradeIDIntermediate
iif(isempty([measures].[DealComponents]),null,
[Measures].[VolumeTradeID]/[measures].[DealComponents])
associated with Deals Distinct measure group
6) creating a measure Volume
iif(isempty([measures].[DealComponents]),null,
sum
(
descendants
(
[DealsTradeID].[TradeID].CurrentMember,
[DealsTradeID].[TradeID].levels( 3 )
)
,
[Measures].[VolumeTradeIDIntermediate]
)
)
associated with deals distinct measure group
7) running the currency conversion wizard and converting [VolumeTradeID], can provide the MDX...but there's a fair bit
so it works except for the rebasing...
but i'm not sure how to progress and it's slow too
a few questions
1) would i be right in thinking that measure groups whould be one-for-one with fact tables? or is there some other logic on how many i should have....?
2) did i really need to create the trade ID level table (it's a pain) ?
3) how do i do the rebasing...try and tinker with the currency converter wizard MDX?
4) when i do rebasing, i need "Local" as a rebase day too...i see that using dimension.type CurrencyDestination creates the local entry in the currency...will i have to treat my rebase dimension as a currency maybe
appreciate this is tricky....that's why i'm stuck
ta
January 4, 2011 at 3:14 am
bump
😀
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply