Martyn

Martyn Bullerwell's Blog

PerformancePoint Server SP2

A good few months ago I wrote about SP1 for PerformancePoint and getting this working on SQL Server 2008.  There were a number of attempts, and with a great deal of hacking, I got an untested environment set up, however this was not very stable and never fully tested.  SP2 is due for release in December, and there are mentions of a Beta available on the web, although unavailable through MSDN, or Connect.  It seems that the two promises of SP2 are Hyper-V compatibility and SQL Server 2008 integration.   

A few good resources for this are as follows:

http://hmorgenstern.spaces.live.com/Blog/cns!28A6BE83102A0EB3!419.entry and http://performancepointing.blogspot.com/2008/11/performance-point-server-sp2-pre.html

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;

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.

PerformancePoint Service Pack 1 - Not long now!

I stumbled across this little nugget of information today http://bimvp.com/blogs/bsm/archive/2008/04/07/performancepoint-server-2007-service-pack-1.aspx, stating that the PPS Service Pack 1 is now in Beta, and also hinting that it's release should be in May (If I read it right, the 5th of May).  Its compatibility with Windows Server 2008 is said be fixed, and the following page highlights what is, isn't and will be, supported by Windows Server 2008; http://support.microsoft.com/kb/948680.  You will find PPS in the "Applications that will be compatible in the future", stating SP1. 

More to come on this in May hopefully!  

PerformancePoint Server over the Internet

This is quite a simple little thing that really hasn't been mentioned yet, maybe because it is so simple, however nevertheless less worth mentioning.   You may have the requirement to be able to allow your users to forecast from where ever, and security permitting, this is a very easy thing to set up.   All communication between Excel and PPS happens over basic HTTP web services, this lends itself very well to configuring PPS for remote working. 

image

To set it up you need to open a port on your firewall to allow communication over the PPS port (46787), and then simply point your connection to your Domain that is pointing to the correct server... and it all works, no issues.  So in short, PPS works over the Internet, SSL is the next challenge.   Initial testing against SSL, i.e. HTTPS, looks very possible.  So to begin this add a new SSL port to the setting in IIS and create a new certificate in IIS, I chose to use 46797. for testing purposes it is as simple as adding a connection to Excel PPS add-in and testing.

image

So, PPS can be deployed over the Internet, using SSL.  This therefore also means that all PPS functions can be secured with SSL and accessed over the Internet.

SQL Server 2008 - CTP

OK so we should have been getting our mitts on SQL Server 2008 RTM yesterday, but it has been delayed, until Q3 which has been know for some time.  Microsoft did, however, go ahead with the official release yesterday, regardless that the product has not been released to manufacture.  However to keep our appetites "wet" Microsoft have released another CTP (February) which can be downloaded here: Download

SQL Server 2008 is just an enhancement to the already extremely successful end to end BI stack that delivers a well rounded platform for the majority of organisations planning, reporting and analysis needs .  Their BI and performance management offering works best when it is implemented as part of a full stack of Microsoft infrastructure that includes SQL Server (including 2008), SharePoint Server 2007 and Microsoft Office (including Excel 2007). This platform is complemented by PerformancePoint Server 2007, and the obvious tight integration with Excel. 

Microsoft are also making further inroads into competing with Teradata, IBM and Oracle with improving the relational database engine with features such as partitioned tables and support for partitioned table parallelism that position it more effectively within the large scale enterprise data warehousing market where IBM, Oracle and Teradata all play. Although still not ready for databases that run into the 100's of terabytes and possibly petabyte range,  the company is making in-roads, it currently has customers working comfortably in the one to 15 Terabyte range and expects SQL Server 2008 to extend this into the 20 to 50 Terabyte range.

PerformancePoint and Windows Server 2008

Well, the testing has continued this week after having issues with SQL 2008 with PerformancePoint, I rebuilt one of our test servers with the Latest Server CTP.  Obviously as I am keen to always know what Microsoft are up to in as many areas as possible, so I thought it wise to install Server 2008 with Hyper-V.  Now this seems like a great idea, however, it seems that this particular version is only available as a 64Bit variant at the moment, and they do not make that all to clear while installing.  So after installing this, and SQL server 2005, as to test PerfomancePoint on Server 2008, it seems that the version check for Windows fails, with is not ideal.  This seems to be solely due to the fact that it is a 64 Bit version. 

So, the next step is to try the Install with the NoReqCheck option ( post on this can be found here ), now this does actually get PPS Server to install, however the configuration will not run.  I have not tested the 64 bit version of PPS on Server 2008, as it seems that Pro Clarity 6.3 is ONLY 32 bit. PPS 2007 currently doesn't offer the Analytics Component that Pro Clarity 6.3 offers. I'm sure, however this will be released in the next version of PPS 2007.

PerformancePoint Server and SQL 2008

So after many hours looking into this issue, it seems that the current release of PPS does not work on SQL Server 2008, and there seems to be no tricking it.  Should you manage to get round the install checks, while setting up the Database Connections to SQL and Analysis servers, the versions are checked once again, and the door is currently closed.

