Sacha Tomey

Sacha Tomey's Blog

Friday Fun: GeoFlow does the Great South Run

GeoFlow was released to public preview yesterday; a new 3D visualization tool for Excel which allow users to create, navigate and interact with time-sensitive data applied to a digital map.

Back in October last year, along with 25,000 other people, my good friend and colleague Tim Kent (@TimK_Adatis) and I ran the Great South Run; a 10 mile run around the City of Portsmouth on the south coast of England.  As it happened, we both wore GPS watches and using the data collected I've created a simple GeoFlow tour of the race.

Tim is Green - I am Red - who wins...  there's only one way to find out ......

Run Race

Creating a Custom Gemini/PowerPivot Data Feed – Method 1 – ADO.NET Data Services

There are already a few good Gemini/PowerPivot blogs that provide an introduction into what it is and does so there is no need for repetition.  What I haven’t seen are examples of how existing investments can be harnessed for Gemini/PowerPivot based self-service analytics.

This series of posts focuses on various ways of creating Custom Data Feeds that can be used by Gemini/PowerPivot natively – Providing a direct feed from otherwise closed systems opens up new channels of analytics to the end user.

Gemini/PowerPivot supports reading data from Atom-based data feeds, this post looks at a quick way of creating an Atom-based feed that can be consumed by Gemini/PowerPivot.  By far the simplest way to develop an Atom-based data feed is to employ ADO.NET Data Services in conjunction with ADO.NET Entity Framework.  With very few (in fact one and a bit!) lines of code, a data source can be exposed as a feed that Gemini/PowerPivot can read natively. 

I am going to use the AdventureWorksDW sample hosted by a SQL Server 2008 R2 instance for this – obviously Gemini/PowerPivot natively reads SQL Server databases, so creating a custom feed over the top may seems a little pointless.  However, this technique may be useful for quick wins in several scenarios, including:

- Preventing the need for users to connect directly to the underlying data source.
- Restricting access to various elements of the data source (tables/columns etc)
- Applying simple business logic to raw data.

ADO.NET Data Services are a form of Windows Communication Foundation (WCF) services, and therefore can be hosted in various environments.  Here, I will simply host the ADO.NET Data Service inside an ASP.NET site.

To create a Native Gemini/PowerPivot feed, you take seven steps:

1 - Create ASP.NET Web Application
2 - Create Entity Data Model
3 - Create the Schema
4 - Create the Data Service
5 - Load From Data Feed
6 - Create Relationships
7 - Test

Step 1) Create ASP.NET Web Application

I’m using Visual Studio 2008 here to create an ASP.NET Web Application.


Step 2) Create Entity Data Model

Add an ADO.NET Entity Data Model item to the project, these files have a .edmx extension and allow us to create a schema that maps to the underlying database objects.


Step 3) Create the Schema

We simply require a 1:1 mapping so will ‘Generate from Database’.  Incidentally, the ‘Empty Model’ option allows you to build a conceptual model of the database resulting in custom classes that can be optionally mapped to the database objects later.


Create a Microsoft SQL Server connection to AdventureWorksDW2008.


Select the appropriate database objects, I’ve selected the following tables:

- DimCurrency
- DimCustomer
- DimDate
- DimProduct
- DimPromotion
- DimSalesTerritory
- FactInternetSales


Once the wizard has completed, a new .edmx and associated cs file is created that respectively contain an Entity Relationship Diagram and a set of Auto Generated Classes that represent the database objects.

Due to the way the Entity Framework handles Foreign Key Constraints we have to apply a workaround to ensure the Foreign Keys on the FactInternetSales table are exposed and brought into Gemini/PowerPivot.  A previous post Exposing Foreign Keys as Properties through ADO.NET Entity Framework walks through the workaround.



Step 4) Create the Data Service

Add an ADO.NET Data Service item to the project.


The service class inherits from a generic version of the System.Data.Services.DataService object, so we need to inform the compiler what class to base the generic object on.  We essentially want to base our Data Service on the class representing our newly created Entity Data Model.  The class name is derived from the database name, unless changed when the Entity Data Model was created, so in our case the class name is AdventureWorksDW2008Entities.

The auto generated service class contains a ‘TODO’ comment that asks you to ‘put your data source class name here’.  The comment needs replacing with AdventureWorksDW2008Entities.

The final step is to expose the resources in the Entity Data Model.  For security reasons, a data service does not expose any resources by default.  Resources need to be explicitly enabled.

To allow read only access to the resources in the Entity Data Model the InitializeService method needs updating with a single line of code.  The code snippet below details the final class implementation, notice the AdventureWorksDW2008Entities reference at line 1 and the the explicit resource enablement at line 6.

Code Snippet
  1. public class GeminiDataService : DataService<AdventureWorksDW2008Entities>
  2.     {
  3.         // This method is called only once to initialize service-wide policies.
  4.         public static void InitializeService(IDataServiceConfiguration config)
  5.         {
  6.             config.SetEntitySetAccessRule("*", EntitySetRights.AllRead);
  7.         }
  8.     }

That’s all that’s needed, by default, ADO.NET Data Services conform to the Atom standard, so in theory the Service is ready to be consumed by Gemini/PowerPivot.

Before we try, it’s worth giving the service a quick test, building and running the solution (F5) launches Internet Explorer navigating to the Service hosted by the ASP.NET Development Server.


You are first presented with an XML document containing elements that represent database objects, you can further drill into the objects by amending the URL.  For example, if you want to see the contents of the DimPromotion table then append DimPromotion to the end of the URL: http://localhost:56867/GeminiDataService.svc/DimPromotion (Case sensitive)

Note:  You may need to turn off Feed Reader View in Internet Explorer to see the raw XML (Tools->Internet Options–>Content->Settings–>Turn On Feed Reader View – make sure this is unchecked)


As a slight aside, the URL can be further enhanced to, filter, top n rows, extract certain properties etc etc. Here are a couple of examples:

URL Effect
http://localhost:56867/GeminiDataService.svc/DimCustomer?$top=5 Return the top 5 Customers
http://localhost:56867/GeminiDataService.svc/DimCustomer(11002) Return Customer with id 11002
http://localhost:56867/GeminiDataService.svc/DimCustomer(11002)/FirstName Return the First Name of Customer 11002
http://localhost:56867/GeminiDataService.svc/DimProduct(310)?$exapnd=FactInternetSales Returns Product with id 310 and all related Internet Sales Records

Confident that the feed is working, we can now deploy the service, and start using the feed in Gemini/PowerPivot. 

Step 5) Load From Data Feed

Open up Excel 2010, launch the Gemini/PowerPivot Client (by selecting ‘Load & Prepare Data’)


Select ‘From Data Feed’ from the ‘Get External Data’ section of the Gemini/PowerPivot Home Ribbon to launch the Table Import Wizard.


Specify the Url from the ADO.NET Data Services feed created earlier, in my case: http://localhost:56867/GeminiDataService.svc as the 'Data Feed Url’ and click Next.

Incidentally, you can use the majority of the enhanced Urls to, for example only select the DimProduct table should you so wish, however by specifying the root Url for the service you have access to all objects exposed by the service.


From the Table Import Wizard Select the required tables, in my case I’ll select them all.  (You can optionally rename and filter the feed objects here too).

Following the summary screen, the Gemini/PowerPivot Client then gets to work importing the data from the ADO.NET Data Service:


Once completed, Gemini/PowerPivot displays all the data from all of the feed objects as if it came directly from the underlying database.


Step 6) Create Relationships

There is one final step before we can test our model using an Excel Pivot Table.  We need to create the relationships between the tables we have imported.  The Gemini/PowerPivot Client provides a simple, if a little onerous way of creating relationships, the ‘Create Relationship’ action on the Relationships section of the Home Ribbon launches the Create Relationship wizard:


Each table needs relating back to the primary Fact table which results in the following relationships:


Step 7) Test

We are now ready to start our analysis, selecting PivotTable from the View section of the Gemini/PowerPivot Client Home ribbon creates a pivot table in the underlying Excel workbook attached to your custom fed Gemini/PowerPivot data model.







So, to allow fast access to, for example, potentially sensitive data, through Gemini/PowerPivot you can quickly build a custom data feed that can be consumed natively by the Gemini/PowerPivot Client data feed functionality.

RIP PerformancePoint Planning

It's nearly a week since the announcement that shook the (PPS) world !  It's been a bit difficult to report on; generally the Adatis blogs try and offer solutions to problems we have encountered out in the real-world.  Now I could say something crass here about the real-world and the decision makers involved...but that would be childish right?

If I was to offer up my feelings, they wouldn't be that far from Alan Whitehouse's excellent post on the subject.  If I had an ounce of class about me, it would be much more aligned with Adrian's poignant discussion opener, the one with the sharp-witted title, but alas....

