Wavesmash has posted a series of tips and tricks shared at a train the trainer event that took place in Denver recently. As suggested, most of the 'nuggets' are from the attendees themselves rather than the course material so, on the plus side there are some real experienced based tips however, I wouldn't treat all as official tips and tricks - I certainly frowned at a couple but that could be due to the explanation rather than the intent.
There's certainly some goodness, and one that made me smile: Regular Refresh of model = happy modeler
http://performancepointing.blogspot.com/2008/08/train-trainer-helpful-tricks.html
http://performancepointing.blogspot.com/2008/08/tips-from-train-trainer-sessions-day-2.html
http://performancepointing.blogspot.com/2008/08/tips-from-train-trainer-sessions-day-3.html
http://performancepointing.blogspot.com/2008/08/tips-from-train-trainer-sessions-day-3_26.html
http://performancepointing.blogspot.com/2008/08/tips-from-train-trainer-sessions-day-4.html
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.
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 !