No Surrogate keys

View previous topic View next topic Go down

No Surrogate keys

Post  Vishy on Tue Feb 28, 2012 1:27 pm

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?

Vishy

Posts : 75
Join date : 2012-02-28
Location : India

View user profile

Back to top Go down

Re: No Surrogate keys

Post  BoxesAndLines on Tue Feb 28, 2012 1:33 pm

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

Posts : 1212
Join date : 2009-02-03
Location : USA

View user profile

Back to top Go down

Re: No Surrogate keys

Post  ngalemmo on Tue Feb 28, 2012 4:56 pm

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.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: No Surrogate keys

Post  Vishy on Wed Feb 29, 2012 3:00 am

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.

Vishy

Posts : 75
Join date : 2012-02-28
Location : India

View user profile

Back to top Go down

Re: No Surrogate keys

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum