Financial Planning Accelerator

146 days after Microsoft announced the discontinuation of the PerformancePoint Planning module, Microsoft has made the PPS Planning source code and project files available on a no-cost, individual license basis for customers and partners. It has been rebranded as Financial Planning Accelerator (FPA) and is unsupported. Interestingly, on the same web page that contains the June 18, 2009 announcement, Microsoft spotlights planning alternatives such as Clarity Systems, Calumo, and Longview Planning. I have not had a chance to work with any of these alternatives, but hope to work with one or more in the future. I’ve closed the chapter of my life working with PPS Planning and have no intention of working with it even though it’s free.

Official announcement
Financial Planning Accelerator
Microsoft is pleased to make available the Financial Planning Accelerator (FPA). The FPA is source code and project files derived from the PerformancePoint Server 2007 Planning module. Based on requests from customers and partners, we are making this code available on a no-cost, individual license.

This is unsupported source code that customers and partners can use to support or change PerformancePoint Server Planning functionality. Derived object code files can be distributed to end users with Microsoft SharePoint Server Enterprise Client Access Licenses. To obtain access to the FPA a license agreement between Microsoft and the customer or partner is required. After that agreement is in place, download instructions will be made available.

Please e-mail to request the agreement.

My Farewell to PerformancePoint Planning

Seventeen months after PerformancePoint Server was made available to the general public (September 2007), Microsoft has announced today (January 23, 2009) that they are discontinuing the Planning module.  The Monitoring & Analytics module will be moved to Office 14 SharePoint.  Planning will receive one more update with a Service Pack 3 release in mid-2009 and will be supported for existing customers. 

Official Microsoft Video Announcement from Guy Weismantel

On a personal note, I am truly disappointed as I have invested a great deal of time learning the Planning module.  I learned of this announcement yesterday and I spent last night reflecting over my experiences with the help of a few beers.  I have been working with the software since May 2007, four months prior to the release date.  I dove in head first and have read nearly four PerformancePoint books.  I received my PerformancePoint certification in June 2008.  I had recently begun writing a white paper on PPS Planning Data Integration using SQL Server Integration Services.  As I reflect over the past two years of my career, I remind myself that it’s not all for naught.  I’ve learned a great deal about Business Performance Management and Business Intelligence that will still apply to my career.  Through this blog, I have met some terrific people across the globe who shared the same passion and aspirations around PerformancePoint.  And for that, I am very thankful.  For those of you silent readers, I hope you have found my postings useful.  My blog will live on.  I haven’t determined the format yet, but it will surely focus on other areas of Business Intelligence and wherever my work and life experiences may take me.  You haven’t heard the last from me.

Friends and curious readers, I raise my glass, and offer a toast to new beginnings. 

I will conclude with a quote from the last line in the movie, Gladiator, as I say my farewell to Planning:

“And now we are free.  I will see you again… but not yet… Not yet!”


Update January 26, 2009:

Here are a couple links to published articles on the rise and fall of PerformancePoint Planning.

Running the Microsoft All Up BI 7.1 VPC on an external hard drive

Yesterday I decided to check out a VPC (Virtual Personal Computer) image that Microsoft offers as a free download.  I found links to the Microsoft All Up BI VPC 7.1 on Russell Christopher’s blog.

This VPC contains everything you would ever want related to Microsoft BI.  SQL Server 2005, PerformancePoint, SharePoint, ProClarity, Office 2007, etc.  Hmm, I wonder when they’ll create an updated VPC with SQL Server 2008…

Anyway, I haven’t had much time to play around with it yet, but I did want to share a minor obstacle I had to overcome to get the VPC running.  I wanted to copy all the VPC files to my Western Digital My Passport 250 GB external hard drive.  However, I kept receiving an error that the drive was out of space.  This left me scratching my head because I could see I had over 230 GB of free space.  After some discussion with a co-worker, we decided I needed to reformat the external hard drive.  It comes out of the package formatted using FAT32.  FAT32 can only handle up to a 4 GB file.  That’s why I was receiving an out of space error.  So, I copied the contents of my external hard drive to my laptop (which is running Windows XP), and then opened up Computer Management by right clicking on “My Computer” and selecting “Manage”.  From here you can find “Disk Management” under “Storage”.  Select the correct drive for your external hard drive and format using NTFS.  I didn’t time how long it took, but it sure seemed like about an hour or so.  Once complete, my hard drive could store the VPC.  From that point, it wasn’t long before I was up and running.  Now I just need to figure out how I’m going to snag some more RAM for my laptop, because 2 GB of RAM is simply not enough!  I found some tips on a blog for speeding up the All Up VPC version 6, but I haven’t tried them out yet.  Another task for another day.

By the way, older operating systems won’t be compatible with NTFS.  From what I’ve read, it sounds like Windows 95/98/Me won’t recognize NTFS.  So, if you’re running an older operating system, be sure to do your homework prior to reformating your external hard drive.

– ez

PerformancePoint Planning Hierarchy Sorting

