greater than > and less than < in MDX

Question

greater than > and less than < in MDX

icandy

Ten Centuries

Points: 1140
More actions
August 27, 2003 at 10:47 pm

#159770

How do you use greater than > and less than < signs in MDX in the following situation. I have two time dimensions, startdate and enddate. I need to find data where dates are less than the enddate and data where dates are greater than the startdates. The query needs to use two dimensions. Thanks

Viewing 15 posts - 1 through 15 (of 16 total)

You must be logged in to reply to this topic. Login to reply

stevefromOZ SSC-Forever Points: 43646 More actions · Answer 1

Hey Alan,

Have you tried using these in the WHERE clause (slicer specification)? This will slice the data to include only the dat you want. Obviously this isn't the way to go if you want to use the < and > to generate row/col members.

Have you got an example fo your MDX to date?

Steve.

icandy Ten Centuries Points: 1140 More actions · Answer 2

couldn't get WHERE clause working with what I wanted to do. Haven't got a example because I can't work out something that works.

Keith Henry Hall of Fame Points: 3759 More actions · Answer 3

MDX slices don't work like SQL where clauses. You don't have fields as such, just members of dimensions

Say you wanted all data between two dates: 31/06/2000 and today 17/09/2003 on a dimension with YMD format.

On an axis you could do:

{[datedim].[all dates].[2000].[06].[31] : [datedim].[all dates].[2003].[09].[17] }

However this is a set, and you can only place a tupel on the slice. You would need a grouping level above this (which you could build with a "with member " calcluated member syntax)

Keith Henry

According to everyone I know I "do something with computers?" for a living, so there you go.

icandy Ten Centuries Points: 1140 More actions · Answer 4

yeah thanks keith,

i've used this but its not what i want.

i need all data with dates greater than begindate and data with dates less than enddate.

Keith Henry Hall of Fame Points: 3759 More actions · Answer 5

How about

filter( {

    descendants([startdatedim].[all],[year],self_and_after)

    * descendants([enddatedim].[all],[year],self_and_after)

} , boolean filter crit )

Still returns a set though...

Keith Henry

According to everyone I know I "do something with computers?" for a living, so there you go.

icandy Ten Centuries Points: 1140 More actions · Answer 6

For those interested

Keith has provided me the following solution

with

member [Measures].TotalAcrossDateRange as '( sum( { { [Start Date].[1900].[January]:[Start Date].[2001].[January] } * {

[End Date].[2000].[January]:[End Date].[8000].[December] } } , [Measures].[Total] ))'

select

{ [Measures].TotalAcrossDateRange, [Measures].[Total]}

on columns

, [State].AllMembers on rows

from DemoWithDate

only problem now, is that it takes ages to process.

suggestions anyone?

Keith Henry Hall of Fame Points: 3759 More actions · Answer 7

That'll be the aggregations on the cube, you need to add them to start and end dates, also you don't need the dates to go off as far as they can in each direction, you can have them both across the same range

You also need a non-empty on rows to ensure that only students with values within that range are selected

Keith Henry

DBA/Developer/BI Manager

Keith Henry

According to everyone I know I "do something with computers?" for a living, so there you go.

icandy Ten Centuries Points: 1140 More actions · Answer 8

we seem to have data integrity problems with some dates of the data being year 8000 and one in 1900 thats why i had to do that :P.

I think nonemptycrossjoin does the job, but i'll test more in detail. I would like to have to not put in 1900 and 8000 and put all start dates < 1/1/2001 and end dates > 1/1/2000. would you be able to give a quick solution keith using the mdx function i posted earlier??

Thanks

stevefromOZ SSC-Forever Points: 43646 More actions · Answer 9

Have you thought about using the firstchild and lastchild functions?

Excuse the gumby names on calc members, but the following is equivalent to x <= 1997.July and y >= 1997.april

WITH

MEMBER [Time].[Bob] AS 'SUM([Time].[1997].FIRSTCHILD.FIRSTCHILD:[Time].[1997].[Q3].[7])'

MEMBER [test time].[bill] AS 'SUM([test time].[All test time].[1997].LASTCHILD.LASTCHILD:[test time].[All test time].[1997].[Quarter 2].[April])'

SELECT {[Customers].[All Customers].[USA].CHILDREN} ON 0

FROM Sales

WHERE

([Time].[Bob], [test time].[bill])

HTH,

Steve.

icandy Ten Centuries Points: 1140 More actions · Answer 10

thanks steve,

i can't seem to work out how to use that in a calculated member.

in the end we used this in a calculated member

sum( {

NonEmptyCrossJoin(

{[Start Date].[1900].[January]:[Start Date].[2000].[December]},

{[End Date].[2000].[January]:[End Date].[8000].[December]}

) } , [Measures].[Total Enrolments])

only problem now, is that when we use 4 dimensions and this measure. it takes like 10 minutes to get the results. 9 minutes slower than using SQL.

any suggestions?

Keith Henry Hall of Fame Points: 3759 More actions · Answer 11

quote:
thanks steve,
i can't seem to work out how to use that in a calculated member.

What aggregations have you added? + have you tried:

sum( {

NonEmptyCrossJoin(

{[Start Date].firstchild.firstchild:[Start Date].[2000].[December]},

{[End Date].[2000].[January]:[End Date].lastchild.lastchild}

) } , [Measures].[Total Enrolments])

Keith Henry

DBA/Developer/BI Manager

Keith Henry

According to everyone I know I "do something with computers?" for a living, so there you go.

icandy Ten Centuries Points: 1140 More actions · Answer 12

Thanks Keith

but ended up being slower.

this is going to be a great presentation to our client. if i use around 2 dimensions though, it kicks ass over to old system and the agility of the datawarehouse also makes it very appealing.

Keith Henry Hall of Fame Points: 3759 More actions · Answer 13

To be honest this is the sort of situation where direct SQL may be better. OLAP is very very good for mining style reports; How many dodads do I have in this sector where we've sold them thingeys in the last 6 months. OLTP tends to be better where you'll be going down to the actual data; Give me all the customers who have been contacted as part of this campaign, but not this other one.

Keith Henry

DBA/Developer/BI Manager

Keith Henry

According to everyone I know I "do something with computers?" for a living, so there you go.

stevefromOZ SSC-Forever Points: 43646 More actions · Answer 14

Hey Al,

Keith is spot on, I know the reasoning behind your project, but one of the most important things I've ever learnt in doing BI consulting is horses for courses. The number of times I've run into trouble becasue I gave in and used olap when i should have done oltp/transactional reporting...

But, seeing as you've come so far. One thing you could try doing (on your dev box first!) is mod your registry setting to increase the size of the "sectors" of data that sit in memory when the cube is processing and also when written to disk and then queried.

The aggregations should not have degraded performance but may not actually help if you're asking for lowest level memebr info.

HTH,

Steve.