Devil: You don’t need to set up a separate star schema database
Angel: Yes you do. We don’t want to interfere with the operational systems
Devil: It’s sooo much work
Angel: We want to create a simplified schema to benefit our end users
Devil: Phooeh. ETL is BORING
Devil: Just query the source system directly
Devil: What could be better than fresh data
Devil: C’mon the reports not that big
Devil: Everybody’s doing it
Intrepid BI Architect: sigh…
Case in point:
This is from an actual email exchange. Names have been changed to protect the innocent.
From: Brave and True BI Developer
Sent: Friday, March 05, 2010 9:33 AM
To: BI Technical Staff
Subject: SSRS Not Responding
Sent: Friday, March 05, 2010 9:33 AM
To: BI Technical Staff
Subject: SSRS Not Responding
Well, this is a new one. I’m on-site at [Dunder-Mifflin] today to do a quick fix on a report and the SSRS instance seems to be refusing to actually render a report. I click on a report that’s been untouched for months with an ultra simple SELECT behind it and the browser just spins with the “Reporting being generated” message in the middle of the browser.
From: Brave and True BI Colleague
Sent: Friday, March 05, 2010 9:34 AM
To: BI Technical Staff
Subject: RE: SSRS Not Responding
From: Brave and True BI Colleague
Sent: Friday, March 05, 2010 9:34 AM
To: BI Technical Staff
Subject: RE: SSRS Not Responding
Check SQL Profiler as you run the report, verify that it is actually reaching the database (with the correct user information, etc.)
From: Brave and True BI Developer
Sent: Friday, March 05, 2010 10:00 AM
To: BI Technical Staff
Subject: RE: SSRS Not Responding
From: Brave and True BI Developer
Sent: Friday, March 05, 2010 10:00 AM
To: BI Technical Staff
Subject: RE: SSRS Not Responding
Got it. There was a transaction open on a table that’s part of a filter on most of the reports. That’s all it was.
______________________________
[My emphasis]
This is why we go to the trouble of setting up an entirely separate system. There should never be a “transaction open on a table that’s part of a filter on most of the reports.”
There are times even now when the voices of temptation ring really loud. Usually this occurs in the form of a client or colleague looking at me like I’ve got two heads when I tell them that we need to spend time building a separate redundant database and ETL to replace a currently running (but poorly performing or problematic) report.