Adatis Community

PerformancePoint Server 2007 PPSCMD GUI

sachatomey — Wed, 27 Aug 2008 21:21:38 GMT

I've built a really simple GUI for a couple of commands of the PPSCMD utility. I always take far too long to work out the syntax and navigate to the appropriate directory (Yes, I ought to update the PATH environment variable) that I felt I could justify building a simple front end to help speed up the usage.

So far I've only implemented the MIGRATE and REPROCESS commands - I use these quite a lot outside of any automated SSIS package so they seemed the most sensible to implement in the GUI first. I do intend on extending it to encompass some of the other commands and I would welcome any feedback towards prioritisation, usage, features and the inevitable bugs. It's currently version 0.1 and more or less ready for 'Community Preview' - there are some omissions such as full error handling and validation that I do intend on implementing over time along with the other commands.

It's a .NET 3.5 application so you will need to deploy it to a client where you are happy to install .NET 3.5 if it's not already present.

You can download version 0.1 from here - (You will need to create a login first)

Below are the screen shots:

Migrate

The migrate command: both import and export variations can be set and executed directly from the GUI. In addition, the command line is generated so you can cut and paste into a command window, batch file or SSIS package.

Reprocess

Need to reprocess a model quickly? Rather than wait for PBM/SSMS to open you can reprocess a model directly from the GUI. Just like Migrate, the command is generated for cut and paste.

Console

Any output you would normally see in the command window is reported in the console as the command is being executed.

Log

You can enable logging to a log file of your choice to record all commands processed through the GUI. Useful for additional auditing and for creating batch files of multiple PPSCMD operations.

Preferences

Preferences and options are set on the preferences dialog.

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

Martyn Bullerwell — Wed, 27 Aug 2008 16:52:03 GMT

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

Martyn Bullerwell — Tue, 26 Aug 2008 20:44:32 GMT

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.

Technorati Tags: SQL Server 2008,Intellisense

Exploring the PerformancePoint Planning Web Service Part 2

Martyn Bullerwell — Sat, 23 Aug 2008 13:38:02 GMT

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:

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.

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.

PerformancePoint Monitoring Web Service - Part 3

Tim Kent — Fri, 22 Aug 2008 16:47:23 GMT

In Part 1 we looked at the basics of connecting to web service and retrieving the metadata. Part 2 looked at how to create a datasource first class object (FCO) and the quirks of the "well known" properties (thanks to Wade Dorrell for the feedback and for raising a usability bug for the next version). In this post we'll look at how to create one of the other FCO's - in this case a report though as you can imagine the process for creating a KPI (the other "singular" FCO) is similar.

