Skip to Content
XMLA Client

Introduction

This BLOG is based offf the XMLA for analysis tool available on SDN at http://www.sdn.sap.com/irj/servlet/prt/portal/prtroot/com.sap.km.cm.docs/library/uuid/6275d190-0201-0010-96b7-ae9d6f09906e.

Recently, I did a few webinars on this topic. See these presentations:

Web Services within SAP NetWeaver 2004s BI – Create, Discover, and Consume! (PDF 1.7 MB)

Consuming a BI Web Service via the Portal – a Practical Application (PDF 1.6 MB)

Simple MDX Tool

The reason I’m writing this blog is that I don’t think people ever understood the value of the code samples delivered in this tool because the samples don’t render your BI Data. Therefore, I modified this tool such that you can enter your own XMLA web service and MDX Statements at runtime in this application and it will render your BI Data in an HTML Table!

Why use this?

There are a number of articles and BLOGs on why Enterprise Service Oriented Architecture is the best way to go. In this case, we can use this web service to pull back BI data and combine it with data from other applications. This html application can sit on the SAP NetWeaver platform or can integrate with any other platform! Recently, I did a call for the ASUG community that described the XMLA web service and QUERYVIEWDATA web service from BI. Both of these web services allow you to utilize BI Data. In this blog I’ll discuss using the XMLA web service in a 3rd party application. In the next blog, I’ll discuss using the QUERYVIEWDATA web service.

What does it look like?

image
image

How do you implement it?

It’s actually very easy to implement. The first step is to download the XMLA for Analysis tool and save this to a folder on your computer. Let’s assume this location is “C:xmla”. All you need to do is build an html file in the same folder “C:xmla” with the following source code:    

XMLA Web Service:
MDX Statement:

One thing to keep in mind is there is a line in this source code that is flagged as “‘textarea” where you will need to remove the single quote and make it “textarea”. This is due to the formatting in the SDN blog utility.

To use it, all you need to do is replace the XMLA web service with your own xmla web service and replace the MDX statement with any MDX Statement. If you need help writing MDX, you can use transaction MDXTEST in your BI system to help generate some sample MDX statements. This html application can run anywhere. It can be on the SAP NetWeaver Application server, or on a user’s desktop.

Summary

This tool is just one simple example of how you can consume BI Data in external applications. You can consume this web service in any external application that can consume web services!

To report this post you need to login first.

50 Comments