We ran into an issue earlier in the year where we wanted to sort our PPS Planning Product hierarchy through an automated process as part of the nightly batch cycle.  The Product dimension is custom defined and we refresh the data for both D_Product and H_Product tables daily.  We created a flat hierarchy in the Member Set (all children have the same parent in the Member Set or H_ table).  So in order to create the hierarchical relationships, we use a Member View based off of member properties (attribute columns) in the Product dimension to create the actual hierarchy relationships.  Regardless of how you decide to create your hierarchical relationships (Member Sets versus Member Views), you’ll likely want to sort the records.  I have not had success trying to update the SequenceNumber field using BizSystemFlag = 300 methodology.  My goal was to avoid writing an actual UPDATE statement against the Appliction database, so I use the work around below when loading data to the H_Product table.  Through this SQL, I am able to set the Sequence Number using the ROW_NUMBER() OVER() functions when inserting records into the H_ table on the StagingDB using BizSystemFlag = 200.  PerformancePoint automatically increments the SequenceNumber by 1024, so I follow the same increment standards in the example below.  The SQL statement sorts the data based upon member property columns (attributes) that are user defined in the dimension.  If you are using SSIS to perform the ETL, the SQL statement would be in your OLE DB Source and the destination would be the H_Product table.  For this process to work, you would first need to delete the data from the H_ table prior to loading using BizSystemFlag = 400.

   NULL as RowId
  ,0 as ParentMemberId
  ,200 as BizSystemFlag
  ,NULL as BizSystemErrorDetails
FROM D_Product d
JOIN (   
     SELECT MemberId, 
            ORDER BY Attribute1, Attribute2, Attribute3, [Name])* 1024 as SequenceNumber
     FROM D_Product
     WHERE Label NOT IN (‘NONE’)
     ) a
ON d.MemberId = a.MemberId
ORDER BY a.SequenceNumber

I hope this helps.
– ez

PerformancePoint LoadingControlID

From time to time, I’ll be questioned how a record was created in PerformancePoint Planning.  A quick check of the LoadingControlID in the table of interest can provide insight into how the record was generated.  This field provides a code, normally 1000 or 2000.  But if you don’t know the meaning of the code, it does not help too much.  I performed a search over the Internet and found very little on the topic of the LoadingControlID.  So, I hope you will find this post helpful.  There is a table in the application database called LoadingControlID that contains all the definitions of these LoadingControlID’s.  Below are the definitions.

0         Unknown
1000   ReferenceDataSubmission
2000   DataLoad
3000   GenericCalculation
3001   SharesCalculation
3002   Consolidation
3003   IntercompanyReconciliation
3004   CurrencyConversion
3005   OpeningBalanceRule
3006   MovementRule
3007   SeedingRule
3008   AllocationRule
4000   DataSubmission
5000   TypeLibraryGenerated
6000   AssociationFactDataMovement

My experiences have shown that data loaded via the PPSCmd have a LoadingControlID of 2000 (DataLoad).  Data that is manually entered via the Planning Business Modeler has a code of 1000 (ReferenceDataSubmission).

When loading new records via the PPSCmd or from the “Load Data From Staging” command in Planning Business Modeler, there’s no need to populate this field as PPS generates the correct code on its own.


PerformancePoint Certification Exam 70-556

I recently found the time to sit for the PerformancePoint certification exam, 70-556.  I am pleased to say that I’m now a “Microsoft Certified Technology Specialist (MCTS) in Microsoft Office PerformancePoint Server 2007”.  To prepare for the exam, I followed the study guide that Microsoft provides at .   M&A makes up a small portion of the exam as the guide suggests.  Good luck!

– ez

Remote PPSCmd Call

I found an interesting blog entry recently that discusses calling the PerformancePoint Planning PPSCmd.exe remotely from an SSIS Control Flow task.  I couldn’t find a place to add a comment on the site, so I decided to create a separate entry on my blog.

Here’s the link to Norm’s blog:

In our scenario, the SSIS package resides on a database server, which is separate from the PPS Planning server.  Initially, I thought we couldn’t install the PPS Planning client on the database server (due to licensing issues), so we wrote a VB.Net application that remotely called PPSCmd on the PPS Planning server.  After I used this for a few months, I realized we could just as easily install the PPS Planning client on the database server, so the remote call was no longer needed.  In Norm’s blog, he suggests downloading an application from Microsoft called PSEXEC to assist in this remote call if you can’t have the ppscmd.exe file on your SSIS server.  If you choose to write a custom app to perform this function, you can borrow some of the code below.  SSIS calls the VB dll from a Script Task.  You’ll need to add the assembly as a Reference in your script task.

You can download the VB code here.

RemoteProcessCaller:  A VB dll that runs and manages a remote process

The component has a class named “RemoteProcess” and its constructor takes five arguments:

– CommandLine (string -the path and file to execute)
– Machine Name (string -the server on which to execute the process)
– UserName (string -the login account to the remote server)
– Password (string -the pw for the login account)
– Timeout (int – the time in milliseconds to run the process.)
The “RemoteProcess” class contains a string function called “RunProcess” which returns a “0” for success and the error message if applicable.
Here’s the SSIS Script Task code:

 Public Sub Main()

        Dim t As New RemoteProcess(“c:\Program Files (x86)\Microsoft Office PerformancePoint Server\3.0\BizModeler\PPSCmd.exe StagingDB /Operation LoadDataFromStaging /Server http://localhost:46787 /Path “”<path>.model:<modelsite name>”” /NoValidation”, “<servername>”, “<userid>”, “<password>”, 800000)

        If Not t.RunProcess() = “0” Then
            Throw New Exception(t.RunProcess())
            Dts.TaskResult = Dts.Results.Failure
            Dts.TaskResult = Dts.Results.Success
        End If
     End Sub

End Class
Hope you find this helpful.  I found it much easier to simply install the PPS Planning ppscmd.exe on the same server as the SSIS package, so we decided to scrap this work.  Having the PPSCmd.exe local just requires changing the server argument to use the server name rather than localhost.  But if you’re in a situation where you must perform a remote process call, you can try adapting this code, or merely download the PSEXEC application from the link on Norm’s blog.  I’ve never tried the PSEXEC application so I cannot attest to it one way or the other.  Let me know how it works out for you.