>SSC-Insane, No-one like me queries any OLTP live servers. That's not what we are talking about.
>As you know, the business guru is given read access to a daily snapshot server. From which I make my Excel datamart.
>100% of the business report customers want output in either Excel or pdf (easy save-as from Excel) or Word (again, easy from VBA).
Its my fault for assuming you're querying OLTP servers, my bad!
Do your reports still go out during your sick day? If you have a backup process then I'm good with that too.
I'm personally a fan of SSRS, it does all the output functions for excel and pdf, but you can schedule them also, no need for a button in that case. This doesn't mean that excel has no worth, far from it! As far as word is concerned, I remember producing some sort of reports with word and vba macros but often they were meant to be run by the end user consumer.
>So it's much more efficient to keep the datamart in Excel for any table less than a few hundred thousand records.
I'll revisit that in a second.
>Data warehouses do not have quality control. I have quality control charts on all my datamart tables. Going back years.
Thats probably not a useful generalization. If I were to extrapolate this to its logical conclusion, you're saying that your datamarts are golden but OLTP is crap. We'd have to figure out this before even touching on data warehouses. By applying quality control ONLY to your datamarts, you are creating a duplicate and inconsistant view of data which now means you need a third set of data, the "mismatches" between your view and the rest of the company.
Don't get me wrong, I've tracked OLTP malfunctions before, some transactions are just going to happen that are out of the norm, and documenting these probably make sense. Oftentimes the datamarts and warehouses I've used and produced are specifically MEANT for quality control, I have one today (the 10 minute refresher mentioned below) that is used for directing corrective action in the OLTP, practically the definition for data quality control. For process quality control, you should be able to apply this to any process regardless of what tool is used to implement it. We shouldn't infer that SQL isn't subject to quality control because its a tool like anything else, produce incorrect processes using any tool will result in a bad time for everybody, I guarantee it!
>Data warehouses are a day behind in their data and months behind in reporting capability. They are a mausoleum for data.
That doesn't really make much sense either. I get the "day behind" thing, but on the other hand, it isn't impossible to vary the frequency of different processes. The datamart I use frequently, refreshes every 10 minutes. On the other hand, months behind in reporting capability is probably just not a useful generalization.
For the efficiencies of excel datamarts, I don't really think thats a rule set in stone. There are so many attributes to explore with business processes that just comparing a blanket "efficiency" metric is probably not that useful, heck that actual "efficiency" metric is probably going to vary according to who's doing the measuring, its really that subjective.
Heck if someone is a poor slob like me having to go troubleshoot a vba procedure that is now malfunctioning after the original author is long gone, you are going to get a really vastly different assessment of that particular "efficiency."
Just contributing contrasting viewpoints here, let me know more if I'm mischaracterising your talking points.