+ Start a Discussion

Rolling your own 'analytic snapshots'

I'm curious if anyone out there has any feedback, suggestions, dire warnings, on creating what would be, in effect, a custom 'analytic snapshot'.   Bear with me...


We have a strong need to compare snapshots in time of our project management, but the formulas managers use to come up with their metrics are fairly complex (arcane, even). We're  calculating data that doesn't exist at the individual record level - and is at times more complex than even the (very useful) 'formula field' reporting offers.


Up until now -  our 'best guess' solution has been using the Office connector to pull data from many reports into Excel, then developing formulas and pivot tables to process the data up to the point where it means something (so we think :smileywink ) 


But this results in dozens of tabs (if you're trying to save monthly data snapshots) and requires a person to open the spreadsheet on the 1st of the month in order to get the snapshot we need.


My first solution at improving this mess was to write an apex controller that calculated all the metrics at init(), then called a visualforce page to format the data and  then save the page result  (rendered as an xls file)  as an attachment inside salesforce. This solves the batching problem, and removed the need for creating tabs and formulas in excel (Pivot tables remain)...but then I thought: 


If I already have the data I need calculated, why not save the data to an object in salesforce...then I can keep everything in Salesforce and gain reporting capability i didn't have before .The hope being I could use standard salesforce reports/charts to do exactly what I was doing with spreadsheet formulas, tabs & pivot tables.


So I'm looking at basically modelling my own 'analytic snapshot' SObejct (basically a 'wrapper Sobject' ). If this concept works, we could transfer the majority of our remaining 'spreadsheet hell' into salesforce.  Dare I dream? Am i nuts? Or just trying to be too cute?