Once again, its not too hard to work out that we'll need to use the CreateReportView function and as before we'll create a ReportView object in our code that will get passed to the function once we've set up all the properties.

    private static PPSM.ReportView CreateReport(PPSM.PmService mon)
    {
        //declare our reportview object
        PPSM.ReportView rep = new PPSM.ReportView();
        //set the guid for our object - this gets used as the primary key in the db table
        rep.Guid = System.Guid.NewGuid();

We know how to set the "well known" properties from part 2 and it's exactly the same process here (including the identifier) so it makes sense to create a re-usable function to do this for all our FCO's

    private static PPSM.BpmProperty[] SetWellKnownProperties(string name, string description, string owner)
    {
        //declare the three individual property types for the property array
        PPSM.BpmPropertyText bpmName = new PPSM.BpmPropertyText();
        PPSM.BpmPropertyLongText bpmDesc = new PPSM.BpmPropertyLongText();
        PPSM.BpmPropertyUser bpmOwner = new PPSM.BpmPropertyUser();

        //set some details for the properties
        bpmName.Text = name;
        bpmDesc.Text = description;
        bpmOwner.Login = owner;

        //initialise the properties array
        PPSM.BpmProperty[] wkprop = new PPSM.BpmProperty[3];

        //set the properties array with the three elements
        wkprop[0] = bpmName;
        wkprop[1] = bpmDesc;
        wkprop[2] = bpmOwner;

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

        return wkprop;
    }

Lets call the function and also set the type of report (in our case an Olap Grid). All good so far....

        //set the well known properties
        rep.Properties = SetWellKnownProperties("My Report", "My Rep Desc", "Me");
        //set the type
        rep.TypeName = "OLAPGrid";

There are a couple more obvious properties that can be set (e.g. begin points and end points) but from here things get more complicated. The majority of the important properties are stored in a single XML property of the ReportView object called CustomData. This means that you'll have create and manipulate an xml document in your code that matches exactly the format required.

In practical use this may preclude using purely the web service to create FCOs completely from scratch. As an alternative workaround you could start from a template report stored on the server and then use and update the custom data property from that template. In reality of course you are unlikely to be automating the creation of reports completely from scratch - much more likely to just be updating the report datasource for example. Just for the sake of proving the concept, this is what we'll do in our case.

The other workaround is to code against the object model in Microsoft.PerformancePoint.Scorecards.Client.dll rather than the WSDL of the web service. This exposes the properties more clearly and is, as I understand, what happens behind the scenes in Dashboard Designer. IMHO this slightly defeats the object of a web service but I'm sure this will be rectified going forward.

As we've already gone through the process of creating a new reportview object we'll carry on down that path for this example. Of course it might be simpler in reality to grab an existing reportview object using GetReportView and simply change the required properties and assign a new guid. You use the CreateReportView function regardless of whether you are creating or updating - the existence of the object's guid in the underlying database table will define which happens.

This function returns the customdata for an object

    private static string GetCustomData(PPSM.PmService mon, string ReportID)
    {
        Guid g = new Guid(ReportID);
        PPSM.ReportView rep = mon.GetReportView(g);
        return rep.CustomData;
    }

Next we need to declare an XMLDocument to store the customdata and call our function passing in the Guid of a known report. I'm sure there are much cleaner ways to work with XML objects but it's Friday night!!

        //declare an xmldoc to hold the customdata
        XmlDocument custData = new XmlDocument();

        //you'll need to find the id of a similar type report from your PPS Monitoring database
        //load the custom data from ana existing report into an xmldocument
        custData.LoadXml(GetCustomData(mon, "d8c5ffbd-c01c-409f-ab1b-d0695227049b"));

Now obviously using the GUI of Dashboard designer it's easy too grab the data source by name. In code you would need to come up with a clever way of doing this or just work with knowing the guid of the datasources you want to use for the report. It's possible that you may have just created or updated a datasource as part of your application.

        //update the datasource elements of the customdata - quick and dirty hack!
        custData.DocumentElement["QueryData"].ChildNodes[1].InnerText = "3da20613-0a02-4e6f-ad17-6a42ec4d6b62";
        custData.DocumentElement["QueryState"].ChildNodes[2].InnerText = "3da20613-0a02-4e6f-ad17-6a42ec4d6b62";

        //and now lets pass the edited customdata to our new reportview object
        rep.CustomData = custData.InnerXml.ToString();

Finally pass to the web service

        //finally lets pass our modified reportview object to the webservice
        mon.CreateReportView(rep);

Updated project is on the download site as usual and includes an example of the customdata xml for a reportview object. Thanks as usual to Wade, Alyson and Tim at MS for taking the time to answer my DFQs!

PerformancePoint Planning - Currency Conversion (Triangulation)

sachatomey — Mon, 18 Aug 2008 19:59:44 GMT

I've blogged about some of the financial consolidation elements of PerformancePoint planning before. Most of the elements related to financial consolidation e.g. Share calculations, intercompany transactions etc are specific to consolidation and only implemented when performing some form of statutory consolidation. Currency conversion, on the other hand, can be common to any element of PerformancePoint Planning whether it's a budgeting, forecasting, planning or, in fact statutory consolidation solution.

In it's simplest, out of the box, form, currency conversion is pretty straightforward. You need an exchange rate assumption model to hold the exchange rates and you need a financial model without shares that contains a pre-defined currency conversion rule that utilses the exchange rate assumption model. You run the pre-defined rule on a set of financial data and currency conversion is calculated for you. There's little more to it than that.

This post looks a little (and I mean a little) deeper in to currency conversion and demonstrates the triangulation features contained within PPS-P.

Consider this set of exchange rates, taken from www.xe.com today:

On the face of it, we have a few rates that allow us to convert from British Pound to Japanese Yen, US Dollar to both British Pound and Euro, and finally a rate for converting Euro to Japanese Yen.

When the in-built currency conversion rule is run against, for example, a set of US financial data we can determine the British Pound equivalent. The Net Profit/Loss of 110,500 USD, converted to British Pound at a rate of 53.5 British pence per dollar converts to 59,117.50 GBP - As you would expect:

What would happen if we were to run the currency conversion rule again but this time try and convert the US Dollar figures to Japanese Yen?

The astute among you would point out that we don't have an exchange rate for converting US Dollar to Japanese Yen. The more astute among you might say that we could determine the JPY figures by first converting to GBP and then converting the GBP figures to JPY. The even more astute among you might suggest that you could calculate the Japanese Yen two ways, either by first converting to GBP and then to JPY or by first converting to EUR before converting to our desired JPY.

Who's right? Well, all of you, kind of!

If I run the currency conversion job again but instead of selecting GBP as the reporting currency, I select JPY, I get the following result:

No conversion ! Why - well, we don't have an exchange rate set for converting USD to JPY!

This is where triangulation comes in. With our current set of exchange rates there are two methods for converting USD to JPY. Either through GBP or EUR. You set which 'route' the triangulation takes by setting the model property 'Default Currency'. By default this is not set, hence our failed conversion above.

However, if you were to set it to either GBP or EUR you will get some results, but there is a catch - the 'route' you take returns different results. Incidentally, you don't have to redeploy the model when you change the property you just need to save the model.

Triangulate through GBP

110,500 USD at a rate of 0.535 equals 59,117.50 GBP. Convert the GBP to JPY at a rate of 205.524 and that equates to 12,150,065.07 JPY.

Triangulate through EUR

110,500 USD at a rate of 0.535 equals 75,029.50 EUR. Convert the EUR to JPY at a rate of 162.068 and that equates to 12,159,881.006 JPY.

The two 'routes' highlight a variance of 9815.936 JPY (about 90 USD)! A triangular arbitrage !

So, two questions:

1) Which 'route' should you use?
That's a business decision and if it's going to be a common conversion then direct rates ought to be favoured over triangulation.

2) How do you exploit this model to work out guaranteed triangular arbitrage to make millions through currency trading?
I'm working on it !

Type 2 SCDs - Calculating End Date when you only have Effective Date

Tim Kent — Sun, 17 Aug 2008 20:40:04 GMT

Here's a quick one for creating an end date column when you only have an effective date column in your data source to work with. The secret is to join the table to itself with a greater than join on the effective date. You then use the effective date from your joined table to give you your end date.

SELECT     
    DT1.AccountID 
    ,DT1.EffectiveDate
    --Add a default end date for current record and subtract a day so the end date is one day before the next start date  
    ,ISNULL(DATEADD(d,-1, DT2.EffectiveDate), '31/12/2099') AS EndDate 
    ,DT1.Price
FROM         
    dbo.PriceTable AS DT2 
    RIGHT OUTER JOIN
        dbo.PriceTable AS DT1 
        DT2.AccountID = DT1.AccountID 
        AND
        DT2.EffectiveDate > DT1.EffectiveDate

Troubleshooting the PEL Allocate Statement

Jeremy Kashel — Wed, 13 Aug 2008 22:01:15 GMT

The PEL Allocate statement can sometimes throw unexpected error messages, especially when you're setting up a pull or push cross model allocation rule.

I've been keeping track of a few of the more obscure ones, which I've listed in the table below. Hopefully this will help anyone out there who's struggling to get the cross model rules working.

Error Message

Solution/Explanation

No hierarchy ‘xyz’ exists in [dimension]

Check that you have specified the correct member set in the source, target and scope parts of the rule.

Member set is not defined correctly for cross model mapping. The number of members in source and target member sets in cross model mapping must match.

This message can have 2 meanings. Firstly, as it suggests, it can be that the number of members in the source and target mapping arguments do not match. Or, prior to SP1, it can be this rather more serious problem - http://support.microsoft.com/kb/942640

Execution of rule ‘xyz’ failed. This may have occurred because changes have been made to the model structure without redeploying the model site.

This can occur at execution time when you try and map two members twice. E.g. if your source is {2009, 2009} and your target is {Jan, Feb}. It doesn’t like the 2009 in there twice.

Each dimension scope in cross model mapping must be a list of concrete dimension members

Unfortunate but true – you can’t use relative member functions such as Descendants in the 4^th and 5^th arguments of an allocate statement.

Some of the model's dimension members are invalid or missing. The model may need to be redeployed.

