Is a Dimensional Model Suitable for all Types of Reporting (Even Operational)?
June 20, 2005
Mark Rittman

Next week I'm off to see a client in Ireland, who's using us to help put together the design for their data warehouse. They're looking to use tools such as Business Objects, Discoverer and so forth, there's a certain element of OLAP-style decision support reporting required, but the vast bulk of the reporting they're going to need to do is simple, tabular reporting. Their current tool is Cognos Impromptu, running against a normalised set of OLTP-style tables, and they want the warehouse to take the load off of their existing systems and satisfy all of their reporting needs, both operational and analytic/forecasting.

The question is, should the warehouse I put together for them be dimensional in design? Or should it be a normalised, "Inmon"-style warehouse? Now normally, I would stick to my guns when putting a dimensional data warehouse together, as I'm of the opinion that even though the warehouse is designed around facts and dimensions, it can still meet all reporting needs - there's not something special about dimensional designs that leave them unable to meet certain types of operational reporting requirements. Sure, you've got denormalized data, and you might need to set up a new fact table to provide a new set of reports where the data doesn't currently exist, but if you're building a reporting system, regardless of whether most of the reports are operational (and not OLAP, or crosstabs) - a dimensional data model will still fit the bill. Or does it?

Now of course you could say that the operational reporting could be met from the ODS. Certainly if the warehouse was only loaded weekly, and the ODS was loaded daily or more frequently, I'd agree with you. But if the ODS (which I usually consider as being the data integration layer, not the reporting layer) is as up-to-date as the dimensional star schemas fed from it, why not do the reporting off of the star schema?

So, what's your opinion? If the bulk of your reporting is operational (and not OLAP), and taken as a given that you're actually going to use a warehouse, do you still do it all from the dimensional star schema, or do you in these circumstances keep the dimensional model for the instances where you do require OLAP reports, and use the ODS to meet the operational reporting requirements? Me, I'm tending towards the latter, but my "heart" still says that the dimensional model can meet all the reporting needs, regardless of whether the reports are tabular or crosstabs.