Sacha Tomey

Sacha Tomey's Blog

Essential Adatis Blogs

I've been speaking to a couple of people recently that, although subscribe to my blog, are not aware of our PPS Planning Sharepoint Web Part that Tim has blogged about here and here.  This post is more of an awareness that we have other bloggers onboard and much of the content is of the 'essential' variety.

Currently blogging are Tim, Martyn and Jeremy with our aggregated feed that includes the lot.

Share Calculations and Effective Ownership

In PerformancePoint Planning Server there are several pre-defined model types that satisfy different requirements. One of these model types is Financial Model with Shares Calculations and its primary use is for the financial consolidation of a group of companies where the ownership of the companies within the group is not 100%.

Take the following international group of companies:

image

Adatis (UK) is the ultimate parent (and in this case the holding company too) and own a 90% shareholding of Adatis (Italy) and Adatis (US). Adatis (Canada) is only 60% owned, although notice that Adatis (Italy) owns the remaining 40% of Adatis (Canada). Adatis (France) and Adatis (Switzerland) are both 50% owned by Adatis (Italy) who also owns 45% of Adatis (Germany).

In order to work out the consolidated accounts of the Adatis (UK) Group, one of the first steps is to determine the effective ownership of each of the companies within the group. This needs to be done from the perspective of the holding company.

What do I mean by effective ownership? I mentioned earlier that Adatis (Canada) was 60% owned directly by Adatis (UK) and 40% owned by Adatis (Italy). So you would be forgiven by thinking that Adatis (Canada) is 100% owned within the group albeit split between two of the companies.

However, notice t hat Adatis (Italy) is only 90% owned. This means that Adatis (UK) only effectively owns 90% of the 40% of Adatis (Canada) plus the 60% it owns directly. Therefore, the effective ownership is 90% of 40% = 36% + 60% = 96%.

Below outlines the effective ownership from the perspective of Adatis (UK) for the entire group.image

PerformancePoint Server ‘With Shares’ Financial Model contains a built in Shares Calculation job that should, in theory, determine the effective ownership for a group of companies.

In order to test the PerformancePoint Shares Calculations you need to load data into two of the three additional measure groups that are automatically created in a ‘With Shares’ Financial Model.

Shares Outstanding

Each company needs a set of shares issued and the number of those shares that have voting rights. To keep everything simple I’ve issued all companies with 10000 shares with all shares having voting rights.

image

Shares Owned

For each of the parent companies the share ownership needs specifying. We have two parent companies, Adatis (UK) and Adatis (Italy). The shares owned have been set according to our company breakdown illustrated above.

Adatis (UK) owned companies

image 

Adatis (Italy) owned companies

image 

Control and Ownership

With the data loaded the shares calculation job can be executed. The results are posted to the third of the additional measure groups, Control and Ownership.

image

It’s the POWN columns, highlighted, that contain the effective ownership and encouragingly this matches our expectation. So it looks like, under these simple conditions the Shares Calculation job does what it should.

Incidentally, there are a few additional columns that are of interest.

- The PCON columns are the percentage that will be used in the consolidation.

- The PCTRL (Ultimate Percent Control) columns specify the controlling percentage. This is essentially controlled by the number of voting shares compared to shares owned and effective ownership.

- The ‘Overridden’ equivalents can be used to override the results of the shares calculation job and are the actual values used in the consolidation process. The non-overridden columns exist to show the outcome of the calculation. These can be altered but will have no effect as they are not used in the consolidation process itself.

- The final additional column represents the Consolidation Method. There is a simple formula that PPS uses to determine the Consolidation Method:

o If the ultimate percent control (PCTRL) is >= 0.0 and < 0.2, the consolidation method is NONE.

o If the ultimate percent control (PCTRL) is >= 0.2 and < 0.5, the consolidation method is Equity.

o If the ultimate percent control (PCTRL) is >= 0.5 and <= 1.0, the consolidation method is Full.

o If the entity is the holding company for the model, the consolidation method is Holding.

It’s important to note that there is no automatic way of setting a proportionally consolidated company. The consolidation method can be changed and will need to be changed if, for example, you wanted to proportionally consolidate Switzerland, and/or France as opposed to the Full consolidation by default.

PerformancePoint Server Job Parameters

Just a quick tip to save some mouse clicks. 

When you are setting parameters for period start/end type variables you only have to set the end period if it differs from the start period.  I used to think all parameters were mandatory.

