January 29, 2011 at 3:02 am
Hi
i am having a database with date & time dimension separately. Data has granularity of 1 min. I need data based on filter like from 2001-01-12 12:02:20 to 2001-01-20 22:22:20. I created the below mdx query for that. Here date range is working fine but it is giving data only for selected time limit and not considering time range.
SELECT [Measures].[Value] ON COLUMNS,
([Dim Date].[Date Key].[Date Key].ALLMEMBERS * [Dim Time].[Time Key].[Time Key].ALLMEMBERS )
ON ROWS FROM
( SELECT ( [Dim Time].[Time Key].&[00:00] : [Dim Time].[Time Key].&[16:39] ) ON COLUMNS FROM
( SELECT ( [Dim Date].[Date Key].&[20110107] : [Dim Date].[Date Key].&[20110110] ) ON COLUMNS FROM
[Performance]))
Any one plz help me on this.
Thanks in advance
Deepka
February 14, 2011 at 2:38 pm
Indeed, your MDX just does what you asked it do do.
Because you created 2 seperate dimensions (date and time) with no relationship (parent child hierarchy). The MDX creates first a sub-cube between 2 date ranges
this is subcubed with the time-ranges
resulting in
startdate - starttime
startdate - time 2
startdate - time 3
startdate - Endtime
|
|
Enddate - starttime
Enddate - time1
Enddate - time2
Enddate - time3
Enddate - EndTime
Telling you this is the key to a solution.
You need to include only those tuples into your set that belong to startdate, time >= 2010-01-01 00:00 hh and <= 2010-01-10 16:39
This being accomplished by the set operations UNION, INTERSECT and DISTINCT.
I solved your issue with by using UNION.
SEE BELOW:
Remark: This does not include the special condition when startdate equals enddate[/i]
WITH
SET timeperiod
AS
UNION( UNION ( {[Dim Date].[Date Key].&[20110107].nextmember:[Dim Date].[Date Key].&[20110110].prevmember} *{[Dim Time].[Time Key].[Time Key].MEMBERS }
, {[Dim Date].[Date Key].&[20110107]}*{[Dim Time].[Time Key].&[00:00]:[Dim Time].[Time Key].&[00:00].lastsibling })
, {[Dim Date].[Date Key].&[20110110]}*{ [Dim Time].[Time Key].&[16:39]: [Dim Time].[Time Key].&[16:39].firstsibling })
SELECT
[Measures].[Value] ON COLUMNS
, timeperiod ON ROWS
FROM [Performance]
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply