SharePoint Magazine - Part 4 available now

My next article for SharePoint Magazine is available now.

Dissecting Dashboards (Part 4 of 6)

Can Dynamics CPM pick up where PerformancePoint left off?

Can Dynamics CPM pick up where PerformancePoint left off? And how does all of this fit in with SSRS Report Builder and Project Gemini, a key part of Microsoft's self-service BI drive? When you say things like "Microsoft Forecaster is a budgeting and planning application that allows companies to build budgets based on specific objectives", one has to wonder!

And "Enterprise Reporting is designed for sophisticated group reporting and consolidation needs, and includes advanced multi-dimensional consolidations, eliminations and multicurrency capabilities. Enterprise Reporting’s planning environment is easily customizable to fit diverse budgeting and forecasting needs across all industries and organizations. Enterprise Reporting provides strong analytical modules to make it extremely easy to create new ad-hoc reports on the fly"

Seriously? That's quite a bundle of tasks. Enquiring minds want to know more!

PerformancePoint Planning: Bug in the bsp_DI_ValidateLabelTable Proc

Somewhat irrelevant due to shut down of PerformancePoint Planning but it's an issue I encountered so I will record it for posterity and future generations (*snigger*).

There is a bug in the bsp_DI_ValidateLabelTable proc which causes it to fail when you are using a specific hierarchy in the model rather than the default 'all members' set. The problem arises when you pass in an incorrect model name.

The proc does not validate that the model name is actually valid before continuing (naughty naughty) which means you can actually cause errors if it just so happens that the value you pass in is a valid object - just not the name of the model itself. Let me show you want I mean.

First let's just mock up some object labels (please note these are NOT real. I would never be so generic but for the purpose of examination...).

Application: PPS

RootModelSite: PPS_Planning

Model: PPS

When calling PPSCMD you often refer to PPS:PPS_Planning.

When executing the bsp_DI_ValidateLabelTable proc, it asks for modelname as a parameter. In my example, I will have a scope in FK_relationships for PPS:PPS_Planning to identify the modelsite but that's not the one I need. Nope. I need PPS_Planning:PPS, which identifies the model.

But I'm half asleep and I've typed PPS:PPS_Planning in the past 24 hours so many times that I just don't think. Uh oh, all of a sudden the proc fails - but not after having made a few updates already.

Silly mistake to make, no question. Still, it does seem a bit dodgy to take an string input from a user, which is also a unique label used to identify a specific record, and not validate that it actually exists before making updates using that value!

SharePoint Magazine - Part 3 Out Now!

My next article for SharePoint Magazine is available now.

http://sharepointmagazine.net/technical/administration/kpis-–-who-am-i-and-where-do-i-come-from-part-3-of-6

No more PPS Planning...

The news, as I'm sure everyone has now heard, is that PerformancePoint Planning is being retired. See the official press release at:

http://www.microsoft.com/presspass/features/2009/jan09/01-27KurtDelbeneQA.mspx

PerformancePoint Monitoring will be bundled into SharePoint as part of Office 14 - and be known as PerformancePoint Services for SharePoint. Wacko.

PerformancePoint Planning: Data Integration

Quick Overview of the steps required in an ETL package to load a PerformancePoint Planning application:

1. Sync Dimensions from App to Staging (call ppscmd)
2. Sync Models from App to Staging (call ppscmd)
3. Stage Dimensions into Label table (data flow)
4. Load Dimensions (call ppscmd)
5. Stage Hierarchies into Label table (data flow)
6. Load Dimensions (call ppscmd)
7. Reindex Dimension tables (sql command)
   
8. Stage Fact into Label table (data flow)
9. Load Fact (call ppscmd)
10. Reindex MG tables
    
11. Deploy Model (call ppscmd)

This presumes you are using the 'Label' table approach.

PPS-P: Where, oh where, does my data go?

A user enters a value into a PerformancePoint Planning form... so what happens next? And why are we the least bit interested?

Firstly, to answer the why do we care question, ask yourself this...

1. When do your extracts from PPS run (presuming you run it more regularly than say once overnight)?
   


2. Do users eg approvers expect to see updates immediately - particularly at certain times of the cycle ie month end?
   


3. What data do reviewers, approvers and report consumers expect to see? Approved? Currency Converted?

So we do care after all. So now to what actually happens. In fact, there are a few things that can happen. Let's examine each separately.

(1) The user selects the Save Privately action

• The data is stored on the local file system - known as the Offline Cache. This location is usually C:\Documents and Settings\%username%\Local Settings\Application Data\Microsoft\PerformancePoint\OfflineCache
• In the Offline Cache, the Catalog.xml file stores a list of all assignments stored locally
• In the Offline Cache, each assignment is stored in a .dat file. The dat file an xml file which contains binaries.
• Data is NOT sent to the SQL server or the cube
• The status of the assignment is still 'started'

(2) The user selects the Submit Draft action

• The data is stored in the SQL table called MG_ModelName_Default_Partition
• The resulting values from Definition Rules are stored in the MG table also however they're linked to the 'No Assignment' assigment
• Once the pre-configured polling period has passed, the cube called ApplicationLabel_ModelSiteLabel will be refreshed
• The status of the assignment is 'Partial'

(3) The user selects the Submit Final action.

• The data is stored in the SQL table called MG_ModelName_Default_Partition
• The resulting values from Definition Rules are stored in the MG table also however they are linked to the 'No Assignment' assigment
• Once the pre-configured polling period has passed, the cube called ApplicationLabel_ModelSiteLabel will be refreshed
• The status of the assignment is 'Submitted'

(4) A data movement job occurs

• The data is copied from the source SQL table called MG_ModelName_Default_Partition to the destination table called MG_ModelName_Default_Partition
• Data is aggregated when the scope of a dimension (in the data association) has been set to "All" on the source model.
• All data is copied: Partial, Submitted, Approved
  

Then of course the question is, how long does all this take? Sadly, there's no simple answer to that question, as so aptly discussed by Peter Ebley. To paraphrase Peter, there are a few steps that occur:

1. Send it to the server (Status = Pending)
   Depends on the size of the submission (How many rows? Is the workbook attached? How many dimensions?) and network bandwidth.
   
   
2. Process the Queue (Status = WaitProcess)
   Depends on the Planning Process Poll Interval (recommendation is 1 minute) and how many items are in the queue and of course, how long it takes to validate the submission.
   
   
3. Cube Processes
   Depends on the OLAP Cube Refresh Interval property in the Workflow tab of PAC.

PerformancePoint Planning - Enable/Disable Spreading

Contrary to the incorrect instructions on Office Online, to enable and disable spreading on a matrix you must do the following:

From the Advanced group on the PPS ribbon, you select Options and the SpreadingType drop-down allows you to enable/disable spreading.

Using SAP as a Data Source

I was recently asked a question which I thought might be of value to everyone.

Can you please let me know...
1. Is share Point and Performance Point can be used in SAP BI or is it strictly Integrated with MircoSoft Technologies!!

So my response was...

I’m assuming you mean “can you use SAP as a data source?”

Firstly, SharePoint isn’t a BI technology as such. It’s a collaboration, portal & document management tool. However it does have the ability to create lists and simple KPIs which you could use SAP data as a source for if you wanted to.

That said, I’m sure your SAP operations team would never allow you to link directly to SAP – you would need to extract into a database first.

