Need to query SAP BW from your .NET application? This is a trivial task with XML\A and C#. XML\A is a specification that allows multidimentional datasources to be queried by applications. It is how Reporting Services talks to SAP BW (http://technet.microsoft.com/en-us/library/bb508810(SQL.90).aspx).
Step 1: Find out the path to your SAP BW XML\A service, Mine looks something like this "http://<Server>:<Port>/sap/bw/xml/soap/xmla"
Step 2: Open a .NET project and create a web reference to that service, I called mine MsXmlAnalysis
Step 3: Modify the WebService Proxy Class, When .NET generates the proxy class it declares the Execute Mehtod with two arguments the ExecuteCommand and a PropertiesType. The PropertiesType is a base class for the DiscoverProperties object, but has no properties. To fix this we actually want to pass a DiscoverProperties object to the Execute Method. While this compiles fine the XML serializer has a problem serializing the request. To fix this add "[XmlElement(typeof(DiscoverProperties))]" before the PropertiesType argument in the Execute Methods Signature. It should look like this "public System.Xml.XmlElement Execute([System.Xml.Serialization.XmlElementAttribute(IsNullable=true)] ExecuteCommand Command, [XmlElement(typeof(DiscoverProperties))]PropertiesType Properties) {"
Step 4: Execute the MDX Query:
using (MsXmlAnalysis.MsXmlAnalysis ws = new MsXmlAnalysis.MsXmlAnalysis())
{
//the _userId and _password are your login credentials to the SAP Server
NetworkCredential nc = new NetworkCredential(_userId, _password);
//the url is the path you found in Step 1
ws.Credentials = nc.GetCredential(new Uri(url), "Basic");
ws.Url = url;
MsXmlAnalysis.ExecuteCommand cmd = new MsXmlAnalysis.ExecuteCommand();
cmd.Statement = "MDX QUERY HERE";
MsXmlAnalysis.DiscoverProperties properties = new MsXmlAnalysis.DiscoverProperties();
properties.PropertyList = new MsXmlAnalysis.DiscoverPropertiesPropertyList();
properties.PropertyList.LocalIdentifier = "1033";
properties.PropertyList.DataSourceInfo = "default";
properties.PropertyList.Content = "SchemaData";
properties.PropertyList.Format = "Tabular";
XmlElement elem = ws.Execute(cmd, properties);
//Do something with the element here
}
You can also query metadata out of the SAP BW using the XML\A
Here is a list of different requests you can do: DISCOVER_DATASOURCES, DISCOVER_PROPERTIES, MDSCHEMA_CUBES, MDSCHEMA_CUBES, MDSCHEMA_DIMENSIONS, MDSCHEMA_HIERARCHIES, SAP_VARIABLES, MDSCHEMA_MEASURES, MDSCHEMA_PROPERTIES. If you want to limit your search to a specific cube specify a catalog and cube specify those values also (some of the queries require catalog and cube to be specified).
using (MsXmlAnalysis.MsXmlAnalysis ws = new MsXmlAnalysis.MsXmlAnalysis())
{
NetworkCredential nc = new NetworkCredential(_userId, _password);
ws.Credentials = nc.GetCredential(new Uri(url), "Basic");
ws.Url = url;
MsXmlAnalysis.DiscoverProperties properties = new MsXmlAnalysis.DiscoverProperties();
properties.PropertyList = new MsXmlAnalysis.DiscoverPropertiesPropertyList();
properties.PropertyList.LocalIdentifier = "1033";
properties.PropertyList.DataSourceInfo = "default";
properties.PropertyList.Content = "SchemaData";
properties.PropertyList.Format = "Tabular";
MsXmlAnalysis.DiscoverRestrictions restrictions = new MsXmlAnalysis.DiscoverRestrictions();
restrictions.RestrictionList = new MsXmlAnalysis.DiscoverRestrictionsRestrictionList();
if (catalog != null && cube != null
&& catalog.Length > 0 && cube.Length > 0)
{
restrictions.RestrictionList.CATALOG_NAME = catalog;
restrictions.RestrictionList.CUBE_NAME = cube;
}
XmlElement elem = ws.Discover(request, restrictions, properties);
//Do something with the element here
}