SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Date Dimension Filtering


Date Dimension Filtering

Author
Message
Jay Paolucci-371454
Jay Paolucci-371454
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 13
Hello,

I have a date dimension [Facility].[Close Date] which I need to filter on. The criteria for the filter is to keep any records with [Facility].[Close Date].&[1899-12-30T00:00:00] or any record that has a [Facility].[Close Date] that is greater than a date I am passing into the query that represents the start of a reporting period. I have tried several different approaches to this including various variations on filter(), conditional statements within the filter, sub queries and various date parsing techniques to no avail. Can someone please advise me as I am brand new to MDX and have run out of ideas.

Thanks,
Jay Paolucci
Igor Micev
Igor Micev
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10394 Visits: 5157
Post your query, so someone may help you easier.

Igor Micev,
My blog: www.igormicev.com
Nevyn
Nevyn
Hall of Fame
Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)

Group: General Forum Members
Points: 3540 Visits: 3149
In this case I believe it would be easier and better to include logic in your fact table about whether or not the facility was closed at the time of the fact entry, and have a separate dimension to represent that.

Doing this in MDX gets tricky because it doesn't really treat dimension data according to the raw data coming in. It treats it based on the hierarchies you define. There are some MDX functions that are specific to date dimensions and implement some of the logic, but its not so simple as saying give me dates greater than a certain one. In this case you need to tell it to give you a specific member and every member after it, plus one other member.

That ought to be possible, but is pretty convoluted. And I don't think the mdx query should be doing the heavy lifting here. If pressed, I'd probably use LastPeriods and Prevmember to get all date members before your report date, then use except on a set of all the date members to get only the ones I wanted, and then add the default date in to the set. And assuming you didn't want to actually split up the measure by closing date, I would do all that in a calculated member within an aggregate. I think.

As I said, the much cleaner solution is to have a Facility Status dimension (with open or closed) and have code in the view defining the fact table populating it.
Jay Paolucci-371454
Jay Paolucci-371454
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 13
Here is a simplified example of what I'm trying to do. It uses the where filter that I've been trying to get to work:

select [Measures].[Is Exited] on 0,
([Facility].[Close Date].children) on 1
FROM [HUMANRESOURCES]
where (FILTER([Facility].[FACILITY].[ALL].children, ([Facility].[Close Date] > cdate('01/01/2014')) or ([Facility].[Close Date].&[1899-12-30T00:00:00])))
Igor Micev
Igor Micev
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10394 Visits: 5157
Jay Paolucci-371454 (7/22/2014)
Here is a simplified example of what I'm trying to do. It uses the where filter that I've been trying to get to work:

select [Measures].[Is Exited] on 0,
([Facility].[Close Date].children) on 1
FROM [HUMANRESOURCES]
where (FILTER([Facility].[FACILITY].[ALL].children, ([Facility].[Close Date] > cdate('01/01/2014')) or ([Facility].[Close Date].&[1899-12-30T00:00:00])))


Does the following query return rows?

select [Measures].[Is Exited] on 0,
([Facility].[Close Date].children) on 1
FROM [HUMANRESOURCES]
where (FILTER([Facility].[FACILITY].[ALL].children, ([Facility].[Close Date] > cdate('01/01/2014')) ))

Are there rows for this date [Facility].[Close Date].&[1899-12-30T00:00:00] ?

Igor Micev,
My blog: www.igormicev.com
Jay Paolucci-371454
Jay Paolucci-371454
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 13
That does not return rows, but it appears that it should as there is a 03/31/2014 close date. Could this be a date format issue? The designer of the cube is representing no close date as [Facility].[Close Date].&[1899-12-30T00:00:00], so there are many records with that as the close date value.
Igor Micev
Igor Micev
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10394 Visits: 5157
Jay Paolucci-371454 (7/23/2014)
That does not return rows, but it appears that it should as there is a 03/31/2014 close date. Could this be a date format issue? The designer of the cube is representing no close date as [Facility].[Close Date].&[1899-12-30T00:00:00], so there are many records with that as the close date value.

Definitely, you should check it out.

Igor Micev,
My blog: www.igormicev.com
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search