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.
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."
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
2. Click Web Service URL and click "Advanced"
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.
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.
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
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
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.
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.
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.
In April I wrote about Reporting Services in SQL 2008 with MOSS 2007 (here), and also a small article on the SQL 2008 feature pack February CTP. This was all tested on the February CTP, of SQL 2008. The feature pack is required to get the Add-In for SharePoint Integration. Andrew has beaten me too it and written a great reference article on the latest RC0 version of SQL 2008 Feature Pack that can be found here. Between PPS SP1 and the RC0 release, I hope to get round to testing all the new releases as soon as possible.
What a treat, I am sure you have all already heard the SP1 has been released for PerformancePoint, which hopefully will enable us to run PerformancePoint on SQL Server 2008. As well as this it seems that RC0 has been released on TechNet for SQL Server 2008. I will be getting an environment for testing set up as soon as possible and begin testing compatibility. Details of availability of this can be found on Andrew Fryer's blog: http://blogs.technet.com/andrew/archive/2008/06/06/sql-server-2008-rc0.aspx
Sometime ago Adatis undertook a small project with Reporting Services, running on SQL 2005. This project is an ideal candidate to test SQL 2008. Recently I have had many people asking me about SQL 2008 in the BI space, as our clients are starting to look at this new version. A key requirement for many clients will be that their existing databases and reports will transfer smoothly to 2008.
So, to begin we must import the SQL 2005 database. Here, at first, we fail at the first hurdle. You cannot simply back up a SQL 2005 Database and Restore it to SQL 2008 (so it seems) - After numerous attempts and fiddling - the following error kept returning to haunt me:
So, as an alternative I tried the Copy Database Wizard. This was not without issues, these included security issues, and then Issues with creating certain views, and security. This is because it simply uses scripts to do the tasks in the background so the object have to exist for the Database to be successfully created. Therefore - at present - there is no easy way to get a SQL 2005 database onto the SQL 2008 platform; however it is achievable with a little tinkering. One of our favorite tools, (RedGate) would probably aid with this.
After a complete rebuild of the server, with no Side by Side installation of SQL Server 2005, 2008 now allows restores of 2005 databases - phew... So, given we have a well set up environment - we can simply restore SQL 2005 Databases to SQL 2008!
To be completely up to date, I will use Visual Studio 2008, to open our reporting Services project. First thing this tries to do is Update the project, now at this point it is wise to have either backed up and taken your local copy offline (disconnect From Source Safe) or to have branched the code. At a Minimum - ensure that you label the project in source control before the update.
The Upgraded to 2008 should go pretty smoothly. And the First thing to do, after the update, is to connect your project to the new SQL 2008 Database, and build the project - if your project built successfully in 2005, it should build successfully in 2008 too. Now if you try and open a report you may get following message:
It seems that although we converted the project successfully - each RDL also need converting. Once all your RDL's are correctly updated, check each report in the Preview pane. If these work - then you are there. All that’s left to do if deploy to Reporting Services 2008, which is done in exactly the same way as before.
There are a few fundamental changes between developing RS in the new version. One of the most obvious in the number of tabs in the report designer window. The Data Tab seems to have gone in BIDS 2008, making very difficult to manage the data for each report. At present the only way i can see to create multiple datasets for a single report is in the code behind. However once the Data sets have been configured for each report - getting the data from each Dataset is a doddle:
The integration with Dundas seems to have bought about property panes for setting up all the aspects of the reports, and also a greater selection of Charts, and new to 2008, Gauges - which has been part of the Dundas suite for some time now.
Also, there is no "debug" way of running the reports, like there was in 2005 - to see the whole thing in action the reports must be deployed.
So, to summarise - we can import and deploy SQL server 2005 Reporting Services reports and re-deploy them onto Reporting Services 2008 without too much hassle at all. However, judging from BIDS 2008 it is not ready to be used as a development platform, as certain "simple" tasks have been complicated. We must remember that SQL Server 2008 is still in CTP from February so development is a good 3 months on already - and with the enhancements to the look and feel of the reports and the performance increase, Reporting Services on 2008 should be a worthwhile investment.
Have both been released this month! Visual Studio 2008 Service Pack 1 (As a beta) can be found here: http://msdn.microsoft.com/en-us/vstudio/products/cc533447.aspx It includes some enhancements, however most noticeably the improvements in event wiring up in WPF, between the design view and the code behind.
Enterprise Library 4 can be found here: http://msdn.microsoft.com/en-us/library/cc512464.aspx It is for .NET 3.5 and Visual Studio 2008, so fits well with the release of SP1. The enhancements can be found here: http://www.codeplex.com/entlib/Wiki/View.aspx?title=EntLib4%20Backlog.
The rumors were that the service pack for Performance Point (SP1) were that it was due to be released in May. This looks like it was not correct, apologies for the information in my previous post being incorrect. We will just keep our eyes peeled for the release and report on this as soon as it comes in, I’m confident it’s well on its way and due any day now.
Recently I have had to install .NET 3.5 on a few machines with varying success. I have found that it is best to download the entire package, prior to installation - this can be found here: .NET Framework 3.5 full package.
Upon installation I found that the package, on certain machines on Windows XP, hangs on the following screen:
Now although this seems a little strange, I found that removing the network cable, therefore disconnection from the internet, kicked the internet back into life. Before doing so, wait for the installation to hang, this can be done by keeping an eye in task manager and when all activity stops, just disconnect any connection to the network/internet and then reconnect when the next screen appears.
Microsoft have released a raft of White papers on SQL 2008. These can be found here: http://www.microsoft.com/sqlserver/2008/en/us/white-papers.aspx. Although fairly high level the first business intelligence white paper outlines some of the significant changes made to 2008, in order for it to better compete in the Data Warehousing space.
There are 6 separate white papers dedicated to Business Intelligence alone. The introduction to new Data Warehousing features has a great deal of information on new features that will further enhance the BI offerings from Microsoft. This document includes details on the following enhancements:
SQL Server Relational DBMS DW Improvements:
- Star Join
- Partitioned Table Parallelism.
- Partition-Aligned Indexed Views
- GROUPING SETS
- MERGE
- Change Data Capture
- Minimally Logged INSERT
- Data Compression
- Backup Compression
- Resource Governor
Integration Services Improvements:
- Lookup Performance
- Pipeline Performance
Analysis Services Improvements:
- MDX Query Performance: Block Computation
- Query and Write back Performance
- Analysis Services Enhanced Backup
- Scalable Shared Database for AS
Reporting Services Improvements:
- Reporting Scalability
- Server Scalability
More Posts
Next page »