One of the surest ways, tried and true, to achieve BI happiness, is to do it in a bar. Especially with a nice drink, in the evening, and a good companion. Don’t believe me? Just try it.
Well, that is exactly what I was doing the other night. I was in a bar with a friend, and after a couple of drinks, we began discussing, what else, BI. I was trying to explain what is the BI space, what are the main components of the architecture of a typical BI implementation, and came up with this napkin drawing (disclaimer: this is not the actual napkin used in the bar, the original didn’t survive the night).
On the left side, you have the various source systems. These are the operational software packages your business runs on. Your ERP, CRM, e-Commence, the numerous Excel spreadsheet you could not close your books without, etc. These all get loaded up into the Data Warehouse. The Data warehouse can be called a reporting database, an mart, an ODS, or many other things, the concept is typically the same, you rather do your reporting off a database that is separate from your operation one, so it does not impact performance, and you can combine data from different systems as well as organize it so its suited for reporting. The process of moving the data from the source systems and reorganizing it is called ETL: Extract Transform and Load. On top of your DW sits your BI vendor meta data layer that describes the database and allows isolation of reports from the physical DB layer. All major BI vendors have such a layer and there are many benefits to this architecture, not only shielding your reports from changing databases, as well as ensuring consistency and allowing ad-hoc reporting capabilities. Finally, out of your meta data layer, you get your plethora of reporting capabilities, reports, dashboards, BI apps, web services, etc, etc, etc… And that is it in a nut shell. You can add a couple more circles for data governance and process encompassing the whole thing, and you pretty much got an enterprise BI system in place.
Of course each and every single component on this diagram is a complete eco systems with a huge variety of software/hardware options to choose from, complex implementations and so forth, but I’ll leave that for another napkin…