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!




This entry was posted in Business Intelligence, Modeling/Architecture, SQL Server. Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *