Business Intelligence: Data Marts
Life finally gets exciting. We’ve loaded our data through the ETL into an ODS. We’ve enriched the data into a usable form for aggregated, multidimensional reporting. Now we just need to create the facilities for that multidimensional reporting. This is going to be a challenge to explain in layman’s terms, but I’ll give it a shot.
We’re all pretty used to relational databases (RDBMS) by now. These standard table structures allow us to easily and efficiently store data. However, the structure of RDBMS does not facilitate multidimensional reporting very well. For that, we need a star schema. What the heck is a star schema you ask? Star schemas are basically a way of organizing data into what we call facts and dimensions. Let’s take a couple of real world examples.
- Let’s say we want to look into Joe’s performance management. We’ll call Joe’s performance score the “fact.” This fact is nice to have, but to really see what’s hiding behind it, we’d want to add a time dimension so that we could drill through and understand the trends in Joe’s performance.
- To make this a step more complex, let’s say we wanted to go a level higher and view everyone’s performance score correlated with time and job code. Now, the performance score is still the fact, but the dimensions are now job code and time. Note that we have aggregated the data for the organization. We’d probably want to go one step further and ad the organizational unit as a dimension so we could compare across businesses and departments as well. Let’s take this out one level further. Now that we have organizational unit, that might actually be a set of data fields. We might want to be able to look at performance over time by org unit, and further break that into location, manager ID, state, or country. You can see how this quickly gets fairly convoluted and you’d never want to write an ad hoc report linking all these tables.
Business Intelligence: Cubes
So if RDBMS tables are not appropriate for analytical multidimensional reporting, what’s better? If RDBMS tables are 2 dimensional, then I guess what we want is a cube – literally called a cube. In general, a cube does not actually exist anywhere. The rules and structure of a cube generally exist either in the data warehouse or in the end user reporting tool (which is where I prefer it). This cube is simply a virtual representation of the data mart that allows easier visualization and reporting. But really, everything you needed was already in the data mart.
So here’s what we’ve discussed:
- The ETL pulls data from multiple sources into the ODS
- The ODS holds raw data for possible operational reporting and data extract
- The ETL pulls data from the ODS into the enriched data warehouse (we now have a data warehouse)
- The Data Marts create star schemas that organize data into meaningful structures which will later facilitate multidimensional reporting
- The reporting tool creates cubes which refer back to the data marts and create those beautiful analytical reports!!!
