Hello all,

I'm running into a unique situation where I'm not able to get the
desired results via an MDX query.

I have a fact table that look like the following:

create table FactLabor (
ID int not null,
EquipHierarchyKey int not null,
LaborMinutes int null
)

ID EquipHierarchyKey LaborMinutes
1 11631 60
2 9165 30


The dimension table looks like this:
create table DimEquipHierarchy (
EquipHierarchyKey int not null,
Level1EquipID int not null,
Level2EquipID int null,
Level3EquipID int null,
Level4EquipID int null,
Level5EquipID int null
)

EquipHierarchyKey Level1 Level2 Level3 Level4 Level5
11631 88965 66714 NULL NULL NULL
9165 71013 66714 NULL NULL NULL
234 23433 55555 7898

The dimension table represents a machine in a manufacturing
environment. When labor is "charged" to a machine, the OLTP system
creates 1 record for each level of the hierarchy. This is changed to a
flattened dimension table in ETL that will have 1 record for each
unique hierarchy arrangement.

I have created 1 hierarchy in the cube. This hieararchy contains
levels 1-5 (1 starting on the top). One of the requirements of this is
to ask a question like this: "Give me all labor minutes that occurred
for machine X and anything that is a child of X".

In SQL, my where clause would do something like this WHERE Level1 =
66174 or Level2 = 66714 or Level3 = 66714. This would get me all of
the dimension table records that this machine was involved with.

In MDX I tried something like this to get all of the cases where mach
id 66714 existed at level two or three (through the hierarchy i
created).
SELECT [Measures].[LBR MINT NBR] ON ROWS,
{[DIM EQUIP HIERARCHY].[Hierarchy].[LEVEL2 EQUIP ID].[66714], [DIM
EQUIP HIERARCHY].[Hierarchy].[LEVEL3 EQUIP ID].[66714]} ON COLUMNS
FROM [DW DIMENSION]

For some reason, the minutes associated with only one of the dmension
records show up (on level2). If I do the same MDX, but ignore the
hierarchy, I get an error saying that members belong to different
levels in the hierarchy:
SELECT [Measures].[LBR MINT NBR] ON ROWS,
{[DIM EQUIP HIERARCHY].[LEVEL2 EQUIP ID].[66714], [DIM EQUIP
HIERARCHY].[LEVEL3 EQUIP ID].[66714]} ON COLUMNS
FROM [DW DIMENSION]

If instead, I only query for level 2 (without the hierarchy), I get the
correct results:
SELECT [Measures].[LBR MINT NBR] ON ROWS,
{[DIM EQUIP HIERARCHY].[LEVEL2 EQUIP ID].[66714]} ON COLUMNS
FROM [DW DIMENSION]


I know this is a pretty long post, I apologize. I just want to make
sure that the scenario is understood. It is probably as simple as a
setting or a change in the MDX query.

Any pointers would be greatly appreciated.

Regards,

Dan

Hello David,

looks like the DimEquipHierarchy can contain data on each level of the
hierarchy.
Did you set it up as a regular hierarchy, where the higher levels usually
don't contain data, or as a parent child hierarchy, where you can have
members with data on any level?
In any case the higher level by default aggregates the values from the lower
levels.
Do you use AS 2005 or 2000?

HTH
Helmut
Supporting PASS: www.sqlpass.de; www.sqlpass.org


<daniel.lenz (AT) qg (DOT) com> schrieb im Newsbeitrag
news:1158157440.781483.60730 (AT) h48g2000cwc (DOT) googlegroups.com...

Judging by the behaviour you describe, you must be using AS2005. From
your data it looks like machine 66714 has two different parents. Is this
correct?

I think there are two possible scenarios from what you describe:

1) If the hierarchy is the correct way around with level 1 at the top
and any machine can have more than one parent, then you probably need to
look into some sort of many-to-many dimension relationship, but I can't
think of an easy way to set this up with multiple levels.

2) It is probably more likely that the hierarchy should be the other way
around with level 5 at the top. If this is the case then it will be
easier to model.

It sounds like your dimension records possibly start out in a parent
child structure. I don't know how big your dimension is, but it sounds
like it may be easier to model and query if it were not flattened and
left as a parent child hierarchy. This would avoid the need to have to
list out all the levels when trying to filter for a given machine id and
you would naturally get all the child machines aggregating up the
hierarchy.

--
Regards
Darren Gosbell - SQL Server MVP
Blog: http://www.geekswithblogs.net/darrengosbell

In article <1158157440.781483.60730 (AT) h48g2000cwc (DOT) googlegroups.com>,
daniel.lenz (AT) qg (DOT) com says...