How can i query Microsoft Analysis Services using MDX from inside a Java application?

How can i query Microsoft Analysis Services using MDX from inside a Java application?
LVL 15
dave4dlAsked:
Who is Participating?
 
mrcoffee365Commented:
Your description sounds good.

If you look at the first link I gave you, it describes how to create an Http post in Java.  The XML is sent in the body of the Http request, so if you look at the code samples, it is in the text sent with the post after all of the header variables are sent.

If you haven't sent HTTP requests programmatically before, then I recommend googling for how to do it -- there are tons of references online for it.  It's also included in many Java books (and other programming books).

Sun has great Java tutorials, including this area about sending HTTP posts:
http://java.sun.com/docs/books/tutorial/networking/urls/readingWriting.html

We use HttpClient as a helper class from jakarta commons, which is very helpful if you are doing a full-blown request/response action, although it might be more than you want to deal with at first:
http://jakarta.apache.org/httpcomponents/httpclient-3.x/

Other links about sending HTTP requests with Java:
http://www.javaworld.com/javaworld/javatips/jw-javatip34.html

The best thing to do is to try it.  Have you tried to use the code samples and information I've given, and make an HTTP request to your server?  Then you'll understand more of what the problems are, and be able to give us more information about the problems you're running into.
0
 
mrcoffee365Commented:
It appears that Microsoft Analysis Services communicate via HTTP, so you should be able to send and receive XML messages via HTTP from your Java program.  This is a good starting article on doing that:
http://sqljunkies.com/WebLog/mosha/archive/category/442.aspx
0
 
dave4dlAuthor Commented:
thanks for your help!  that article was enlightening.  

Have you ever seen or could you write a snippet of java code that queries the cube (using xmla)?
0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
mrcoffee365Commented:
I don't have code that goes against Microsoft Analysis Services -- we don't have it installed.

Have you tried it, and does something not work?

There are many code examples of making HTTP requests from Java, for example:
http://www.experts-exchange.com/Programming/Languages/Java/Q_20370019.html

You'll need to know the port on which the MAS is listening (I don't know if it's port 80).  You'll need to construct an XML message, then put that in the body of your post to that port.  

This document has a sample XML request in XMLA:
https://www.sdn.sap.com/irj/servlet/prt/portal/prtroot/docs/library/uuid/9d8dd190-0201-0010-069b-98b83362f6a3

If you have documentation of the MDX server, then the only Java-specific part is how to make a request to an HTTP listener, and the above link should help you there.
0
 
dave4dlAuthor Commented:
Thanks for taking the time to reply.  I get that we need to query via a http request using xmla.  What i don't get is what the http request would look like.  Would you mind posting an example of what the http request would look like (just tell me what the POST data would look like for a request to msmdpump.dll).
0
 
mrcoffee365Commented:
The 2 links I provided above were
1.  The Java code for making an HTTP request
2.  An example of the XML for making an XMLA request to an MDX listener.  So I think I've posted the examples.

Have you tried them?  What errors have you found?

Have you tried the Microsoft documentation?  This is documentation for configuring the OLAP interface in SQL Server:
http://www.microsoft.com/technet/prodtechnol/sql/2005/httpasws.mspx
Presumably you have already done this, but I'm confused by your question about how to send a request to the configured URL.

So the http request would look like the request in the second link (the SAP documentation above).

I suggest that you try writing the Java application code to make the http request, using as an example the code in the first link I gave you.  The body of the request would be XML similar to the example request in the second link.  The URL you post your request to is described at the bottom of the Microsoft documentation.

0
 
dave4dlAuthor Commented:
I might be misunderstanding your posts, or maybe i am not being clear enough with my question so i will write down my concept of what you are saying and you can tell me where i am wrong:

In order to query MSAS from java we have to use XMLA and in order to do this you have to set up msmdpump.dll on the server and point it to your target MSAS server (i have done this).
To submit your query you build an http request (for msmdpump.dll) in java.  This http request is sending the xmla in a multipart/form variable using the POST method.  The response from msmdpump.dll should contain the xmla with your result set.

Please tell me if this is not correct and if it is not, what is wrong with it.  If it is correct, what is the name of the post variable i place the (request) xmla into?
0
 
mrcoffee365Commented:
I've been thinking about your question, and I wonder if you have all of the tools you need to do what you asked about?

Do you have an existing Java application to which you want to add a query against the MDX server?  If not, you need to get the JDK from Sun:
http://java.sun.com/javase/downloads/index.jsp

Follow the installation instructions, including adding JAVA_HOME to your environment variables (it makes life easier) and adding the [java_home]/bin to your command line path.

Then you'll be able to compile Java programs.  You'll be creating a Java program which makes HTTP requests to your MDX server.  Your Java program is either a standalone program, in which case it has a main method in it.  The simplest Java program to post an HTTP request with parameters in the body of the request is here:
http://www.informit.com/guides/content.aspx?g=java&seqNum=44&rl=1

You'll need the jakarta httpclient jar to run this program, which you can get from the link I gave you previously:
http://jakarta.apache.org/httpcomponents/httpclient-3.x/index.html

If you can compile and run the sample program from the informit link above, then you can begin setting up the XML for the MDX request to your MDX server.
0
 
dave4dlAuthor Commented:
mrcoffee365,

I seriously appreciate the time you have put into helping me with this question.  It is way above and beyond the norm.  I have written programs that make web requests (and catch responses) before in C# but this is my first time in Java.  I have a bit of reading to do to digest all the links you have given me but i have a pretty clear idea of what i need to do.  Sorry to leave the question open so long but I will be done with this within the next week.

I hope you have a good evening tonight!
0
 
mrcoffee365Commented:
If you've written Web requests in C#, then you can use a lot of the structure of that code in your Java program.  The names of the classes and methods are different (slightly), but the code itself is almost identical.  You'll establish a connection (UrlConnection or actual socket connection -- the 2 Java examples above each show a different way), assign any values to the headers that you need (e.g., method), then send the body to the connection.  Then catch the response.

If I were you, I'd create a small standalone Java program which does all that, posting to the URL for  my MDX server, and see what a simple Get does (you can test the URL first by using telnet to go to the MDX server, to see if it's listening).  Just print the result to stdout (or System.out.println, the Java way to print to the console).  Once you are doing the basic communication, you can worry about the exact XMLA request which will get you the response you need.
0
 
dave4dlAuthor Commented:
Thanks a lot for all your help!!
0
 
mrcoffee365Commented:
Glad to help -- good luck.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.