Missing Data/MDX Query Problems
I've also posted this on microsoft.public.sqlserver.olap.
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