In the mean time, SQL 2005 will still work!  SQL 2008 is still due to Launch SQL 2008 in February, however it now looks as if the RTM (Release to Manufacture) will now not be until Q3 of 2008 - so we are unlikely to see,even early adoption

until July time.  Microsoft see  SQL server 2008 as a critical step forward towards their "broader vision for business intelligence", so I think we can be rest assured that PerformancePoint will be integrated before release date. 

In the mean time I will update you with any further developments on this, and will continue testing with further CTP's and patches.

Install Performancepoint Server on SQL Server 2008

Testing the latest technology on the latest platforms is always very important to us, in order to ensure we are up to date, give our clients an unbiased view of what's out there and what's coming up, and how it will affect them.  We have been involved with PerformancePoint for sometime now and have had good success, however with the impending release of SQL Server 2008 and Windows Server 2008, we thought it time to get up to date with PPS on a Server 2008 box, running SQL 2008. 

With Windows Server 2008 in RC1 and SQL Server 2008 in RC1, we know we are now close to testing the fully fledged version.  So, time to install it all.  Sever Installs with no dramas at all, quite hastily in-fact.  Following that is SQL 2008, which also installs without any hicup this time, however does take a very long time to install, and interestingly installs Visual Studio 2005, not 2008 (which is released the same time as SQL and Windows Server).  So we are up and running, we have a perfect install of SQL 2008 on a Windows server 2008 sever, time to install PPS.

PPS is a small install, just 60MB download, and 4 different installations: Planning Server, Business Modeller, Excel Add-In, and Monitoring Server.  For the Server Install we just want to install the Planning Server, and the Monitoring Server for the time being.  We will start with the Planning Server. PPS Prerequisits

On Checking the prerequisites for this we instantly come up with a problem.  PPS Planning, looks for a specific version of SQL, SQL server 2005, and therefore does not recognise SQL 2008.   This causes somewhat of a problem.  

Changing the Registry also doesn't seem to help, while trying to trick the install of the correct version.  All the other components seem to install fine, and therefor the only issue currently is Planning.  An obvious solution would be to install SQL 2005 Side By Side, and simply use the SQL 2008 Database.  However this is not an ideal solution, and I will be continuing work with this in the new year, as well as raising this issue with Microsoft.

I will post a follow up to this when we have resolved the installation issues.  In the meantime, have a great new year!

     

The PerformancePoint WebServices - Retrieving Data

In a previous post, I have discussed retrieving information from the PPS WebServices, namely, retrieving the system information, that returns information on the Applications stored within PPS.  To retrieve any further data from PPS WebServices, we need to know the application ID, which is returned from the GetSystem() method.

Once you have retrieved the applications, you may notice that there are not a huge amount of WebServices methods available for use.  This is because one WebServices is used to retrieve most data from PPS.  Again we are going to use the MetaDataManager.asmx, WebServices, and the method we can use is the Request method, that expects an object called a PerformancePointRequest, and in turn returns a PerformancePointResponse.  A PerformancePointRequest can be made to retrieve all sorts of information from PPS, and this is always returned to a PerformancePointResponse.  Here is an example of code that returns Cycle instance information.

MetaDataManager.QueryFilter ppQueryFilter = new MetaDataManager.QueryFilter();
ppQueryFilter.ResultDataType = MetaDataManager.ReferenceDataType.CycleInstances;

MetaDataManager.QueryWorkflowData performancePointRequest = new MetaDataManager.QueryWorkflowData();
performancePointRequest.TargetObjectId = ApplicationID;
performancePointRequest.QueryFilter = ppQueryFilter;
MetaDataManager.PerformancePointResponse ppsResp = ppsMDM.Request(performancePointRequest);
return ppsResp;

So the PerformancePointResponse will return a standard object that can be successfully cast to a number of different objects, I have not used all objects as yet, however we have most experience with the 'WorkflowDataResponse' object.  So casting the PerformancePointResponse object, to a 'WorkflowDataResponse'  object, we know have a Workflow Data Object that is a set of data tables that re compressed.  So.. To get the data we need to de-compress this.  The following shows how I have gone about decompressing this:

First we need to get the Table Schema, that is compressed:

public static DataTable UnpackTableSchema(byte[] packedTableSchema)
{
  DataTable table2 = new DataTable();
  XmlSerializer dataTableSerializer = new XmlSerializer(typeof(DataTable));
  if (packedTableSchema == null)
    {
      return null;
    }
      DataTable table = null;
      using (MemoryStream stream = new MemoryStream(packedTableSchema))
      {
        using (GZipStream stream2 = new GZipStream(stream, CompressionMode.Decompress, true))
        {
          table = dataTableSerializer.Deserialize(stream2) as DataTable;
        }
      }
      table2 = table;
    }
return table2;
}

Once we have the unpacked schema, we can unpack the data and load it against the schema:

There are quite a few methods to actually unpacking the data, so if you are interested please do not hesitate to contact me.  Also, once we have finished the product, it will be available for download at http://www.adatis.co.uk.  Should you like to take a look at our BETA please email us at devteam@adatis.co.uk