Today was a big day for us in our little BI/DW team. We now have a full year of dimensionally modelled admissions data sitting in the warehouse. We capture every current admission period application in a daily transaction fact table and perform a Kimball periodic snapshot of this same table every day. So we have over 1.6 million rows of history and 12 dimensions to report on them with.
This is also a significant day because we can now retire the last of our relational database models that the ‘old’ admissions reporting was using. This approach worked ok but was severely limited in terms of providing analysis of the detail behind the summaries we produced.
As you’ll know, I’m not a big advocate of OLAP and another example of an optimised fact table is a nightly ETL step that hammers through the 1.6 million rows and picks up those related to all current periods and any prior periods as they stood on this day previously. This amounts to 17,000 rows at present and allows very rapid ‘then and now’ analysis via a greatly trimmed down fact that retains the atomic level of detail and all the dimensionality. The ETL step takes about a minute and gives us near-instant analysis as a result.
Of course this technique doesn’t allow time-series reporting in a hurry (you’d still have to go off the big fact for that) but it represents a variation on the aggregates approach I’d talked about previously.
By the way, I’ve noticed the time between my posts is growing - its been a challenging time for us just recently as so much is happening. I guess that is a good sign, there is a growing demand for BI outputs and that means more demands on our time to make those a reality.