Secondly, you can certainly use SAP as a data source for both PerformancePoint modules - Planning and Monitoring & Analyzing. However you would never link directly to the system from PerformancePoint. You would almost always extract from your transactional system into a datawarehouse and that would be your data source (there may be exceptions from time to time but generally it's is wise to separate your reporting engine and your transactional engine. There are numerous gains in doing so - security, performance, aggregation, modelling).

The other key consideration is licensing. SAP is pretty strict about licensing when it comes to extracting data so you would need to take that into consideration also.

Hope that answers your question!

PerformancePoint Planning - Impersonate a Contributor during Form Development

It was be absolutely invaluable to have the ability to impersonate a 'contributor' during form development.

Currently, when developing a form, I have 2 sesssions of Excel open - one as me and one as a contributor. This means I need to create a cycle and an assignment also and republish every time I make a change to the form I'm working on. Merely to validate that I am getting 'yellow cells' as expected (which is by no means a sure thang!).

A feature, similar to Analysis Services where I can impersonate a particular user/role would eliminate the need for all that extra effort - republishing, regenerating etc.

If you too would value this functionality, please vote for it at https://connect.microsoft.com/feedback/ViewFeedback.aspx?FeedbackID=380070&SiteID=181

PerformancePoint Planning - Object Names

Just a quick FYI....

In PerformancePoint Planning, objects are named as follows:

• Cubes: ApplicationLabel_ModelSiteLabel
• Databases: As specified in PAC during create
  
• Application Label must be unique and cannot be changed
• When connecting to an application, the Application Name is displayed

SharePoint Magazine - Part 2 available now

My next article for SharePoint Magazine is available now.

http://sharepointmagazine.net/technical/administration/sell-sell-sell-why-build-a-dashboard-anyway-part-2-of-6

PerformancePoint Planning: Error When Deleting Members of a Member Set

Unfortunately I discovered a problem in PPS Planning today which caused me a few headaches... thought I would pass it on to you!! The issue occurs when editing/creating a dimension member set... a save will fail if you do an outdent and a delete member immediately afterwards. Let me demonstrate.

Let's presume you have a structure something like this:

--- Expenses
--- --- Salaries & Related
--- --- --- Salaries
--- --- --- --- Base Salary
--- --- --- --- Bonuses
--- --- --- --- Allowances
--- --- --- Tax Expense

But you want to remove the Salaries level and end up with a structure that looks like this:

--- Expenses
--- --- Salaries & Related
--- --- --- Base Salary
--- --- --- Bonuses
--- --- --- Allowances
--- --- Tax Expense

If you do this:

1. Outdent the 3 leaf level members
2. Remove the Salary level member
3. Save

You will get an error when you save similar to the following:

In order for it to work correctly, you MUST (in this order):

1. Outdent the 3 leaf level members
2. Save
3. Remove the Salary level member
4. Save
   

I have logged this problem on Microsoft Connect. Please feel free to vote! https://connect.microsoft.com/feedback/ViewFeedback.aspx?FeedbackID=375153&SiteID=181

SharePoint Magazine - Part 1 Out Now!

I've commenced a 6-part series on building PerformancePoint dashboards in SharePoint which you can read at SharePoint Magazine.

I'll be releasing a new part each week for the next 6 weeks covering the various aspects of building dashboards - including why bother at all!

Have a read if you're interested. Feedback so far has been excellent.

Microsoft 70-556 Exam - MCTS Performance Point

Last week I completed and successfully passed the 70-556 exam - which gives me an MCTS PerformancePoint certification.

The exam was relatively simple - I scored highly in all areas but one. Obviously a weak point there on my part... my success in the other areas was due to a week and a bit of study using the Rational Guides to PerformancePoint (authored by my esteemed colleagues Adrian Downes and Nick Barclay) - the Planning volume and the Monitoring & Analyzing Volume. Much thanks to them!

The exam is strongly skewed towards Planning - only a few questions on Monitoring & Analysing - and the M&A questions were very simple. There are quite a few questions on Business Rules and Configuring Models & Dimensions - and these are complex areas so spend time on them in your study.

If you are doing the exam and using the Rational Guide books, don't ignore the Bonus chapters - you'll need them!

Next up is the 70-445 (MCTS SQL BI) which is much harder... I'm currently spending a lot of time with the Microsoft Training kit. Wish me luck...