Comparing two dimensions

Hi, I'm writing an MDX query in VS2003 (using RS2000) with AS2000 and I want to know how can I just display the mesaures for the members of a dimension that are equal to another dimension. For example, If I display dimension1 and dimension2 I get (not all of the members in both dimensions are equal):

Cat - Cat
Cat - Dog
Cat - Car
Cat - Horse
Dog - Cat
Dog - Dog
Dog - Car
Dog - Horse
Car- Cat
Car- Dog
Car- Car
Car- Horse
Horse - Cat
Horse - Dog
Horse - Car
Horse - Horse

But I really want to get:

Cat - Cat
Dog - Dog
Car - Car
Horse - Horse

I've tryed using FILTER and I can't make it work, need help here. When I try to use INTERSECT I get the following error:

TITLE: Microsoft Visual Studio

Query preparation failed.

ADDITIONAL INFORMATION:

Calculation error - unknown error (urn:schemas-microsoft-com:xml-analysis)

BUTTONS:

OK

Filter is the right way to go. somethign like the following should work

Select Filter( {<dim1.hier1.level1>.Member * <dim2.hier2.level2>.Members}, <dim1.hier1.CurrentMember.Name = dim2.hier2.CurrentMember.Name ) on 0 from <cube>

Note that this is a bit of an unusual request and won't perform well on large dimensions as joins are normally built into the structure of the cube rather than done at run time.

|||It worked, thanks a lot man. I'm starting to learn MDX and it's quite amazing.

Comparing strings in MDX

Hello gurus,

Is there an equivalent to the TSQL LIKE '%mystring%' function in MDX?

e.g. to filter a Product dimension to only those products containing 'IPOD' or whatever?

Thanks

Not in straight MDX, but it is possible to use stored procedure to do that. There is a open source project to build library of sprocs that few of us participate, and it has the implementation of Like function.

Check it out here:

http://www.codeplex.com/ASStoredProcedures/Wiki/View.aspx?title=StringFilters

HTH,

Mosha (http://www.mosha.com/msolap)

|||

Yes, there are VBA functions.

You can use InStr() as an equivalent of T-SQL like.

IMHO, using of self written stored procedure in this case is like using a cannon aganst sparrows.

|||

Mosha,

How nice to hear from you. I was at a talk by Chris Webb last Saturday and he was singing your praises. As I have been fumbling with MDX for the last couple of months I bought your Fast Track book this week because I feel I have been trying to run before I am fully clear on the basics.

The sprocs you mention look like a very useful extension to MDX, though I note that they are not recommended for a production environment at the moment. Vladimir's suggestion of using InStr seems to work fine in this case.

Thanks

|||Thanks for this Vladimir. That works just fine - I wasn't aware that these VBA functions were available.

Comparing strings in MDX

Hello gurus,

Is there an equivalent to the TSQL LIKE '%mystring%' function in MDX?

e.g. to filter a Product dimension to only those products containing 'IPOD' or whatever?

Thanks

Not in straight MDX, but it is possible to use stored procedure to do that. There is a open source project to build library of sprocs that few of us participate, and it has the implementation of Like function.

Check it out here:

http://www.codeplex.com/ASStoredProcedures/Wiki/View.aspx?title=StringFilters

HTH,

Mosha (http://www.mosha.com/msolap)

|||

Yes, there are VBA functions.

You can use InStr() as an equivalent of T-SQL like.

IMHO, using of self written stored procedure in this case is like using a cannon aganst sparrows.

|||

Mosha,

How nice to hear from you. I was at a talk by Chris Webb last Saturday and he was singing your praises. As I have been fumbling with MDX for the last couple of months I bought your Fast Track book this week because I feel I have been trying to run before I am fully clear on the basics.

The sprocs you mention look like a very useful extension to MDX, though I note that they are not recommended for a production environment at the moment. Vladimir's suggestion of using InStr seems to work fine in this case.

Thanks

|||Thanks for this Vladimir. That works just fine - I wasn't aware that these VBA functions were available.sqlsql

Comparing 2005 with 2004 YTD

Hi,

I would like to compare current YTD (2005) with 2004 YTD, how do I do that using MDX? ParaLLELPERIOD (Year) and PERIODSTODATE(Year) ?I am not an MDX expert, but I believe something like this should resolve it.

(parallelPeriod([Accounting Week Calendar].[Accounting Year]),[Time Series Analysis].&[1])

Time Series Analysis is a dimension itself, with one record with a field called OLAP (it can be anything) and the value is set to 1.

Thanks
Sutha