MS SQL Data Warehousing - add two time hierarchies to a dimension?

Author

add two time hierarchies to a dimension?

Word 2003 memory Leakage

2005-10-12, 11:23 am

I got problem when trying to add two exactly same time hierachies to a
dimension.

There are two fields StartTimeID, and EndTimeID, which point to Time
dimension table by TimeID. I would like to use these two time hierachies to
restrict my query.

Something like SQL: Select ... from ... where StartTimeID > 1000 and
EndTimeID < 2000 for a time range specified.

I am testing this. Is this possible in MDX after I have the hierachies?

Guangming

Word 2003 memory Leakage

2005-10-12, 11:23 am

I found the solution: add one more named set (like View) of Time table and
join these two time ID fields to these two Time dimension tables. Everything
should be ok.

"Word 2003 memory Leakage" wrote:

> I got problem when trying to add two exactly same time hierachies to a
> dimension.
>
> There are two fields StartTimeID, and EndTimeID, which point to Time
> dimension table by TimeID. I would like to use these two time hierachies to
> restrict my query.
>
> Something like SQL: Select ... from ... where StartTimeID > 1000 and
> EndTimeID < 2000 for a time range specified.
>
> I am testing this. Is this possible in MDX after I have the hierachies?
>
>
> Guangming

Darren Gosbell

2005-10-17, 1:24 pm

I think you will still have issues, the two MDX ranges will be evaluated
independantly, effectively resulting the equivalent of a logical OR
between the two conditions.

eg: Select ... from ... where StartTimeID > 1000 OR
EndTimeID < 2000 for a time range specified.

Because multiple rows in the fact table typically rollup into a single
cell in the cube these sort of queries are difficult to implement in a
cube.

I don't know if anyone else out there has any experience with this?

--
Regards
Darren Gosbell [MCSD]
< dgosbell_at_yahoo_do
t_com>
Blog: http://www.geekswithblogs.net/darrengosbell

Sponsored Links