Hierarchy MDX

Below is my hierarchy :

Program 1

Supplier 1

Supplier 2

Supplier 3

.

.

.

Supplier N

I need an MDX expression which on retrieves Suppliers for Program 1 and so on.

So right now when i browse my cube I see all Suppliers for all Programs because of which there are alot of blank rows.

I need only Suppliers unique to each Program. So the final outcome of the hierarch above should be :

Program 1

Supplier 1

Supplier 5

Supplier 7

since Program 1 has only 3 suppliers.

TIA

- Russ

Not really all that clear on the structure of your dimension. Take a look at CROSSSJOIN, NONEMPTYCROSSJOIN, NONEMPTY, EXISTS, and the NON EMPTY keyword.

B.

|||

Bryan,

Thanks for the pointers. I'll look into the Keywords that you mentioned.

However, here is my situation in a little more detail.... This is what it looks like when i browse my cube :

- Program 1

- Supplier 1

.

.

.

.

- Supplier 10

- Program 2

Supplier 1

.

.

.

.

Supplier 10

and so on..."ALL" the 10 suppliers are showing up for each program. However , Program 1 has only 3 suppliers and Program 2 has 5 suppliers. I need an MDX which can be applied as a formula at the Program level and applies to all its supplier children displaying only the ones which exist with values and not all 10 suppliers.

I could do this manuall by removing each Supplier which doesnt exist for that Program but i need a generic MDX which looks at the Program Number and applies it to its children at all levels.

Any ideas ?

Thanks

Russzee

|||

Hi russzee,

how the Programs and Suppliers are related in your cube desing? Only in fact table? Do you use any of wide spreaded SSAS Client like Excel or Proclarity or you write MDX self?

|||

Russzee,

Vladimir is trying to clarify the structure of your dimensions and cube. What is not clear is whether Programs and Suppliers exist within a single dimension or are part of two separate dimensions associated with your cube. Either one of these situations can give you a structure such as you identified.

B.

|||

Hello,

Thanks once again for the follow up..

Program and Suppliers are two seperate dimensions in SSAS 2005 and there is a Fact Table called SupplierProgram linked in a Star Schema as shown below :

Program <- SupplierProgram > Supplier

So I have dynamic Supplier row members for each Program and i need an MDX which can identify the 'Supplier' row members on the fly from their parent 'Program' Members.

My client application is Performance Point Dashboard Designer which has no MDX support in CTP_2 so I'm looking to create this MDX in SSAS 2005 and port it to the Performance Point Designer.

Thanks

russzee

|||

Here are a few queries that might get you headed in the right direction. Within a dimension, SSAS employs auto-exists functionality for this kind of thing. Across dimensions, you have to leverage a fact table to determine what represents a valid combination. These results may all look the same (except for the baseline query that demonstrates a full cross product). Still, there are subtle differences between these you need to understand before taking one path or the other.

Good luck,
Bryan

Code Snippet

select -- BASELINE QUERY

[Measures].[Reseller Sales Amount] on 0,

Product.Category.Category.Members * Employee.Employee.Employee.Members on 1

from [Adventure Works]

;

select

[Measures].[Reseller Sales Amount] on 0,

NON EMPTY Product.Category.Category.Members * Employee.Employee.Employee.Members on 1

from [Adventure Works]

;

select

{} on 0,

NONEMPTYCROSSJOIN(Product.Category.Category.Members,Employee.Employee.Employee.Members) on 1

from [Adventure Works]

where [Measures].[Reseller Sales Amount]

;

select

{} on 0,

Product.Category.Category.Members *

NONEMPTY(Employee.Employee.Employee.Members,Product.Category.Category.Members)

on 1

from [Adventure Works]

where [Measures].[Reseller Sales Amount]

;

select

{} on 0,

Product.Category.Category.Members *

EXISTS(Employee.Employee.Employee.Members,Product.Category.Category.Members,'Reseller Sales')

on 1

from [Adventure Works]

;