We've spent the best part of the week speaking to customers, partners and Microsoft about what to do next.  The timing was choice - would you believe, we actually had three new PerformancePoint Planning phases kicking off this week, according to my project plan - I should be setting up Kerberos as we speak..  [There is always a positive right?]

Some customers are carrying on regardless, they...

...already have planning deployments and are too far invested and dependent to back out at this stage or, 

...have a short-term view (That's not a criticism) and need a "quick" fix with a low TCO to get them through some initial grief.  (Typically these customers are going through rapid organisational change, or form part of a recent acquisition and, to help them see the wood from the trees during the transition, require short/sharp solutions)

Other customers, with longer-term views, feel the product, or more importantly, the suitably skilled resource pool, will drain away far quicker than the life-span of the much touted Microsoft product support.  I have to agree - Fact - Adatis will not be employing or training anymore PerformancePoint Planning Consultants.  I doubt many other consulting firms will either.

It's those customers with the longer-term view that are the ones currently in limbo - they are experiencing pain, they need pain relief, what should they do - wait and see what Office 14/15 offers? (There is talk of some planning functionality appearing in future Office versions - what truth there is in that..?).

The Dynamics customers could wait for the resurrection of Forecaster - I do have information on good authority that they will be developing Forecaster to be closer, in terms of flexibility, to PPS Planning.  I had originally heard the opposite view in that Forecaster will be replaced with a cut down version of PPS Planning.  Either way, I'm sure some of the PPS Planning code-base will be utilised, which could end rumours of PPS Planning being 'given' to the community as some form of community/open-source arrangement.  An arrangement that is, in my opinion, a non-starter anyway, "Hey, Mr FD, We've got this great open-source budgeting and forecasting product we think you should implement!" - yeah right !

Another rumour (and mixed message) is that Service Pack 3 will contain some of the requested features that were earmarked for version 2 (After all, the code has already been written, right?) this rumour was actually started by Guy Weismantel in his Announcement Video.  However, the information I have since received, clearly states that Service Pack 3 will contain stability and bug fixes only - so which is it to be?  It's unlikely for a service pack to contain new features, but it's not unheard of; anyone remember the original release of Reporting Services?  That arrived as part of a service pack for SQL Server 2000.

The burning question I cannot get answered is, have Microsoft actually stepped out of the BPM market for good?  We are told that Excel, Sharepoint and SQL Server provide BPM - I can't see, without Planning, how they can.  Short of hard-coded values, renewed Sharepoint/Excel hell, another vendor or bespoke planning solution, businesses can't set plans which have further reaching implications; effectively Planning's demise is also, effectively, shelving the Scorecard/KPI functionality from the M&A toolset too !  It will be interesting to see the new Monitoring & Analytics Marketing, will they still demo Strategy Maps and Scorecards, or will they now focus on Decomposition trees and Heat maps? Monitoring & Analytics may, in practice, just become Analytics..

I would have thought the cost of continuing to develop the product (even if it were a lemon, which Planning certainly wasn't)  is far less than the potential loss of revenue that Microsoft will face due not only to the loss of confidence by its customers (who are going to think twice about investing in any Microsoft product now, let alone a V1) but perhaps more significantly, the doors it opens to it's competitors who can offer a complete BI\BPM stack. 

Planning was foot in the customer's door for BI - once you put planning in, the customer had already bought the full BI stack, and in most cases, our customers were wowed by what they could now achieve. 

I suspect Cognos and SAP are still partying now!

PerformancePoint Server Planning SP1 - Clear Changes After Workflow Action

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.

PerformancePoint Suggestion box

Inspired by Adrian's post on the TechNet Planning Forum here's my list of 'I wants' for PPS-P.  I've cross posted this to the forum discussion too.  A few of these have been suggested before but there are some new ones in there too - In no particular order:

Application Migration Wizard
There are several methods for doing this currently but nothing slick.  I was hopeful that the new Data Migration Tool was the answer to this but alas, no.  It only migrates data (don't get me wrong this is useful) - what we really need is an Application Migration Tool that migrates everything, both initial 'go-live' migrations but also 'update release' migrations.  It should support making a copy of the application on the same server too (With a different name of course!).

Developer Edition
The PerformancePoint Server components can only be installed on Windows Server.  This is a tad annoying for development and demo purposes.  Currently we have a Windows Server built laptop for demos and sales activities.  For development, when connecting to the server is not always feasible we have a couple of pre-built PPS-P 'development' hard disk drives that we use to swap out with our non-server development boxes and manually migrate to the official servers periodically.  It would be great if, like SQL Server, there was a 'Developer Edition' that could be installed on XP and/or Vista.

Improved Support for Business Rule Re-Use
There are several ways that Business Rule re-use could be implemented but initially I'd like to see a 'formula' tab where, for example, you can define global formulas and expressions that can be selected (re-used) within various scope statements.  Taking this a step further, how cool would it be if the global formulas were actually functions that accepted parameters so you could produce a smaller set of generic formulas that can be re-used throughout the application.

Improved Rule Editing
The editor is a little clunky at present.  We need something more akin to SQL/MDX Query Editor in Management Studio.

Web-Based Data Entry
Whether via Excel Services or some other 'pivot table' style entry form.  A couple of customers are banking on Microsoft providing this in the future.  We at Adatis would love to know if they've actually started something like this in Redmond as we'd be up for developing something in-house if not !

Extensible PEL
Much like custom tasks can be developed in SSIS and stored procedures can be created for Analysis Services - It would add some real power if PEL keywords could be added via custom .NET assemblies that are registered with the PPS-P Server.  Not only can complex, vertical specific logic be encapsulated, intellectual property can be protected, opening up a third-party plug-in market.

Process Cycle Independent Data Entry Forms
It would really aid productivity if data entry forms can be created without the need to create a Process Management Cycle and associated assignment.

Application/Model Templates
Imagine a 'Retail Budgeting Template' or a 'Transportation Forecasting Template' and how that could potentially wipe weeks off an implementation.  This is a tall order due to the global nature of the product and the diversity of requirements within industry verticals so it might be better to make lower level component type templates that can be plugged in to a larger solution   e.g.  UK Tax and NI and Salary assumption models.

Protect Sensitive Data
Some data that is forecasted and budgeted is extremely sensitive, salary information for example.  Assignments should be marked as sensitive and as such should challenge the user opening it for some form of authentication.  Integrated security is not secure !

Spread Across Time
Not having the ability to spread across time or entering data at non-leaf level (as you can with the other dimensions) results in more models than you would ideally choose, increasing complexity.

Custom Spread Methods
Even and Ratio Spread don't satisfy every client spreading requirement.  If custom spread logic could be developed on the server and exposed through the Excel add-in for specific assignments every requirement could be fulfilled.

Unlimited Undo/Versioning
Without the ability to copy applications, proper versioning and unlimited undo/redo (across sessions) would help.  Adopting the Visual Sourcesafe approach of versioning, pinning, branching and merging would greatly assist with development / deployment / testing cycles.

Centralised validation rules that can prevent invalid data from being submitted to the server would help reduce approval/review workload.

Dimension Member Creation
Excel Add-In support for dimension member creation would assist greatly for certain budgeting/forecasting scenarios.  Workflow and data collisions would need to be considered otherwise the data integrity could be jeopardised.

Multiple Model Data Entry
Allowing an assignments to capture data for multiple models would provide the ability to simplify the implemented process and provide greater support for, amongst others, assumption model based what-ifs.

Submission Triggered Jobs
It's common to want to run a job once all data has been submitted.  Currently the job has to be run manually or scheduled.  Allowing jobs to be triggered via data submissions would provide greater automated support for implemented processes.

Excel Formula Auditing

A slightly off-topic post this one but it’s too good not to share. I say it’s too good, I only found out about this yesterday, maybe everyone knows about it already!

In Excel 2007 - apparently the functionality was introduced in Excel 2003! How out of date am I?! - there is a ‘Formula Auditing’ chunk on the Formulas Ribbon tab.


There are all sorts of goodies in here.

Trace Precedents allows you to find out the cells that make up the current cells data.

If you select a cell that is sourced from a formula and then click ‘Trace Precedents’ arrows appear indicating which cells the value is sourced from. Click ‘Trace Precedents’ again more arrows appear indicating where those source values are sourced from and so on and so forth. Below I’ve clicked ‘Trace Precedents’ four times to go back 4 levels from the selected cell J8.


You can go the other way too; Trace Dependents details all cells that that depend on that value. Selecting cell B3 and clicking ‘Trace Dependents’ four times traces all related dependencies.


It’s worth having a play with the other commands in the chunk too.  Show Formulas, Error Checking, Evaluate formula and the Watch Window, together with the tracing functionality could really help debugging or reverse engineering complex spreadsheets. I just wish I knew about it all before now !