This is a run time error that can occur when you are trying to write to non-leaf members. The first argument of the allocate statement will contain the problem, as this is where the cells to receive the allocation are specified. Often it’s because ‘this’ has been entered as the first argument, which could result in using non-leafs for the argument, as it will inherit any non-leafs specified in the rule’s scope statement.

Allocation statement must have 5 parameters when they appear in Cross Model Allocation Rule.

Cross model allocation rules expect 5 parameters, in the correct format. So if you don’t have 5 parameters, then there’s the problem. However, be careful how you specify the parameters. For example, if you specify 2 members in the source mappings, such as [Time].[Monthly].[2007], [Time].[Monthly].[2008], then be sure to enclose them in brackets, otherwise the 2^nd member will be interpreted as your 5^th argument.

Unable to compile the cross model allocation rule. The target contains the dimension ‘xyx’ but no explicit mapping has been provided.

If an extra dimension(s) exists in the target model, the allocate statement requires you to specify what member you expect the data to be written to in that dimension.

Technorati Tags: PerformancePoint,PPS,PEL,Cross Model Allocation

Using NativeSQL Business Rules for Seeding or What Ifs

Jeremy Kashel — Tue, 05 Aug 2008 11:45:00 GMT

You need to give really careful consideration to procedural business rules in PerformancePoint Planning. This is where multi-dimensional knowledge and experience helps, as you need to know whether its best to use a SQL or MDX implementation for your rules.

User Requirements

One area where I've found that this is particularly relevant is user requests for procedural rules that accept parameters that are member(s) of a dimension. E.g. Lets run the rule for the selected member in the entity dimension, perhaps to carry out a what if on a particular entity. This is fine for a single member, but on occasion I've found that the users want to pick either a)a leaf level member (which is fine) or b)a non-leaf member, and for the rule to apply the logic to all descendants of that member.

The Technical Problem

What technical problem? You can use the descendants function to achieve this right? You can with an MDX rule, but you can't with a SQL rule. So, surely just use an MDX rule then?

This is where you have to think relational world Vs OLAP world. Putting a statement such as Descendants($Entity$, 10, leaves) in the scope of your rule will mean that when the rule is run, then it will always operate on the leaves of the dimension, regardless of whether you pick a leaf or non-leaf member. But the issue is that if your scope statement covers cells that have definition rules against them, then an MDX query will pick up the results of of those cells and and shove them into the fact table!

So can you use the Except() function or something similar to filter out those cells that you don't need? Yes you can, but not if the members start to span different levels, the compiler won't let you do that. As I said, you can't use a SQL implementation because using Descendants($Entity$, 10, leaves) in the scope statement will tell you - "A SQL implementation is forbidden by a parameter or variable reference in a MemberSet expression unless the reference is the entire expression."

The Solution

Rules that operate on fact data should really use SQL, its quicker and avoids hitting calculated data. I'm sure that you've seen this coming a mile off (!), but one way to go about this is to use a NativeSQL rule.

You can reference database objects in your NativeSQL rule, one of which will make it easier for you carry out descendant/hierarchy type functions in SQL itself. A function will get created in the Calc schema of your Planning application database in the format fnCalc_0_DimensionName_MemberSetName_IsAncestorOrSelf - this will occur when you deploy your model site. Using this function in conjunction with NativeSQL will allow you to get the leaves of a member quite easily.

The following NativeSQL rule creates a copy of one scenario's data into another scenario, which is referenced by a parameter:

--Although integers, the parameters are actually 'members of a dimension' parameters
create proc Calc.[sp$procedure] @scenario int, @entity int as
begin
begin transaction

    insert into dbo.[tbl$factTable] (alldim$except(dim$Entity, dim$Scenario), dim$Entity, dim$Scenario,
    column$ruleId, Value, LoadingControlID, bmo$CreateDateTime, bmo$ChangeDateTime, ContextID)
    select alldim$except(dim$Entity, dim$Scenario), dim$Entity, @scenario, $ruleId, Value, -9, getutcdate(),
    getutcdate(), -1
    --No need to hard code the fact table name
    from dbo.[tbl$factTable] t
    inner join 
    (
        select e.memberId from dbo.d_Entity e
        --Get leaves of the member key that is in @entity
        where Calc.fnCalc_0_Entity_Divisions_IsAncestorOrSelf(@entity, e.memberid) = 1 and 
        e.memberid not in (select h.parentmemberid from dbo.H_Entity_Divisions h)
    )    lv on lv.memberid = dim$Entity
    where t.dim$Time >= 200701 and t.dim$Time <= 200712 and (dim$Scenario = 1)

