netterdotter


Hi,

I'm very new to AS and MDX so I'm probably not using the correct terms here, sorry about that.

I'm using a cube created by PerformancePoint Server. Among other things, I have the dimensions Project and Customer. Each Project has what PPS calls a dimension property which is linked to Customer. In AS I assume that this is added as an attribute in the Project dimension.

What I would like to do is query out all the values for each Project and its corresponding Customer for a certain month (a bit simplified in this example).

The code below does almost what I want, except that it of course gives a result for each Customer with each (open) Project, instead of just the Projects belonging to that Customer.

select

{[Customer].[All Members].Members}*

{descendants([Project].[IsOpenCustomer].[True],,leaves)}*

{[Measures].DefaultMember}

on columns,

{[Time].[Base View].[Month].&[200709]}

on rows

FROM Financial

Thanks!




Re: New to MDX. How to use "links" between dimensions

Deepak Puri


Are the dimensions: Customer and Project directly "linked" via attributes, or only indirectly linked via the fact table In the latter case, you could try the Non Empty prefix, like:

Code Block

select

Non Empty

{[Customer].[All Members].Members}*

Descendants([Project].[IsOpenCustomer].[True],,leaves)}

on columns,

{[Time].[Base View].[Month].&[200709]}

on rows

FROM Financial







Re: New to MDX. How to use "links" between dimensions

netterdotter

Thanks for the reply. The dimensions are linked directly through attributes. I'm going to use the PPS form for forecasting, so the Non Empty solution won't work as I need to show every project for every customer, provided the project belongs to said customer.






Re: New to MDX. How to use "links" between dimensions

Deepak Puri

In that case, could you describe the attribute structure of the Customer and Project dimensions, and how they're linked - presumably, this follows a standard pattern for Performance Point-generated cubes Also, are any links configured in the cube between them, such as via Referenced or Many-to-Many relationships






Re: New to MDX. How to use "links" between dimensions

netterdotter

Yes, it's the standard way PPS generates cubes, so I'm not entirely sure about how everything works on a lower level.

Project has the following DimensionAttributes:

Customer

Customer_Description

Customer_Description_Name

IsOpen

MemberDescription

MemberId (key)

MemberName

ProjectCode

SequenceNumber

SourceMemberID

Customer has the following:

CustomerCode

MemberDescription

MemberId (key)

MemberName

SequenceNumber

SourceMemberId

Project also has a two hierarchies, the releveant being one which groups by IsOpen and Customer

The linking, which created thhe Customer attributes in Project was done in PPS by adding a linked dimension property to Project.





Re: New to MDX. How to use "links" between dimensions

Deepak Puri

Based on the above, I 'm not sure whether the Customer key attribute (MemberId

) corresponds to the Customer attribute in the Project dimension. Assuming it does:

Code Block

select

Generate([Customer].[All Members].Members,

{[Customer].[All Members].CurrentMember}

* Exists([Project].[IsOpenCustomer].[Customer],

{([Project].[IsOpenCustomer].[True],

LinkMember([Customer].[All Members].CurrentMember,

[Project].[Customer]))}))

* {[Measures].DefaultMember}

on columns,

{[Time].[Base View].[Month].&[200709]}

on rows

FROM Financial