3 ms·

I never liked OLAP cubes because they discouraged ad-hoc data exploration since if there wasn't already a cube that modeled the data a certain way you couldn't

I never liked OLAP cubes because they discouraged ad-hoc data exploration since if there wasn't already a cube that modeled the data a certain way you couldn't do it. You had to develop it, or hope a DBA had time to develop it sometime in the next two months. They were fine if you already knew the questions you needed answered, but they probably contributed to some companies focussing on the wrong things because they couldn't easily explore data for new insights. I much prefer the current setup I work under: We have an ODS with a whole bunch of denormalized views for the most commonly needed data built on incremental updates to a clone of the production database. If one of the views doesn't have the data required or it's not structured as required I can simply bring one of the cloned prod tables into my SQL query. If I find myself repeating that process for the same data often, I create another view for it, but I always have all of the data needed for any analysis or exploration. If I need up-to-the-second data I can write the boilerplate using ODS and cloned prod, then join to only live prod data required. Things like the added complexity of a constructing a pivot table in SQL are a non issue. Report writing software handles that and with a lot more flexibility to the end user for interaction.

speby 5y ago
This is why sometimes you end up with BOTH a traditional ETL data warehouse, for common, structured reporting and analytics and also an unstructured data lake(s) that pull raw data in from many sources to allow for ad-hoc exploration. But like others have said here, eventually you might find a novel pattern or something worth tracking on a regular, go-forward basis, and will want it to built into the data warehouse, for all of the various benefits a data warehouse provides for everything else it does so well.

speby 5y ago

This is why sometimes you end up with BOTH a traditional ETL data warehouse, for common, structured reporting and analytics and also an unstructured data lake(s) that pull raw data in from many sources to allow for ad-hoc exploration. But like others have said here, eventually you might find a novel pattern or something worth tracking on a regular, go-forward basis, and will want it to built into the data warehouse, for all of the various benefits a data warehouse provides for everything else it does so well.