Tim

Tim Kent's Blog

Adatis Goes Gold!

We are proud to announce that Adatis Consulting has been awarded Microsoft Gold Certified Partner Status.  This achievement reflects our continued investment in the latest Business Intelligence and Performance Management technologies from Microsoft and is, in no small part, thanks to our customers, partners, and the outstanding team at Adatis.

PerformancePoint Monitoring Web Service - Part 2

In my last post we looked at the basics of connecting to the web service and using some simple functions to retrieve some metadata.  Those of you who downloaded the sample project would have spotted that I added some functionality to retrieve all the annotations for a given scorecard - something we've been asked for by a number of clients.  This time we'll look a little deeper and look at how we could automate production of the FCOs (First Class Objects) within PPSM.

The process of building a dashboard in code is in essence just as you would do it in Dashboard Designer.  Remember when you are using Dashboard Designer it's actually making calls to the web service behind the scenes - no dll's involved.  If you use a tool such as Fiddler2 you can see exactly what is getting passed in the calls.

So first we need to create a data source.  Once again our friendly PPSM dev team have named the functions just as you would expect so no prizes for guessing we need to use the CreateDataSource function.  You then need to create a datasource object in your code which is passed to that function.  This is fairly straight-forward in that you set the attributes of the data source object you've created and give it a unique ID (GUID).  The usual apologies for the state of my code!

private void CreatePPSDS(PPSM.PmService mon)
{
   //declare the datasource
    PPSM.DataSource ds = new PPSM.DataSource();

    //set the attributes of the data source
    ds.SourceName = "ADOMD.NET";
    ds.ServerName = "Localhost";
    ds.DatabaseName = "Adventure Works DW Standard Edition";
    ds.CubeName = "Adventure Works";
    ds.CubeDisplayName = "Adventure Works";

    //give the data source a GUID
    ds.Guid = System.Guid.NewGuid();

The first quirk is that the data source name, owner and description aren't directly exposed on the DataSource object but in a BpmProperty array called Properties so first you need to declare each element individually so you can set it's individual properties; Name is a BpmPropertyText, Description is a BpmPropertyLongText and Owner is a BpmPropertyUser.  This properties array is common to all FCO's (and in fact inherited from the base Element Object) so this will be the same method we use for Data Sources, Scorecards, Dashboards etc.

        //declare the three individual property types for the property array
        PPSM.BpmPropertyText dsName = new PPSM.BpmPropertyText();
        PPSM.BpmPropertyLongText dsDesc = new PPSM.BpmPropertyLongText();
        PPSM.BpmPropertyUser dsOwner = new PPSM.BpmPropertyUser();
        
        //set some details for the properties
        dsName.DisplayName = "Name";
        dsName.Description = "Name Description";
        dsName.Text = "A data source name";
        dsName.Visible = true;
        dsDesc.Text = "MyDataSourceDescription";
        dsOwner.Login = "TCK";

Next we have to initialise the Properties array on the DataSource object itself so we can assign the individual properties we declared earlier to the elements within it.  And here comes the next quirk - and I'll unashamedly admit that this one had me completely foxed! Each element of the DataSource properties array requires a uniquename, and not just any unique name but a very specific one - 8dd07d4d87794510afdb1f07664359bc.  Without this your data source will be created but won't have a name, description or owner!  Thanks to Alyson Powell Erwin of the PPSM Team for solving this one for me :)

        //initialise the properties array of the data source
        ds.Properties = new PPSM.BpmProperty[3];

        //set the properties array with the three elements
        ds.Properties[0] = dsName;
        ds.Properties[1] = dsDesc;
        ds.Properties[2] = dsOwner;

        //declare a GUID and set the unique id of each property
        ds.Properties[0].UniqueName = "8dd07d4d87794510afdb1f07664359bc_Element_Name";
        ds.Properties[1].UniqueName = "8dd07d4d87794510afdb1f07664359bc_Element_Description";
        ds.Properties[2].UniqueName = "8dd07d4d87794510afdb1f07664359bc_Element_Owner";

Note the "_Element_..." on the end of the UniqueName - this is also mandatory.  That's all the quirks now :)

