Martyn

Martyn Bullerwell's Blog

SQL Server 2005 Service Pack 3

SQL Server 2005 SP3 is just a Beta at this stage, however it is available for download here, and it includes all the patches released to date:

http://www.microsoft.com/downloads/details.aspx?FamilyId=D22317E1-BC64-4936-A14B-7A632B50A4CA&displaylang=en

Not a huge amount of fixes, but a few for Reporting Services that caught my eye:

  • Optimised Sharepoint Integration, to make reports respond faster.
  • Connector to Teradata
  • Enhanced PDF rendering

On the subject of service packs, it has now been released that PerformancePoint Server SP2 "should" support SQL Server 2008, lets hope we get our hands on this soon. 

Connection Strings in PPS Monitoring

ODBC connection string in PPS Monitoring can become a little awkward, therefore I thought it wise to write a quick blog post with various connection strings in for a quick reference. 

Using ODBC connection strings, you can connect to many data sources including: SQL Server, Access, Oracle, Excel etc. Lets remember that PPS monitoring is best used when connection to multidimensional data sources, and the built in reports can use only Multidimensional sources.  KPI's can however use these connection strings and therefore, it is often a requirement to connect to relational stores. Here are a few examples of connections strings

  • SQL Server: Driver = {SQL Server}; Server=SERVERNAME; Database=ADVENTUREWORKS
  • Access: Driver= {Microsoft Access Driver (*.mdb)}; dbq=c:\SourceLocation\SourceAccessDB.mdb; uid=USERNAME; pwd=PASSWORD;
  • Oracle: Driver= {Microsoft ODBC for Oracle}; server=SERVER; uid=USERNAME; pwd=PASSWORD;
  • Excel: Driver={Microsoft Excel Driver (*.xls)}; dbq=C:\SOURCELOCATION\WORKBOOK.xls; ReadOnly=True;

Slow report in Reporting Services, even though the stored procedure runs so quickly?

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.

SQL Server 2008 Intellisense

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.

image image

 

Exploring the PerformancePoint Planning Web Service Part 2

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.

GetSystem

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:

  • Name
  • Id
  • Label
  • ParentId

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.

image

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.

Reporting Services 2008 - Connection Problems

Having recently rebuilt a server entirely with "2008" technology I have a small problem with reporting services.  When trying to look at the Reports, I received the error:

"The underlying connection was closed: Could not establish trust relationship for the SSL/TLS secure channel."

image

I found, that even though we are not trying to look at the secure site, the certificate causes issues.  For development and testing purposes I found it easiest to remove the certificate.  To do this perform the following steps:

1. Open Reporting Services Configuration Manager

image

2. Click Web Service URL and click "Advanced"

image

3. Remove the SSL certificates, both for IPv4 and IPv6 and click ok, and then Apply.  This will remove the SSL certificates, you should now be able to use Reporting Services 2008. 

AdventureWorks for SQL Server 2008

For those of you who wish to learn SQL Server 2008, The Adventure Works databases are available for download here for some of the same data we are familiar with: http://www.codeplex.com/MSFTDBProdSamples/Release/ProjectReleases.aspx?ReleaseId=14274

There are a number of downloads available, so choose carefully.  There are 4 main versions, however I would recommend that you download the full Zipped version, which will give you all four of the AdventureWorks databases, it is around an 85mb download.   If you download the MSI version, it does not install and configure the databases for you; however the files are placed here:  C:\Program Files\Microsoft SQL Server\100\Tools\Samples. Once downloaded, there are a few ways to load the samples, the easiest being restoring the .BAK file to SQL, remember to do this for both DW and the OLTP AdventureWorks Databases.   For the Cube, you get a project that opens in SQL Server 2008, and from my experience simply deploys (as long as you have set up security correctly).

SQL2008.AdventureWorks_DB_Scripts_v [2005|2008].[ia64.msi|x64.msi|x86.msi|zip]: T-SQL scripts and CSV files for loading all four databases in the AdventureWorks family of databases and diagrams for one year-model, either v2005 or v2008.

SQL2008.AdventureWorks_DW_BI_v[2005|2008].[ia64.msi|x64.msi|x86.msi|zip]: T-SQL scripts, CSV files and database backups for loading the AdventureWorks Data Warehouse database, diagrams and Visual Studio/BIDS projects for Analysis Services projects for business intelligence samples for one year-model, either v2005 or v2008.

SQL2008.AdventureWorks_LT_DB_v[2005|2008].[ia64.msi|x64.msi|x86.msi|zip]: T-SQL scripts and diagrams for the AdventureWorks "Lite" database for one year-model, either v2005 or v2008.

SQL2008.AdventureWorks_OLTP_DB_v[2005|2008].[ia64.msi|x64.msi|x86.msi|zip]: T-SQL scripts, CSV files and database backups for loading the AdventureWorks OLTP database and diagrams for one year-model, either v2005 or v2008.

 

PerformancePoint Planning Tuning

There have been a few questions, surrounding the performance of PerformancePoint, especially with large data volumes. There are many things that can be done to improve PPS performance and Microsoft has released a very detailed white paper on large volume performance testing and detailed tuning.  It includes a number of tests that can be run, and the results across a number of different server configurations.  Well worth a read - can be found here:

http://go.microsoft.com/fwlink/?LinkID=100209&clcid=0x409

Exploring the PerformancePoint Planning Webservice Part 1

