Devils and Angels of BI Architecture

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

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

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

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.

So to all the intrepid BI Architects out there: fight the good fight!