"Upgraded" dimensional model for OLAP / analytics

Hello everyone,

I'd be very grateful to receive any suggestions and tips for my issue:

I am working with what you could describe a "legacy" data warehouse. Our data warehouse was put together years ago without really planning it's structure, and in addition changes to the source systems have resulted in lots of ad hoc -solutions. As a result, our data warehouse is far from ideal or the usual tenets of DWs. The data model is based more on the way data is managed in the main source system rather than on substance or end-user point of view. For example, there is no time dimension at all, instead any time series comparisons and other time-based slicing and dicing is done on the report coding and based on the datetime columns of the individual fact rows. In addition, the current DW dimensions rely quite heavily on operational codes that are not accompanied with descriptive columns, so it's quite awkward for a new user to get in terms with it. And so on.

I have been proposing rebuilding the data warehouse to correspond more to a substance-driven dimensional model. This obviously would require considerable resources and would make the reports that are now in place obsolete, so it seems that it's not going to happen, not at least in the near future. Instead, we have to seek ways to improve the user-experience with the current DW. One of the things I've been proposing is to develop analytics tools/OLAP cubes, so that power users would get better interface to the data and be able to make their own reports and ad hoc -queris (at the moment all of our reporting is done using pre-defined reports, and the only way to answer to new reporting needs is to code new reports, a slow process given the poor state of the DW itself).

And here is finally my question: Would we be able to develop some kind of "improved" data mart to feed our cubes, i.e. work some of the tables and use some kind of "mini-etl process" to develop dimensional subsets of the DW? For example, an obvious need would be to implement a time dimension for the cubes to facilitate easier time analytics. I would also like to change the structure of some of the dimensions, splitting dimensions that really do not belong together, as well as add descriptive columns to improve the understandability of the dimensions. How difficult it is to implement this kind of restructuring to the current tableswhen you develop databases for the cubes? For me, it would seem a plausible and quick-win way to improve the user access to the data, but is there some technical caveats in this kind of solution that I cannot see?

Thanks for any insights!

Seems like a plausible approach. The cubes offer an abstraction layer to the underlying data warehouse tables. Without management buy in though, you are creating extra work for yourself. I would aim for a standalone small project to build with the new design. With positive business feedback, the DW redesign might be an easier sell.

If management buy-in is stiff for the ideal, if you could squeeze in a small prototype that shows what you're talking about in terms of how the descriptive attributes provide for a better user/analytical experience (maybe on a smaller subset of your data), then you may be able to open a door for more resources. If not, then you know it's a deal-with-it type of scenario.

The mini-ETL process you're speaking makes me think of building some of this into views that the OLAP program would read to build out the dimensions. Not as ideal as pushing all of that into an ETL-only process, but could work if you're strapped for resources.

If you do go down the road of building some of this ETL-type work into the OLAP views, find a way to document it extensively, external to the OLAP program, somewhere. For yourself and the next person after you, as well as any DBAs who may not be privy to the OLAP side of things.

Brad