MS SQL Server OLAP - All Member Formula and migration to 2005

Author

All Member Formula and migration to 2005

Don

2006-03-24, 7:40 am

Hi,

I have problem with Scope definition.

I have cube in AS2000 with simple Time dimension (only Year and Month), and
few other dimensions.

Time dimension has 'All Member Formula'
iif([Measures].CurrentMember.Name = "Nr objects",
Sum([Time].CurrentMember.Children,[Measures].CurrentMember),
Max([Time].CurrentMember.Children,[Measures].CurrentMember))

Same expression is in Custom Rollup Formula of Time.Year level.

All Measures have Sum aggregation.

When I migrate Cube to AS2005 formulas was migrated to Scope functions
SCOPE ([Time].[Time]);
This = Case
When [Measures].CurrentMember.Name = "Nr objects "
Then Sum([Time].CurrentMember.Children, [Measures].CurrentMember)
Else Max([Time].CurrentMember.Children, [Measures].CurrentMember)
End;
END SCOPE;
and
SCOPE ([Time].[Time].[Year].members);
This = Case
When [Measures].CurrentMember.Name = "Nr objects "
Then Sum([Time].CurrentMember.Children, [Measures].CurrentMember)
Else Max([Time].CurrentMember.Children, [Measures].CurrentMember)
End;
END SCOPE;

In 2000 cube formulas on Time dimension affect Measures of all other
dimensions, but in 2005 cube, Scope works only for Time dimension.

How I can get that functionality in 2005. I try to define Scope for every
dimension but with no success.

Thank You very much,

Don

Deepak Puri

2006-03-24, 8:26 pm

Hi Don,

Can you give some samples of how results differ from AS 2000 to AS 2005
- it's not clear what is meant by: "In 2000 cube formulas on Time
dimension affect Measures of all other dimensions"?

- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.droptable.com ***

Don

2006-03-27, 3:29 am

Hi Deepak,

Thanks for answering.

I did solve the problem by defining two measures

First one is [Measures].[cube Nr object]), and have source on fact table
with Sum aggregation

Other one, defined after [Time] Scope definition, is
CREATE MEMBER CURRENTCUBE.[MEASURES].[Nr object]
AS 'Max([Time].CurrentMember.Children, [Measures].[cube Nr object])';

When I work with other dimensions [Nr object] return Max agregation, and
[cube Nr object] return Sum.

In 2000 I did not have to define new measures. In 2000 all members always
return Max aggregation.

Thx,
Don

"Deepak Puri" wrote:

> Hi Don,
>
> Can you give some samples of how results differ from AS 2000 to AS 2005
> - it's not clear what is meant by: "In 2000 cube formulas on Time
> dimension affect Measures of all other dimensions"?
>
>
> - Deepak
>
> Deepak Puri
> Microsoft MVP - SQL Server
>
> *** Sent via Developersdex http://www.droptable.com ***
>

Sponsored Links