Use attribute from dimension to multiply measure in Dynamic Cube

[shainkijain]

Hello Experts,

My first attempt at Cognos Cube designer. Everything was looking fine and now I am stuck at two issues. One of that is -

We have fact table with measures like Amount MO, Amount YTD etc. And we have one lookup table for Currency & Exchange Rate.

So requirement is to multiply measures with Exchange rate value from lookup table.

It was very easy to do in FM and all works fine there. But in cube designer, Measure dimension can only refer to one physical table. Also tried with calculated measures and used property from Exchange rate dimension to multiply with measure. But this does not give any output and calculated measure is coming as blank.

P.S. We need to use this dynamic cube as analysis package.

Thanks!
SJ

[bus_pass_man]

Yeah it's strict about the one fact table per cube rule.

You will probably need to munge the two tables together in the data base.  While you're at it, performing the calculations might be better done in the db too.

[shainkijain]

Thanks for the reply.

If I do that in database, fact table will explode by 40 times or so and that will make life hell. Only reason we want to move to dynamic cube from DMR is for performance improvement. And I think that will be difficult if something like this is done.

[bus_pass_man]

...that will make life hell.

I hope not.

Yeah anyway that's the trade off between being able to implement your application in dynamic cubes and not being able to.

Other things being equal, scanning through records in a table will be faster than scanning through two tables, joining the results, and performing calculations on them -- even if done by the data base.  This should be true for your existing FM application.

Any way you're, presumably, going to use aggregates to take advantage of dynamic cubes' aggregate awareness support.

[shainkijain]

make sense. I might give it a try to see performance implication on model. Thanks for your suggestion.

Cheers!