Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Date Dimension Filtering Expand / Collapse
Author
Message
Posted Tuesday, July 22, 2014 1:06 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, September 12, 2014 1:57 PM
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
Post #1595251
Posted Tuesday, July 22, 2014 1:49 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 2:51 AM
Points: 3,076, Visits: 3,194
Post your query, so someone may help you easier.



Igor Micev,
SQL Server developer at Seavus
www.seavus.com
Post #1595270
Posted Tuesday, July 22, 2014 2:22 PM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Tuesday, November 18, 2014 11:25 AM
Points: 637, Visits: 2,143
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.
Post #1595275
Posted Tuesday, July 22, 2014 4:05 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, September 12, 2014 1:57 PM
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])))
Post #1595302
Posted Wednesday, July 23, 2014 3:53 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 2:51 AM
Points: 3,076, Visits: 3,194
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,
SQL Server developer at Seavus
www.seavus.com
Post #1595400
Posted Wednesday, July 23, 2014 9:18 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, September 12, 2014 1:57 PM
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.
Post #1595536
Posted Wednesday, July 23, 2014 9:58 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 2:51 AM
Points: 3,076, Visits: 3,194
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,
SQL Server developer at Seavus
www.seavus.com
Post #1595569
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse