Autoexist when queying

Hi all,

I think it's a simple question but i can't seem to get it right:

I have a parent child dimension, with key Object, and an attribute Type. Every Object has a type, consider this example

Object Type
--
Top Model
Child 1 Portfolio
Child 2 PortFolio

When i run this query:

select
time.month.members on 0,
objects.object.members * objects.[type].members on 1
from [Dream2007]

I get this on Axis 0:
Top Model
Top Portfolio
Child 1 Model
Child 1 Portfolio
Child 2 Model
Child 2 Portfolio

How can i het the following result with only the existing combinations?:

Top Model
Child 1 Portfolio
Child 2 PortFolio

I tried non empty, but that doesn't work because they can be null. I also have an attribute relationship between Object and Type.

Any ideas?

TIA,

GJ

Maybe there's some issue with the attribute relationships - you can compare with the Adventure Works Organization parent-child dimension, where each Organization has a Currency Code:

>>

select {} on 0,

[Organization].[Organization].[Organization].Members

* [Organization].[Currency Code].[Currency Code].Members on 1

from [Adventure Works]

-

AdventureWorks Cycle USD
Australia AUD
Canadian Division CAD
Central Division USD
European Operations EUR
France EUR
Germany EUR
North America Operations USD
Northeast Division USD
Northwest Division USD
Pacific Operations AUD
Southeast Division USD
Southwest Division USD
USA Operations USD

>>

|||

I tried to set the Cardinality of the relationship to one, but this did not change it.

Actually, when i looked at it more closely, higher members have their own type, and the types of their children too. So the top object has all the types of all members, the leaf members for the PC hierarchy have only their own type (and the [all] type if i set IsAggretable to true) I only checked the top members at first, so it seemd as if it was the crossproduct of all members from both hierarchies.

Is there any way to disable this behaviour, so a member would only have the type it gets from the dimtabel row, not all its children? Or is this conceptually impossible?

I have a lot of caluculations that need to apply to a specific type of member only. I could scope on Level.Ordinal, but then i would have to keep track of which types of objects exist on which levels.

Regards,

GJ

|||

Going back to the Adventure Works Organization dimension, I think this query illustrates the issue which you raise:

>>

select {} on 0,

[Organization].[Organizations].Members

* [Organization].[Currency Code].[Currency Code].Members on 1

from [Adventure Works]

AdventureWorks Cycle AUD
AdventureWorks Cycle CAD
AdventureWorks Cycle EUR
AdventureWorks Cycle USD
European Operations EUR
France EUR
Germany EUR
North America Operations CAD
North America Operations USD
Canadian Division CAD
USA Operations USD
Central Division USD
Northeast Division USD
Northwest Division USD
Southeast Division USD
Southwest Division USD
Pacific Operations AUD
Australia AUD

>>

Comparing it to the earlier query, each member is located in both the dimension key attribute hierarchy: [Organization].[Organization], and in the parent-child hierarchy: [Organization].[Organizations]. And in the attribute hierachy, each member is only associated with a single currency. Can you give an idea of what scoped calculations you need; and could these be translated to the Adventure Works Organization dimension?

|||

I tried it with the key hierarchy, not the PC, and indead now get the results i expected.

I guess it would be logical for members in a PC hierarchy to have the attributes of it's descendants too for rolling up additive measures, because else there would be nowhere for these aggregates to go, or they wouldn't show up in queries.

The problem we have is we're looking to migrate a forecasting app we built on AS2000 to 2005. we have loads of calculated members like this one that all build upon each other

CREATE MEMBER CURRENTCUBE.[PP].[Calcs200].[WO_component_ML_maandbedrag] AS
iif(Object.CurrentMember.Level Is Object.Levels(7),
Iif(Object.CurrentMember.Properties("ObjectType") = "WO_component",
Iif(Object.CurrentMember.Properties("ObjectLevel") = "8",
iif(Tijd.CurrentMember Is Tijd.Members.Item(Val(Object.Currentmember.Properties("ObjectStartDateOrdinal"))) ,
0 + (0 + Val([Object].Properties("Component_Maandbedrag"))),
0 + (Tijd.Members.Item(Val(Object.Currentmember.Properties("ObjectStartDateOrdinal"))), [PP].[WO_component_ML_maandbedrag] )
),
0),
0),
iif(Object.CurrentMember.Level.Ordinal < 7,
0 + SUM(Filter(Descendants(Object.CurrentMember, Object.levels(7)), Object.CurrentMember.Properties("ObjectLevel") = "8" AND Object.CurrentMember.Properties("ObjectType") = "WO_component"), ([PP].[WO_component_ML_maandbedrag]) ) ,
0 + (Ancestor(Object.CurrentMember, Object.levels(7)), [PP].[WO_component_ML_maandbedrag] )
)
), SOLVE_ORDER= 11 , FORMAT_STRING='#,##.##' ;

Where we use iif(Object.Currentmember.Level or Object.Currentmember.Properties to make sure the calcs get done (only) on the right spot.

So we were pretty excited about Scope, because then we wouldn't have to use Currentmember and Filter all the time, this would eliminate all the extra things we have to do to solve performace.

Now i'm trying to understand how this works exactly, but i'm still having trouble getting my head around 2005. 2000 was alot easier in this respect.

I don't think adventureworks has anything that comes near, but i'll look into it.

Regards, GJ

|||

Unfortunately, I can't quite comprehend all the logic in the calculated member above. But for the problem of filtering members of a parent-child hierarchy with a specific property value, using "Autoexist" behavior - here's an Adventure Works query which returns members of the [Organization].[Organizations] hierarchy whose Currency Code is "CAD" - and it excludes any ancestors, since their own Currency Code isn't "CAD":

>>

select {} on 0,

Generate([Organization].[Organizations].Members,

{([Organization].[Organizations].CurrentMember,

StrToMember("[Organization].[Organization].&["

+ [Organization].[Organizations].Properties("Key")

+ "]"),

[Organization].[Currency Code].[CAD])}) on 1

from [Adventure Works]

Canadian Division Canadian Division CAD

>>

|||

PS: maybe I did learn something at last month's SQL PASS in Seattle - my memory just flashed back to a session where the use of LinkMember() in lieu of StrToMember() was mentioned (blog link below); so here's a cleaner version of the query:

select {} on 0,
Generate([Organization].[Organizations].Members,
{([Organization].[Organizations].CurrentMember,
LinkMember([Organization].[Organizations].CurrentMember,
[Organization].[Organization]),
[Organization].[Currency Code].[CAD])}) on 1
from [Adventure Works]

http://sqljunkies.com/WebLog/mosha/archive/2006/11/16.aspx

>>

SQL PASS in Seattle: What has the UDM Done For You Lately by Dan Bulos

.. 'bad' MDX functions for AS2005. StrToMember is one of them..we agreed that in that particular scenario the script indeed could've been rewritten to use LinkMember..

>>