Next we can add members to our roles if required.  These are stored in the Memberships array of the DataSource

        //initialise the Memberships array
        ds.Memberships = new PPSM.Membership[1];
        //for each membership you want to add you then need to initialise the element of the array
        ds.Memberships[0] = new PPSM.Membership();
        //then set the properties (note the control backslash in the login name)
        ds.Memberships[0].Login = "Domain\\login";
        ds.Memberships[0].Role = "Editor";

Finally call the CreateDataSource method passing your DataSource object of the web service and your work here is done!

        //finally pass to the web service call
        mon.CreateDataSource(ds);

If you need to update the data source then you retrieve the data source using GetDataSource, update the properties as before and then pass the object the CreateDataSource function. This automatically deals with versioning.

The sample project is here: PPSMWebService.zip (24.93 kb)  (don't forget to open as a web site rather than a project in VS)

Next time we'll look at how to create one of the other 1st class objects using our data source.

PerformancePoint MCPs

I was a little surprised to see that (as of June 2008) there are already 329 people certified in PerformancePoint (i.e. have passed the 70-556 exam) and whilst it's not the hardest exam (IMO compared to 445 and 446) it's a good sign that PPS uptake is getting stronger and stronger.

Mind you most of the 329 probably work for CapGemini who should have had 3000 consultants trained in PPS by the end of 2007!! Amazing ;)

Comments on our blogs

Thanks to all of you who take the time to leave comments on our blogs - it's always good to know that people are actually reading :)

Unfortunately for every one genuine comment we get hundreds of spam comments so we've decided to make it a requirement to sign in to add a comment.  Please be assured that any information you provide will be used for nothing other than managing your logins.  The first time you leave a comment after signing in we will need to approve your account but after that your comments should appear automatically.  Please drop us a mail (blogs (at) adatis.co.uk) if you have any issues.

Thanks 

Tim

Google charts in PPSM

Don't tell MSFT I said this but this is really quite cool: 

http://code.google.com/apis/chart/

The Google charts API allows you to create charts as a web page by simply setting the querystring:

http://chart.apis.google.com/chart?chs=400x200&chd=t:50,10,20,20&cht=p3&chl=Bully|Jezza|Sach|Tim&chtt=Who+ate+all+the+pies

If you use it in conjunction with a simple web page that is picking up the parameters from a PPSM filter (like Nick B has created here) you've got some instant functionality - e.g. display the country/state you've filtered on.  Here, I've taken Nick's example and built the response.redirect statement to include the country code from a tabular data source based on the parameter selected:

image 

Taking it a step further you could create a custom report type using the SDK for PPSM that connects to one of the built in data sources and prepares a querystring.

The main drawback is of course that you need an Internet connection to display the charts.  Another drawback is that there is a limit to length of a querystring so thousands of data points is not possible (nor should it be allowed in charts!!) but other than that this a definitely a short-term solution to some of the <whispers!> shortcomings of the built-in PPSM V1 chart.

Sample Code and BSWX

PerformancePoint Server SP1 released!

This will be cross posted all over the place but Service Pack 1 has now been released! Details from the PPS Technet Forum:

PerformancePoint Server 2007 Service Pack 1 (x86):  http://www.microsoft.com/downloads/details.aspx?FamilyId=28B1F86B-B7F2-4215-8BC9-8F8507FF8831&displaylang=en

PerformancePoint Server 2007 Service Pack 1 (x64): http://www.microsoft.com/downloads/details.aspx?FamilyId=6245C354-9191-4C4D-8C0C-C10D6C778AF8&displaylang=en

PerformancePoint Server 2007 Evaluation Version (x86): http://www.microsoft.com/downloads/details.aspx?FamilyId=6372C24F-67DD-42DD-B034-748907B23420&displaylang=en

PerformancePoint Server 2007 Evaluation Version (x64):  http://www.microsoft.com/downloads/details.aspx?FamilyId=3ADAC793-DEF0-4BA8-A9AB-228979B8DB40&displaylang=en

What’s New for PerformancePoint Planning Server SP1

· Windows Server 2008 Support

· Add-in for Excel Performance Improvements – The Performance of opening reports and opening and refreshing workflow assignments is improved significantly.

