No Surrogate keys

Hi All,
what about having dim having only natural keys and fact too and not maintaining any history so that DWH can be made just in time thing i.e at the end of the day or during the day making data available for reporting purpose. Anybody has done this before?

Yep, it's called the application database. I've never done anything like this for a data warehouse.

guptavishwas wrote:Hi All,
what about having dim having only natural keys and fact too and not maintaining any history so that DWH can be made just in time thing i.e at the end of the day or during the day making data available for reporting purpose. Anybody has done this before?

A lot of people did it that way. Based on those experiences, they learned why you should use surrogate keys.

As far as data latency goes, it has nothing to do with surrogate keys. It has to do with purpose. A data warehouse, dimensional or otherwise, is primarily designed to address strategic and tactical requirements (longer time horizon). Operational requirements are usually handled by an ODS, which is most often in a more normalized form and tightly scoped (shorter data retention, limited subject matter). They often mimic data structures within the operational system. ODS's are typically used by operational departments and customer service to look at specific things (an order, orders for customer x, etc...) not to do mass analysis of a year's worth of activity.

Thanks for insight.

What I see is most of the BI tools are made to work on dimensional modelling so if we want to use those advanced tools we need to have dimensional modelling done. If I remain with ODS it is difficult to work with web of tables (OLTP). For example I work in Cognos, If I try bring those OLTP tables then it would be all mess and would be difficult for Cognos to build query on the run as there will be multiple paths to satisfy the same request.

I may try to create different different small small schema to get rid of the problem mentioned above but it will be again big mess.

We really don't have strong tools to report from OLTP.