Measures that depends on other measures

I want to have a measure that depends on other measures so have I understood it correctly if I sum up my options like this:

A) I can make a calculation where I can basically do anything but it gets evaluated every time at querytime. This calculation can either be per query (using with member), per session (create member) or intrinsic to the cube (under calculations) but in each case it's still evaluated at querytime.

B) I can create my dependent measure and set it's measureexpression. This way the answer is permanently stored in the cube but I can only use simple binary exps like MeasureA*MeasureB

C) I can create my measure with some dummytarget, use writeback to fill it in and then convert the writeback partition to be the normal readonly partition.

D) I can precalculate this in SQL in the sourcedatabase as another column and just read it in as a normal measure.

I want to do a more complicated thing then B allows and A seems too slow from what I've tested. C I haven't tested but since it writes the complete facttable back to a new table in the sourcedatabase it seems to be more workintensive - unless what you want to do is more easily done in mdx then sql. Otherwise D is the only option left. Would this be a correct description of my options or have I missed something?

Could you describe the kind of cube and calculation you have in mind - I don't think that you can generalize which option is best for all cases?|||

Well some highlevel general guidelines is exactly what I'm trying to find out - I don't have a specific cube right now

Of course not one option being best for all cases but rather roughly what kind of cases each option is good for and especially if there's any further options I've missed here.

To reiterate what I got so far: take A if it's fast enough. B if you prefer additional queryspeed over storagespace and the measuredependency is simple enough. Then I'm a little at a loss over C vs D - C feels like you're writing the other measures twice which ought to slow it down while D means you only have to write the actual dependent measure (provided you have writeaccess to the source ofc). On the other hand perhaps some computations are easier formulated/faster in MDX then SQL which would speak for C. Any thoughts here?

|||

This option:

D) I can precalculate this in SQL in the sourcedatabase as another column and just read it in as a normal measure

will typically be used for a measure which can be defined at the leaf level of all dimensions, since loading data at non-leaf members of a parent-child dimension will introduce its own issues. It also isn't as practical where the calculation involves multiple measure groups/ fact tables. I'm not sure about the relevance of option C, if writeback is not required in your scenario.

|||

"for a measure which can be defined at the leaf level of all dimensions"

Yes this was the scenario I had in mind. And yes I agree C feels awkward and that it would feel neater/more practical to solve it "inside" the cube rather then in SQL but the speed of dynamical calculation is just too slow. Anyway now I know that can't really be done - thanks for the help.