Dimension Hierarchy - Only Show Parents With Children

May be a simple question here..

Can anyone tell me how to restrict a hierarchy to show only parents that have children e.g.

Country A

Company #1

Company #2

Country B

(No companies - do not want to show Country B in the hierarchy)

Many thanks,

Ian.

Hi Ian,

What front end do you use? What MDX queries are you issue to the server? What hierarchy do you have? I this regular or parent child?

If it is regular hierarchy, then what is your DV schema? Star or snowflake? If snowflake, then change key source for the Country attribute from Country_PK in Country table to the Country_FK in the Company table. Then you get what you want.

|||

Hi Vladimir, thanks for the quick repsonse...

1) I plan to use ProClarity Analytics Server 6.x and Desktop.. currently just using BI studio browser

2) The schema is as below.... quite a few hierarchies!

3) One of the big problems I've faced is linking all the different dimensions together, there are many more then this, due to the [Employee] dimension being linked to a time based snapshot table. See my post http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1312135&SiteID=1. How do you link all these dimensions together to reach the fact table?

*Hierarchy

Legal Entity

*Hierarchy

Legal Entity ID

PK

*Hierarchy

ParentLegalEntityID

FK

Line Of Business

LineOfBusinessID

FK

`

Geo Area

Line Of Business ID

PK

GeoAreaID

FK

Geo Area ID

PK

Name

Name

Parent Geo Area ID

FK

<Attributes>

<Attributes>

Name

Deaprtment

Department ID

PK

*Hierarchy

Parent Department ID

FK

LegalEntityID

FK

GeoAreaID

FK

Name

<Attributes>

Employee

Employee ID

PK

*Hierarchy

Parent Employee ID

FK

DepartmentID

FK

GeoAreaID

FK

Name

<Attributes>

FACT

Employee ID

FK

Measures

Many thanks

Ian.

|||How many entries have the tables Employee, Department? SSAS doesn't like parent child dimensions. It is true.|||

Hi Vladimir,

The counts for the tables are as follows: -

[Legal Entity] - 30 rows

[Department] - 320 rows

[Geo Area] - 430 rows (this is a view that has been flattern out from a many-to-many and a classifaction FK)

[Employee] ~ 10,000 rows

[FACT] - 390,000 rows

If SAS2005 does not like parent-child relationships... any clues on how hierarchies could be constructed!! ~;0)

Thanks for your help

Ian.

|||

Hi Ian,

"If SAS2005 does not like parent-child relationships..." - It would be a true problem, if you have 1,000,000 millions of employee.

Your intention is to create one dimension, containing more then one parent child hierarchy. I really have no expirience with such use case.

|||

Vladimir, thanks for the reply anyway...

Ian.

|||

Without competly understanding your problem, either restrict these blank heirarchies from yoru source SQL qeury, or use MDX as follows:

SELECT { Measures.[Measure] } ON 0,
FILTER([Heirarchy].Members, [Heirarchy].CurrentMember.Children > 0) ON 1
FROM [Cube]

Which will return only Company A.