Apologies for the long title, however it had to be said. I have been working with Reporting Services recently, with very complex reports, not from a reporting services point of view, but from a T-SQL stored procedure aspect. The query in question is over 1500 lines long, and needs to perform well when the report is run. So with a little bit of performance tuning we managed to get said stored procedure to run in under 4 seconds (on a fairly powerful box). However as soon as this was run through Reporting Services, the performance ground to a halt. We are talking about 1 minute 30 seconds to run this report. So after a little help from my friends (thanks to Tim for finding this blog) we found that this was the exact problem.
So - its all down to "Parameter Sniffing" !! But what on earth is parameter sniffing, and how does it help/hinder us?
As taken from a Microsoft white paper:
"Parameter sniffing" refers to a process whereby SQL Server's execution environment "sniffs" the current parameter values during compilation or recompilation, and passes it along to the query optimizer so that they can be used to generate potentially faster query execution plans. The word "current" refers to the parameter values present in the statement call that caused a compilation or a recompilation.
Just a bit of "Googling" will get you many more resources on the subject, and well worth reading.
One of the under mentioned, but long awaited feature of SQL Server 2008 is intellisense. Up to now it has always been add on available from companies like RedGate, called SQL Prompt, or SQL Assist. To get it working is SQL 2008; you pretty much have to do nothing, however sometimes it doesn't come up without pressing Ctrl + Space. So it works well with T-SQL, however does it work with MDX? In short - not really, when writing an MDX query in SSMS, you do get some intellisense prompts, however none of which are representations of the data within the cube. However you do get keyword prompts, which could be useful. So next stop, full intellisense for MDX queries - hopefully.
For those of you who haven't yet used SQL 2008, here are some screen shot of intellisense in action.
Sorry for those who have been waiting for this, but here it finally is, Part 2 of using the planning web service. In Part 1 we looked at the Web services exposed by PerformancePoint Planning. We also looked at the difficulties of de-serializing the XML, into the the object that are generated from the WSDL. Today we will look into how to get the BizSystem information from the web service.
Firstly lets remind ourselves what we are looking to get from the web service.
As we can see form XML document we have a node called Applications which contains an ArrayOfBizApplications, which contains, in this case 2 BizApplications. In turn if we try and use the object created from the WSDL in code, we find that we have an object called BizSystem. Lets have a look at this object.
public partial class BizSystem : MetadataObjectBase {
private System.Data.DataSet applicationsField;
/// <remarks/>
public System.Data.DataSet Applications {
get {
return this.applicationsField;
}
set {
this.applicationsField = value;
}
}
}
public abstract partial class MetadataObjectBase {
private string nameField;
private string descriptionField;
private IdRef bizTypeIdRefField;
private System.Guid idField;
private string labelField;
private System.Guid parentIdField;
/// <remarks/>
public string Name {
get {
return this.nameField;
}
set {
this.nameField = value;
}
}
/// <remarks/>
public string Description {
get {
return this.descriptionField;
}
set {
this.descriptionField = value;
}
}
/// <remarks/>
public IdRef BizTypeIdRef {
get {
return this.bizTypeIdRefField;
}
set {
this.bizTypeIdRefField = value;
}
}
/// <remarks/>
[System.Xml.Serialization.XmlAttributeAttribute()]
public System.Guid Id {
get {
return this.idField;
}
set {
this.idField = value;
}
}
/// <remarks/>
[System.Xml.Serialization.XmlAttributeAttribute()]
public string Label {
get {
return this.labelField;
}
set {
this.labelField = value;
}
}
/// <remarks/>
[System.Xml.Serialization.XmlAttributeAttribute()]
public System.Guid ParentId {
get {
return this.parentIdField;
}
set {
this.parentIdField = value;
}
}
}
As we can see, the BizSystem contains one property, that is BizApplication, which is quite simply a DataSet. We can see from the XML that the Biz Application is unlikely to be able to be de-serialized into a dataset, and when we do try and access the dataset, we do not get any data tables contained within the dataset, nor any data from the XML.
private void LoadBizSystem()
{
MetaDataManager.MetadataManagerWebService ppsMDM = new MetaDataManager.MetadataManagerWebService();
System.Net.CredentialCache myCredentials = new System.Net.CredentialCache();
NetworkCredential netCred = new NetworkCredential("Administrator", "LetMeIn", "AServer");
myCredentials.Add(new Uri(ppsMDM.Url), "Basic", netCred);
ppsMDM.Credentials = myCredentials;
MetaDataManager.BizSystem bizSystem = ppsMDM.GetSystem(false);
DataSet ds = bizSystem.Applications;
DataTable dt = ds.Tables[0];
}
Although not necessarily the best way, what we can do is write a custom class that the XML can be de-serialized to, thus giving us the data from the Biz System. To begin with we need to create an object that will allow us to de-serialize the BizSystem into it, and therefore allowing us to access the properties of the BizSystem. From the XML we can work out that there are a number of attributes, and to keep this simple we will just prove that we can get the data from the BizSystem; these are:
Creating this object will allow us to retrieve the the attributes of a BizApplication. The object can be created as follows:
[XmlRoot("BizApplication")]
[Serializable]
public class BizApplication
{
private string element_Name;
private string attribute_id;
private string attribute_Label;
private string attribute_ParentId;
[XmlElement("Name")]
public string Name
{
get { return element_Name; }
set { element_Name = value; }
}
[XmlAttribute("Id")]
public string Id
{
get { return attribute_id; }
set { attribute_id = value; }
}
[XmlAttribute("Label")]
public string Label
{
get { return attribute_Label; }
set { attribute_Label = value; }
}
[XmlAttribute("ParentId")]
public string ParentId
{
get { return attribute_ParentId; }
set { attribute_ParentId = value; }
}
}
This object will allow us to gain access the the 4 attributes named above, however, the BizApplication are is contained in an array of applications, so we need to create another object that contains an array of BizApplications. Also in this object we can embed the de-serialization routine, and create a method that takes an XML Document of BizSystem, to de-serialize. So we need to get the XML returned from the web service, as opposed to the attempted de-serialized version. So we need to alter what the call to biz system returns. Now this is a bit of a hack, however I can assure you that it does work. We need to change the object that is returned from the web service, so if we go to the definition of Biz System, and alter the reference so it returns an XmlNode[].
[System.CodeDom.Compiler.GeneratedCodeAttribute("System.Xml", "2.0.50727.3031")]
[System.SerializableAttribute()]
[System.Diagnostics.DebuggerStepThroughAttribute()]
[System.ComponentModel.DesignerCategoryAttribute("code")]
[System.Xml.Serialization.XmlTypeAttribute(Namespace="http://www.microsoft.com/PerformancePoint/MetadataManager")]
public partial class BizSystem : MetadataObjectBase {
private System.Xml.XmlNode[] applicationsField;
/// <remarks/>
public System.Xml.XmlNode[] Applications
{
get {
return this.applicationsField;
}
set {
this.applicationsField = value;
}
}
}
So now we have the object returned as an XmlNode, so its time to write the ArrayOfBizApplications object, and the de-serialize method.
[Serializable]
[XmlRoot("ArrayOfBizApplication")]
public class ArrayOfBizApplication
{
private BizApplication[] element_BizApplicationList;
[XmlElement("BizApplication")]
public BizApplication[] BizApplication
{
get { return element_BizApplicationList; }
set { element_BizApplicationList = value; }
}
public static ArrayOfBizApplication LoadApplications(BizSystem bizSystem)
{
//Get the Applicaiotn list as an object.
Object bizApplication = null;
ArrayOfBizApplication arrayOfBizApplicaion = null;
try
{
bizApplication = bizSystem.Applications;
}
catch (Exception err)
{
//Log Exceltion here..
//Try and Cast it! (Untested)
bizApplication = (Object)bizSystem.Applications;
}
//We know this is an XML object - it has come back from the webservice
XmlNode[] xmlNodes = (XmlNode[])bizApplication;
StringBuilder sb = new StringBuilder();
sb.Append("<ArrayOfBizApplication>");
//We Only want an array of BizApplicaiotoins
foreach (XmlNode xmlNode in xmlNodes)
{
if (xmlNode.Name.ToLower() == "arrayofbizapplication")
{
sb.Append(xmlNode.InnerXml.ToString());
}
}
sb.Append("</ArrayOfBizApplication>");
XmlSerializer serializer;
System.IO.StringReader sr = new System.IO.StringReader(sb.ToString());
XmlTextReader xmlTReader = new XmlTextReader(sr);
try
{
serializer = new XmlSerializer(typeof(ArrayOfBizApplication));
arrayOfBizApplicaion = (ArrayOfBizApplication)serializer.Deserialize(xmlTReader);
}
catch (Exception err)
{
string test = err.Message;
throw err;
}
finally
{
//Close the reader.
xmlTReader.Close();
}
// return the list of BizApplication.
return arrayOfBizApplicaion;
}
}
We finally have some data from the Get System, we can browse our objects, which has some basic data within them.
Upon more investigation and examination of the XML that is returned you can easily extend these objects to return as much data as you require from Biz System. If you have any questions, or have had more success with the Planning Web service please contact me. For now, I hope to make more progress with the web service over the coming months.