You must be Logged on to comment or reply to a post.

  1. Dirk Herzog
    Thanks a lot. We’re doing a lot of using XML/A data in third party applications and I always cursed the fact that I had no chance to do any real testing besides MDXTEST. I’ll try this tool and I guess it will help a lot.

    Dirk

    (0) 
    1. Prakash Darji Post author
      Glad it helps. Let me know what you think would be good for the next blog. I’m thinking about building a similar application using the queryviewdata web service, but you don’t have to specify an MDX statement. You can just specify a query and infocube! Let me know if this would interest you. I’m curious, what 3rd party application are you using this with?
      (0) 
  2. Steve Arone
    We’ve been experimenting with QUERY_VIEW_DATA but are having some performance problems and figuring out how to pass variables (selection parameters) to limit the results.
    We’re using the following service
    {our NW2004s server url}/sap/bc/srt/rfc/sap/QUERY_VIEW_DATA?sap-client=200&wsdl=1.1
    (0) 
    1. Prakash Darji Post author
      If you see my presentation that is the attached link above, you will see that to use variables and pass selection criteria, you must use the xmla web service, not the queryviewdata web service. I will be putting together another blog with more details on the queryviewdata web service, so stay tuned.
      (0) 
      1. Durairaj Athavan Raja
        Hi Prakash,

        Thanks for this excellent weblog and the linked documents. Have a small question. you said “you will see that to use variables and pass selection criteria, you must use the xmla web service, not the queryviewdata web service

        My i know why we have to use only xmla when we want to use variables and pass selection criteria.

        Thanks in advance.

        Regards
        Raja

        (0) 
        1. Prakash Darji Post author
          Well, I have not found a way to pass parameters such as variable values and selection criteria through the queryviewdata web service. If you know of a way to do this, I’d be very interested in how you achieve this. I find this very easy to do with the xmla web service. Therefore, I always use xmla for parameter passing and dynamic calls and use querviewdata for static datasets where I want to pull predefined queries with filter values that are built into the query…
          (0) 
            1. Prakash Darji Post author
              Thanks! Just tested this for filter values, variable values, and navigation state API commands and works great. So far, it looks like this supports all web api commands so we can interact with this web service in multiple ways. I updated my presentations above and included examples of filters and variables in there. I’ll be uploading the updates shortly. Again, I appreciate you pointing this out so I could correct the blog and presentations!
              (0) 
                1. Anton Wenzelhuemer
                  hi,
                  imho, there are lots of ressources on SDN showing how to create simple WS clients using various scripting languages as well as using one of the MS Office products, e.g. Infopath on a non-programmatic level and others of the Office family using VBA.

                  anton

                  (0) 
                2. Prakash Darji Post author
                  Yes. I’m working on that now. I’ll have a simple test client for queryviewdata that allows you to input queryname, infoprovider, querview, and will render a variable screen! I’ll also create inputs available for free characteristics with a checkbox as well. Should be pretty dynamic and show how to use queryviewdata. I’m also planning on rendering a hierarchy of queries in your roles, similar to the role menu web item…
                  (0) 
                  1. Robin Smedberg
                    Hi,

                    I’ve been using QUERY_VIEW_DATA and successfully implemented filters and variables.

                    Do you know if there is a way to change the navigation status? To change which characteristics are shown on the rows/ columns?

                    When debugging RRW3_GET_QUERY_VIEW_DATA, it seems that it doesnt support commands like SWITCH_AXES or SET_NAV_STATE.

                    BR
                    Robin

                    (0) 
                    1. Prakash Darji Post author
                      This function supports all BW 3.x web api commands, but getting the syntax quite right can be tricky.

                      For example, you have to put some values under the CMD sequence and use the appropriate escape characters:

                      “CMD_1=CMD%3DEXPAND%26IOBJNM%3D0CRM_MKTELM__0CRM_TACTIC%26DATA_PROVIDER%3DDP”

                      (0) 
  3. Dieter Laude
    Hi Pradesh,
    I am right know working on a project where the access to BI 2004s query content into a model of the web dynpro application is necessary. We also have the obligation to transfer a couple of parameters to the query and get back the query result set into the model for further processing. How could I do this? Could you send me a possible solution?

    Thanks in advance

    dlaude@gmx.net

    (0) 
    1. Prakash Darji Post author
      If you’re using the xmla web service, then you can write and mdx statement to bring back the data however you want.

      If you’re using the queryviewdata web service, the presentation in the links above show how to pass filter and variable values to this web service. Did you see the slides in the presentations above?

      (0) 
  4. Rob Moore
    Hi there
    I’m working through the SAP “How to consume data within Yahoo Widgets” document which uses the QueryViewData Web Service.
    We do not have ABAP stack version of NW2004s having not yet upgraded to the Java stack.
    I have two questin that I hope you can help me with:
    1. Is it practical to do the above with the BW system as above? Nothing in the document says it is not!
    2. When connecting to the J2EE server are there specific port numbers that must be used? We use WSADMIN & configure the J2EE server path, but then get a page not found error when we try to open the Web Service homepage.

    Thanks in advance!
    Rob

    (0) 
  5. RAJESH SARIN
    Hi Mr Prakash,

    I have set up “Query_View_Data” taking reference from your Weblog
    Work with XMLA Web Service for BI Data in External Applications! –> Document : Web Services within SAP NetWeaver 2004s BI – Create, Discover, and Consume! –> Page Nos. 26 to 42.

    I am able to test my Query Webservice from my BW system using transaction WSADMIN & the Request as well as Response are successful.

    Now I am trying to create a Visual Composer – iView.
    In this iView, I am trying to define the webservice (Tools –> Define Webservice).

    The URL which I enter for the webservice is :
    http://:/sap/bw/xml/soap/queryview?wsdl
    Port I am using same as my VC / EP Port.

    I am getting an Error Message : Invalid Webservice URL

    Am I missing anything ?
    Please suggest.

    Thanks and regards – Rajesh Sarin (09987014089)

    (0) 
  6. Freek van Hoof
    Hello Prakash,

    Im currently working on a project wicht uses the Query_VIEW_DATA webservice to distribute BI data to a .Net application.

    All goes well except for the fact that we have not yet succeeded to make our query language dependant (e.g. english, dutch). Is there any way we can distribute our queries in different languages while using just one system user.

    I hope you have a solution to my problem.

    (0) 
    1. Prakash Darji Post author
      Hey, you should be able to have it be language dependent. However, when you establish your connection to the BI system, you will need to set the logon language to the language you want.

      Cheers,
      Prakash

      (0) 
  7. Philipp Landmann
    Hi Prakash!

    this blog is really helpfull.
    I´m looking for a reference or HOW-TO how Web API commands can be used with query_view_data Web Service and which commands can be used at all.

    And hints?

    Best regards,
    Philipp

    (0) 
  8. Ajay Kumar
    Hi Prakash,
       Thanks a bunch for putting together this blog it really helps. I have followed the steps in “How to Consure BI Data Within Yahoo Widgets” document and try to use the QUERY_VIEW_DATA web service.
       I get an error at the Request and Response step as follows…

    “An error has occurred. Maybe the request is not accepted by the server:

    CX_INVALID_TRANSFORMATION:An exception with the type CX_INVALID_TRANSFORMATION occurred, but was neither handled locally, nor declared in a RAISING clause.The transformation /1BCDWB/WSS0050220190704439404 could not be executed”

    I checked that the service is active in SE80 as suggested in the document.

    Any idea why this could be happening ?

    Thanks,
    Ajax.

    (0) 
  9. Calum Davidson
    Hi,
    I’m trying to test the xmla interface in our system.

    I have identified the correct URL for the service and can hit it direct from a browser and see xml being returned:

    http://…./sap/bw/xml/soap/xmla?sap-client=030

    So far so good I think.

    I have downloaded the html tool and entered the above address into the XMLA Wed Service text box.

    I have gone to transaction mdxtest and generated a default expression from one of our queries and entered that in the MDX Statement text box.

    Then when I click on submit I get an error:
    Line: 61
    Char: 2
    Error: Permission denied

    Line 61 is:
    objXMLHTTP.open(“POST”, URL, false);

    Is this permissions error related to a config issue in SAP itself or some issue with how I am running from my desktop?

    Any suggestions would be much appreciate.
    Kind Regards,
    Calum.

    (0) 
  10. Viswadeep Sunkara

    Hi Prakash,<br/><br/>I have followed the steps specified in your PDF and configured the query_view_data and i recieve the message “Cannot download WSDL from http://DB1DB1.na.com:8001/sap/bc….: Itis not allowed to access this service”. Do i need to enable some other service (WSDL).<br/><br/>XML generated from the execution:<br/>  <?xml version=”1.0″ encoding=”utf-8″ ?> <br/>- <wsdl:definitions targetNamespace=”urn:sap-com:document:sap:soap:functions:mc-style” xmlns:http=”http://schemas.xmlsoap.org/wsdl/http/” xmlns:n0=”urn:sap-com:document:sap:rfc:functions” xmlns:soap=”http://schemas.xmlsoap.org/wsdl/soap/” xmlns:tns=”urn:sap-com:document:sap:soap:functions:mc-style” xmlns:wsdl=”http://schemas.xmlsoap.org/wsdl/” xmlns:xsd=”http://www.w3.org/2001/XMLSchema“><br/>- <wsdl:types><br/>- <xsd:schema xmlns:xsd=”http://www.w3.org/2001/XMLSchema” xmlns:tns=”urn:sap-com:document:sap:rfc:functions” targetNamespace=”urn:sap-com:document:sap:rfc:functions” elementFormDefault=”unqualified” attributeFormDefault=”qualified”>

    (0) 
  11. Victor Figueroa
    Hi, thanks for your time.

    Maybe you can have ideas for this problem, we have Query using var and listing only values the 0COUNTRY defined in Auth.

    I have problems with the use of the subject to S_RS_AUTH in BI 7.
    For example, my object is ZCOUNTRY_CL and used 0COUNTRY with the “CL”.
    In the “Value Help” from Visual Composer displays all countries, while in BI7 shows only those countries that are within the authority.

    The idea that I can think of is to create a ZBAPI to call a Query that I return the country of approval, Using for this RSCRM_BAPI or something similar in BI 7.

    I would appreciate if you could indicate some other option to upload in the “Value Help” values of authority, or give me any suggestions to improve my idea.

    Any idea is read Query via Z BAPI for use in Match Code the Value Help.

    Thank you again.

    (0) 
  12. Witalij Rudnicki
    Hi,
    I heard that there are no plans to further enhance or improve MDX-based BW interfaces, including XML/A due to BObj acquisition. Just maintenance.

    Is it correct?

    Thanks,
    -Vitaliy

    (0) 
    1. magic lhq
      How to Enhance the SAP standard WebService XMLA or others
      Now we will show the SAP BW data for other application, we apply the SAP standard WebService XMLA to retrieve the BW Cube or ODS data for external system, but the authorization for data scope is a question, I didn’t know how to pass the logon user name and password to the BW system,  the SAP standard WebService XMLA did not provide the paramters for input these variants. So I want to enhance this webservice but didn’t know how to do it, if anyone know this please tell me, thank you very much!
      (0) 
  13. magic lhq
    Thank you for this  good article!
    Now we want to publish the BW WebService to other server, then user can call this webservice from this server which have been set visible of the internet, because the BW server is very import and save the secret information for the company, so we want to publish the webservice to other server for internet user can call it.
    If you know this please tell me, thank you very much!
    (0) 

Leave a Reply