SSAS IA64 Server Fun: Using VBA functions in MDX

Occasionally I have the pleasure of working with Analysis Services 2005 on an IA64 (64 bit Intel Itanium) servers and on the whole, most things work the same. But there is the occasional small  difference that can cause some frustration.

Background: In Analysis Services 2000, you could not use VBA functions in MDX calculations as the VBA library was COM based and had not been ported to the 64 bit platform. In SSAS 2005, the core VBA functions have been implemented in a .Net library which will run on the IA64 platform.

The Issue: The only catch is the on a 32 bit machine, you can either just call the function or prefix it with a VBA! prefix.

eg

LEFT([Customer.CurrentMember.Name,10)

or

VBA!LEFT[Customer.CurrentMember.Name,10)

Solution: However on the IA64 platform, both of these syntaxes will fail. The trick is that the VBA functions actually exist in a .Net assembly called VBAMDX. If you prefix your function calls with the name of this library they will work on both the 32 bit and the IA64 platforms.

eg.

VBAMDX.LEFT[Customer.CurrentMember.Name,10)

[Update Nov 6 2006] furmangg mentioned in the comments that he is not having this issue on his IA64 server. I think this highlights an issue with the IA64 platform, it's adoption rate has been so slow that when you do hit any issues it is not clear if it's a bug or a configuration issue or something else. Hence why I decided to post about this behaviour, even though it sounded strange. If you hit this issue you will have to choose if you want to live with the VBAMDX work around, or cross your fingers and re-install.

Print | posted on Sunday, October 29, 2006 12:09 AM

Comments on this post

# re: SSAS IA64 Server Fun: Using VBA functions in MDX

Requesting Gravatar...
Darren, will you post the link to the bug report on connect.microsoft.com when you report this? I'd like to keep track of when this will be fixed.
Left by furmangg on Oct 31, 2006 1:12 AM

# re: SSAS IA64 Server Fun: Using VBA functions in MDX

Requesting Gravatar...
Darren, we're on IA64 with SP1 + the "select all" hotfix... and I'm not seeing this behavior luckily.
Left by furmangg on Nov 06, 2006 5:34 AM

# re: SSAS IA64 Server Fun: Using VBA functions in MDX

Requesting Gravatar...
Interesting... We have had a few other strange things happen, so maybe it is just this one server. My client is actually seriously considering moving away from IA64 to x64. The production servers are setup in an Active-Active cluster, still running SQL 2000. We have the 1 Dev server setup in a "single node" cluster and have had numerous little issues like this. We have found (at least in Australia) that getting support for the IA64 platform is, at best, "difficult".
Left by Darren Gosbell on Nov 06, 2006 7:45 AM

# re: COMPARING DATES

Requesting Gravatar...
I have three columns
AsOs date, ReivewDate, and No Of OSP

I need a count of OSP whose ReviewDate is less than Asof date

so i wrote a query something like this


with member [Measures].[test] as
iif([RieviewDate].[Review Date].currentmember<[RieviewDate].[As Of Date].currentmember,
,count([Measures].[No Of OSP],"not reviewed"))
select [Measures].[test] on 0,
[Dim LOB Details].[LOB Level1].members on 1
from [CMLGroupNewRevised]


but the format of the reivew date and AsOfDate is like this

[RieviewDate].[Review Date].&[2004-06-11T00:00:00]

[RieviewDate].[As Of Date].&[2008-04-01T00:00:00]



the above query is not giving me right answer

can u please tell me why?

i guess its because i m comparing string (2008-04-01T00:00:00])
whether its gerater than or not

i think i need to use vba function but i dont know where to use that vba function in this particular query

Left by PRASANNA KJ on Sep 24, 2008 2:23 AM

# re: SSAS IA64 Server Fun: Using VBA functions in MDX

Requesting Gravatar...
One of the reasons is that doing a less than comparison will not work as this is equivalent do doing:

(RieviewDate].[Review Date].currentmember, Measures.DefaultMember) < ([RieviewDate].[As Of Date].currentmember, Measures.DefaultmMmber)

You could work around this by testing if one of the members intersects with a ranged set from the other attribute, but I am not sure if this would work in your case as I do not know how your attributes are related and how they are linked to the fact table.

The test using an intersection would look like the following:

Interset(RieviewDate].[Review Date].currentmember null:[RieviewDate].[As Of Date].currentmember).Count > 1
Left by Darren Gosbell on Sep 24, 2008 7:43 AM

Your comment:

 (will show your gravatar)