It's particularly handy for a Consolidation Job as there are 2 sets of periods to set.  It annoys me slightly that there is no Dynamic time selection available for Job parameters so you have to scroll through the time dimension to find a specific member whereas now I only have to do this twice, not 4 times !

ConsolidationJob

PerformancePoint Planning Staging Fact Load Validation

PerformancePoint provides a staging database to load data into.  Data loaded can be validated within the staging database before synchronising to the main application.

Below is a quick description of the validation steps performed - it could help feed into an ETL specification - if any of the 'rules' are broken validation will fail and the main application model cannot be synchronised.

Foreign Key Checks
All foreign keys must reference dimension members that exist.

Leaf Level Checks
Can only load leaf level facts unless the Business Process member is set to MANADJ or FXADJ.

Header Accounts
Facts cannot be loaded against accounts of type 'Header'

Business Process Members
The following Business Process members are calculated and cannot be loaded:
- CONSOLIDATED
- PREALLOC
- POSTALLOC
- PREELIM

Time Data View
Facts can only be loaded against the 'Periodic' dimension member.

Currency Checks
Input Currency must match Entity currency unless you are loading against the FXAdj Business Process in which case you can specify a different currency.

Flow Checks
The following Flow members are calculated and cannot be loaded:
- FX
- FXO
- FXF
- OPE
- MVT
- INT

Account Type/Flow Checks
No Flow is allowed on Income Statement/Profit and Loss and Non Financial accounts.

Flow is required for Balance Sheet accounts.

Intercompany Checks
Entity, Intercompany and Account dimension members must all have appropriate Entity and Account types, that set the 'Intercompany' member property to true for all Intercompany facts

Book Review: Monitoring and Analyzing with Microsoft PerformancePoint Server 2007

Tim Kent already reviewed this book last month after polishing it off over a weekend.  Tim liked the book and gave a positive review.  However, I felt that it is difficult to gauge how good the book is as, to be fair, Tim is hardly a newbie when it comes to PPS Monitoring and Analytics. 

I've focused my PPS time on the planning element and normally throw the M&A elements over Tims fence so I've not spent as much time with Monitoring and Analytics as I should have.  Tim has got wise and has installed a new, higher fence, therefore, I thought it was about time I put the book to the 'newbie' test.

It's my first Rational Guide and I really like the concept.  The guides are limited to 250 pages so they have to be concise and to the point.  I like the 'pocket' size, the formatting and layout that I presume is consistent across all Rational Guides.

Considering the book is penned by two authors, I couldn't tell.  The writing style is clear and consistent and pitched at the same level throughout the book.  The detail is very well written with forward and backward references to other parts of the book to seamlessly link everything together.

The book is split into 5 parts, Introduction, Installation and Config, The Elements, Implementation and Management and finally Extras which at first I thought was just an index and an advert for the other related Rational Guides - wow, thanks, you shouldn't have - but as it happens, on the very last page is information on how to register your book to obtain more information such as extra chapters and samples that the authors, Nick Barclay and Adrian Downes, could not squeeze into the already packed 250 pages.  One perhaps obvious casualty of the page limit is ProClarity, in the words of the authors, "It is beyond the scope of this book to detail the functionality available in the ProClarity product suite..".

The introduction covers a lot of ground; Performance Management, BI and the Microsoft BI Stack and PerformancePoint Server 2007 itself.  A whopping 20 pages of intro which does eat into the 250 page limit - I'm not sure whether the scope of the intro was specified by the Rational Guide editors or the authors wanted to widen the audience but I'm not convinced it needed all of it at the expense of product related material.  It will be interesting to see how much of it is re-used in the PerformancePoint Planning companion book.

The main body of the book follows a 'theory and practice' type approach.  Firstly explaining, in detail, each subject or feature, following up with a step-by-step tutorial to implement what you've just learnt.  Each chapter and section tends to build on the previous one so it's worth reading from front to back rather than darting around chapters of interest and this is largely down to the nature of the product rather than any conscious decision I'm sure.  All in all, for me, this approach works; not only do I gain some background insight and theory behind the product, I also gain some touchy-feely experience that you can't glean from just reading alone.

If you are a newbie wanting a book to smooth out the learning curve to get you up to speed quickly and easily, with not only the product but with the theory behind the product too, this book, in my opinion, is it.  It can't take you to guru status but it provides a solid framework to provide the initial stepping stone.  To sum up, rather than spend half of the 4-day, ~£1000, Microsoft Partner PPS course learning about Monitoring and Analytics, take a couple of days off, buy this book and use it on a VPC image of the MS BI Stack - I bet you'll learn more!

71-556 PerformancePoint 2007 Beta Exam - We only bl**dy did it !

Back on November 9th, the, I think, last available day it could be taken, Tim Kent and I took the Microsoft Office PerformancePoint Server 2007, Application Development Beta Exam (71-556) at the Prometric Testing facility hosted by Global Knowledge Training in Wokingham.

Soon after, we were both invited to Seattle to attend the PPS Exam Preparation workshop to review the exam in readiness for the official release.  Disappointingly I couldn't make it due to other PPS related commitments, however, much to my disgust Tim went without me, and by all accounts had a blast on the slopes of Whistler meeting a like-minded bunch of people.

Today we both found out we actually passed!  This means we don't have to re-take the officially released exam as we get an automatic credit for passing the beta version - nice !

I have not seen any official release information for the exam proper but it must be imminent now that we've received our results.  I'm not sure if this is fact but I have been told that Microsoft use the '71' prefix for Beta exams so we should expect the official exam to be numbered 70-556.  Anyone know if this numbering convention is true?

Obviously my hands are tied by the exam NDA so I can't reveal any details about the content of the exam.  Already publicly available is the list of topics that the exam covers, this list, repeated below, does match my experience of the beta exam.  I'll stop there as I've probably already said too much !  Good luck if you go for it.  Oh yeah, happy new year !

Microsoft Office PerformancePoint Server 2007, Application Development Exam Topics

Configuring Dimensions

  • Configure properties of predefined dimensions
  • Configure the time dimension
  • Configure a member property
  • Populate dimensions and member sets
  • Update dimension members and member set, and member properties

Configuring Models

  • Create a PPS model
  • Configure the current period
  • Configure model and dimension usage within sites
  • Transfer data between models

Configuring Business Roles and Process Management

  • Configure business roles
  • Configure security for users
  • Configure assignments
  • Configure an approval structure

Developing Business Rules

  • Develop an assignment rule
  • Develop a definition rule
  • Develop an allocation rule
  • Configure template rules
  • Configure rule execution

Building Excel Add-In Forms and Reports

  • Build an Excel Add-In report
  • Configure an Excel Add-In report as a form
  • Configure report filters
  • Format an Excel Add-In report

Developing Dashboards

  • Create a report by using the Dashboard Designer
  • Add a data source connection
  • Add a new Web page report
  • Configure a KPI by using Dashboard Designer
  • Create a scorecard
  • Build a dashboard

Installing PerformancePoint Server

  • Install the monitoring and planning services
  • Configure a PerformancePoint application
  • Configure the PerformancePoint server
  • Assign users to administrator roles

PerformancePoint 2007 Planning - Account Type Crib Sheet

UPDATE:  Version 2 details can be found below (Link updated)

Being a non-accountant type, choosing the right account type for members of the account dimension was a little bit of 'trial and error'.  After much hair pulling and frustration at my lack of accountancy knowledge I put together a crib sheet to help make some more educated selections.

I laminated a copy and kept it to hand so I could take a sneaky peak every now and again - well, it appears that a couple more people had taken a sneaky peak and have also found it more than useful - considering they are accountancy types it struck me that it is more useful than I originally thought.

image So, as it's the festive period, and a time for giving; here's your, for accountancy and non-accountancy types alike, very own, laminated Account Type Crib Sheet. 
Just add laminate!




 PPS-ACCS.xps (183.94 kb)


Merry Christmas and a Happy New Year !



Planned Performance Improvements for PerformancePoint Server 2007 Planning

There are a few performance improvements planned for SP1 and earlier in an interim hotfix.  The details I have are high-level but enough to get at least a little excited that development is on going and pertinent to 'in-the-field' issues.

In an upcoming hot fix (KB946222) expected release prior to SP1 is currently being worked on.

Issue:
When a system contains a large number of assignments, retrieving the list of available assignments for a user has a significant performance issue.

Resolution:
Optimised stored procedure for retrieving assignments to dramatically cut time to retrieve list of available assignments.

Performance issues targeted for SP1 include:

  • Slow performance when re-opening assignment after save/submit.
  • No option to disable what-if calculation on assignment or matrix level which leads to unnecessary what-if queries that slow form performance
  • Successfully submitted change-lists are not automatically from the assignment in the V1 design which leads to unnecessarily large what-if queries
  • MDX query construction optimization
  • Client-Server data volume
  • These are targeted issues and hence they may not make the cut but it's encouraging all the same.

    MDX Stored Procedures (Part 2)

    What seems like a lifetime ago I posted about MDX Stored Procedures and how we've built a helper class that provides a wrapper to an MDX query.

    The helper class has been stable for quite sometime now and a few people have been asking me when I'm going to release it.  Well, today is the day.

    You can download a Visual Studio 2005 C# Quick Start project from here.

    The idea behind the class is that text files containing parameterised MDX queries can be executed against a cube with just a couple of lines of code.

    Here are some notes to complement the quick start to get you going:

    Step 1:  Create an instance of the helper class

    MDXHelper helper = new MDXHelper
        ("localhost",
        "Adventure Works DW Standard Edition",
        "Adventure Works",
        MDXHelper.MDXResourceFormat.RelativeURL,
        "MDX/Product Category List.txt");

    There are a couple of overloads on the constructor.  The one above specifies all the options.  The first three parameters set the Analysis Services Server, the database, the cube.  The last two parameters specify what format the MDX query.  In this case it's a Relative URL meaning that the next parameter must specify a relative URL pointing to a text file containing the desired query.  The alternative to a Relative URL is 'Text' which means the last parameter must be the query itself rather than a path to a file.

    Step 2:  Add the parameters to the parameter collection.

    helper.Parameters.Add(
        "@ProductCategory",
        CategoryList.SelectedValue);

    Parameters are simply name/value pairs and are held internally as a generic dictionary<string,object>.  Parameters are optional but you will get an error if you invoke a parameterised query without defining a value for every parameter.

    Step 3: Execute the Query

    DataTable categoryList = helper.ExecuteDataTable();

    A DataTable or a CellSet can be returned, again various overloads exist to provide additional flexibility, for example you can set the resource format and query path on the ExecuteDataTable e.g.

    DataTable result = helper.ExecuteDataTable(MDXHelper.MDXResourceFormat.RelativeURL, "MDX/Internet Sales by Country.txt");

    Additional Notes

    The query definitions are cached in the ASP.NET application cache so if you make any changes to the query definition files you'll need to recycle the application pool.

    The helper class will only work for Web Projects.  There is no support for WInForms/SmartClient deployments.

    If you are returning a data table you can specify some additional options. 

    • Untyped is the fastest and default, returning a plain data table based on a flattened CellSet object. 
    • Typed brings back a Data Table with the Schema column data types set to match the CellSet object it was derived from.  This can be slow for large datasets.
    • UntypedFormatted brings back a plain data table but using the formatting e.g currency formats etc held in the cube.

    There are some additional properties containing meta data debugging information that have been useful.  ExecutedMdxQuery contains the actual MDX that was executed after the parameter replacements have occurred.  DatabaseServerVersion, CubeDescription, CubeLastProcess and CubeLastSchemaUpdate are pulled from the server when a query is executed.

    You can use parameters to define any aspect of the query.  E.g.  You could use parameters to swap rows and columns, define measures, pretty much anything you can achieve with search and replace.  On one hand this provides no end of flexibility but on the other provides diddly squat compile time debugging!

    The same database connection is maintained between query execution calls.

    All feedback gratefully received.

    Planning a PerformancePoint Planning Implementation

    There is a useful spreadsheet (what other format would it be!?) to assist in the requirement gathering and the planning of the implementation for a PerformancePoint Planning Application.

    It's a strange mix of high and lower level detail but it does ensure you capture and at least think about each element of your application and the business requirements it should satisfy.  It's a useful starting point/stake in the ground although some of the questions will require much more thought and analysis behind the scenes than others.

    The main topics it covers are:

    • Completing an Impact Assessment
    • Application, Model, and Model Site Planning Considerations
    • Model Type Planning Considerations
    • Dimension Planning Considerations
    • Currency Translation Planning Considerations
    • Data Loading Planning Considerations
    • Business and Process Planning Considerations
    • Reporting Planning Considerations
    • Business Rule Planning Considerations
    • Model-to-Model Association Planning Considerations
    • Diagramming the Application