· Add-in for Excel offers a new workbook option Clear Changes after workflow action – When you set this option to True, Add-in For Excel automatically clears any changes after the workflow action occurs when the form is used in an assignment. This improves performance of subsequent queries. However, if submission fails with this options set the user will be unable to resubmit the changes and the changes will be lost.

· Add-in for Excel offers a new workbook option Enable what-if analysis – When you set this option to True for a matrix, Add-in For Excel automatically performs what-if calculations that show the effect the change will have on an assignment if submitted. If False, no changes are applied to the matrix, this can help improve query performance.

· Annotations – Forms and Reports can now be created for models that have annotations disabled.)

· Workflow Assignments – Contributors can now use the discard action after submitting an assignment to clear the version of the form template they recently submitted allowing the most recently published version of the form template to be retrieved.

· User Dimension – Allows creation of dimension properties that link to the user dimension.

· Kerberos Support – The PPS Planning Service can now reside on the same IIS server as other IIS sites that also require Kerberos authentication.

· Data Import Wizard is decoupled from Microsoft Dynamics AX and works with any data provider – The data import wizard is now a generic data integration wizard that allows customers and partners to develop their own data providers to integrate source financial data. The PerformancePoint Server Data Integration Toolkit, provided by Microsoft, is a companion tool that provides a data integration framework. You can use this framework to create your own data providers. To obtain this toolkit, visit the following web site: http://go.microsoft.com/fwlink/?LinkId=117552&clcid=0x409

What you need to know about installing:

http://technet.microsoft.com/en-us/library/cc514367(TechNet.10).aspx

What’s New for PerformancePoint Monitoring Server SP1

· Microsoft Windows Server 2008 Support

· Default Display Conditions can be Created – It is now possible to add a default display condition report view (see related blog post also posted today)

· Multi-select Filter Enhancements – Multi-select Monitoring server filters now pass to multi-select SQL Server Reporting Services report parameters and Strategy Map views

· Time Intelligence Post Filter formula date corrections

o In some instances filters were passing a one day offset, this is resolved

o Date format of filter will now honor localized formats

· Negative numbers will not cause a text wrap in scorecards

· Dashboard filter performance improvements

· Expand/collapse functions work properly with hidden parent KPI members

· Tabular filter enhancements

· Limit of 1,000 characters in an MDX expression for KPIs has been removed

· No longer blocked from using SQL Server 2008 as a data source (NOTE: full support for 2008 is coming in SP2, we just won’t prevent you from running on it with SP1)

What you need to know about installing:

· The SP1 file MUST be installed on every PPS Monitoring Server in your environment

· The SP1 file MUST also be installed on every MOSS/WSS box that has the PPS Web Parts installed

· Dashboard designers should download the Dashboard Designer from the server again after the server has been upgraded

PerformancePoint - BI Report Automation Publisher

On a slightly similar note to the PPS Planning data migration tool recently released by Microsoft, Kevin Idzi (also MSFT) got in touch to let us know about project he has recently released on Codeplex for automating moving reports between servers/environments when using PerformancePoint, ProClarity Analytic Server or MOSS/SharePoint.  Check it out here:

www.codeplex.com/bipublisher

I haven't had a chance to look at in any depth yet but first impressions look pretty impressive and it will certainly be useful to us! 

PerformancePoint Monitoring Web Service - Part 1

Update: Part 2

Update: Part 3

Both the Planning and Monitoring components of PPS provide their functionality via web services.  Martyn and I spent quite a while picking the planning web service apart for our web part.  It was less than straight-forward as it uses the same method to carry out different functions with different objects.  The Monitoring Web Service by comparison is much easier to decipher.  You view the methods by entering the following in your web browser:

http://<yourmonitoringservername>:40000/WebService/PmService.asmx

image

Most of the things you can do in Dashboard Designer and SharePoint are included in the list which means the possibilities of what you can do programmatically are huge!  On top of this there's also the Monitoring SDK which allows even further extensibility such as custom reports/data sources.

Over the next few weeks/months I'll be taking a look at the Monitoring web service in more detail and showing examples of what you can do with it.  This week it's 101 - connect to the web service and list objects.  And no comments on the quality of my code - it's been a while!

