I often write custom reports to be able to analyze huge data from a SharePoint farm. I can ansewr questions such as:
– Number of documents updated per user per month over time.
– What are the metadata fields and values in use, and their frequency by department.
I typically export as a CSV, using pipe delimited format (to avoid misinterpreting the ubiquitous comma), and import into a spreadsheet.
What happens when the dataset is too large for Excel? The data I analyze often pushes the limits of Excel or my machine. In that case, I’ll import into SQL Server. To make it repeatable, I’ll create a import process, that drops and recreates the target table, defines the fields, and how to handle errors for each, then export as an SSIS DTSX package.
To run a DTSX package, I’ll import into Business Intelligence Studio as a package and run from there.
Once in SQL Server, one can handle larger datasets, with all the tools and techniques of SQL for dealing with really big data.
The data can be exposed as an SSRS report.