if @@error <> 0
rollback transaction
else
commit transaction

end

I suppose the above rule may look a little bit daunting, due to all the dollar signs everywhere. These are just the preprocessor commands, which will get replaced when the stored procedure gets created. But other than that, its just an insert statement. In some cases the syntax is actually quite handy, as you can use statements such as alldim$except(), which is a short hand way of returning fact table dimension column names very easily. The idea behind the preprocessor commands is that you can protect the logic in your rule from minor structural database changes.

The key is that when the rule gets run, it has normal parameters, meaning the user will see the following interface:

In the case above, the member key of North America Operations will end up in the stored procedure, as I have a parameter in the stored procedure called @entity. Inside the stored procedure it is then used in combination with the IsAncestorOrSelf function to pick up the leaf level descendants.

The only caveat that I will make is that this hasn't come from a production environment, so I've skimped a bit on a few of the operational details such as deleting the existing data, decent error handling and returning the number of records affected. These are all things that you should add in if you use this kind of thing in a production environment!

Technorati Tags: PerformancePoint,SQL,NativeSQL,Business Rules

The Second Annual Microsoft BI Conference, 6th-8th October 2008 - Seattle

sachatomey — Thu, 31 Jul 2008 20:56:50 GMT

I was unable to make the first Microsoft BI Conference in Seattle last year but Tim made it, and reported that it was a great ~~excuse for an extended weekend of partying and snowboarding in Whistler~~ way to meet like minded people, learn the latest buzz and feel part of the BI Community.

Well, this year I can make it and I will be joining Tim and Jeremy for the duration. Bad luck for me though, it's too early for snow ! ;o)

I've checked out the Tracks and Sessions and there is quite a few I'm up for. They haven't published the schedules yet so whether I can jump tracks and still fit everything will remain to be seen but here are my picks:

Business Value
- Emerging Trends and Technologies in the Business Intelligence Market
- The Path to Pervasive Performance Management
- The ROI of Performance Management

Clients and Applications Track
- Advanced Office PerformancePoint Server 2007 Planning Business Rules and Calculations
- Integrating Microsoft Dynamics Data for Planning, Analysis and Reporting in Office PerformancePoint Server 2007
- The 4th Annual BI Power Hour

Customer Track
- Master Data Management (MDM) Customer Roundtable
- Successfully Implementing the Complete Microsoft BI Stack
- The Evolution of “Difference Makers” at Energizer Holdings, Inc. Using Microsoft Business Intelligence

Deployment & Best Practices Track
- Designing High Performance Cubes in SQL Server 2008 Analysis Services
- Mastering Master Data Management
- Office PerformancePoint Server 2007 Planning - Implementation Tips And Tricks

Partner Track
- BI Unplugged - a Panel Discussion

Platforms and Infrastructure Track
- Optimizing Query Performance in Microsoft SQL Server 2008 Analysis Services
- SQL Server 2008 & Beyond
- SQL Server 2008 Integration Services New Features and Enhancements

Phew! Well, after that lot I'm definitely gonna need a drink, so if you fancy a bevvy, hunt me down, be great to hook up !

Reporting Services 2008 - Connection Problems

Martyn Bullerwell — Wed, 30 Jul 2008 23:05:37 GMT

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.

PerformancePoint Server Planning SP1 - Clear Changes After Workflow Action

sachatomey — Tue, 29 Jul 2008 15:10:08 GMT

There's a new workbook property that was introduced in PPS Service Pack 1. The 'Clear Changes After Workflow Action' effectively splats the change list for the workbook once the assignment has been submitted (either draft or final).

The property can only be reached through the Report Properties dialog, and is at the workbook level:

This property defaults to false which, under certain circumstances can hinder performance.

Whenever you change data on a matrix, the slice that you affected is saved to a change list. You can view what's on the change list by choosing 'View -> Show Current Changes' from the PPS Add-In for Excel.

Here's an example change list; two budget accounts for the same time period and department have been updated to the included new values.

The default behaviour (and the behaviour prior to SP1) is that, for the life of the assignment, the change list is maintained for every cell that is updated The change list is simply appended to, so you can imagine, on a large workbook with several matrices spanning several filter slices, the change list can become quite large.

