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.

MDX Query designer working correctly?

I've been working with the MDX query designer in Reporting Services 2005 (RTM Developer Edition) and have come across a behavior that does not seem correct.
I can build a report using the wizzard, preview it, deploy it, and all is good with the world. But if I so much as click on the Data tab in the report designer, the report becomes invalid.
Is this by design, or is there some other way to edit a report created with the MDX query designer?
Regards,
Clayton

This is known issue in Report Designer.

Here is workaround:

1. After the report wizard completes, close the report.

2. Open the report in code view

3. Edit the report removing all carriage returns and all whitespace (blanks) between the two tags

<rd:MdxQuery> and <QueryDefinition>

4. Save the report and close the window

5. Open the report in Design Mode.

MDX query designer behaving correctly in Reporting Services?

I've been working with the MDX query designer in Reporting Services 2005 (RTM Developer Edition) and have come across a behavior that does not seem correct.

I can build a report using the wizzard, preview it, deploy it, and all is good with the world. But if I so much as click on the Data tab in the report designer, the report becomes invalid.

Is this by design, or is there some other way to edit a report created with the MDX query designer?

Regards,

Clayton

This is apparently a known problem. I posted this to the Reporting Services forum as well and recieved an answer there. Heres the link:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=171034&SiteID=1

Regards,

Clayton

mdx query

I write the following MDX query that functions correctly:

WITH MEMBER [PERIOD CONTAB].[FY MONTH].[Totale] as

'AGGREGATE ( { [PERIOD CONTAB].[FY MONTH].&[2007-01-01T00:00:00] : [PERIOD CONTAB].[FY MONTH].&[2007-09-01T00:00:00] } )'

SELECT NON EMPTY { [Measures].[EUR] } ON COLUMNS ,

{

[Operating Cost]

,[COST ELEMENT].[MACRO COST].[Total Operating Cost]

,[COST ELEMENT].[MACRO COST].&[Capital Charge]

,[COST ELEMENT].[MACRO COST].[Total Contract Cost]

}

*

UNION (

{[PERIOD CONTAB].[FY MONTH].&[2007-01-01T00:00:00] :[PERIOD CONTAB].[FY MONTH].&[2007-09-01T00:00:00] }

, [PERIOD CONTAB].[FY MONTH].[Totale])

DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS

from [PMO_MDB]

If I try to insert the query inside Reporting Services substituting some data with query parameter in the following way I get an error:

WITH MEMBER [PERIOD CONTAB].[FY MONTH].[Totale] as

'AGGREGATE ( { STRTOMEMBER(@.FY_MONTH_START) : STRTOMEMBER(@.FY_MONTH_END) } )'

SELECT NON EMPTY { [Measures].[EUR] } ON COLUMNS ,

{

[Operating Cost]

,[COST ELEMENT].[MACRO COST].[Total Operating Cost]

,[COST ELEMENT].[MACRO COST].&[Capital Charge]

,[COST ELEMENT].[MACRO COST].[Total Contract Cost]

}

*

UNION (

{ STRTOMEMBER(@.FY_MONTH_START) : STRTOMEMBER(@.FY_MONTH_END) }

, [PERIOD CONTAB].[FY MONTH].[Totale])

DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS

FROM ( SELECT ( STRTOSET(@.PROJECTSTREAM, CONSTRAINED) ) ON COLUMNS FROM [PMO_MDB])

WHERE ( IIF( STRTOSET(@.PROJECTSTREAM, CONSTRAINED).Count = 1, STRTOSET(@.PROJECTSTREAM, CONSTRAINED), [PROJECT].[STREAM].currentmember ) )

CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS

The query seems sintaticcally correct !

So what happened !

Here is the error:

TITLE: Microsoft Visual Studio

Query preparation failed.

ADDITIONAL INFORMATION:

Parser: The FY_MONTH_START parameter could not be resolved because it was referenced in an inner subexpression. (msmgdsrv)

BUTTONS:

OK

Having the same problem in reporting services. Have you found anything?|||

Sounds like it's having a problem with the STRTOMEMBER function inside of the custom member definition. Since you know that your member will only be a single value, you could always write the query in dynamic mdx and pass the parameter values in. The top would look something like this:

Code Snippet

=" WITH MEMBER [PERIOD CONTAB].[FY MONTH].[Totale] as

'AGGREGATE ( { " + Parameters!FY_MONTH_START.Value + " : " + Parameters!FY_MONTH_END.Value + ") } )'

SELECT NON EMPTY { [Measures].[EUR] } ON COLUMNS , " +

It's a little tricky to get the designer to accept dynamic mdx. You have to write the original mdx query first. Then you have to click on the elipses for the dataset to bring up the dataset dialog. Then in the dialog, click the "f(x)" button to fill in the text. The dialog box that comes up will allow you to enter in the "=" sign and do the rest of your dynamic mdx.

|||

Thanks for the reply

I solved simply removing the character ' in the member definition !

Don't know why .... but now it functions -)

WITH MEMBER [PERIOD CONTAB].[FY MONTH].[Totale] as AGGREGATE ( { " + Parameters!FY_MONTH_START.Value + " : " + Parameters!FY_MONTH_END.Value + ") } )

.....

Cosimo