MDX equivalent of SQL Query

2
votes

What will be MDX query equivalent to:

select * from tableName where somecount > num1 and somecount < num2?

somecount is a dimension in my case (not a dimension).

As far as I have understood, WHERE Clause in MDX Queries doesn't support less than or greater than operation. somecount in my case is Timestamp. Since I want to aggregate data on parameters passed by user (can be aggregated yesterday, last 7 days, last 15 days, last month etc). So, precomputing the timestamp and storing it as Year-Month-Day-Time won't help.

3
mysqlmdxmondrian
To achieve "somecount > num1" you need to read up on the MDX Filter() function. – Magnus Smith

3 Answers

1
votes

There is a common mistake mixing SQL and MDX due to the similar syntax. but MDX is not like SQL.

I'd advise going through this Gentle MDX Tutorial to get a better understanding.

Select *

There is no equivalent of * in MDX you have to specify your axes by putting your dimensions. You can use [Measures].members to select a list of measures for example.

.. where somecount > num1 and somecount < num2?

The translation for this is using MDX subselects, strange but MDX where clause is something different. It's more like :

Select [Measures].members on 0
from (
   select {All the members that match your filter} from [MyCube] 
)

There is a couple of ways getting a list of members, check the list of MDX functions available to get an idea of all that is possible.

Good luck with your first steps in MDX.

0
votes
SELECT MEMBERS ON ROWS  FROM (SELECT Filter(filter) ON COLUMNS  FROM [FACT_TABLE])

Filter should be something like this 

Filter([TIME].[YEAR].[YEAR].AllMembers, (
[TIME].[YEAR].CurrentMember.member_caption>="2009" AND 
[TIME].YEAR].CurrentMember.member_caption<="2012" ))
0
votes
SELECT Measures.members ON ROWS,
      Dimension.members ON Columns
  FROM Cube
 WHERE somecount.value > num1 
   AND somecount.value < num2