Re: Missing Data in Cube / MDX Question
- From: Darren Gosbell <jam@xxxxxxxxxxxxxxxxx>
- Date: Thu, 14 Sep 2006 22:07:37 +1000
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@xxxxxxxxxxxxxxxxxxxxxxxxxxxx>,
daniel.lenz@xxxxxx says...
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
- References:
- Missing Data in Cube / MDX Question
- From: daniel . lenz
- Missing Data in Cube / MDX Question
- Prev by Date: Re: MDX Query Syntax Performance
- Next by Date: Re: how to use less than operator on dimension in mdx queries.
- Previous by thread: Re: Missing Data in Cube / MDX Question
- Next by thread: Adomd.net ExecuteCellSet Exception
- Index(es):
Relevant Pages
|
Loading
