Source for Accumulating Snapshot Fact table

View previous topic View next topic Go down

Source for Accumulating Snapshot Fact table

Post  kbarrett on Tue Jul 21, 2009 12:23 pm

I have a question about accumulating snapshots and I was hoping someone could shed some light on the subject.

Does Kimball give any guidance anywhere (books, online, etc.) as to whether one should build the supporting transactional fact tables that relate to the accumulating snapshot before building the snapshot?

We are looking at building a snapshot fact table for the entire insurance policy lifecycle (from initial submission to quote to binding/issuing the policy to first claim (if any) to final audit, etc. with all of the associated dates and lag metrics.

In the case of insurance, we would have a transaction fact table for submissions (one record per submission), one for quotes (one record per quote version), one for policies (one record per rating line item), etc. as well as the associated dimensions, and we would simply build the snapshot from these underlying fact and dimension tables.

The issue that I'm concerned about is the notion of building the ETL to support the snapshot. My gut tells me that I should build the supporting transaction fact tables/dimensions first (at least the bare minimum to support the snapshot) and that the ETL for the snapshot really should come from the other fact and dimension tables not directly from the source systems.

Do you get what I'm trying to ask? I'm not sure if I'm explaining it well.

Thanks in advance.

kbarrett

Posts : 2
Join date : 2009-02-03

View user profile

Back to top Go down

Re: Source for Accumulating Snapshot Fact table

Post  ngalemmo on Wed Jul 22, 2009 4:50 pm

kbarrett wrote:Does Kimball give any guidance anywhere (books, online, etc.) as to whether one should build the supporting transactional fact tables that relate to the accumulating snapshot before building the snapshot?

Yes... it is fundimental to building a data warehouse. It is also one of the most common misunderstandings is creating a dimensional data warehouse. The use of the term 'data mart' is the problem... it's interpretation is far too broad. So, when Dr. Kimball wrote 'a data warehouse is a collection of its data marts' he was referring to atomic data marts... that is to say, fact tables at the lowest level of detail attainable from the source systems. Without a foundation of atomic data marts, you cannot sucessfully integrate data across the enterprise. The creation of aggregates (summaries, snapshots, fact integration) is a secondary effort after the supporting atomic data marts have been created.
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: Source for Accumulating Snapshot Fact table

Post  BoxesAndLines on Thu Jul 23, 2009 9:43 am

I think most people build the transaction fact table first. Once they realize how difficult it is to get lifecycle information out of the transaction fact table, they end up building an accumulating snapshot. Each fact table is ideally suited to answering different questions.
avatar
BoxesAndLines

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

View user profile

Back to top Go down

RE:Source for Accumulating Snapshot Fact table

Post  Prasanna on Wed Oct 21, 2009 12:24 am

To build a datawarehouse whether it is transactional/operational or snapshot/periodic the prime important is to list out "what do we want?" all such fields fall under facts. Next is to check what granularity of detail data do we have and check that if we can achieve the "what" list with the available granularity.Third is to "How to get 'What'?" Thus dimensions are pulled.

Kimballs approach of gathering all possible datamarts is the best suited for your case.You have to model the best suited DW first and then then a meaningful ETL for it. ETL can be just built once you finalaize all facts/dimensions for your requirement and keep the skeletal part (dimension Model) ready before you look into ETL.

Prasanna

Posts : 6
Join date : 2009-10-20

View user profile

Back to top Go down

Re: Source for Accumulating Snapshot Fact table

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