May 25, 2010 at 2:18 pm
Hi
We are dicussing approaches of acessing data in SSAS from SAP BW.
1st raw data needs to be aggregated and made into cubes and this needs to be passed into downstream applications using SAP BW.
END application is an online system which will display the result but this online system gets all data through SAP and this SSAS data has to be passed through SAP.
I am new to SSAS and have no ideas about SAP, so really stuck with what should be the best approach to achieve this.
Please share your candid comments / ask any question if required.
Thanks!!
May 28, 2010 at 7:06 am
Hi All,
I have exploring various options and came across two approaches.
1.Using a WebService to query the SSAS and pass the result to the downstream applications.
2.Use a Stored Procedure using OPEN Query through Linked Server and pass the result to downstream applications.
As per the requirement downstream application expect the result in XML format.
Though i am yet to start with the WebService approach , i started working on the Stored Procedure approach and got the the data from the SSAS cubes but it was not in XML format. And then to convert the data into XML format i tried using FOR AUTO XML but this also didnt give proper format of the XML.
It would be helpful if you can suggest some materials or appraoches to implement this , and also let me know if i am proceeding in the right direction.
June 1, 2010 at 9:04 am
On converting the data to XML format,
Can u use function FOR XML for converting the data in to the required format; Note tag name has to be provided as an argument for the function.
June 11, 2010 at 10:37 am
Thanks SriHarsha for your reply , but just FOR XML doesn't give data in proper format besides client expects data to be of CLOB type. Am not very sure of this approach so started with the Web Service approach.
I have developed a web Service which queries the SSAS cubes using ADOMD.NET and from the cellset i get the data in a xml file, which will be read by the client application.
I have implementted this using a basic query and got the result in xml by manipulating the cellsets.
I wanted to know if we can build dynamic MDX queries in ADOMD.Net and how to process complex data from cell set .
//Sample Query
string sMDX = "Select [Measures].[Calls Taken] on columns,[Agent Dimension].[Active Flag] on rows from [eGlue] where [Agent Dimension].[Agent Type].[A]";
How should i build this query if i want to make this dynamic , using parameters ? Please share some exmamples or tutorials.
//Getting XML data from CellSet
StringBuilder xmlData = new StringBuilder();
xmlData.Append("<?xml version='1.0' encoding='utf-8'?>");
xmlData.Append("<ConsumptionHub>");
Axis axis = cellSet.Axes[0];
Member member = axis.Positions[0].Members[0];
xmlData.Append("<" + Convert.ToString(member.Caption) + ">");
xmlData.Append(cellSet.Cells[0].Value);
xmlData.Append("</" + Convert.ToString(member.Caption) + ">");
xmlData.Append("</ConsumptionHub>");
I just had data in 2 dimension and it was a basic query , but will it be possible to manipualte data like this if we have huge data or rather is this the right way ?
How should we return the data so that client application can use it ? Executexmlreader() returns the whole schema which is not required by the cosuming applicatipon [which is SAP PI ]. And execute cellset returns data in cellset format which will not be recognised by the SAP PI .
It will be helpful if you can share your comments on feasbility of my approach and inputs on whether its right or not.
Thanks !!
March 3, 2011 at 5:51 pm
No body know this? I am interested too.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply