Missing Data/MDX Query Problems

I've also posted this on microsoft.public.sqlserver.olap.

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

[3155 byte] By [DanL] at [2007-12-25]
# 1

Dan,

You should be able to get the "OR" behavior you are looking for by putting your selection in a set in the WHERE clause. Try the following:

SELECT

[Measures].[LBR MINT NBR] ON COLUMNS
FROM [DW DIMENSION]

WHERE ({[DIM EQUIP HIERARCHY].[Hierarchy].[LEVEL2 EQUIP ID].[66714], [DIM
EQUIP HIERARCHY].[Hierarchy].[LEVEL3 EQUIP ID].[66714]})

HTH,

Steve

StevePontello at 2007-9-3 > top of Msdn Tech,SQL Server,SQL Server Analysis Services...
# 2

Steve,

I've tried that recommendation along with the other queries I listed above, but it doesn't include all of the values it should. I've traced it down to this: When a piece of equipment has 2 parents (due to a slowly changing dimension), both parents aren't included in the result set.

I've managed to hack my way around it, but i'm not sure how I can get this to be easy for a "user" to utilize with Excel.

I've created 2 user hierarchies. 1) is a hierarchy that starts with parents at level 1 and all the children under it. 2) is a hierarchy that starts with the children at level 1 and all its parents "under" it in the designer.

I then am able to use the "parent to child hierarchy" (1) and put it in the rows part of the cube browser. I then have to use the "child to parent" hierarchy (2) to do the filtering. I drag it on top and then choose MDX and use the syntax that you specified above.

I'm still not understanding why analysis services doesn't understand it when I have the parent at level 1, but it works.

Thanks for the suggestion,

Dan

DanL at 2007-9-3 > top of Msdn Tech,SQL Server,SQL Server Analysis Services...

SQL Server

Site Classified