In line with SOA architecture of many applications built today, PPS is no exception, both the Planning and Monitoring parts of PPS use a Web service's to function.  For the moment we will explore using the planning web service with a web application in .NET 3.5.

There are 2 separate web services that are exposed, these are MetaDataManager.asmx and DataManager.asmx

MetaDataManagerWebServiceDataManagerWebService

So to begin we will create a web application in order to access the web services.  Most of the web methods cannot be invoked directly from a web browser as they do not accept primitive types.   Those that do work are the following:

MetadataManagerWebService:

  • Connect - Returns a Boolean result to test an active connection to the planning web service.
  • Disconnect - Disconnects from the planning service.
  • GetSystem (bool expandModelSitesContents) - Returns all information relating to the Server that the web service is called on, this contains a lot of information, and the relevant GUID's for the applications and sites.

DataManagerWebService:

  • GetOverrideStatusChoices - returns the Work Unit Statuses that are available within the Planning system

Retrieving Data from the Web Service:

Let’s start with a really simple retrieval of data from the Web Service, and use the method in MetaDataManager called GetSystem.  This will form the building blocks of an application that can identify, Sites, ad models in a PPS deployment.

We need to add the web reference to the project, add both MetaDataManager.asmx and DataManager.asmx.

image 

Now to test the connection lets set up the connection to the web service and call GetSystem, this should return all the information from the PPS Planning System installed on the server we are calling the web service from.

MetaDataManager.MetadataManagerWebService ppsMDM = new MetaDataManager.MetadataManagerWebService(); 
System.Net.CredentialCache myCredentials = new System.Net.CredentialCache(); 
NetworkCredential netCred = new NetworkCredential("frfrfr", "frfrr", "frfrfr"); 
myCredentials.Add(new Uri(ppsMDM.Url), "Basic", netCred); 
ppsMDM.Credentials = myCredentials; 

MetaDataManager.BizSystem bizSystem = ppsMDM.GetSystem(false);

This should not return an error, however upon further inspection you will find that the BizSystem object has a Dataset called applications, of which has no DataTables, not what we expect, especially on inspection of the XML Output that is returned from the Web service when executed in a browser. On closer inspection of the XML, it is obvious that the xml could never de-serialise into a simple DataSet... So what’s going on - how do we get the data?

<?xml version="1.0" encoding="utf-8" ?> 
      <BizSystem ...>
              <Name /> 
              <Description /> 
              <Applications>
                      <ArrayOfBizApplication>
                                 <BizApplication Id="65c3097d-8df4-4e88-a826-3d19332e2e0e" 
                                   Label="AdventureWorksCycles" 
                                    ParentId="5f4a9fe1-15a7-4215-8bbc-a9e6d14e4c3a" 
                                    xmlns="">
                                      <Name>AdventureWorksCycles</Name> 
                                      <Description /> 
                                      <RootModelSite Id="a87d3d69-73df-48b3-82c6-a96398e552db" 
                                        Label="AWC" 
                                        ParentId="65c3097d-8df4-4e88-a826-3d19332e2e0e">
                                       <Name>AWC</Name> 
                                       <Description /> 
                                   </BizApplication>

                                   <BizApplication 
                                     Id="65c3097d-8df4-4e88-a826-3d19332e2e0e" 
                                     Label="Adatis" 
                                     ParentId="5f4a9fe1-15a7-4215-8bbc-a9e6d14e4c3a" 
                                     xmlns="">
                                      <Name>Adatis</Name> 
                                      <Description /> 
                                      <RootModelSite  
                                        Id="a87d3d69-73df-48b3-82c6-a96398e552db" 
                                        Label="AWC" 
                                        ParentId="65c3097d-8df4-4e88-a826-3d19332e2e0e">
                                       <Name>ADATIS</Name> 
                                       <Description /> 
                                   </BizApplication>
                        </ArrayOfBizApplication>
              <Applications>

It appears that we need to write our own methods to de-serailise this object; this can be easily achieved, and gives us the ability to fully understand this object.

 

PPS SP1 and SQL Server 2008 RC0

So, one of my previous posts I explored using PPS with SQL 2008 Feb CTP.  Now with the release of PPS SP1 and SQL RC0, I thought a perfect time to re-test the installation.  With a complete new installation of Windows Server 2008, SQL 2008 RC0, and PPS SP1, the testing has started all over again.

The installation of SQL 2008 goes smoothly now, and very easy, as does the installation of PPS and PPS SP1.  However the issues come in after installing all of PPS and then attempting to configure PPS Planning (Monitoring to come later this month). 

After Installing SQL 2008, you will also need to install the following items from the SQL 2008 and SQL Server 2005 feature pack:

  • Analysis Management Objects 9.0 (SP2)
  • SQL Server Native Client 9.0
  • ADOMD.NET 9.0 SP2

After installing of these, we can now get the pre-requisite page, and also allows the use of the relational part of SQL Server 2008, however Analysis Services still does not accept the SQL 2008 instance, after much investigation, it does not seem possible to install PPS, even with SP 1,  to use Analysis Services SQL 2008 instance.  With much regret, I have currently installed an Instance of SQL 2005, just Analysis Services.   This allows the install the complete.

Now many people have said that the 2005 Instance can be upgraded to SQL 2008, however I have found that deploying a new model site, still fails once upgraded to SQL 2008. This investigation will continue.