Business Intelligence and Analysis Services in the "Real World"

A reader sent me an email this weekend:

I wonder if I could as your advice as a BI / Data warehousing specialist.I have been studying Analysis Services recently having read a couple of books Which step me through the process of building the cubes etc but as I don’t come From a DB background one thing is not clear to me is how does one determine that They need to use BI / Analysis Services etc in the real world? As you,  I am a .NET developer with a background of building thick client apps and am  Familiar with creating layered architectures etc building on frameworks like NHibernate  Etc to abstract out the DB stuff into my more familiar object world.   My question how does one Generally interface with this Data Warehousing / Analysis Services stuff in the real world? I am looking for answers from people who have used these technologies in anger and not Like me from canned text book examples scenarios etc. Thanks for your time it is would be appreciated.

And I wrote an response, but after reading the response I figured I could post it up here as it is pretty general.

Basically what I have seen is this. You make your application (either web or windows) that saves transactional data, or logs, or something like that. End users usually want reports off of that data. At first, developers will report directly off that data (OLTP database). It becomes slow, and unusable after tons of data is there, so then developers tweak timeouts, things like that. Then the next step is custom summarizations into other tables, and then archiving off transactional data. Usually, because developers don’t know about data warehousing/BI stuff, all this stuff is custom up to this point.

 

Eventually, someone realizes that, “hey, there is a way to do this better”, which involves a structured ETL either using stored procs or SSIS or something similar. Also, instead of just ad-hoc or custom summarization tables, a well designed data warehouse (OLAP) database is key.
Now, from there, you can write your reports off your OLAP database, which is OK because the data is summarized, scrubbed, etc. But you really give it an adrenaline boost when you create a cube off that data warehouse OLAP db, it takes care of the summarization, the relationships, all that. You put the reporting in the hands of your end users (excel 2007 for example) – let them pivot and slice and dice the data, its all ready and set for them to do it, with really nothing you have to do on your end except make sure the cube is processed regularly off the DW.

You are basically abstracting your OLTP data up two levels… In all reality you should be able to query your OLTP data for a time frame and get say revenue, and then query the OLAP and the CUBE and get the same results. Now, with your cube, you can still access data from it in your .NET apps using AMO, which is cool as well, or you can write Reporting Services reports directly off the cube as well, makes it a lot easier than writing custom .NET reports.
So, interfacing with your Data Warehouse, the best options to get data in is usually SSIS packages or stored procedures, no .NET coding really. To get data out, you would probably want to use Reporting Services, or you can query it with SqlClient in .NET apps like you would a regular OLTP database.
The cube, you can get data out using AMO objects in .NET, you can query using MDX, XMLA, etc in management studio, or you can write reporting services reports, but the best client is Excel 2007, its built to be tied to SSAS 2005, so it really works out well.

One thing about all the technologies (SSIS, SSRS, SSAS, AMO, etc) is that there really isn’t a lot online as far as examples and documentation, or it is scattered. It is getting better though. Most of the stuff I do I just have to figure out on my own, through trial and error, but it is OK.

Maybe not the cover all response but it kind of covers what I have seen in the past, from my own experiences, and others.