MeasureGroups at different granularity in the UDM

We have run into an issue with Report Builder and was wondering if anyone else has experienced this problem. We have two different measure groups. The first group is transactional at the daily grain and the other is an accounting balance table at the month grain. The measures in the second group are semi-additive. Everything against the cube is working fine. However, when a report model is generated against the cube, queries cannot be built between the semi-additive measures and the date dimension. The transactional table is fine. Has anyone else seen this behavior? I should also note that the month key is the same key as the last day in the month for business reasons. Month account balances should be converted to foreign currencies on the basis of the month end conversion rate and we are only tracking a single conversion rate table. We could always redesign this if needed.

I’m afraid that Report Builder models over a cube can only expose relationships from a measure group to a dimension that are at the key granularity. Other relationships (from the accounting balance measure group in your case) are ignored. The only alternative is to model in the cube as being at the day level (related to the last day of the month), though this would effect how it is seen through other clients.

|||

Thanks for your answer even that's not the answer I was hoping to get. I originally had the Account Balance measure group associated with the last day of the month but the query performance was very poor. By changing the association to the month level, a representative query that was running for minutes returned in less than a second. Even with a NonEmpty crossjoin of six levels, one with 130,000 members and another with 5,000 members! Gee, think AS 2005 is a bit of an improvement over AS 2000? Therefore, switching back is not an option.

Any chance this will be fixed in the future? Because I actually think the interface for Report Builder is more intuitive than ProClarity. We just haven't really found an Adhoc query tool for AS that we really like yet.

sql

MeasureExpression Property - How to use

I have a cube with several MeasureGroups.

I want to provide a calculated measure "Average Sale Price" which is calculated as "Gross Revenue" / "Product Count".

Both the "Gross Revenue" and "Product Count" measures belonging to the same measure group ... "Royalty Statement". Ideally I would like the "Average Sale Price" measure also to belong to the "Royalty Statement" Measure Group also.

I am therefore trying to use a MeasureExpression calculation on a regular measure, as opposed to a Calculated Member to achieve the calculation. (or can someone advise how to make a Calculated Member appear within a specific Measure Group ?)

On the measure properties
- "AggregateFunction" is set to "Sum"
- "MeasureExpression" is set to "[Measures].[Gross Revenue] / [Measures].[Product Count]"

On deploying the cube I get ... "Error 1 Errors in the metadata manager. The 'Product Count' right operand of the measure expression of the 'Average Sale Price' measure cannot belong to the same measure group. "

Can I really not have a MeasureExpression with the numerator and denominator in the same Measure Group ?

I have futzed with things a bit including getting the cube to process by using a denominator from a different MeasureGroup, and playing with the Source object binding.

What am I missing ?

Thanks in Advance

Marcus

No. I do not think that you can use the same measure group in measure expressions. This is the new version of Lookup cube(MDX) in AS2005 with limited support for calculations except (*, /)

You can achive the same with a calculated member.

Regards

Thomas Ivarsson

|||

Marcus,

You can easily associate your calculated measure with any existing measure group. To do this, please follow these steps:

- Open your project in SQL Server Business Intelligence Development Studio & double click your cube

- In the Cube editor, go to the Calculations tab and double click your calculated member "Average Sale Price"

- In the toolbar, just right from the "Form View" and "Script View" toolbar buttons, click on the "Calculation Properties" toolbar button

- In the Calculation Properties dialog that comes up, select your calculated member "Average Sale Price" from the dropdown and set the associated measure group for it to be "Royalty Statement"

- Deploy the project and you are ready to go

Hope this helps,

Artur

|||

Thanks Artur, that helps.

If I do this, can I also assume that the Dimension Usage characteristics for that Measure Group now apply to the Calcuated Member ?

Marcus

|||

Unfortunately the answer is no. This is a common request from customers and is planned to be implemented in our next release. For Yukon, you could use the following work around: if you want to associate the new calculated member with specific dimensions, you could set the non-empty behavior for the calculated member to a measure from a measure group that intersects with those dimensions.

Hope this helps,

Artur

MeasureExpression Property - How to use

I have a cube with several MeasureGroups.

I want to provide a calculated measure "Average Sale Price" which is calculated as "Gross Revenue" / "Product Count".

Both the "Gross Revenue" and "Product Count" measures belonging to the same measure group ... "Royalty Statement". Ideally I would like the "Average Sale Price" measure also to belong to the "Royalty Statement" Measure Group also.

I am therefore trying to use a MeasureExpression calculation on a regular measure, as opposed to a Calculated Member to achieve the calculation. (or can someone advise how to make a Calculated Member appear within a specific Measure Group ?)

On the measure properties
- "AggregateFunction" is set to "Sum"
- "MeasureExpression" is set to "[Measures].[Gross Revenue] / [Measures].[Product Count]"

On deploying the cube I get ... "Error 1 Errors in the metadata manager. The 'Product Count' right operand of the measure expression of the 'Average Sale Price' measure cannot belong to the same measure group. "

Can I really not have a MeasureExpression with the numerator and denominator in the same Measure Group ?

I have futzed with things a bit including getting the cube to process by using a denominator from a different MeasureGroup, and playing with the Source object binding.

What am I missing ?

Thanks in Advance

Marcus

No. I do not think that you can use the same measure group in measure expressions. This is the new version of Lookup cube(MDX) in AS2005 with limited support for calculations except (*, /)

You can achive the same with a calculated member.

Regards

Thomas Ivarsson

|||

Marcus,

You can easily associate your calculated measure with any existing measure group. To do this, please follow these steps:

- Open your project in SQL Server Business Intelligence Development Studio & double click your cube

- In the Cube editor, go to the Calculations tab and double click your calculated member "Average Sale Price"

- In the toolbar, just right from the "Form View" and "Script View" toolbar buttons, click on the "Calculation Properties" toolbar button

- In the Calculation Properties dialog that comes up, select your calculated member "Average Sale Price" from the dropdown and set the associated measure group for it to be "Royalty Statement"

- Deploy the project and you are ready to go

Hope this helps,

Artur

|||

Thanks Artur, that helps.

If I do this, can I also assume that the Dimension Usage characteristics for that Measure Group now apply to the Calcuated Member ?

Marcus

|||

Unfortunately the answer is no. This is a common request from customers and is planned to be implemented in our next release. For Yukon, you could use the following work around: if you want to associate the new calculated member with specific dimensions, you could set the non-empty behavior for the calculated member to a measure from a measure group that intersects with those dimensions.

Hope this helps,

Artur