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

Measure Groups in AS2000

Hi,

I have a lot of CurrencyMeasures, > 80, and a would like to create something like a 'measuregroup' in AS2000. I e Euro, Dollar should be measuregroups that contain measures, i.e Group_Euro_InvoiceAmount, Group_Euro_TaxAmount.

Do anyone have some ideas about this?

Hi,

From my understanding, measure groups are related to a fact table. Therefore if all of your measures are coming from the same fact table, I doubt you will be able to send them to different measure group.

If all of your measures are coming from the same fact table maybe you should look into the display folder property to separate them in a logical way.

Another soution could be to split your fact table into multiple named query (one named query per currency) and use those named query in your cube. This would allow you to have one measure group per currency

HTH,

Eric

|||

Larra,

If you are in AS2000 and not planning on moving to 2005, you can achieve the same result by creating multiple cubes, one per desired group, and then creating a virtual cube to pull them together.

If the source fact table is the same for all your measures, this will not be very efficient unless you add filters to your cube source to limit the rows read for each cube.

Depending on the client tool being used, you may not achieve the segmentation of measures that you are looking for though, as all measures in a virtual cube appear lumped together just as they would if you built a single cube.

|||

Thanks Clayton,

The client is using Cognos PowerPlay as the client tool to view AS2000 cubes. Do anyone know how PowerPlay is treating the measuregroups from the different cubes in the virtual cube?

_

Larra

Measure group related question

I have a cube with two fact tables, two measure groups. I also created few calculated measures.

On client side I see two measure groups and below the calculated measure.

Is there any way I can show that calculated measure in one of the measure group. It's easy for end user to see number measure and % measure both next to each other.

I hope this can be possible using MDX some how update mdx for measure group or some thing...

Thank you - Ashok

Ashok,

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

|||

You are the man Thanks. Is there any way to move a measure from one group to another. I know If I change underline Table/View it can be done but is there any way in cube design time I can move measure from one group to another.

-Ashok

|||

Ashok, moving a measure from one measure group to another is not currently supported in AS 2005.

--Artur

meassure groups with different amount of dimensions

Hi everybody,

I've got a Fact Data table with a value and 16 dimensions.

Now I want to create a second measure group Color with a value and 3 dimensions.

I've filled this table with values and id's for each dimension.

Bu when I make an mdx query with a measure from the FactData and a measure from the second measure group (Color), only the first measure has a value, the second (the measure from Color) is null.

Is these something I've forgotton to set in the cube?

thanks in advance

Filip

Hi,

If you run a simple query (below) do you get two columns of data in the results?

select {[Measures].[Measure Group 1],[Measures].[Measure Group Color]} on 0

from [Cube]

results:

Measure Group 1 Measure Group Color

123231 123213

If not I suspect something else is wrong, perhaps check that you have set up the joins between the dimensions and the facts correctly. If you do get values in both, perhaps it is worth while posting your query.

Hope it helps,
Matt

|||

Hi,

that query runs.

but I have another problem now, this measure group I've created for storing color information, only can't have a Sum or Count as aggregation.

I cannot see the 65280 or ... value for the color I want to use.

I didn't included a period dimension to this measure group.

Is that the cause?

Filip

|||

Hi,

Sorry for delay I was investigating. If the measure is not aggregatable then you will get Null in the measure unless you go down to the granularity in which the data is held at. This is an area not that familar with and finding it difficult to prove.

But if you don't include a dimension in measure and the measures are aggregatable it will not causes nulls to appear, you get funny results. e.g.

select {[Measures].[Measure Group 1],[Measures].[Measure group 2]} on 0,

[Dimension only on measure group 1] on 1

from [cube]

Results:

Measure Group 1 Measure Group 2

dim a 45 1234 1234 being the total in measure group 2

dim b 23 1234

dim c 78 1234

Sorry I could answer it more positively.

Matt

|||

thank you for your help!

it works at a certain level.

at the granularity level, it works fine,

but once it starts aggregating, the value 256 becomes a sum of x times 256.

I have to solve that problem...

thansk for you help

Filip