How do I implement a real time *financial* statistics engine from SQL server data for dashboard display?

Question

We currently use excel automation to calculate time series statistics and store the results in our SQL Server 2008 database for easy display/sorting/etc. later.

I'm currently redesigning the home screen of our app to present the most important information (as identified by the team using the app) in dashboard form. I'd like the display to be realtime. Data isn't added often but does requires some serious data crunching.

Once the UX is designed where do I go for implementation?
Server side, client side? F# looks like a great language for this type of data crunching and I'm willing to hire a developer but have to think there are other tools we can employ before going this route.

Thanks for your suggestions.

Answer 1

Right now, the way to show dashboards and key performance indicators for your data marts and warehouses is Performance Point Server. That's getting merged into MOSS next year, however. The number of players in this market is shrinking by the day ... MS acquired Proclairty, SAP grabbed OutlookSoft and Oracle picked up Hyperion. Frankly I would look for a MS partner (I'm partial to Avanade) to help define and realize your BI strategy.

Answer 2

Server-side, same as you have now with results stored in the database, but replace the Excel automation with CLR procedures/functions, or even vanilla SQL procs for the simple stuff.

CLR assemblies written in F# might make a lot of sense.

Also, by doing it server-side, when it does need to be real-time, you won't need to push as much data between tiers. The CLR assemblies will have access to as much atomic data as necessary, and only have to push a greatly reduced output to the client.

XML format might be appropriate for the interchange, rather than traditional scalar parameters and tabular result sets. eg, parameters expressed as XML data, return output likewise.

Answer 3

For a web-based application, assuming you're prepared to spend some money to buy tools and save yourself money, the slickest-looking solution I've come across is FusionCharts.

http://www.fusioncharts.com/LiveDemos.asp

I've had some involvement with Flex, and have been impressed. I haven't spoken to anybody who has developed in FusionCharts, but I have to say that they look damn fine!

Answer 4

Really depends on the nature of the data and how the statistics are calculated, in either case the two things you need to do:

(a) trigger an event when any new data is added or deleted

(b) Don't recalculate, this could be very costly, instead only do partial calculations, by calculating the difference between the old data and new data.

Here is a silly example:

Record A = 2

Record B = 1

Record C = 1

Record D = 5

now lets say your dashboard is calculating the mean, in this simple example we would store (perhaps in the same database) some of the partial calculations results: eg-

NumRecords = 4 RecSum = 9

Mean = [RecSum]/[NumRecords]

So when data is changed there are only three things that could be done (per transaction):

record deleted (case 1), record added (case 2), record changed (case 3):

case 1 (record A is deleted) thus:

NumRecords-1 > 3 RecSum-{Record A Value} > 9-2 =7

Mean = [7]/[3]

And you can do similar "Partial" calculations for other cases, the idea being is that you don't throw away your last solution, and you calculate around the differences. of course on more complex statistics this would be more involved.

But as you can see, if you imagine you had a million records, then not needing to pull all those records again in order to completely recalculate would allow it to be "realtime"