Source for Accumulating Snapshot Fact table

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

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.

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.