The first thing we need to is set up a visual studio project.  This could be a web project, Windows form, web part etc.  In this example we'll be creating a simple web page so are using a asp.net web site project.  Next add a web reference to the Monitoring Web Service by right-clicking on your project in the solution explorer pane:

image

Enter the address of the web service as per above and give it a suitable reference name (in the examples below it's PPSM).  To keep things simple I'm going to just add the code to the page load event of the default page.  First we have to initialise the web service and pass it our credentials:

protected void Page_Load(object sender, EventArgs e)
{
    //initialise the web service
    PPSM.PmService mon = new PPSM.PmService();
    //you could make the url of the web service configurable
    //mon.Url = <your url>

    //next we have to pass the logged on user credentials to make
    //sure that we have permissions to make the web servoce calls
    mon.Credentials = System.Net.CredentialCache.DefaultCredentials;

From here we can now call the web service methods as required.  First of all lets list the dashboards on our server and loop through them listing the name using the GetDashboards method.  Note that we have to use a BpmPropertyText object to get to the dashboard title.

//now we simply use web service calls to get the objects
PPSM.Dashboard[] dash = mon.GetDashboards();

//loop through the dashboard array and print the title
for (int a = 1; a < dash.Length; a++)
{
    //in this case we have to pass the title back in a BpmPropertyText object
    PPSM.BpmPropertyText dashname =  (PPSM.BpmPropertyText)dash[a].Properties[0];
    Response.Write(dashname.Text);

}

Lets do the same now with Scorecards using the (yep you guessed it) GetScorecards method:

//declare an array of scorecard objects and initialise using the
//get scorecards method
PPSM.Scorecard[] scd = mon.GetScorecards();

//loop through the scorecard array and print the title
for (int j = 1; j < scd.Length; j++)
{
    //again we have to pass the title back in a BpmPropertyText object
    PPSM.BpmPropertyText scdname =  (PPSM.BpmPropertyText)scd[j].Properties[0];
    Response.Write(scdname.Text);
}

Next we can do some specific things with each of the scorecards in the array.  In this case get all the KPI's associated with each scorecard using the GetKpisFromScorecard. This take an argument of the scorecard guid which we can get from the array elements in our previous call

//next we're going to get the kpi's for the current scorecard 
PPSM.Kpi[] kpi = mon.GetKpisFromScorecard(scd[j].Guid);

Response.Write("<b>Kpis in " + scdname.Text + "</b>");
Response.Write("<br>");

for (int i = 1; i < kpi.Length; i++)
{
    PPSM.BpmPropertyText kpiname = (PPSM.BpmPropertyText)kpi[i].Properties[0];
    Response.Write(kpiname.Text);
     
}

No need to clean up any connections - one of the perks of the web service (everything is returned as xml objects).

Pretty straightforward stuff really.  Next time we'll look a bit deeper!  I've attached the sample project below.  It has a web reference to a localhost monitoring service so you'll need to update it if you're running the code against a remote server.  There's no solution file as it's a web site project so you'll need to use the open web site option in VS.

PPSMWebService.zip (24.93 kb)

PerformancePoint Monitoring Caching

I've been asked this a few times recently so thought I'd write a quick post.  There's three places you can set whether or not caching occurs in PPSM:

1) On an individual report or scorecard object

2) On the data source itself

3) As a setting on the PPSM server


Don't forget to take into account the performance hit that you may cause by changing this setting

The PPSM scaling document - http://technet.microsoft.com/en-us/library/bb660539.aspx

And don't forget as well that if you're using 'per user' security you won't get the benefits of caching over app pool security

Then of course you've got to consider the whole Sharepoint side of things...

Another new PerformancePoint Blog....

As Adrian mentions there's a really good community starting to build around PerformancePoint and some excellent bloggers putting out some great posts.  I'm sure those of you who have been on the PPS Planning forum will recognise Paul Steynberg's name and any of you who've read his posts will know that he seriously know his stuff, particularly from and finance and accounting perspective (and being able to apply that to IT/IS).   

Paul has recently started his own blog and I've no doubt it will be well worth subscribing to:

http://paulsteynberg.blogspot.com/