Submitting the assignment effectively submits the change list for processing by the server, first updating/inserting the appropriate records into the fact table and subsequently re-processing the Analysis Services partition. It follows then, that the larger the change list, the slower the submission process.

Before SP1, this forever growing change list issue was resolved with little user training. As part of the submission process you would invite your users to manually clear the change list:

By 'Clearing Current Changes' you throw away the changes to the cells and have to rely on the data being safe and sound on the server. This process helped keep the change list to a more manageable size thus gaining on the submission performance.

The new 'Clear Changes After Workflow Action' property in SP1, if set to true, will perform the 'Clear Current Changes' step for you automatically. This helps keep the change list lightweight (providing of course, the user regularly submits). However, as I have already implied, there is one issue to be wary of; with the property set to clear changes, if your submission fails, the change list is lost and there is a real danger of losing data.

AdventureWorks for SQL Server 2008

Martyn Bullerwell — Mon, 28 Jul 2008 22:55:20 GMT

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.

Technorati Tags: SQL Server 2008,AdventureWorks

Worth it's weight in....

Tim Kent — Mon, 28 Jul 2008 21:29:50 GMT

Following on from Sacha's post re Adatis achieving MS Gold partnership I just wanted to:

a) blow our own trumpet a little bit more ;-); and

b) add my thanks to our customers, partners and especially the awesome Adatis team who have worked their a*ses off to make this possible!

Whilst Gold partnership isn't perhaps the rarest thing these days it's still a big deal for a small company like ourselves to achieve it.

I'll go now before I do a Gwyneth....sniffle...

Adatis Goes Gold!

sachatomey — Mon, 28 Jul 2008 12:33:00 GMT

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

Tim Kent — Thu, 24 Jul 2008 11:32:33 GMT

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 (you'll need to login and 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

Tim Kent — Wed, 23 Jul 2008 13:48:00 GMT

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 ;)

PerformancePoint Planning Tuning

Martyn Bullerwell — Tue, 22 Jul 2008 20:51:16 GMT

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

BI Evening Slides for PEL Vs MDX

Jeremy Kashel — Fri, 18 Jul 2008 15:00:01 GMT

Many thanks to Chris Webb for organising last night's BI evening at TVP. I found the session very useful, it was great to meet people who are actively implementing MS BI solutions, as well as to see Jes Kirkup's presentation on Excel Services.

The slides from my own presentation, PEL Vs MDX, can be downloaded using the link below (you'll need to register/log in)

PEL Vs MDX Presentation

PerformancePoint Planning - Loading the account dimension members from CSV

sachatomey — Thu, 10 Jul 2008 16:10:30 GMT

More for my benefit this post; I can never remember the format for the CSV file to load the Account dimension.

Below is an example csv file in it's simplest form that should help get me started faster the next time I need it !

Label,Name,Description,AccountTypeMemberId
1000,Salaries Permanent Staff,,Expense
2000,Temporary Staff Workload,,Expense
4000,National Insurance Costs,,Expense
5000,Pension Costs,,Expense

Comments on our blogs

Tim Kent — Thu, 10 Jul 2008 10:11:00 GMT

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

BI Evening, TVP, 17th July 2008

Jeremy Kashel — Fri, 04 Jul 2008 09:06:01 GMT

I'll be giving a talk titled 'PEL Vs MDX' at the UK SQL Server Community's BI Evening on the 17th July at Microsoft's TVP offices in Reading.

The content will be geared towards MS BI developers, and will highlight the differences between the two languages, with the aim that those with MDX experience will be able to make a fast start with PEL.

Also speaking is Jeremy Kirkup from TAH, on 'Using Excel Services with Analysis Services and MOSS'.

If you are just starting to explore delivering BI solutions with Excel Services and SharePoint then there are some issues that it is wise to be aware of in advance. This session will describe some real world lessons gained while creating BI solutions which expose Analysis Services data through the Excel Web Access web part.

The Excel 2007 client has first class support for the some of the advanced features of Analysis services such as drill-through. However, when exposing pivot tables to a browser through the Excel Web Access web part this feature is not available. The session will discuss a couple of approaches to implementing drill-through functionality for EWA based pivot tables using the MOSS SmartPart, AJAX and the Excel Web Services.

You can register for the event here.

Technorati Tags: PEL,MDX,PerformancePoint,BI Evening